confusing date handeling

[ano]

nice story seems realistic, but i guess many readers need examples from what u telling


btw i have new problem my pc crashed and using my friends pc , i open my last form and got ## in my date fields except in the imported table
seems that excel import create different table structure then vba .
so na my import is dd/mm.. and my new tables created from import are mm/dd...
thats probably reason why filter not work on imported table

[Welshgasman]

open my last form and got ## in my date fields

Like this you mean ?

[ano]

Like this you mean ?

yes

but 1st time it was mm/dd na its dd/mm but not 24hours thats why ###

im still thinking what format has to do with compare date , compare date as string is not logic because often counting in the dates
btw i just debug.print filter without problems. seems changing pc needs more time for sync without warning/mention it.
expected those are in datebase not in access, because share with more people.

[Welshgasman]

That # is just because the column is not wide enough to show the value?
That happens to ANY column that is not large enough to show the value, nothing to do with dates.

[ano]

tnx i know that but the size was already set correct, just the other pc changed from HH to hh ( 24/am-pm)
just finding a way to keep my events and change back to HH

[moke123]

i just debug.print filter without problems.

And could you show us what it resolved to?

[ano]

And could you show us what it resolved to?

few debugs:
Debug.Print "v5:", v5, Forms!form1.rates_subform.Form.filter
Debug.Print v6, v5, Forms!form1.advance_subform.Form.filter 'between


Debug.Print "v4:", v4, Forms!form1.rates_subform.Form.dt.Value, Format(Forms!form1.rates_subform.Form.dt.Value, "yyyy/mm/dd HH:mm:ss")
Forms!form1.rates_subform.Form.filter = "(format([dt],'yyyy/mm/dd HH:00:00') = format(#" & v4 & "#,'yyyy/mm/dd HH:00:00'))"
Forms!form1.rates_subform.Form.FilterOn = True
Debug.Print "dt:", Forms!form1.rates_subform.Form.dt.Value, ";", Forms!form1.rates_subform.Form.filter
i never had am/pm in my spreadsheets / databases just now on other pc dd/mm am/pm (its an recent insider preview version) most look quite nice just the am/pm bothers me. and need to find out why sometimes only time no date
v4: 16/12/2023 8:00:00 am 01/01/2024 8:00:00 am 2024/01/01 08:00:00
dt: 16/12/2023 8:00:00 am ; (format([dt],'yyyy/mm/dd HH:00:00') = format(#16/12/2023 8:00:00 am#,'yyyy/mm/dd HH:00:00'))
v5: 16/12/2023 8:00:00 am (format([dt],'yyyy/mm/dd HH:00:00') = format(#16/12/2023 8:00:00 am#,'yyyy/mm/dd HH:00:00'))
01/12/2023 8:00:00 am 16/12/2023 8:00:00 am (format([datum],'yyyy/mm/dd HH:mm:ss') between format(#01/12/2023 8:00:00 am#,'yyyy/mm/dd HH:mm:ss') and format(#16/12/2023 8:00:00 am#,'yyyy/mm/dd HH:mm:ss'))
v4: 16/12/2023 8:00:00 am 16/12/2023 8:00:00 am 2023/12/16 08:00:00
dt: 16/12/2023 8:00:00 am ; (format([dt],'yyyy/mm/dd HH:00:00') = format(#16/12/2023 8:00:00 am#,'yyyy/mm/dd HH:00:00'))
v5: 16/12/2023 8:00:00 am dt = null
01/12/2023 8:00:00 am 16/12/2023 8:00:00 am (format([datum],'yyyy/mm/dd HH:mm:ss') between format(#01/12/2023 8:00:00 am#,'yyyy/mm/dd HH:mm:ss') and format(#16/12/2023 8:00:00 am#,'yyyy/mm/dd HH:mm:ss'))
v5: 16/12/2023 8:00:00 am dt = null
01/12/2023 8:00:00 am 16/12/2023 8:00:00 am (format([datum],'yyyy/mm/dd HH:mm:ss') between format(#01/12/2023 8:00:00 am#,'yyyy/mm/dd HH:mm:ss') and format(#16/12/2023 8:00:00 am#,'yyyy/mm/dd HH:mm:ss'))
v4: 16/12/2023 8:00:00 am 16/12/2023 8:00:00 am 2023/12/16 08:00:00
dt: 16/12/2023 8:00:00 am ; (format([dt],'yyyy/mm/dd HH:00:00') = format(#16/12/2023 8:00:00 am#,'yyyy/mm/dd HH:00:00'))
v4: 16/12/2023 8:00:00 am 01/01/2024 8:00:00 am 2024/01/01 08:00:00
dt: 16/12/2023 8:00:00 am ; (format([dt],'yyyy/mm/dd HH:00:00') = format(#16/12/2023 8:00:00 am#,'yyyy/mm/dd HH:00:00'))
v5: 16/12/2023 8:00:00 am (format([dt],'yyyy/mm/dd HH:00:00') = format(#16/12/2023 8:00:00 am#,'yyyy/mm/dd HH:00:00'))
01/12/2023 8:00:00 am 16/12/2023 8:00:00 am (format([datum],'yyyy/mm/dd HH:mm:ss') between format(#01/12/2023 8:00:00 am#,'yyyy/mm/dd HH:mm:ss') and format(#16/12/2023 8:00:00 am#,'yyyy/mm/dd HH:mm:ss'))
v4: 16/12/2023 8:00:00 am 16/12/2023 8:00:00 am 2023/12/16 08:00:00
dt: 16/12/2023 8:00:00 am ; (format([dt],'yyyy/mm/dd HH:00:00') = format(#16/12/2023 8:00:00 am#,'yyyy/mm/dd HH:00:00'))
v4: 31/12/2023 01/01/2024 8:00:00 am 2024/01/01 08:00:00
dt: 04/01/2024 8:00:00 am ; (format([dt],'yyyy/mm/dd HH:00:00') = format(#31/12/2023#,'yyyy/mm/dd HH:00:00'))
v4: 31/12/2023 01/01/2024 8:00:00 am 2024/01/01 08:00:00
dt: 04/01/2024 8:00:00 am ; (format([dt],'yyyy/mm/dd HH:00:00') = format(#31/12/2023#,'yyyy/mm/dd HH:00:00'))
v5: 04/01/2024 8:00:00 am (format([dt],'yyyy/mm/dd HH:00:00') = format(#31/12/2023#,'yyyy/mm/dd HH:00:00'))
12:00:00 am 04/01/2024 8:00:00 am (format([datum],'yyyy/mm/dd HH:mm:ss') between format(#12:00:00 am#,'yyyy/mm/dd HH:mm:ss') and format(#04/01/2024 8:00:00 am#,'yyyy/mm/dd HH:mm:ss'))
on ur request

[moke123]

Looks like you have delimiters wrong.

A filter for a form should look something like

strFilter = "Datum = " & format(somefield, JetDte)

or

strFilter = "Datum between " & format(somefield, JetDte) & " and " & format(anotherfield, JetDte)

[ano]

me.strfilter="inventory_date=#12/13/1999#" to avoid dd/mm problems, dateparts if still have problems
me.strfilter="invenrory_date=#" & format('13/12/1999','mm/dd/yyyy') & "#"
me.strfilter="inventory_date=#" & format(rst.fields(0),'mm/dd/yyyy') & "#"
or
me.strfilter="inventory_date=" & format(rst.fields(0),'\#mm\/dd\/yyyy\#')

the problem understanding is what jetdte and somefield are defined (jetdte as string, somefield as date)
so if rst.fields(0) not a date field then it need between # to

[Bulzie]

Holy post explosion Batman!!!

[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

solving the problem with date creating records with vba dont use vba but use (no twisting dd/mm any more)
p1 = rs.Fields(i + 1).Name 'nick name
p2 = rs.Fields(i).Name 'field name, the earn
st = "INSERT INTO EARN SELECT Sheet1.[Field1] AS dt,'" & p1 & "' AS md, Sheet1.[" & p2 & "] AS mn FROM sheet1 WHERE nz(sheet1.[" & p2 & "],0) > 0;"
db.Execute st
this has no format problem

[Welshgasman]

Only b is a date type.

[ano]

Only b is a date type.

yeah but twisting dd/mm is very disturbing and format is not the easiest way to solve.
as u can see sql is not complex
tnx for all support