Solved

access dates dlookup

Posted on 2013-02-07
3
626 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 143

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

839 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