finding a date by using Dmin

[iankerry]

Hi

I have a continuous form with 600 records with dates in it, some past dates some future dates.

When the form loads I want it to go to todays date, or the nearest date before today if there isnt a todays date.

The code i use at the moment is:

dtmBestDate = DMin("[DateField]", "[dbo_eventsflicks]", "[DateField] >= #" & date & "#")

Datefield is the field in the record that holds the date of an event
dbo_eventflicks is the table name.

it sometimes works, but if there isnt a todays date or a date for yesterday for examply it just goes to the first record.

Can anyone suggest an amendment for me that will help?

many thanks

ian

 

[DCrake]

Have you tried to reverse the logic

dtmBestDate = DMax("[DateField]", "[dbo_eventsflicks]", "[DateField] <= #" & date & "#")

In other words get me the highest date before todays date.


David

 

[iankerry]

Hi thanks for your reply. Interesting logic.

Whilst trying it, i disocered that there must be something else going wrong, as there is a 7 may date and a 6 may date (which is what dtmBestdate was returning).

here is the rest of my code:

Dim dtmBestDate As Date
dtmBestDate = date
dtmBestDate = DMax("[DateField]", "[dbo_eventsflicks]", "[DateField] <= #" & date & "#")
[datefield].SetFocus
RunCommand acCmdSortAscending
DoCmd.FindRecord dtmBestDate

Weird, becuase it always used to work! Can you see any problems with this code? If not then there must be something else going on that i need to investigate.

best

ian

 

[DCrake]

It may well be to do with how Access is interpreting the dates ie dd mm yyyy or mm dd yyyy. I tested it myself and had to change the date to Medium date on a date that could be either.

David