date initializing

  • Thread starter Thread starter W
  • Start date Start date
W

W

Hi,

I want to initialize date variables.
However, the following options are not successful (I use the European fate
format : dd/mm/yyyy)

neither
dDate = (“# / / #â€)
nor
dDate = # / / #
nor
dDate = cdate(“ / / “)
nor
dDate = cdate (# / / #)

Could anyone help me please ?

Thank you,

W
 
Hi,

I want to initialize date variables.
However, the following options are not successful (I use the European fate
format : dd/mm/yyyy)

neither
dDate = (“# / / #”)
nor
dDate = # / / #
nor
dDate = cdate(“ / / “)
nor
dDate = cdate (# / / #)

Could anyone help me please ?

Thank you,

W
Click to expand...

A Date/Time value is stored as a Double Float number, a count of days and
fractions of a day since midnight, December 30, 1899. As such it must either
be a valid date (between #1/1/100# and #12/31/9999 23:59:59#), or NULL.

It's not a text string, it doesn't contain slashes or blanks or other
punctuation; regardless of how it's formatted for display it's either a
number, or it's NULL.

In what context are you trying to do this initilization? How have you dim'd
dDate? I'd suggest dim'ing it as a Variant and not initializing it at all (it
will be NULL by default, which is what you appear to need).

John W. Vinson [MVP]
 
W said:
Hi,

I want to initialize date variables.
However, the following options are not successful (I use the European fate
format : dd/mm/yyyy)

neither
dDate = (“# / / #”)
nor
dDate = # / / #
nor
dDate = cdate(“ / / “)
nor
dDate = cdate (# / / #)

Could anyone help me please ?

Thank you,

W
Click to expand...

When you're specifying dates in VBA you must use mm/dd format. European
formatted dates are accepted by the Access user interface (ie queries, forms
etc.), but not VBA. You could use this little function if you like:

Public Function USdate(ByVal dt As Date) As String
'Returns a US date from the date dt (for use as a criteria string)
' ie: USdate("31/01/97") becomes "#01/31/1997#"

USdate = "#" & Format$(dt, "mm/dd/yyyy") & "#"
End Function

So for example you could specify a cutoff date like this:

strCutOff = USdate("16/12/2007")
SQL = "SELECT <something> FROM <somewhere>" & _
" WHERE CutOff=" & strCutOff

It's just something us Europeans have to tolerate. Bloody Americans - can't
even write a date properly :)
 
Stuart McCall said:
When you're specifying dates in VBA you must use mm/dd format. European
formatted dates are accepted by the Access user interface (ie queries,
forms etc.), but not VBA. You could use this little function if you like:
Click to expand...

Actually, that's not quite true, Stuart. The CDate function respects the
Regional Settings, therefore as long as the Short Date format has been set
to dd/mm/yyyy (through the Control Panel), CDate("01/02/03") should return
01 February, 2003.
 
Douglas J. Steele said:
Actually, that's not quite true, Stuart. The CDate function respects the
Regional Settings, therefore as long as the Short Date format has been set
to dd/mm/yyyy (through the Control Panel), CDate("01/02/03") should return
01 February, 2003.
Click to expand...

Yes, I ought to have been more specific (a known and admitted shortcoming of
mine). You need US date format when building criteria in code. Although 01
February 2003 would be accepted if I remember correctly. I always use my
little function. Never let me down since Access 2.0.
 
I have a form-based module, where I declare the variables right on top.

So :

Option explicit
Dim dDOB as date, dDateOfPurchase as date, …
…

After having completed the data for the first record, I want to fill out the
data for the second record.

As dDOB and dDateOfPurchase received dates for the first record, I want to
reïnitialize them, put them back to zero as it were.

So, in my module I use a function :

Sub Reinitialize
dDOB = #dd/mm/yyyy#
…
End sub

(or whatever, but this does not work), where dd stands for the day (e.g. as
in 13), mm for month (e.g. as in 02) and yyyy for the year (e.g. as in 2007)

Not only this, but also the form controls should be reïnitialized, so

Me.DOB= #dd/mm/yyyy#
…

‘of course these should all be blanks, or whatever, but this also generates
an error.

Thanks for your help,

W
 
As Stuart pointed out, in most cases, you MUST use mm/dd/yyyy (or an
unambiguous format).

Having said that, though,

dDOB = #13/02/2007#

should give you 13 February, 2007, because there is no 13th month. However

dDOB = #11/02/2007#

will ALWAYS give you 02 November, 2007, regardless of how your Short Date
format is set.

If your Short Date format has been set to dd/mm/yyyy, then

dDOB = CDate("13/02/2007")

should work as well., or you can always use

dDOB = DateSerial(2007, 02, 13)

See Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I had in my September 2003 Access
Answers column for Pinnacle Publication's "Smart Access" newsletter. (The
column and accompanying database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)
 
I have a form-based module, where I declare the variables right on top.

So :

Option explicit
Dim dDOB as date, dDateOfPurchase as date, …
…

After having completed the data for the first record, I want to fill out the
data for the second record.

As dDOB and dDateOfPurchase received dates for the first record, I want to
reïnitialize them, put them back to zero as it were.

So, in my module I use a function :

Sub Reinitialize
dDOB = #dd/mm/yyyy#
…
End sub

(or whatever, but this does not work), where dd stands for the day (e.g. as
in 13), mm for month (e.g. as in 02) and yyyy for the year (e.g. as in 2007)

Not only this, but also the form controls should be reïnitialized, so

Me.DOB= #dd/mm/yyyy#
…

‘of course these should all be blanks, or whatever, but this also generates
an error.
Click to expand...

Again:

Dates ARE NOT TEXT STRINGS.

Blanks are not, * cannot be *, valid values in a date field.

A date *is a number*. For example today is 39422 (because it is that many days
from December 30, 1899 to December 16, 2007); the time as of when I checked
this was 39432.776099537, because 6:37:35 was that fraction of a day since
midnight.

Unless you have default values in the form controls, or the table fields,
there is no need to initialize anything! Just leave the controls NULL (or set
them to NULL if you're going to the mostly wasted effort to use unbound
controls).


John W. Vinson [MVP]
 
Back
Top