Solved

access dates dlookup

Posted on 2013-02-07
3
609 Views
Last Modified: 2013-02-07
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
0
Comment
Question by:developingprogrammer
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38863073
>i just want to compare number with number
then you had to store the date as number, and not as date

actually, computers don't speak "numbers", they only speak "bits and bytes".

and ms access will, with a date field, only speak "date values".
so, in ms access, you HAVE to format the value for the query into date format. it will work.
the issue with this line :
"[Date and Time of Update]=#" & dateLatestUpdate & "#")
is that it does a implicit conversion of the date value into a string.
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
the article is not about ms access, but about the principle.

you will have to adapt to what ms access interface ...
0
 

Author Comment

by:developingprogrammer
ID: 38863097
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!! = ))
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38863209
(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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now