date format problem

[benjamin.weizmann]

hi
I do have queries which add date in this format:

", #" & Format(Now, "dd/mm/yyyy hh:nn:ss") & "#,"

but in the tables I see it as mm/dd/yyyy hh:nn:ss

i'm begging - how can I fix it and avoid it when use in any computer?

thanks
Ben

 

[isladogs]

Check the default date settings on your computer.

If you are using code you must format dates in the US format mm/dd/yyyy
The values will be stored as numbers but will be displayed in your default format unless you tell it to use a different format

Hope that makes sense

 

[benjamin.weizmann]

Check the default date settings on your computer.

If you are using code you must format dates in the US format mm/dd/yyyy
The values will be stored as numbers but will be displayed in your default format unless you tell it to use a different format

Hope that makes sense

hi thanks
there is not any other way to do it?
this database uses by many users in any computer

 

[CJ_London]

To add to Colin's comments, dates are stored as decimal numbers. The value before the dp is the date expressed as a number starting from 0. 0 is 30th December 1899 and today is 43040. Time is after the dp and is a value based on the time now in seconds divided by the number of seconds in a day (86400). So 6am would be 0.25, 6pm 0.75.

The date you see is just a formatted view, either specified by yourself or per the (windows) system international/time settings

 

[benjamin.weizmann]

I don't cate about format but....
when I do DOA recordset
it's show wrong data - and the calculate dateadd or diffate gives wrong!

strSQL = "SELECT * FROM fault_diary WHERE [machinenum]=" & Me.Listmach.Value & " AND [date_start_fault]<=#" & currnow & "# AND ([date_repair_fault]IS NULL OR [date_repair_fault] >=#" & DateAdd("h", -24, currnow) & "#) ORDER BY ID_fault;"


Set rstfault = dbsc.OpenRecordset(strSQL, dbOpenDynaset)

If rstfault.EOF Or rstfault.BOF Then GoTo cont

With rstfault
.
.
.
.
If rstfault![date_start_fault] < DateAdd("h", -24, currnow) Then
=11/01/2017 15:41 (instead 01/11/2017) < 31/10/2017 16:00
effstart = DateAdd("h", -24, currnow)
Else
effstart = rstfault![date_start_fault]
End If
If IsNull(rstfault![date_repair_fault]) Or rstfault![date_repair_fault] > currnow Then
effend = currnow
Else
effend = rstfault![date_repair_fault]
End If

diffh = DateDiff("n", effstart, effend) / 60 <- gives wrong!!!!

 

[isladogs]

See my previous comments about formatting dates in VBA code.

You need something like

=#" & Format(Date,"mm/dd/yyyy") & "#

 

[benjamin.weizmann]

See my previous comments about formatting dates in VBA code.

You need something like

=#" & Format(Date,"mm/dd/yyyy") & "#

thanks
but see my first post, it's what I did

 

[arnelgp]

date should be saved in mm/dd/yyyy format
if you are saving your date in dd/mm/yyyy format
chances are, youll get month and date
interchanged when got saved.

you try this on sample table with
blank record: todays date nov 1, 2017

currentdb.execute "insert into sample_table(dateField) select #" & format(date, "dd\/mm\/yyyy") & "#"

result: jan 11, 2017.

 

[isladogs]

thanks
but see my first post, it's what I did

I've read your first post again and that's not what you wrote.

 

[benjamin.weizmann]

I've read your first post again and that's not what you wrote.

ok
thanks


I still believe it's not the main cause
cause I used this database almost 2 month and I never met with this problem

 

[isladogs]

Trust me. Failing that do a site search or Google search.
It is a fundamental cause though you may have others.

If you don't explicitly format using mm/dd/yyyy you can't be sure whether you will get the correct results
E.g 10/10/2017 will work as 10 Oct regardless
11/10/2017 will be read as 10 Nov as Access assumes the format as mm/dd/yyyy.
14/10/2017 may well be read as 14 Oct as it can't be anything else. However it won't always do so.

Because its so unreliable just hoping it will work, its not worth the risk.

 

[Pat Hartman]

CJ already mentioned that dates are stored as double precision numbers NOT STRINGS.

1. Format(....) changes a date data type to a string so it will no longer compare correctly or sort correctly. For example 01/01/2017 will be less than 12/31/2016 because strings are compared/sorted character by character, left to right.
2. Access uses your Windows system date settings to determine how to display dates for human consumption. This has nothing to do with how they are stored. So, unless you specifically use the Format property or the Format() function, dates will be displayed based on the Windows settings. I suggest that you never apply a date format to the date field in the table. Formatting data at the table level obfuscates the actual value and you could spin your wheels for days researching a problem and never realize that the data isn't what you think it is. Only format controls on reports or forms and ONLY if you need the format to be different from the standard Windows setting.
3. If you are NOT using standard US date format as your Windows setting - mdy order, then, when you pass a string date to a query, it MUST be formatted using Format() into mdy or ymd order. This is an SQL rule and it probably exists because the server may be in an area of the world where date formats are different and a choice had to be made. Either use US standard mm/dd/yyyy or something non-ambiguous such as yyyy/mm/dd. and "they" chose door #1.
4. NEVER use a formatted date field in an expression. Always use the raw datetime value (except where noted earlier and you are specifically passing a STRING date to SQL).

 

[benjamin.weizmann]

CJ already mentioned that dates are stored as double precision numbers NOT STRINGS.

1. Format(....) changes a date data type to a string so it will no longer compare correctly or sort correctly. For example 01/01/2017 will be less than 12/31/2016 because strings are compared/sorted character by character, left to right.
2. Access uses your Windows system date settings to determine how to display dates for human consumption. This has nothing to do with how they are stored. So, unless you specifically use the Format property or the Format() function, dates will be displayed based on the Windows settings. I suggest that you never apply a date format to the date field in the table. Formatting data at the table level obfuscates the actual value and you could spin your wheels for days researching a problem and never realize that the data isn't what you think it is. Only format controls on reports or forms and ONLY if you need the format to be different from the standard Windows setting.
3. If you are NOT using standard US date format as your Windows setting - mdy order, then, when you pass a string date to a query, it MUST be formatted using Format() into mdy or ymd order. This is an SQL rule and it probably exists because the server may be in an area of the world where date formats are different and a choice had to be made. Either use US standard mm/dd/yyyy or something non-ambiguous such as yyyy/mm/dd. and "they" chose door #1.
4. NEVER use a formatted date field in an expression. Always use the raw datetime value (except where noted earlier and you are specifically passing a STRING date to SQL).

so much thanks - it's what I need, to understand the rationale
but now, how can I show in forms, subforms dates as dd/mm/yyyy ?!

thanks!
Ben

 

[CJ_London]

in the format property of the relevant control put either

shortdate

or

dd/mm/yyyy

properties can be found by clicking on the property option on the ribbon when in design view

 

[benjamin.weizmann]

thanks u so much
last question please,
do I need to covert to format yyyy/mm/dd also in this case:


strSQL = "SELECT * FROM fault_diary WHERE [machinenum]=" & Me.Listmach.Value & " AND [date_start_fault]<=#" & currnow & "# AND ([date_repair_fault]IS NULL OR [date_repair_fault] >=#" & DateAdd("h", -24, currnow) & "#) ORDER BY ID_fault;"

???

thanks

 

[isladogs]

Just use the same ideas for handling dates throughout your database.
In this case, use

Format(MyDateValue,"yyyy/mm/dd")

 

[benjamin.weizmann]

please be practice
do u meant I need to covert even if it select sql? even without any edit?

 

[isladogs]

please be practice
do u meant I need to covert even if it select sql? even without any edit?

Sorry - not sure what you mean
You need to handle all date values in VBA as described earlier

 

[benjamin.weizmann]

did u see my question on select sql statement?
it's above
I thought u answered to it

 

[isladogs]

Yes I thought I answered it as well!