Filtering on Date fields problem (1 Viewer)

Geirr

Registered User.
Local time
Today, 07:45
Joined
Apr 13, 2012
Messages
39
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.
 
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
 
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.
 
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.
 
Then, in the query I changed the criteria to >= #01.10.2024# And <#02.10.2024#
some of your Dates maybe has Time element.
 
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.
 
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.
 
In a standard table, one of the fields have the Data Type: Date/Time, with format ShortDate.
String dates MUST be formatted as US standard = mm/dd/yyyy OR the unambiguous yyyy/mm/dd.

Dates ae stored internally as double precision numbers. The integer portion is the number of days since 12/30/1899 and the decimal is the elapsed time since midnight. When you format a date, you turn it into a string. When a date is a string, it acts like a string. so 01/10/2024 is < 02/09/2024 because 01 is < 02.

When you format a date, you may also be losing significant information such as the time component. Just because you can't see it, doesn't mean that it isn't interfering with your sort/selection.

NEVER use Now() when you only want the Date().
 
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.
https://support.microsoft.com/en-us...493ded-e544-4144-9103-b9b1d1865147#formfilter
If you want the time with the date and do not want to dispense with the time, there is a feature to filter the date with the time for a specific day of the month, for example, or for a whole month. You can use it in inquiries or tables. It is also present in the Excel program when making a filter for a specific column. You can review the link and learn more.
 

Users who are viewing this thread

Back
Top Bottom