DLookup with date field and other criteria (1 Viewer)

Delid4ve

Beginner but fast learner
Local time
Today, 09:01
Joined
Oct 6, 2015
Messages
50
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

Banishment Pending
Local time
Today, 04:01
Joined
May 11, 2011
Messages
11,611
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

Beginner but fast learner
Local time
Today, 09:01
Joined
Oct 6, 2015
Messages
50
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

Beginner but fast learner
Local time
Today, 09:01
Joined
Oct 6, 2015
Messages
50
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.
 

Attachments

  • FGas_CylinderLog_All_2017-03-13 - Copy.accdb
    780 KB · Views: 134
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:01
Joined
Feb 28, 2001
Messages
26,996
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

Banishment Pending
Local time
Today, 04:01
Joined
May 11, 2011
Messages
11,611
I don't see the code you initially posted in that database. I found this:

Code:
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

Beginner but fast learner
Local time
Today, 09:01
Joined
Oct 6, 2015
Messages
50
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

Beginner but fast learner
Local time
Today, 09:01
Joined
Oct 6, 2015
Messages
50
sorted:
DLookup("[F-GasLogID]", "[F-GasLogs]", "cdbl([actiondate]) = " & cdbl(Forms![F-GasLog_Add].Form.[Actiondate]) & "")

Thx guys, been messing with me all day.
 

fo007fo

New member
Local time
Today, 12:01
Joined
Feb 15, 2020
Messages
1
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:01
Joined
Feb 19, 2002
Messages
42,970
Open the table in design view.
Look at the properties for the date field.
Is there a format in the format property?

REMOVE the format. No user ever looks at tables and by formatting the date field you are obfuscating its actual value. Some of the dates have time components. Once you know that the date field includes a time component, you can deal with it or fix the code that is putting time in a field where you don't want time.
Date() = date only
Now() = date + time of day

Use the function that gives you what you want and don't be sloppy about randomly using either.
 

robot

New member
Local time
Today, 09:01
Joined
Apr 20, 2020
Messages
1
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
 

Users who are viewing this thread

Top Bottom