Link to home
Start Free TrialLog in
Avatar of developingprogrammer
developingprogrammer

asked on

access dates dlookup

guys, i think this has probably been the most asked thread around. i just want to solve this once and for all.

i've got the below function and my code doesn't work. i've got other code that works and i know that if i just put an american format function it will work - so no problems getting it to work then.

but what i wanna know is, how can i once and for all do date lookups in a format agnostic (or rather atheistic!) way? i'm so tired of having to format dates. computers speak numbers. and i know it's being stored as a floating point with decimals for the time. how can i compare floating point with floating point? i don't wanna format it as a amercian / human date anymore and compare. i just want to compare number with number. help!!

edit: ok, i said i could get it to work - take that back. ahhhh!!!!!!!!! i HATE how access handles darn dates! why make something so simple so complicated?!!!!!

help help guys!!



Function LatestVersion() As String
Dim dateLatestUpdate As Date
dateLatestUpdate = DMax("[Date and Time of Update]", "tblVersioning")
Debug.Print DLookup("Version", "tblVersioning", "[Date and Time of Update]=#" & dateLatestUpdate & "#")
'the immediate window shows Null --> which means my dlookup failed
'the value of dateLatestUpdate is 7/2/2013 4:19:10 PM  by the way, i got this by printing it to the immediate window
'there is only 1 record in the table tblVersioning

End Function

Open in new window

date-and-time-dlookup.png
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of developingprogrammer
developingprogrammer

ASKER

ah ok i see, thanks so much angelIII, i realised it was converting the date value into the string cause dlookup is using a string literal for the criteria - thus regardless what i save it as it will be converted into a string.

thanks so much once again for clarifiying this!! = ))
(What follows is based on my understanding of Access date handling - I may not be 100 % correct)

I don't think you can achieve this.  

What you have to do to use a 'date' depends on where the value is coming from.

You can certainly use expressions like this in VBA:

if Int(datefield1) =int(datefield2) then.....

if int(datefield) >Int(Now()) then ....

provided your are dealing with Fields or vba variables.  Both have datatypes and so the values are handled as dates, if they are datatyped as dates..

However, when you are dealing with values displayed in a form, then you are NOT dealing with fields, you are dealing with Controls and everything displayed in a textbox is text.

This is probably clear  where you are dealing with unbound controls - there is no datatype involved - if you want Access to treat it as a date then you have to manage this through functions or other code or as you point out , sometimes including the TEXT value between #..#.  .  But fundamentally what you have to do is to tell Access how to interpret this bit of TEXT.  After all, a bit of text saying 27-11-2009 does not have to be a date - it could be a product code or any other sort of serial number.

This is where many new users have problems and because of some history, some misinformation prevails.  And we also have to bear in mind that Access is itself built from components which might handle things differently - Access VBA does not always behave the same way as Access-SQL

Access-SQL  does Not require you to use US format dates.  What it likes you to use is unambiguous dates.  A date of 27-11-2009 will not be misinterpreted, nor will one of 11-27-2009.  But a date of 01-02-03 can mean anything depending on where you are in the world.
What Access does  is to start by seeing if it can be interpreted as a date in US format,and this is why the idea has grown up that Access 'requires' dates to be in US format.
It might be argued that Access should first use the regional settings to test a 'date' value, but this does not happen.

What has happened over the years, in feeding date values into SQL processes,  is that some developers have opted to format dates in US format mm-dd-yyyy , and some have opted to use a more general yyyy-mm-dd (which is the internationally defined standard, although that is pretty much by-the-by).  I am firmly in the yyyy-mm-dd camp.

So in terms of your aim, I don't think you can achieve it.  Most users don't like looking at dates in yyyy-mm-dd format on forms - they prefer something more friendly.  So when you have to take a date from a control and use it in SQL, you have to make it unambiguous which generally involves reformatting it.