Carry forward a date in Aus short date format

  • Thread starter Thread starter Dave
  • Start date Start date
D

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
 
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)
 
Tried that, David.
Whatever date I initially enter, it carries forward as 30/12/1899

????

dave
 
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
 
My mistake, as Ken has pointed out, I wasn't thinking about
the properties of "DefaultValue".

(david)
 
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
Click to expand...

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
 
Back
Top