Filtering on Date fields problem

[Geirr]

Hi.

I'm sorry to bother you with a problem which may be an easy case...

In a standard table, one of the fields have the Data Type: Date/Time, with format ShortDate.

But, when I filter on the field in the table and select a specific date available in the filtering list, I just get one row with the selected date. Even there is several rows with the selected date. And, it's not showing the first or last row, it shows one 'in the middle (among around 50 rows with this date).
I'm aware of issues around different date format depending on location. So when I create a query on the table an set the filter on the same specific date, I get 0 records. When entering #01/10/2024#, ms access will automatic translate/change it to #01.10.2024#. The actual date is 1st of October, but I have also tried #10.01.2024# with no luck.

Then, in the query I changed the criteria to >= #01.10.2024# And <#02.10.2024# and now it gives me the wanted result. But how come? What am I doing wrong since the criteria on the single date won't work? Is the 'time-part' of the field who is making this issue - despite the fact that the format is ShortDate?

Best Regards,
Geirr.

 

[Gasman]

You will have a time element in the field? If you are just inserting Date() and not Now(), then the time will be 0, but there is always a time element.
Format just shows you what you want to see.
Use DateValue() on the field to just extract the date(s) you require

 

[Geirr]

You will have a time element in the field? If you are just inserting Date() and not Now(), then the time will be 0, but there is always a time element.
Format just shows you what you want to see.
Use DateValue() on the field to just extract the date(s) you require

Hmm - thanks Gasman - I think you got me on the Date() versus Now()... I'm afraid I've used Now() too much... and your reply really make sense.

Best Regards,
Geirr.

 

[Gasman]

Well sometimes you want the time.
I used it a db where I actually wanted to know exactly when a record was amended. Just the date would be no good.

 

[arnelgp]

Then, in the query I changed the criteria to >= #01.10.2024# And <#02.10.2024#

some of your Dates maybe has Time element.

 

[GPGeorge]

Hi.

I'm sorry to bother you with a problem which may be an easy case...

In a standard table, one of the fields have the Data Type: Date/Time, with format ShortDate.

But, when I filter on the field in the table and select a specific date available in the filtering list, I just get one row with the selected date. Even there is several rows with the selected date. And, it's not showing the first or last row, it shows one 'in the middle (among around 50 rows with this date).
I'm aware of issues around different date format depending on location. So when I create a query on the table an set the filter on the same specific date, I get 0 records. When entering #01/10/2024#, ms access will automatic translate/change it to #01.10.2024#. The actual date is 1st of October, but I have also tried #10.01.2024# with no luck.

Then, in the query I changed the criteria to >= #01.10.2024# And <#02.10.2024# and now it gives me the wanted result. But how come? What am I doing wrong since the criteria on the single date won't work? Is the 'time-part' of the field who is making this issue - despite the fact that the format is ShortDate?

Best Regards,
Geirr.

As previously pointed out, Access always stores both a date and time in a date/time field. The time portion can be 00:00:00AM, but it is always stored.

Internally, in fact, dates are stored as a double, with the part of the number before the decimal representing the date and the part of the number after the decimal representing the time of day.

You can apply many different display formats to dates. However, the display format does not change the actual stored date/time values. That means that even if the current date value does have a non-zero time, you can configure a date format which conceals it. Access knows it's there, though, and evaluates it accordingly.

Also, Access has both Date() and Now() functions. The former inserts the current date with a time of 00:00:00AM. The latter stores the current date and the current time.

 

[Geirr]

As previously pointed out, Access always stores both a date and time in a date/time field. The time portion can be 00:00:00AM, but it is always stored.

Internally, in fact, dates are stored as a double, with the part of the number before the decimal representing the date and the part of the number after the decimal representing the time of day.

You can apply many different display formats to dates. However, the display format does not change the actual stored date/time values. That means that even if the current date value does have a non-zero time, you can configure a date format which conceals it. Access knows it's there, though, and evaluates it accordingly.

Also, Access has both Date() and Now() functions. The former inserts the current date with a time of 00:00:00AM. The latter stores the current date and the current time.

Thank You.

I realized my mistake as soon as I read the reply from Mr. Gasman above,

Best regards,
Geirr.