DLOOKUP Type Conversion Failure

[Roly Reefer]

Hi,

I am having problems with a DLOOKUP expression in an update query.

I have set up a lookup table (imported from Excel and another table set up myself because of this error) with a date field and a week number field and I am trying to return the week number into another table for the date in another field. It is easy to do in Excel (VLOOKUP), but I have never done this in Access.

I am attempting to get the update query to update another table which has a Date of Booking and a new week number field. When I run the query, it has the error message that it cannot update all of the records, due to a type conversion failure. I thought that this error was due to fields have dissimilar data types and changed them until they were exactly the same - the Week Number are both Long Integer number fields and the dates are a General Date date fields.

The expression is: DLookUp("DLOOKUP Created.Week Number","DLOOKUP Created","Date = " & [2004 Lookup].[Date of Booking]). The lookup table is DLOOKUP Created and this is the return value I want. The 2004 Lookup table is the table that I want to update with the week number, using the date of booking.

Any help will b gratefully receive.

All the best,


Roly

 

[RV]

Try

DLookUp("[DLOOKUP Created].[Week Number]","[DLOOKUP Created]","[2004 Lookup].[Date of Booking] = [DLOOKUP Created].[Date])

Prevent using spaces in object names.
Don't use Date for object names, it's a reserved word and will definitely cause issues...
The "D" functions are not the fastest ones and could cause performance issues.
Rather use joins.

RV

 

[iago18]

If the field you are filtering off of is a 'Date/Time' field, you probably need to surround the criteria date with # signs. Something like this:

"Date = #" & [2004 Lookup].[Date of Booking] & "#"

These symbols tell Access to expect a date variable. Try that and see if it helps.

Also, I agree: don't name fields things like 'Date' because these are special reserved words in SQL and Access.