Help with date format

[aytee111]

I am in the USA and use m/d/y format for short date, dealing with someone in Europe with d/m/y.

- date field is stored on a table in Date/Time datatype (includes the time)
- continuous form showing all records including date in Short Date format
- user right-clicks on date, selects Filter > Between, is shown a window with two fields to enter from and to dates, with Access calendar date-picker
- user enters two dates, clicks on OK, and gets error: "Enter a valid date"

I changed the date format on my computer but the error does not occur, so I wonder if anyone already using the European format has experienced this problem.

 

[Mark_]

Because this comes up rather often, a DATE is stored as a number, not the text you typed in. If you are going off of their local settings (not forcing a format) you should not run into problems.

For myself I would only ever specify a format if there is an end-user specific reason, such as all dates are entered as YYYYMMDD by company policy or some such.

 

[aytee111]

The format is on the form, purely to remove the time as it isn't needed, and is Short Date, not forcing a format. This should come from the Windows settings. Is it this that is causing the problem?

 

[jdraw]

If you have a date field with time, you can use DatePart() to only show Date.

 

[Minty]

Having read up on this - I think the OP is suffering from this problem https://access-programmers.co.uk/forums/showthread.php?t=255995

Not sure what the cause is though?

 

[aytee111]

That is exactly the issue, many thanks for the link. Seems that it is an Access bug as there was no solution.

 

[Pat Hartman]

I didn't examine the link closely but I'm pretty sure there is a solution.

First, make sure that you have not used the Format() function anywhere in the app to format the date into a string and if you have specified a Format property for a control on a form or report, you have used "Short Date" rather than a specific format unless you need something short such as mm/yy. That should ensure that the Access app respects the Windows date format.

That leaves us the problem of embedded SQL. SQL requires dates be in US format - mm/dd/yyyy. That means, that if you have code that creates SQL Strings or Form/repoet FILTERS, you need to make sure the date is in US format. So in those cases, only, you would use the Format() function to ensure mm/dd/yyyy as the string format.

 

[static]

The easy solution to using dates in SQL is to use the month name. Then order doesn't matter.

#5/12/2017# makes you think.

#5/dec/2017#
#december 5 2017#
#dec-5-2017#
#2017 5 dec#

should all work and leave nothing to the imagination.

It'll work in the filter in question here too, so if you have problems, replace the month with its name and check the filter value, or do, file > save object > as query and look at the generated query's SQL.

That might tell you if the date picker is returning day month in the wrong order at least.

 

[Mark_]

On a testing Continuous form I have, I just added a DATE that is formatted as SHORT DATE. I don't have the same problem under ACCESS 2013, but I've got my machine set to US format for dates. Rather than complaining it does act .... odd... with DDMMYYYY input, but I don't have the same issue with the calendars.

Please let us know which version of ACCESS as well to see if this is something they've fixed recently.

 

[aytee111]

This is Access 2013, Windows 10, 64bit. I am also on US format and it works fine for me, even when I change my date and time settings on my computer.

static, not sure what you are telling me to try:
"That might tell you if the date picker is returning day month in the wrong order at least."
how do I find out what the date picker is returning?

 

[isladogs]

Suggest that you upload the db so one of us in Europe can see if it causes a problem for us and possibly find a solution that will fix your issue.

 

[aytee111]

The culprit is the project start date.

 

[jdraw]

aytee111,
I'm in Canada --we use DD MM YYYY. My regional setting is DD-MMM-YYYY.
Dates in the database look fine, and if I add a completion date via the calendar, it all shows OK.
Maybe I'm missing something....

 

[isladogs]

I'm in the UK - dd/mm/yyyy date format

Just added 2 records. Didn't get an error

Then applied the date between filter - again no errors

Also:
1. I suggest you fix your column headers as the text isn't all visible

2. Out of interest, why are you using a start date with a time but not an end date with a time

 

[static]

UK. No error.

 

[aytee111]

The error happens only on the right-click > filter > between. Then using the date-picker to select dates.

This was a QND - quick and dirty - to provide a way to see the error. Certainly not a database I would give to a user!

User wants to track time between steps, last step completion carries time so no need to carry it here as well.

 

[aytee111]

The link in post # 5 describes the problem exactly, in case I haven't.

 

[isladogs]

You did. So does the link.
I did EXACTLY what you said - no error

EDIT :
Just a thought - for BETWEEN to work as intended you MUST have at least one start &date & end date within that date range.
If not you may get an error - though all I got was your default start date for the new record

 

[aytee111]

Drat. I wonder what can be causing it.

You have all been great, many thanks to all for taking the time to help with this.

 

[Mark_]

Just to make sure, I'd have your user walk YOU step by step through what they are entering (dates included) so you can see if they are doing something silly, like choosing everything that starts in 2021 and ends in 2017...