Wilcard search using table

[raytackettsells]

Is there a way to create a query which do a wildcard search of a table based on the data on a form? I am using Longitude/Latitude.



My form has 39.9461061 and I want to have a query reference this number returning on the values in a table using a wildcard search:

like the first two digits and 3 digits after the decimal. So Like 39.946 based off the form. The longitude and can change based on what is entered into the form. Hope this makes sense.

Thanks,

Raymond

[June7]

Yes, you can, try:

SELECT * FROM tablename WHERE fieldname Like Forms!formname!textboxname & "*";

but why not just

SELECT * FROM tablename WHERE fieldname >= Forms!formname!textboxname;

[Dal Jeanis]

Given these are numbers, you may get better results with a query like

WHERE [Latitude] BETWEEN Me.MyField - .001 AND Me.MyField + .001

[oemar00]

Yes this is a better idea, however BETWEEN is an inclusive test the problem might come if the number is in the border such as when the rest of the field is all 9’s or all 0’s.
Would the following code more accurate:

WHERE [Latitude] > Me.MyField – 0.001 AND [Latitude] < Me.MyField + 0.001

This test maintain the exclusiveness of the values being tested.

[Dal Jeanis]

Sure, you could use that. Your original problem didn't seem particularly exact, since 39.9461061 gets you -.0001061 to +0008938 from your original number. If you want +/- .0005, you would get an exact delta on either side.

I also noticed another problem, if you need a worldwide "general" solution. You'll need a more complicated condition to account for longitudes near +180 and latitudes near +/-90. I would suggest you code a public distance calculation function, then use something like

WHERE [Latitude] Between ,Me.MyField) < .001

Or, if your original request was correct, you could use

WHERE [Latitude] BETWEEN (Int(Me.MyField*1000))/1000 AND (999+Int(Me.MyField*1000))/1000

[oemar00]

Dear Dal Jeanis,
I just trying to prevent a loophole in the border but your response assume that the original person "raytackettsells" is the one who reply to your improvement, sorry for the confusion.

Agreed there could be some more loopholes in the border where we could install more walls.

[Dal Jeanis]

Ah, yes, my mistake. Given that the OP was entering latitude 39.94, I'm assuming OP's on the U.S.A Eastern seaboard or Midwest (for instance, Philadelphia, PA or Springfield, OH). As such, he may not need to worry about international date line issues or geographic north and south poles. On the other hand, Southern Spain and Italy are on the 40th parallel as well, so no guarantees.