confusing date handeling

[ano]

vba seems only to understand USA standard even all other MS support non USA standard
dim a,b as date
a=#21/12/1999#
-->
a = #12/21/1999#
b=#1/3/1999#
-->
b = #1/3/1999#
seems its already known many years
but i not really found solution for this


no zip file u need to type in vba ur self to see it happen

[pbaldy]

Does this help?

http://allenbrowne.com/ser-36.html

[Welshgasman]

Use yyyy-mm-dd format. That cannot be interpreted any other way.
When you have 21/12/1999 Access knows that there are not 21 months in the year so it must be in ddmmyyyy format. When you have 01/03/1999 it cannot tell. so assumes mmddyyyy format as that is what it expects.

Just get used to it.

? #2024-01-01#
01/01/2024
? #2024-12-31#
31/12/2024

[ano]

Does this help?

http://allenbrowne.com/ser-36.html

tnx but no tnx . it not fit my filter problem, at least i cannot find/understand the article describe how to filter

Forms!form1.advance_subform.Form.filter = "(format([datum],'yyyy/mm/dd HH:mm:ss') between format(#" & v6 & "#,'yyyy/mm/dd HH:mm:ss') and format(#" & v5 & "#,'yyyy/mm/dd HH:mm:ss'))"
Forms!form1.advance_subform.Form.FilterOn = True

what i think i miss is an option to tell "dd/mm/yyyy" to "yyyy/mm/dd"
i guess underwater the input date is always mm/dd/yyyy unless the month not exist
manipulating with database dates result mostly result in problems

this is even more conusing: Misinterpretation in the User Interface

in office application u have to set ur date or accept system date, so how can date get error if the office application is leading?

enjoy the new year

[ano]

thnx for ur advice , but with few years dd/mm/yyyy its hard to change this into yyyy/mm/dd
i know bec i tried before and stopped looking at the dates problem long time it is known but no solution.

thnx enjoy the new year

[Welshgasman]

i guess underwater the input date is always mm/dd/yyyy unless the month not exist

No, I already told you in other threads.

Dates are stored as numbers. Before the decimal point is the date, after the decimal point is the time.
How you see it/use it depends on your regional settings.
However if you need to use it as a string, then it has to be either yyyy-mm-dd or mm/dd/yyyy. The latter can be ambiguous, the former cannot.

There are rules in programming. That is just one for Access and Excel VBA.

[Welshgasman]

tnx but no tnx . it not fit my filter problem, at least i cannot find/understand the article describe how to filter

Forms!form1.advance_subform.Form.filter = "(format([datum],'yyyy/mm/dd HH:mm:ss') between format(#" & v6 & "#,'yyyy/mm/dd HH:mm:ss') and format(#" & v5 & "#,'yyyy/mm/dd HH:mm:ss'))"
Forms!form1.advance_subform.Form.FilterOn = True

what i think i miss is an option to tell "dd/mm/yyyy" to "yyyy/mm/dd"
i guess underwater the input date is always mm/dd/yyyy unless the month not exist
manipulating with database dates result mostly result in problems

this is even more conusing: Misinterpretation in the User Interface

in office application u have to set ur date or accept system date, so how can date get error if the office application is leading?

enjoy the new year

Put your filter into a string variable and then you can debug.print it and get it correct.
Once correct, you can use that in the filter.

Much much easier.
Same applies to SQL string.

[moke123]

You can also use a constant for the formatting.

Const JetDte As String = "\#mm\/dd\/yyyy\#"
Const JetDte2 As String = "\#mm\/dd\/yyyy hh\:nn\:ss\#"

?Format("13/1/2024",JetDte)
#01/13/2023#

[Welshgasman]

You can also use a constant for the formatting.

Const JetDte As String = "\#mm\/dd\/yyyy\#"
Const JetDte2 As String = "\#mm\/dd\/yyyy hh\:nn\:ss\#"

?Format("13/1/2024",JetDte)
#01/13/2023#

Now that is clever. It even changes the year?

? cdbl(date)
 45293
? format(45293,"\#mm\/dd\/yyyy\#")
#01/02/2024#

I am in the UK.

[davegri]

You can also use a constant for the formatting.

Const JetDte As String = "\#mm\/dd\/yyyy\#"
Const JetDte2 As String = "\#mm\/dd\/yyyy hh\:nn\:ss\#"

?Format("13/1/2024",JetDte)
#01/13/2023#

Nice. Instead of constant you can also use a function:

'---------------------------------------------------------------------------------------
' Method : fcnGetDelimDate
' Author : davegri & moke123
' Date   : 01/02/24
' Purpose: Returns delimited(#) date as string
'   arg can be such as
'   "2/2/22"
'   "2/2/2022"
'   dateadd("m",1,"2/2/22")
'   "41234"
'   41234
'   Date()
'
'   arg cannot be
'   2/2/22 or 2/2/2022  ( resolves as #12/30/1899# after the division arithmetic completes )
'---------------------------------------------------------------------------------------
Public Function fcnGetDelimDate(arg As String) As String
    Dim JetDte As String: JetDte = "\#mm\/dd\/yyyy\#"
    Dim JetDte2 As String: JetDte2 = "\#mm\/dd\/yyyy hh\:nn\:ss\#"
    fcnGetDelimDate = Format(arg, JetDte)
End Function

[Welshgasman]

How does that get to use JetDte2? if a date and time is passed?

[davegri]

How does that get to use JetDte2? if a date and time is passed?

Make another function that uses JetDte2

[orange]

How about

'---------------------------------------------------------------------------------------
' Method : fcnGetDelimDate
' Author : davegri & moke123
' Date   : 01/02/24
' Purpose: Returns delimited(#) date as string
'   arg can be such as
'   "2/2/22"
'   "2/2/2022"
'   dateadd("m",1,"2/2/22")
'   "41234"
'   41234
'   Date()
'
'   arg cannot be
'   2/2/22 or 2/2/2022  ( resolves as #12/30/1899# after the division arithmetic completes )
'---------------------------------------------------------------------------------------
Public Function fcnGetDelimDate(arg As String) As String
    Dim JetDte As String: JetDte = "\#mm\/dd\/yyyy\#"
    Dim JetDte2 As String: JetDte2 = "\#mm\/dd\/yyyy hh\:nn\:ss\#"
    If InStr(arg, ":") = 0 Then
        fcnGetDelimDate = Format(arg, JetDte)
    Else
        fcnGetDelimDate = Format(arg, JetDte2)
    End If
End Function

[Welshgasman]

I seem to be missing the point here?
If the date is ambiguous, then you can still have a bad date?

Most times I would be passing an actual date, that I needed to be formatted as a string for concatenation for a filter or sql string.

[ArviLaanemets]

As Welshgasman stated in post #6, dates are really numbers. As follows, when you cave to cope with dates from different local settings, you will skip all this formatting nonsense, when you handle those dates as numbers.

Examples:
You have a table MyTable with a field MyDate formatted as date (in whatever format you want);
When running a query <INSERT INTO ByTable (MyDate) VALUES (10000)>, a date for May 18th of year 1927 is inserted (in format you used);
When after that run a query <SELECT MyDate FROM MyTable WHERE MyDate = 10000>, the rows of MyTable with MyDate = May 18th of year 1927 are displayed;
When run a query <SELECT 1*MyDate FROM MyTable>, numeric values of all dates in MyTable are displayed;
Etc.

The only case when this doesn't work directly is, when your Access doesn't recognize one of the read dates as a date. When this is the case, I usually read numeric values for year, month, and day for such dates, and compose a date the Access recognizes as such.