05-22-13, 14:00 #1Registered User
- Join Date
- May 2013
Unanswered: Performing a Calculation based on fields located in two seperate queries
I am wondering how to perform calculations using data returned by a query.
My program begins by prompting a user for their state and zip code
After they use the command button on the form, a macro runs two queries, one to determine the city and corresponding latitude and longitude, and one to return the zip code, city, address, and latitude and longitude for all predetermined locations within the specified state.
For example : User is prompted and enters 20850 as the Zip code and MD as the state.
Once they hit the command button, the query UserLocation, will return 20850 Rockville MD and the corresponding coordinates.
Additionally, the second query, Building Location, will return all of the locations for the company within that specific state, as well as their coordinates. In the case of this example, MD will return 51 entries.
This is where i being to have problems
The next step in my program needs to allow me to perform a calculation to determine the as the crow flies milage between the zip code for the customer and the zip code for the company locations in the specified state.
I have the Function for distance written already, I just need some assistance with figuring out how to reference the queried data when trying to assign values to my variables.
For example :
How would i set LatitudeA and LongitudeA to be the coordinates returned by the UserLocation query?
My second problem is that once i can assign the first entries field values to be the variables, how can perform the function for each entry returned by the building variable?
For example :
LatB and LongB would be assigned their values from query BuildingLocation. However, this query returns 51 results.
I figure i need to have a looping structure do something to the effect of :
Do Function Distance while counter <= entries
Is there a way that i can automatically calculate the number of entries? and then assign this to a variable?
Additionally, it is important to note that i've looked into possible ways to perform these actions using recordset and Itemselected, but i'm not entirely sure if i properly understand how to use these tools
Just to reiterate: My question is how to assign a VBA variable its value based on data returned in specific query fields. I also am wondering if anyone has some insight into how to setup some type of loop/array to calculate the number of entries returned so that I can perform calculations for each record.
05-23-13, 03:16 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
providing there is some common piece of information then you can link queries as f they were tables using the join syntax
Select CalculateDistance(Customer.sLatitude,Customers.Longitude, Depots,Latitude, Depots.Longitude) as Distance from Customers, Depots Join Depots on Customers.StateCode = Depots.StateCodeI'd rather be riding on the Tiger 800 or the Norton