Update Query Help

[Tara]

I posted a question some time ago about the need to use data from one field
in a different field. I got a great start, but I'm still stuck. Here's what
I have so far:

UPDATE tblCaseInfo INNER JOIN tblContacts ON tblCaseInfo.CaseInfoID =
tblContacts.CaseID SET tblCaseInfo.StartDate =
DMin("Date","tblContacts","CaseID = " & "CaseID")
WHERE (((tblCaseInfo.StartDate) Is Null) AND
((tblContacts.CaseID)=[Forms]![frmCaseInfo]![CaseID]));

The problem is that the query doesn't filter by CaseID. It simply finds the
earliest ContactDate in the table and applies that to every record that has a
null StartDate. If I create a query to limit the records by CaseID, then tie
it into the above query, I get the message that I must use an updateable
query. I understand why I'm getting that error, but I'm stuck as to how to
get the original query to filter by CaseID.

Any help is appreciated!

 

[John W. Vinson]

The problem is that the query doesn't filter by CaseID. It simply finds the
earliest ContactDate in the table and applies that to every record that has a
null StartDate. If I create a query to limit the records by CaseID, then tie
it into the above query, I get the message that I must use an updateable
query. I understand why I'm getting that error, but I'm stuck as to how to
get the original query to filter by CaseID.

I think the problem is with the quotes around CaseID in the DMin. You're
updating all records where the value of CaseID is equal to *itself* - that is,
all non-null values!

Try

UPDATE tblCaseInfo INNER JOIN tblContacts ON tblCaseInfo.CaseInfoID =
tblContacts.CaseID SET tblCaseInfo.StartDate =
DMin("Date","tblContacts","CaseID = " & tblContacts.CaseID)
WHERE (((tblCaseInfo.StartDate) Is Null) AND
((tblContacts.CaseID)=[Forms]![frmCaseInfo]![CaseID]));

This will concatenate the *VALUE* of the CaseID field in the query (which is
filtered using the form) rather than comparing the CaseID field value to
itself.

 

[Tara]

You were right! Taking the quotes out fixed it.

Thank you so much!

The problem is that the query doesn't filter by CaseID. It simply finds the
earliest ContactDate in the table and applies that to every record that has a
null StartDate. If I create a query to limit the records by CaseID, then tie
it into the above query, I get the message that I must use an updateable
query. I understand why I'm getting that error, but I'm stuck as to how to
get the original query to filter by CaseID.

I think the problem is with the quotes around CaseID in the DMin. You're
updating all records where the value of CaseID is equal to *itself* - that is,
all non-null values!

Try

UPDATE tblCaseInfo INNER JOIN tblContacts ON tblCaseInfo.CaseInfoID =
tblContacts.CaseID SET tblCaseInfo.StartDate =
DMin("Date","tblContacts","CaseID = " & tblContacts.CaseID)
WHERE (((tblCaseInfo.StartDate) Is Null) AND
((tblContacts.CaseID)=[Forms]![frmCaseInfo]![CaseID]));

This will concatenate the *VALUE* of the CaseID field in the query (which is
filtered using the form) rather than comparing the CaseID field value to
itself.