Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Unanswered: Problem with using Date in Dlookup

    I have the following code:

    Code:
    If isNull(DLookup("[PatientNO]", "ESWL", "[PatientNO] = " _
    & NumberOFPatient & " AND [ESWLDate] = #" & Date & "#")) Then
    
    ' Do something
    
    Else
    
    ' Do something else
    
    End if
    THE ONLY PROBLEM is I can't get Date part of the Dlookup statement to work (when date is removed from the equation the statement works). I get either syntax error or type mismatch errors when trying to execute that Dlookup statement.

    I tried various approaches like spliting date into year, month and day; converting date to string etc. but nothing helps!

    So my QUESTION IS: How do I use Dlookup's WHERE part to look up for specific date!

    P.S. ESWLDate field in table ESWL is declared as Date/Time.

    Should be simple, but wizards from Microsoft decided it should be complicated. The same is with Excel VBA. Thanks!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well there doesn't seem to be anything wrong with the expression you have. Maybe instead of Date try using Date() or even Format(Date(),"d mmm yyyy") to deal with possible international format issues.

    HTH
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by StarTrekker
    Well there doesn't seem to be anything wrong with the expression you have. Maybe instead of Date try using Date() or even Format(Date(),"d mmm yyyy") to deal with possible international format issues.

    HTH

    personally I'd try the US date format
    Format(<mydatecolumn/value>,"m/dd/yyyy")

    date() will return the current system date (with no time element but thats irrelevant here)
    otherwise replace <mydatecolumn/value> with whatever is the name of the control, variable or whatever that contains the date parameter

  4. #4
    Join Date
    Apr 2007
    Posts
    108
    I've been searching through VBA help and found immensly helpful snipet: "Date for use in SQL must be in US format..." without telling me what that format is and what is the easiest way to achieve it.

    So through try and error, here is what works in my neck of the woods (where date is expressed as 23.4.2007. for example or dd.mm.yyyy.)

    Code:
    Dim todaysDate as Date
    Dim stringOFTodaysDate as String
    Dim convertedString as String
    
    todaysDate = Date()
    
    stringOFTodaysDate = Format(todaysDate, "mm/dd/yyyy")
    
    'if you use this string in Dlookup you get error because it is now mm dd yyyy 
    'but still separated by . which is declared as date separator in Windows settings
    
    convertedString = Replace(stringOFTodaysDate, ".", "/")
    And voila! After that placing convertedString in place of desired date in Dlookup statement finally produces desired results!

    Anyway, very poor marks to Access team for that, especially when putting explicit date functions, like Date() doesn't get recognised as proper date in Dlookup function.

    P.S. healdem Date is without () because VBA removes them automatically. I guess function call without arguments is the reason.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Riorin
    I've been searching through VBA help and found immensly helpful snipet: "Date for use in SQL must be in US format..." without telling me what that format is and what is the easiest way to achieve it.
    ... try post#3... Format(<mydatecolumn/value>,"m/dd/yyyy")
    Quote Originally Posted by Riorin
    Anyway, very poor marks to Access team for that, especially when putting explicit date functions, like Date() doesn't get recognised as proper date in Dlookup function.

    P.S. healdem Date is without () because VBA removes them automatically. I guess function call without arguments is the reason.
    As advised earlier I suspect the problem is that dates in Access should be specified as US style (m/dd/yyyy), it being an American db, or optionally I think you can get away with YYYY/MM/DD (the preferred ISO date).

    You may also get away with dd mmm yyyy... ie using the date name rather than the date number.. the runtime works out that the month name is the month, whether that will work with your dates I don't know.

    I think you are going to be bashing you head against a brick wall if you keep comparing the way Access does things compared to how you want it to do things and complaining when it falls short of your expectations. Access is a front end database development tool not not a fully featured development language and you have to adapt to the Access way of doing things. If thats a problem for you then Id suggest you find an alternative db development front end, but I don' think you will fare any better with Paradox or the Open Office db

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •