DLookup with date field and other criteria

[Delid4ve]

Coming accross something strange and i dont know whats going on.

So i am using a lookup on a table and checking a date field.

Certain dates work, certain dates dont and i cant work out why.
DLookup("[F-GasLogID]", "[F-GasLogs]", "[actiondate] = #" & Forms![FrontEnd]![F-GasLog_Add].Form.[Actiondate] & "#")

for instance:
23/07/2009
23/06/2010
09/09/2011
14/10/2011
all work and return the ID

06/09/2011
12/08/2011
dont work, returns null

and all are unique dates.
Spent nearly all day trying to work this out and im stumped.
Please help :banghead:

 

[plog]

dont work, returns null

The obvious answer is, are the dates that return null actually in [F-GasLogs] table? Can you upload a stripped down database with just that table?

 

[Delid4ve]

yes they are.
they are all unique values as well, no other row has the same date (even though this should still return the first value i believe).

 

[Delid4ve]

Ive also checked this on a query:
SELECT [F-GasLogs].[F-GasLogID], [F-GasLogs].ActionDate
FROM [F-GasLogs]
WHERE ((([F-GasLogs].ActionDate)=#9/6/2011#));

and it works perfect

**** Just attached stripped down with just that table and sub form.

 

[The_Doc_Man]

The obvious question is whether the dates are stored as text strings in the table. I'm not sure why it works for some of the ones you listed, but Access, being a USA product, sometimes displays a USA-centric view on dates.

For this list:
23/07/2009
23/06/2010
09/09/2011
14/10/2011

Every date is unambiguous. It can ONLY be interpreted as dd/mm/yyyy.

For this list:
06/09/2011
12/08/2011

Every date COULD be interpreted as dd/mm/yyyy or mm/dd/yyyy since both represent valid dates. It might be a coincidence but ... do you have more samples than that - and if so, do they return the correct ID? Or do any of them return the ID for a date where the mm and dd portions are reversed? If you can track down THAT answer and it is YES, then your problem is simply date format issues. You can look that up in this forum because we get hits on that quite often.

 

[plog]

I don't see the code you initially posted in that database. I found this:

DLookup("[F-GasLogID]", "[F-GasLogs]", "[actiondate] = " & sim & " and [equipmentid] = " & Forms![FrontEnd]![F-GasLog_Add].Form.[AssetNo].Column(0) & " and [cylinderid] = " & Forms![FrontEnd]![F-GasLog_Add].Form.[Cylinder].Column(0) & " and [employeeID] = " & Forms![FrontEnd]![F-GasLog_Add].Form.[Employee].Column(0))

Is that what you were referencing? If so, you have more points of possible failure than just the date.

 

[Delid4ve]

its got more to go in the dlookup, but at the moment im just testing in the immediate window. But yes, thats where it will be when it works. I know that doesnt at the mo.

I do definately believe its a US to UK problem, ive seen this in various threads arount tinternet, however i still cant get it working.

Ive tried this:
sim = DLookup("[F-GasLogID]", "[F-GasLogs]", "format([actiondate],""dd/mm/yyyy"") = #" & format(Forms![F-GasLog_Add].Form.[Actiondate],"dd/mm/yyyy") & "#")

still doesnt work.

 

[Delid4ve]

sorted:
DLookup("[F-GasLogID]", "[F-GasLogs]", "cdbl([actiondate]) = " & cdbl(Forms![F-GasLog_Add].Form.[Actiondate]) & "")

Thx guys, been messing with me all day.

 

[fo007fo]

sorted:
DLookup("[F-GasLogID]", "[F-GasLogs]", "cdbl([actiondate]) = " & cdbl(Forms![F-GasLog_Add].Form.[Actiondate]) & "")

Thx guys, been messing with me all day.

Thank you
It helped me a lot

 

[robot]

sorted:
DLookup("[F-GasLogID]", "[F-GasLogs]", "cdbl([actiondate]) = " & cdbl(Forms![F-GasLog_Add].Form.[Actiondate]) & "")

Thx guys, been messing with me all day.

I signed up for this site just to give you a like,
I spent many hours on this problem
Thank you