Date Problem (1 Viewer)

[David44Coder]

Is there an overriding date Format in Access? My table data type and bound textbox property are 'dd mmm yyyy' and that's how it shows in the Form.
But if I read the value from the Form, (or from the Table with Dlookup) there's a 3 character day and comma added e.g.'Thu, 13 Apr 1967'.
I've not asked for it to be like that. Is it the expected behavior?
If yes, how might it best be converted back to dd mmm yyyy ?
Thank you.

 

[June7]

Date/time is stored in Date/Time type field as a double number. Access display default is mm/dd/yyyy hh:nn:ss am/pm. If you get anything different then a format has been applied somehow. Siince you want dd mm yyyy, which is non-U.S. structure, review http://allenbrowne.com/ser-36.html

 

[David44Coder]

Thank you June, I've read a bit about this and I think the problem is related to the Windows regional settings... I tried this same Dlookup test in a different computer and it printed dd/mm/yyyy. In both cases the format was that set in Control Panel.
How might I get the date into VBA code without the 3 letter day because if present the Format command does not work ? At the moment I'm using Split(Date, ", ")(1) but I feel must be a more correct way ?

 

[isladogs]

The Access display defaults to whatever you have in your Windows date settings which is normally setup to match date default for your location e.g. dd/mm/yyyy in the UK and most of Europe.
Either change your settings or (re)format the date to match your desired output.

 

[David44Coder]

> Either change your settings or (re)format the date to match your desired output.
The latter is what I'm attempting but the Format command does not seem to return the right value e.g.
d1 = "Thu, 12 May 1987"
Debug.Print Format(d1, "dd mmm yyyy")
= Thu, 12 May 1987

d2 = "12 May 1987"
Debug.Print Format(d2, "dd mmm yyyy")
= 12 May 1987

If I could remove the "Thu, " part Format works as expected, whether it's a string or a date.

xd = DLookup("[MyDate]", "tblMain", "MyF1= 'Test'")
Debug.Print xd
= Sat, 14 Nov 1987
Debug.Print Format(xd, "dd mmm yyyy")
= 14 Nov 1987

Ideally Date would be a date variable and this is where I come unstuck. I'm reading it from a Form text box. So it's text, right?
And I pass it via me.OpenArgs

xd = CDate(Split(str$(Me.OpenArgs), "#")(0))
Debug.Print xd
Debug.Print Format(xd, "dd mmm yyyy")
This gives an error (no matter how I try it).

 

[isladogs]

Text boxes can hold a variety of datatypes including number, date, currency etc
However, it does sound like it may be a text field

d1 = "Thu, 12 May 1987" is a string not a date

Is this a bound textbox? If so, check the field datatype is DateTime in the underlying table.
If it is date/time, check the Format property for the field.

Using the format ddd", "dd mmm yyyy will show dates as e.g. Sun, 05 Jun 2022.
Either remove the format to use the default settings or change it to your preferred format dd mmm yyyy

See attached

 

[June7]

If I could remove the "Thu, " part Format works as expected, whether it's a string or a date.

xd = DLookup("[MyDate]", "tblMain", "MyF1= 'Test'")
Debug.Print xd
= Sat, 14 Nov 1987
Debug.Print Format(xd, "dd mmm yyyy")
= 14 Nov 1987

This example doesn't make sense to me. Format(xd, "dd mmm yyyy") should return Sat, 14 Nov 1987.

 

[Pat Hartman]

Ideally Date would be a date variable and this is where I come unstuck. I'm reading it from a Form text box. So it's text, right?

If the textbox is bound to a date field, it is a date data type. If the control is unbound, it is a string. To make an unbound control be a non-string format, add a format property so that Access knows what you want.

 

[David44Coder]

Appreciate the feedback, thank you.
isladogs, The textbox is bound to a table, where the Field is a Date type formatted 'dd mmm yyyy'. That format is also in the text box properties.

I read it from the textbox with
Dim sd As Date
sd = Me.[TheDate]
and send this to a Form via OpenArgs. VarType tells me OpenArgs is 8 = String so should be good as a string is wanted.

A Google example shows
Dim myDate As Date, strDate As String
myDate = CDate("2021-01-28")
strDate = Format(myDate, "YYYY-MM-DD")

"2021-01-28" is also a string,so I tried to use
myDate = CDate(me.OpenArgs) Effectively myDate = CDate("Sat, 03 Jul 1937")
But CDate errors with Type Mismatch unless I use = CDate("03 Jul 1937")

But simply removing the day & comma from the string might fail with other, different regional settings?
As the date is a long number, can I get that and use it to build the desired string ?

 

[David44Coder]

I think I sorted it.. however must be in a module, not code in Form.

Function Caller2(i) As String
    If IsDate(i) Then
        Caller2 = Right("0" & Day(i), 2) & " " & MonthName(Month(i), True) & " " & Year(i)
    End If
End Function

 

[arnelgp]

i was thinking you don't need any code.
bring your Form in Design view.
click on the textbox you want to format.
on the Property Pane (right), Format tab->Format, put

dd mmm yyyy

 

[David44Coder]

It's like that already Arnelgp but *still* returned the leading day and comma.
I was surprised... also tried Cstr but no change.

 

[Pat Hartman]

You are obsessed with format. Formatting a date is something we do for people. Internally, to work with a date, we do not format it. Formatting a date changes it to a string and that means when you are using it in code, it acts like a string and not a date.

If you are passing a date via OpenArgs, DO NOT FORMAT it first. Just pass the date field. Then at the other end, you can work with it in code as a date or you can put it in a control on the form/report. If the control is bound to a date data type or if unbound, has a date format, it will still act like date. If you want it to be formatted differently than the Windows date default, then add a format property.

The format property of a control is different from the Format() function. The format property of a control tells Access how a human wants to see the control but it doesn't change what it is internally. The Format() function changes the date from a double precision number to a string so it will no longer act like a date in most cases.

A date formatted the way you are describing is "long date" format. Are you sure that isn't the format you picked for the control.

If your users have different Windows date format defaults and you can't change that, then you have to handle the issue by using date formats on EVERY control that holds a date. DO NOT use Format(). Just bite the bullet and add the the format to every date field on every form or report. The only times you need to use Format() are:
1. If you are exporting to Excel and you don't want to leave it to Excel's default format.
2. If you are constructing an SQL string in VBA and you need to add date criteria. The problem here is that SQL assumes standard US date format of mm/dd/yyyy but much of the world defaults to dd/mm/yyyy. If there is no ambiguity, there is no problem but for dates like 1/2/2022, SQL assumes this is Jan 2nd so if you want it to be Feb 1st, you need to format the date to either mm/dd/yyyy or yyyy/mm/dd in the SQL string.

 

[David44Coder]

Hello Pat, Yes I needed a string as it's expected that way. And I wanted to understand what was going on and why I could not reformat it. Ideally it would be a date and problem gone, but too many changes would have been required.
I believe the Windows regional settings were overriding Access ... I didn't want to alter this, but did try the db on another computer with different regional settings and that seemed to confirm it. Although both the table and control properties were "dd mmm yyyy" Access would only show it as a long date (which afaik, Format() would not work on). It remains a date (now) until reaching the code that required it as a string. It's a routine I did not write myself.

 

[Pat Hartman]

The point I was trying to make is to always keep a date as a date until the last possible moment when you need it to be a string And if you don't actually need it to be a string, use the format properties of controls to specify how it should be displayed while still keeping the field as a date.

I've never seen a case where the Windows date settings overrode the Access property settings on controls. If you were having trouble, perhaps it was caused by formatting and using a string instead of a date.