Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2002
    Location
    The Netherlands
    Posts
    4

    Unanswered: Weird date problem

    Hi y'all,

    I've got some weird problem with Access 97. I have some values that are entered into a table. Then I have to analyse that data. I do that by using a DLookup function. This works excellent apart from one problem. In the 'where' section of the Dlookup function I tell it to look between 2 dates. This is where it goes wrong.

    With debug-boxes I have checked and double-checked if the dates are correct which it should look between, but still it gives me the wrong values back.

    Below is some code from my application

    kwartaal = DLookup("quarter", "weeknummers", "id = " & i & "")
    begin = DLookup("min(id)", "weeknummers", "quarter = '" & kwartaal & "'")
    eind = DLookup("max(id)", "weeknummers", "quarter = '" & kwartaal & "'")
    startdate = Format(DLookup("fromdate", "weeknummers", "id = " & begin & ""), "dd mm yyyy")
    enddate = Format(DLookup("todate", "weeknummers", "id = " & eind & ""), "dd mm yyyy")
    'opgedeeld voor de lay-out
    nrreturn = DLookup("Count(retournummer)", "Retournummer",
    "Right(Left(Productcode, 15), 6) = '" & Me.Product & "'
    AND Datum_Completed Between #" & startdate & "# AND #" & enddate & "#")

    As you can see I set the date to be an in an exact format. There is only 1 value in the database that it should find one time, however, it finds it multiple times. Does anyone know what this problem could be???

    The dates start around 2000 and they end around 2006

  2. #2
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    Well, the dates passed to SQL through code can be a real pain.

    Try using
    Format(YourDate, "Medium Date") to force the month to be evaluated correctly

    Regards,

    Dan

  3. #3
    Join Date
    Jul 2002
    Location
    The Netherlands
    Posts
    4
    Already tried that, but it still didn't work....

  4. #4
    Join Date
    Nov 2001
    Posts
    336
    Hi,

    Check local settings for your PC and see the date format.

    Igor

  5. #5
    Join Date
    Jul 2002
    Location
    The Netherlands
    Posts
    4
    And how should that help?
    I get the date from the table. I make it in a specific format with the function Format() and still it won't work. So I do not see how the format of my system date can affect my comparison between 2 dates.....

  6. #6
    Join Date
    Nov 2001
    Posts
    336
    Have you tried to debug your code?

    Add:

    Msgbox Right(Left(Productcode, 15), 6) = '" & Me.Product & "'
    AND Datum_Completed Between #" & startdate & "# AND #" & enddate & "#"

    before DLookup statement.

    Then compare it with the date stored in the table.

    Just my 2 cents,

    Igor

  7. #7
    Join Date
    Jul 2002
    Location
    The Netherlands
    Posts
    4
    Yupz, also tried that, It just some really weird bug in the software of Access I think (not sure though) I have solved it by setting the quarter when I set the datum_completed. I've done this, because the first quarter it finds is always the right one. However, when it started counting, it saw it many times. Say, like 4 times. That was the weird part. Now everything is working fine, but not in the way I wanted it to work in the first place......

Posting Permissions

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