Updateable query problem (AutoLookup City/State)

[rockman]

I have a database of contacts (tblContacts). One of the fields is fldZipCode.

I have a database of zip codes that contain fields fldZipCode (pk), fldCity, and fldState.

I'd like to create an updateable query (to be used by a form) that joins the Contact information and City/State. The following query is updateable:

SELECT tblContacts.*, tblZipCodes.fldCity, tblZipCodes.fldState
FROM tblContacts LEFT JOIN tblZipCodes on tblContacts.fldZipCode=tblZipCodes.fldZipCode

My problem is the Contacts' fldZipCode may contain a 5 character string or the new 9 character string (Zip+4). The following query is NOT updateable:

SELECT tblContacts.*, tblZipCodes.fldCity, tblZipCodes.fldState
FROM tblContacts LEFT JOIN tblZipCodes on [B][U]Left(tblContacts.fldZipCode,5)[/U][/B]=tblZipCodes.fldZipCode

Can anyone think of a possible SQL syntax that would accomplish this task and still leave the recordset updateable?

Thanks for any thoughts,
Jeff

 

[rockman]

If I understand you right Pat, I think I've tried that too..

Query1: SELECT tblContacts.*, Left(tblContacts.fldZipCode,5) as fldZipCodeRoot
FROM tblContacts

Query2: SELECT Query1.*, tblZipCodes.fldCity, tblZipCodes.fldState
FROM Query1 LEFT JOIN tblZipCodes on Query1.fldZipCodeRoot=tblZipCodes.fldZipCode

...but it makes Query2 unupdatable.

 

[rockman]

This also won't work:

Query1: SELECT tblContacts.fldContactID, Left([fldZipCode],5) AS fldZipCodeRoot
FROM tblContacts;

Query2: SELECT tblContacts.*, tblZipCodes.fldCity, tblZipCodes.fldState
FROM (tblContacts INNER JOIN Query1 ON tblContacts.fldContactID = Query1.fldContactID) LEFT JOIN tblZipCodes ON Query1.fldZipCodeRoot = tblZipCodes.fldZipCode;

In all of these examples, the data is returned correctly but is unupdateable.

 

[rockman]

As much as I hate to do it, it looks like I'm stuck with this cludge:

SELECT tblContacts.*, DLookUp("fldCity","tblZipCodes","fldZipCode = '" & Left(tblContacts.fldZipCode,5) & "'") AS fldCity, DLookUp("fldState","tblZipCodes","fldZipCode = '" & Left(tblContacts.fldZipCode,5) & "'") AS fldState
FROM tblContacts LEFT JOIN tblZipCodes ON tblContacts.fldZipCode = tblZipCodes.fldZipCode;

...unless anyone can think of anything different...

Thanks for trying,
Jeff