Can anyone help before I pull out my hair???

[ianhornby]

I have a data source table with names, addresses and UK postcodes, and another table that has partial postcodes (the bit before the space) and I need to look up the local authority for each postcode. I created a front end that links to both tables and tried converting the full postcode in one table to the first 4 characters and the partial postcode in the other table to the first 4 characters too, using two separate queries, both of which give the right results. Then I used a third query based on both of the above to match up the records from both queries, but I get a "Data type mismatch in criteria expression" error. I am just using queries on tables because I'm not very au fait with VBA and so on. Any ideas, anyone please? (I can happily email all to anyone who wants to look.)

 

[ajetrumpet]

and I need to look up the local authority for each postcode.

Not sure what you're doing with your file, but you might consider initially writing the partial postal code in one field and the local authorities in another...then when you need them, just call a query that concatenates the two fields together. Having the authorities in one field too could aid in a lookup function. Just a thought for you...

 

[Rich]

Post codes should be in a separate look up table anyway

 

[unclejoe]

Yes, pulling out his hair shouldn't be an option.

Post codes should be in a separate look up table anyway