Solved

access dates dlookup

Posted on 2013-02-07
3
656 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

635 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