Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2007

    Unanswered: My vb script won't look up dates in different formats :\


    I have a script in my access DB that automatically fills in a form and creates a confirmation letter. All of it works except it's supposed to flag up if the customers booking is on a raceday (horse racing) as we're near a track and traffic is increased.

    Dim Israceday as Variant

    Israceday = DLookup("[RACEDAYS!israceday]", "RACEDAYS", _
    "[RACEDAYS!racedate] = " & BookDate)

    If Israceday = "Y" Then
    Dim RaceMsg
    RaceMsg = MsgBox("THIS BOOKING IS ON A RACEDAY. Please enclose a raceday traffic warning notice with the confirmation letter.", vbInformation + vbOKOnly, "Raceday Warning")

    I think the problem lies with the dates. At the beginning it says 'Dim BookDate as Date' and that value is taken from another table. The list of dates in the RACEDAYS table is in DD/MM/YY format but for some reason I think it's this that is causing conversion that's problems, Ie when I make a booking with a date I know is in that table, it just returns a null value and halts the script.

    Any ideas?? Thanks!

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    when using dates in Access normally you need to encase them in #

    so id expect to see something like
    Israceday = DLookup("[RACEDAYS!israceday]", "RACEDAYS", _
    "[RACEDAYS!racedate] = #" & format(BookDate, "MM/DD/YYYY") & "#")

    you may not need to do the format, you may get away with
    Israceday = DLookup("[RACEDAYS!israceday]", "RACEDAYS", _
    "[RACEDAYS!racedate] = #" & BookDate & "#")
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2007
    Thanks, you're a star. Worked with the #s

Posting Permissions

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