Carry forward a date in Aus short date format

[Dave]

I'm carrying forward a date in a data entry form by using an after-update
procedure to set the default value.

Private Sub txtRecDate_AfterUpdate()
If Not IsNull(Me.txtRecDate.Value) then
txtRecDate.DefaultValue = "#" & Me.txtRecDate & "#"
End If
End Sub

But as is pointed out in one of Allen Browne's tips, VBA date setting always
assumes US format.
So I enter e.g. 2/7/08, which stores as 2/07/2008, but the next record comes
up as 7/02/2008.

There is some sample code to use an SQL statement to force all dates into US
format on Allen's website, but I'm wondering if I can easily incorporate
some code into the above subroutine to force the carried forward date into
Aus format.

Dave Oakley

 

[david]

Yes, you can use code like that in your subroutine, but better
would be this:

txtRecDate.DefaultValue = Me.txtRecDate

The string concatenation using "&" forces the date to
convert to a string. "#", converts it from a string back
to a date. Leave it all out it remains as a date/time/float

(david)

 

[Dave]

Tried that, David.
Whatever date I initially enter, it carries forward as 30/12/1899

????

dave

 

[Ken Sheridan]

Dave:

The DefaultValue property is always a string expression regardless of the
data type of the control in question, so should be wrapped in quotes not the
date delimiter character:

txtRecDate.DefaultValue = """" & Me.txtRecDate & """"

A pair of quotes character within a string is interpreted as a literal
quotes character.

The reason you get the weird date when you omit any delimiters is that the
'date' is being interpreted as an arithmetical expression; 2/7/8 =
0.0357142857142857, which because of Access's implementation of the date time
data type represents 30 December 1899 00:51:26. Date/time values are
actually stored as a 64 bit floating point number with the origin at 30
December 1899 00:00:00.

What you first did is a very common mistake. I once received an extremely
petulant email from some tosser in California because I'd had the temerity to
point out that by recommending the use of the # character in replying to a
post on this subject in the Utter Access forum he was in fact giving bad
advice!

Ken Sheridan
Stafford, England

 

[david]

My mistake, as Ken has pointed out, I wasn't thinking about
the properties of "DefaultValue".

(david)

 

[Dave]

Solved it by reading Allen's tip more carefully. I used his sample code to
convert the date into string format for an SQL statement like this:

Function SQLdate(vardate as Variant) As String
If IsDate(vardate) Then
SQLdate = Format$(vardate, "\#mm\/dd\/yyyy\#")
End If
End Function

then changed the original:

Private Sub txtRecDate_AfterUpdate()
If Not IsNull(Me.txtRecDate.Value) then
txtRecDate.DefaultValue = "#" & Me.txtRecDate & "#"
End If
End Sub

to:
Private Sub txtRecDate_AfterUpdate()
If Not IsNull(Me.txtRecDate.Value) then
txtRecDate.DefaultValue = SQLdate(Me.txtRecDate)
End If
End Sub

Thanks for trying

Dave