lookup query?

[dd_bdlm]

Hi this seems like a very simple thing to do but I am having no
success.

What I want to do is compare a value from a calculated field with a
value from a table and then if it is in the table display another field
in the table associated with it.

ie I have a table of postcode 'starts' and a corresponding area ID
code. I have a calculated field in a query that strips off the first
two (or one) letter from the postcode. How do I say 'compare this with
the table and give me the resulting area code'?

Thanks DD

[Rog]

Just create a new query linking your table and your query on the
"postcode start" - or am I missing something?

[dd_bdlm]

Hi

I have tried that and I get 'data type mismatch in criteria
expression'. I have created a query with my table and postcode start
query and linked the postcode field (which is a list of possible
abreviated codes) with the postcode start calculation (which is a list
of actual abreviated codes). I have a feeling I am so close! Many
thanks for the help
DD

Rog wrote:

Just create a new query linking your table and your query on the
"postcode start" - or am I missing something?

[Rog]

Make sure the fields you link have the same type. For instance, if the
PostCodeStart field in your table is numeric (long), you can force the
calculated field in the query to have the same type by using CLng(...)

[dd_bdlm]

I think I have worked it out. There are missing postcodes in my table
and out of 10000 records I have 25 '#error' results. So I will need to
find a way to add to the code to see if I can eliminate errors. Any
ideas? The code I am using is this:
Function StripPC(PostalCode As String) As String

If Len(PostalCode) >= 2 And Mid(PostalCode, 2, 1) < "a" Then
StripPC = Left(PostalCode, 1)

Else

StripPC = Left(PostalCode, 2)
End If

End Function

many thanks for your help
DD
Rog wrote:

Make sure the fields you link have the same type. For instance, if the PostCodeStart field in your table is numeric (long), you can force the calculated field in the query to have the same type by using

CLng(...)

[Rog]

change your function line to:

Function StripPC(PostalCode)

so that it will not only accept strings, and start the code with

If IsNull(PostalCode) then
StripPC = null
ElseIf Len(PostalCode) etc.

[dd_bdlm]

Many thanks Rog that seems to have done the trick! Finally!
DD

Rog wrote:

change your function line to:

Function StripPC(PostalCode)

so that it will not only accept strings, and start the code with

If IsNull(PostalCode) then
StripPC = null
ElseIf Len(PostalCode) etc.