Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

access dates dlookup

Posted on 2013-02-07
3
Medium Priority
?
716 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 2000 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

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.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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