Date Problem

[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.

 

[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.

 

[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.