Can some explain this in dates?

[twgonder]

I've read dozens of blogs on dates and walk away each time more confused than ever.
This may be a numbnut question, but I keep coming back to this and am baffled.

I'm using the Colombia region settings, so that means d/mm/yyy.

If I do this in the immediate window:

?now
14/09/2023 10:38:45 p. m. 


?#1-2-3#
2/01/2003 


?cdate("1/2/3")

1/02/2003

Do we use the "#" in VBA for other than building SQL statements?
Why did the second example flip the date and month (if it's not only for SQL in a USA format)?

[Minty]

By default Access manipulates dates in US format mm/dd/yyyy.
As you can imagine this is very confusing for any other locale.

This is where it gets silly - Any Date presented between # # delimiters are assumed to be in mm/dd/yy format, UNLESS it can't be e.g. #23/11/23#
In this case Access "helpfully" understands that it must be the 23-Nov-2023. You can see this effect in the query builder and the immediate window.

The best way to circumvent this behaviour is to create a function that forces all dates into a unambiguous format when you are building SQL statements- and I prefer #yyyy-mm-dd# which works in every locale and on SQL server if you ever go there.
This is a variant of the Allen Browne function:

Function ServerDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            ServerDate = Format$(varDate, "'yyyy-mm-dd'")
        Else
            ServerDate = Format$(varDate, "'yyyy-mm-dd hh:nn:ss'")
        End If
    End If
End Function

The other benefit of this format is that during debugging, there is no misunderstanding or wondering what format the date being passed around is in.

[CJ_London]

# tells sql that the string between them is to be treated as a date, it works in vba as well. In the case of an ambiguous string, it defaults to the US format/value of mm/dd/yyyy.

so this in unambiguous because there are not 15 months in the year
?#15-09-2023#
15/09/2023

this is ambiguous, so defaults to US format
?#10-09-2023#
09/10/2023

In VBA you can use the # in a calculation although personally, I wouldn't



?#15-09-2023# = date()
True

but only works if the string is unambiguous - so this calc fails (which is why I wouldn't)
?#10-09-2023# = (date()-5)
False

So your example of #1-2-3# is ambiguous (but see below for another issue)

cDate on the other hand returns the date value in the format per your windows settings

?cdate("10-09-2023") = (date()-5)
True

be aware that your test data is not really valid for using #. For example an 'unambiguous' day of say 15 treats the 15 as being the year (per sql standard of yyyy-mm-dd





?#15-1-3#
03/01/2015

whereas with cdate

?cdate("15/1/3")
15/01/2003

Edit - I'm with Minty - I always format to yyyy-mm-dd if I need to use # in sql

[twgonder]

@ Minty & CJ_London Thanks, and I saw most of this in the blogs of Richard Rost, Mike Wolfe and Philipp Stiefel. I decided on the ISO format long ago, displaying in the German format (which has me going nuts in testing when entering in this format) and I'm trying to work theirs and something similar to Minty's into my own set of routines that pass back a global error for logic checking. My head started spinning when consolidating all the blogs, considering, entry vs. query vs. VBA, Nulls and now ZLS in dates, which no one seems to consider. Then there is the user that may refuse to enter a date in an ISO format, and insists on using their regional format which then may confuse my logic as well as Access. The permutations had me going bonkers especially after 12 hours of this and writing functions. Add to that, I was modifying all my code to accommodate Mike Wolfe's DocTests function that needs modification for my global err idea. Aggghhhh!

For example, the modified Allen Browne code ignores Nulls and ZLS, while the Mike and Phillipp solutions ignore ZLS.

[CJ_London]

Then there is the user that may refuse to enter a date in an ISO format, and insists on using their regional format which then may confuse my logic as well as Access.

You are aware that dates are stored as doubles, all the different views of a date is just a format

?cdbl(date())
45184
?cdbl(now())
45184.4956597222

the folowing works because the date is not ambiguous
?cdbl(#15/9/2023#)
45184
?cdbl(#09/15/2023#)
45184

this doesn't for dd/mm formats because it is ambiguous
?cdbl(#12/9/2023#)
45269

but these do
?cdbl(cdate("12/9/2023"))
45181

?cdbl(cdate("2023-09-12"))
45181

?cdbl(cdate("12/9/23"))
45181



?cdbl(cdate("12-9-23"))
45181




so it should not matter what format they enter a date in. It has to be a valid format for the region (I would suggest that '1/2/3' is not valid) and in the appropriate date range which is standard validation. If you want to allow users to be sloppy in not using a proper format, that is down to you.

Only time I can see it being an issue is if you have users in different time zones and the date needs to be consistent to one time zone - in which case you would need to adjust the entered date by adding/subtracting the appropriate number of hours to get to a single time zone (seem to recall you had a question on that some time ago). Or if all users are are on the same server, use the server time.

[Minty]

By default, Access will recognise the local regional date format if it is input on a form provided the field is bound to a date.

So in the UK 2/3/23 on a form or even 2/3 is correctly interpreted, and stored as 02-March-2023 (Or more accurately is stored as 44987.00)


It is only when taking that date/ form value and doing something in VBA that you need to be aware of how it is handled.

[twgonder]

@ CJ_London & Minty, posts #5 & 6. Yes, I know how dates are stored in Access, that isn't the problem. And yes, I have multiple servers, each with a time offset from the main server. Users can connect to different servers via Terminal from different time-zones using different regional settings. I've burned out a lot of brain cells on that problem.

If access allows "1/2/3" then who am I to say (to a user with their particular region and manner of working) it's not valid? Confusing yes, but I have to deal with that confusion. In addition, I have to get users to think in terms of a total time, not just the date. Some blogs on doing proper Between dates (not using "Between") abound, but that's more a developer problem than a user issue.

For now, the brain numbing has been caused by the "#", where it's properly used, Nulls and ZLS and what VBA and ACE need. I keep getting hints that it gets more complicated with SQL server, and I'm not sure if it gets even more complicated using other than MS SQL Server, like Azure.

I am working mostly in VBA now, where one isn't likely to hard code a date. In queries though, dates are often "hard coded".

Thanks again for your ideas, it helps me to sort out all the confusing concepts to see how others deal with them.

[Minty]

As far as I'm aware a bound date control can't accept as ZLS.

[CJ_London]

who am I to say (to a user with their particular region and manner of working) it's not valid?

you're the developer, that's who. If your client states that's what they want, then you need clarity on all the different ways a user is allowed to enter the data. Who's to say 1/2/3 means 2003 and not 1903 or 2013 or 2023? or any other year that ends in a 3. Perhaps look at some of the other apps these users are using, might give you a clue.

This is not an access problem, it will apply to any language - perhaps you can point to a website where a user can enter 1/2/3 and it produces the correct date, whatever that may be.

Good luck, but without a clear definition of what 1/2/3 means, there is nothing you can do and nothing more I can add

[twgonder]

Let me take a moment to share what I learned from my experimenting in query builder.
I created a simple table. In ID 3 I just tabbed through the entry. In ID 4 I entered "" for a ZLS, thinking that I might want to say "The date doesn't exist" rather than "The date is unknown". I wasn't sure how Access was going to deal with this.

Here's the query design:

Attachment 50763

And the result:

Attachment 50764

Adding to the confusion is that Access allows a ZLS entry, but apparently changes it to a Null.

Mike Wolfe offered this code for dates:

Function Dt(DateVal As Variant) As StringConst Pound As String = "#"
    If IsNull(DateVal) Or IsEmpty(DateVal) Then
        Dt = "Null"
    Else
        Dt = Pound & DateVal & Pound
    End If
End Function

but I'm not sure how "Null" is going to work in a query, because the above test needs "Is Null", but since Mike didn't include an example using Null, I'm not sure I understand what he was doing.

[CJ_London]

neither attachments are attached - on this forum you can't copy/paste.

you can try

if nz(dateval,"")="" then

[twgonder]

neither attachments are attached - on this forum you can't copy/paste.

I used "Insert Image" from the toolbar. Is this the wrong way to add an image?

Here, I'll try again the same way:





Before saving, it looks good in what I'm seeing.

When I look at post #10, I can see links for the attachments, and they work. I don't always get the same result when posting images, and I'm not sure why.

[Minty]

As I said you can't store a ZLS in a date field, it will simply be a Null

[twgonder]

I'm testing my Mike inspired function, called fDt, and it returns this:

?fdt("2003-2-1")
#2003-02-01 00:00:00#

Yet, when I use it in a query criterion, it errors out:



After note: As best as I can figure out, Mike returns a string. So, I'm guessing the function can't be used straight-up in a query, that he meant it only to be used in building VBA SQL strings. I'm guessing that it's also true for his other quoting and date between functions. That makes testing the functions in a query a bit more difficult if not impossible.

[twgonder]

As far as I'm aware a bound date control can't accept as ZLS.

It accepted it (in a table entry) without a problem. What it does with it is another issue.
I'll go test a form.

After note: Entered "" just fine in a form. Windows 11 with Access 2021.