Databases
StackOverflow2013
Postsdbo
POMicrosoft Access - SQL-generated field evaluated as Text instead of Single data type
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMicrosoft Access - SQL-generated field evaluated as Text instead of Single data type
    primarykey
    data
    text
    <p>I have a SQL statement (saved as "LocationSearch" in Access) that calculates distance between two points and returns the "Distance" as a generated field.</p> <pre><code>SELECT Int((3963*(Atn(-(Sin(LATITUDE/57.2958)* Sin([@lat]/57.2958)+Cos(LATITUDE/57.2958)*Cos([@lat]/57.2958)* Cos([@lng]/57.2958-LONGITUDE/57.2958))/Sqr(-(Sin(LATITUDE/57.2958)* Sin([@lat]/57.2958)+Cos(LATITUDE/57.2958)*Cos([@lat]/57.2958)* Cos([@lng]/57.2958-LONGITUDE/57.2958))*(Sin(LATITUDE/57.2958)* Sin([@lat]/57.2958)+Cos(LATITUDE/57.2958)*Cos([@lat]/57.2958)* Cos([@lng]/57.2958-LONGITUDE/57.2958))+1))+2*Atn(1)))*10)/10 AS Distance, * FROM Locations ORDER BY (3963*(Atn(-(Sin(LATITUDE/57.2958)* Sin([@lat]/57.2958)+Cos(LATITUDE/57.2958)*Cos([@lat]/57.2958)* Cos([@lng]/57.2958-LONGITUDE/57.2958))/Sqr(-(Sin(LATITUDE/57.2958)* Sin([@lat]/57.2958)+Cos(LATITUDE/57.2958)*Cos([@lat]/57.2958)* Cos([@lng]/57.2958-LONGITUDE/57.2958))*(Sin(LATITUDE/57.2958)* Sin([@lat]/57.2958)+Cos(LATITUDE/57.2958)*Cos([@lat]/57.2958)* Cos([@lng]/57.2958-LONGITUDE/57.2958))+1))+2*Atn(1))); </code></pre> <p>All the nasty math code you see is what calculates the distance (in miles) in the SQL statement using Latitude and Longitude coordinates.</p> <p>However, the problem is that the Distance field that is generated by the SQL statement seems to be returned as a string. If I then add SQL code that asks for locations between a distance of 0 and 45 miles, it returns ANY Distance value that starts between "0" and "45". This includes a location with a distance of "1017" miles. Apparently, the Distance field is a text field, not a number field. So I can't use the "BETWEEN" statement. I also can't evaluate using "&lt;" and ">" because it has the same problem.</p> <p>I saved the SQL query above as a saved query called "LocationSearch". This way I can run secondary queries against it, like this:</p> <pre><code>SELECT * FROM LocationSearch WHERE Distance &lt; @MaxDistance </code></pre> <p>Access will ask for the @lat, @long and @MaxDistance parameters, then the locations will be returned in a recordset, ordered by distance. However, the problem that occurs is when I enter a MaxDistance of 45. With a table containing locations on the West Coast of the US, and a @lat of 47 and a @long of -122 (near Seattle), Access returns the following:</p> <p><img src="https://i.stack.imgur.com/tgBwH.jpg" alt="enter image description here"></p> <p>Notice also that the "Distance" field is right-formatted so it appears to be a numeric field, yet for some reason the query returns a location in San Diego, which is 1,017 miles away. My guess is that it was evaluating the Distance field as a text field, and in an ASCII comparison, I believe that "1017" lies between "0" and "45".</p> <p>One other thing: I'm using ASP 3.0 (classic) to access this query using JET OLEDB 4.0.</p> <p>Anyone know how to define the Distance field as a number?</p> <p>Thanks!</p> <p><strong>--- EDIT ---</strong></p> <p>Using HansUp's idea from his answer below, I tried this query to force Access to consider the Distance field as a Single precision number:</p> <pre><code>SELECT * FROM LocationSearch WHERE CSng(Distance) &lt; @MaxDistance </code></pre> <p>Even this returned the exact same results as before which included the location in San Diego, 1017 miles away.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USj0k
    1. USDr. Cool
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COmy advice: leave the imperial, and go to metric!
      singulars
      1. POMicrosoft Access - SQL-generated field evaluated as Text instead of Single data type
      1. USPhilippe Grondier
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload