Multi field criteria

[sharonir22]

Hi



I have a table that has location coordinates in 2 different fields - X and Y.

I have built a function called Distance which accepts 2 location's data (4 variables) and returns the distance between them.

I would like to design a query that will be called from a form that shows all the records in which the distance between them and the form entered location is smaller than 5.

The problem is i don't know how to set a criteria using 2 fields.

HELP

[pbaldy]

Sounds like you can call the function from the query, passing 2 variables from the data and 2 from the form, and put a criteria on that field.

[June7]

What two fields? You have only one parameter - distance. How do you calculate distance between locations in table? Show sample data. I think what you want will be complicated. This is handled by complex algorithms in specialty applications like ESRI ArcGIS mapping and Google Earth. Replicating this in Access will probably require a query that self-joins the table so every location is associated with every other location (Cartesian relationship) then calculate distance for each of these pairings and apply distance parameter to that field.

[sharonir22]

A location is defined by 2 parmeters. X as horizontal location and Y as diagonal location. Both numbers with 6 digits. the function uses pythagorean theorem to calculate the distance between them.

[sharonir22]

I have a form used to enter a location (x and y) and I want a query that returns all the records that are in a 5 meter radius of it.

[June7]

I edited my previous reply as you were reading. Review again.

Wait, I misread the OP. I think Paul has it right.

[sharonir22]

If I have a field in which the location data is stored as xxxxxx/yyyyyy is there a way to seperate the x and the y just to use the function?

[June7]

I thought you said the x and y are in different fields - they should be.

String manipulation functions can parse the data.


In query:

For the x: Val([field name])
or Left([field name], 6)
or Left([field name], InStr([field name], "/")-1)

For the y: Mid([field name], InStr([field name], "/")+1)
or Mid([field name], 8)
or Right([field name], 6)
or Right([field name], InStrRev([field name], "/")-1)

In VBA can use Split() and an array variable.

[pbaldy]

You can use the Split() function, or if the values are always 6 digits you can use the Left() and Right() functions.

[sharonir22]

Just to update, i converted my 2 fields into 1 and used the string functions as suggested. My query now works pefectly and i'm very pleased

[June7]

I am confused. I thought you said the data was already 1 field? X and Y should be in separate fields.

[sharonir22]

It was in 2 fields.now its much easier

[June7]

I would never put x and y values into one field. I would figure out what needs to be modified in function.

However, if you are satisfied with resolution, I guess that's what counts.

[pbaldy]

FWIW, I'd have kept them separate too. It's always easier to join multiple fields together as needed than to split one apart.

[June7]

The exception being date/time data. Because of the multitude of date/time functions for extraction of parts, better to have one field for date/time than separate fields for month, day, year, hour, minute, second or to even split date from time. Keep date/time as one field. However, dealing with date/time ranges that cross midnight can be frustrating regardless of how saved. Midnight is saved without time part, just the date.