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
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.....
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......