Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    4

    Angry Unanswered: Problems with Date() in visual basic Access 2003

    Hi

    I have been having an incredibly annoying problem in Microsoft Access 2003. For some reason, everything has been working fine for the past number of months, but now it isn't. Basically, I have written some visual basic code. One part of this code is to retrieve records between two dates. The first of these is 'dateadd("m",-3,Date()) and the second is Date().

    So therefore, this should return the records between approx May 2009 and August 2009. And this used to work perfectly fine. But now it chooses to bring up just the records from March 2009?? I therefore think it is a problem with the Date() function, as my PC date and time is perfectly correct. I have tried changing this to Now() which does not work either.

    I have also tried writing this function and similar functions directly into the query and it does not work.

    Funnily enough though, if the date() is added to a form, it does bring up the correct date.

    Please help.

    Why is my Date() not working in VBA??

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by djpeacock
    Hi

    I have been having an incredibly annoying problem in Microsoft Access 2003. For some reason, everything has been working fine for the past number of months, but now it isn't. Basically, I have written some visual basic code. One part of this code is to retrieve records between two dates. The first of these is 'dateadd("m",-3,Date()) and the second is Date().

    So therefore, this should return the records between approx May 2009 and August 2009. And this used to work perfectly fine. But now it chooses to bring up just the records from March 2009?? I therefore think it is a problem with the Date() function, as my PC date and time is perfectly correct. I have tried changing this to Now() which does not work either.

    I have also tried writing this function and similar functions directly into the query and it does not work.

    Funnily enough though, if the date() is added to a form, it does bring up the correct date.

    Please help.

    Why is my Date() not working in VBA??
    Hi

    I don't know what part of the world you are in, but I suspect you need to format the date strings (the relevant line of you code would also be usefull) to force American date formats thus

    Format(DateAdd("m",-3,Date) ,"mm/dd/yy")
    and
    Format(Date,"mm/dd/yy")

    As you code returns March data I assume this was run yesterday (the 3rd), and if run today (4th) your current code woul return April data !!??


    MTB

  3. #3
    Join Date
    Aug 2009
    Posts
    4
    Hi Im actually from England.

    My code is

    LDate = dateadd("m", -3, Date)
    TDate = Date


    strSQL = "SELECT * FROM tbl003_Main_Validated_Data " & _
    "WHERE tbl003_Main_Validated_Data.Histology IN(" & strCriteria & ") And tbl003_Main_Validated_Data.Gender IN(" & strCriteria2 & ") And tbl003_Main_Validated_Data.Consultant IN(" & strCriteria3 & ") And tbl003_Main_Validated_Data.Site IN(" & strCriteria4 & ") And tbl003_Main_Validated_Data.Date_of_MDT Between " & "#" & LDate & "#" & " AND " & "#" & TDate & "#"

    I am not sure how to include the format function that you suggested, and not sure if this is the reason why there are problems?

    Please help.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by djpeacock
    Hi Im actually from England.

    My code is

    LDate = dateadd("m", -3, Date)
    TDate = Date


    strSQL = "SELECT * FROM tbl003_Main_Validated_Data " & _
    "WHERE tbl003_Main_Validated_Data.Histology IN(" & strCriteria & ") And tbl003_Main_Validated_Data.Gender IN(" & strCriteria2 & ") And tbl003_Main_Validated_Data.Consultant IN(" & strCriteria3 & ") And tbl003_Main_Validated_Data.Site IN(" & strCriteria4 & ") And tbl003_Main_Validated_Data.Date_of_MDT Between " & "#" & LDate & "#" & " AND " & "#" & TDate & "#"

    I am not sure how to include the format function that you suggested, and not sure if this is the reason why there are problems?

    Please help.
    Hi

    Being as you are in good Old Blighty this should do it

    LDate = Format(dateadd("m", -3, Date),"mm/dd/yy")
    TDate = Format(Date,"mm/dd/yy")

    Let us know

    When constructing SQL strings in VBA (or VB) you will always need to format dates as America format due to VBA not taking any account of the computor local date setting. However, the Query Designer does take account of the local setting, as you will see if you switch from the designer grid to SQL view and back.


    MTB

  5. #5
    Join Date
    Aug 2009
    Posts
    4
    Hi

    Thanks very much for your help so far. I did as you said and included the following code in place of the old code.

    LDate = dateadd("m", -3, Date)
    LDate = Format(dateadd("m", -3, Date), "mm/dd/yyyy")
    TDate = Date
    TDate = Format(Date, "mm/dd/yyyy")

    strSQL = "SELECT * FROM tbl003_Main_Validated_Data " & _
    "WHERE tbl003_Main_Validated_Data.Date_of_MDT Between " & "#" & LDate & "#" & " AND " & "#" & TDate & "#"

    This worked fine at first. But then for some unknown reason, it decided to not retrieve the correct records. The code above has been input into the query as Between 8/8/2007 and 5/8/2009.

    Cannot see where I'm going wrong really. And its iritating that it works one minute and then the next it doesn't. Any further help or ideas is greatly appreciated??

  6. #6
    Join Date
    Aug 2009
    Posts
    4
    It now seems to be working okay again now???

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by djpeacock
    This worked fine at first. But then for some unknown reason, it decided to not retrieve the correct records. The code above has been input into the query as Between 8/8/2007 and 5/8/2009.
    I am a little puzzled, are you sure these date are correct?

    You query string should return this (fragment)

    Between #05/05/09# AND #08/05/09#

    BTW you have redundant ampersands in the code, ie. could/should be

    Between #" & LDate & "# AND #" & TDate & "#"


    MTB

Posting Permissions

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