Lookup value from another table that is not exact and report!

[chrislant]

Hi all,

I am trying to create a query which finds the long/lat for a postcode. However I only free long/lat information so instead of the data being a full postcode with them co-ordinates I have first part of the postcode.



My data looks like this:

TBLUsers
FirstName|LastName|Postcode|Sport
Joe | Blogs |AB12 3CD|Football
Dave | David | B60 2AB | Football
John | Smith | AB12 3CD | Swimming
Betty | Briggs | B60 2AB | Football

TBLPostcodes

code	lat	lng
AB10	57.131086	-2.122482
AB11	57.13121	-2.082261
AB12	57.098381	-2.1724
AB13	57.108	-2.237
AB14	57.101	-2.27

B60	52.317093	-2.04617
B61	52.348538	-2.087815
B62	52.439416	-2.047562
B63	52.450894	-2.071439
B64	52.474398	-2.069161
B65	52.48484	-2.04352

In excel I would simply use a vlookup and put it so it was not looking for a exact match however I do not know how to do this in a query. Could someone please help!

Thanks

Chris

[June7]

One approach is DLookup. Try expressions in query that has TBLUsers as source:

DLookup("lat", "TBLPostcodes", "code='" & Left([Postcode],4) & "'")
DLookup("lng", "TBLPostcodes", "code='" & Left([Postcode],4) & "'")

[ssanfu]

One approach is DLookup. Try expressions in query that has TBLUsers as source:

DLookup("lat", "TBLPostcodes", "code='" & Trim(Left([Postcode],4)) & "'")
DLookup("lng", "TBLPostcodes", "code='" & Trim(Left([Postcode],4)) & "'")

If the postal code is "B60 2AB", left 4 would be "B60 " (includes a space). Don't know if the space is included in the look up table. So I would add the "Trim()" function


DLookup("lat", "TBLPostcodes", "code='" & Trim(Left([Postcode],4)) & "'")
DLookup("lng", "TBLPostcodes", "code='" & Trim(Left([Postcode],4)) & "'")

[June7]

Good catch ssanfu, I saw 4 characters in first record and didn't check further.

[chrislant]

One approach is DLookup. Try expressions in query that has TBLUsers as source:

DLookup("lat", "TBLPostcodes", "code='" & Left([Postcode],4) & "'")
DLookup("lng", "TBLPostcodes", "code='" & Left([Postcode],4) & "'")

Thanks this works a absolute treat! I now have just found another problem though! I have some postcodes like S1 2BP which in my lookup table I only have S1. I am getting blanks for these. Do you have any ideas how to fix this one?

Cheers

Chris

[June7]

Okay, too much variability in the prefix. Change to: Left([Postcode], InStr([Postcode]," ")-1)

You might want to get familiar with all the string manipulation functions.

[ssanfu]

Now your pattern has changed. I would write a small function that would take care of all cases.

Private Function GetLongLat(pCode As String, pCoord As String) As Double
'returns a double of the Lat. or Long.

   Dim d As DAO.Database
   Dim r As DAO.Recordset
   Dim sSQL As String
   Dim pos As Integer  'position of the space in the postal code
   Dim PC As String  'first part of Postal code

   Set d = CurrentDb

   pos = InStr(1, pCode, " ")
   PC = Trim(Left(pCode, pos))

   GetLongLat = ""

   sSQL = "SELECT " & pCoord & " FROM TBLPostcodes WHERE code = '" & PC & "';"
   Set r = d.OpenRecordset(sSQL)

   If Not r.BOF And Not r.EOF Then
      GetLongLat = r.Fields(0)
   End If

   r.Close
   Set r = Nothing
   Set d = Nothing

End Function

In the query, you would use

TheLat: GetLongLat([Postcode], "lat")
TheLng: GetLongLat([Postcode], "lng")

[ssanfu]

Okay, too much variability in the prefix. Change to: Left([Postcode], InStr([Postcode]," ")-1)

You might want to get familiar with all the string manipulation functions.

First again!!!! Drat!! And taking the easy way.......

[chrislant]

Okay, too much variability in the prefix. Change to: Left([Postcode], InStr([Postcode]," ")-1)

You might want to get familiar with all the string manipulation functions.

That worked perfect! Cannot believe how much I forgot.