Object does not contain the automation object in dlookup query results

[AaronWestcott via AccessMonster.com]

I hope someone with better eyes and knowledge can help.

I have a query (qry_all_transit_change) that produces and error. See code
below.

SELECT qry_all_transit.DATE, qry_all_transit.MODE, qry_all_transit.
All_Transit, DLookUp("All_Transit","qry_all_transit","[DATE]=#" & DateSerial
(Year([DATE]),Month([Date])-1,1) & "# AND [MODE]=" & [MODE]) AS
All_Transit_Change
FROM qry_all_transit;

qry_all_transit has the follwoign structure:
SELECT tbl_NTD_ALL_YEARS_DATE_FIX.DATE, tbl_NTD_ALL_YEARS_DATE_FIX.MODE, Sum
(tbl_NTD_ALL_YEARS_DATE_FIX.VALUE) AS All_Transit
FROM tbl_NTD_ALL_YEARS_DATE_FIX
GROUP BY tbl_NTD_ALL_YEARS_DATE_FIX.DATE, tbl_NTD_ALL_YEARS_DATE_FIX.MODE;


The All_Transit_Change field in the qry_all_transit_change query displays an
error when the query is run. The error message is "Object does not contain
the automation object...". The dlookup works without the mode criteria added
in.

Any help with this would be greatly appreciated.

 

[Ken Snell [MVP]]

Is MODE a text field in the table? If yes:

SELECT qry_all_transit.DATE, qry_all_transit.MODE, qry_all_transit.
All_Transit, DLookUp("All_Transit","qry_all_transit","[DATE]=#" & DateSerial
(Year([DATE]),Month([Date])-1,1) & "# AND [MODE]='" &
[MODE] & "'") AS All_Transit_Change
FROM qry_all_transit;

 

[John Spencer MVP]

What type of field is MODE? You are treating it as if it is a number field.
If it is a text field then you need quote marks added to delimit the value.

DLookUp("All_Transit","qry_all_transit","[DATE]=#" &
DateSerial(Year([DATE]),Month([Date])-1,1) &
"# AND [MODE]= """ & [MODE] & """ ") AS All_Transit_Change

You can add quote marks by using two quote marks in a row to have one quote
mark appear in the string that is created. Alternative is you can use an
apostrophe as a quote and build the DLookup this way


DLookUp("All_Transit","qry_all_transit","[DATE]=#" &
DateSerial(Year([DATE]),Month([Date])-1,1) &
"# AND [MODE]= '" & [MODE] & "' ") AS All_Transit_Change

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[AaronWestcott via AccessMonster.com]

Ken and John,

Yes MODE is a text field. While I have not implemented your proposed
solution yet I am sure that is the problem. Thank you.

What type of field is MODE? You are treating it as if it is a number field.
If it is a text field then you need quote marks added to delimit the value.

DLookUp("All_Transit","qry_all_transit","[DATE]=#" &
DateSerial(Year([DATE]),Month([Date])-1,1) &
"# AND [MODE]= """ & [MODE] & """ ") AS All_Transit_Change

You can add quote marks by using two quote marks in a row to have one quote
mark appear in the string that is created. Alternative is you can use an
apostrophe as a quote and build the DLookup this way

DLookUp("All_Transit","qry_all_transit","[DATE]=#" &
DateSerial(Year([DATE]),Month([Date])-1,1) &
"# AND [MODE]= '" & [MODE] & "' ") AS All_Transit_Change

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I hope someone with better eyes and knowledge can help.

[quoted text clipped - 19 lines]

Any help with this would be greatly appreciated.