date/time shift

[RipperT]

I have a form into which users log data categorized by date and time. The
forms date field default value is set to =Date(). However, entries made on
our 3rd shift (10pm - 6am) after midnight need to belong to the previous
days date (the date on which the shift began). I would like to code the
default value of date field so this is taken care of. Is the following even
close?

Private Sub Form_Current()
Dim MyDate
Dim MyTime
MyDate = Date
MyTime = Time
IF MyTime >=#12:01:00 AM# and MyTime <=#6:00:00 am# THEN
Me.Date.DefaultValue = (MyDate-1)
ELSE
Me.Date.DefaultValue = MyDate
End If
End Sub

Many thanx,

Ripper

 

[Klatuu]

This looks just fine with one exception. Change 12:01 to 12:00
The last minute of a date is 11:59 PM then when the date rolls over, it
becomes 12:00 AM. In fact, you really only need the test for <= 6:00 AM, but
the way you write it is good because there is no ambiquity for a human who
has to read it.

 

[RipperT]

I ended up with this:

Private Sub Form_Current()
Dim MyDate
Dim MyTime
MyDate = Date
MyTime = Time
IF MyTime >=#12:00:00 AM# and MyTime <=#6:00:00 am# THEN
Me.Date.DefaultValue = MyDate-1
ELSE
Me.Date.DefaultValue = MyDate
End If
End Sub

The line:
Me.Date.DefaultValue = MyDate

returns an invalid use of null error. Does anyone know why?

Thanx,

Rip

 

[Klatuu]

What is Me.Date.DefaultValue? The way you write it, it looks like you have a
control on your form named date. Since Date is a reserved word in Access,
that may be causing the problem. Try changing the name of the control.
Other than that, your code looks fine.

 

[RipperT]

I changed the field Date to TempDate and got this:

DatePrivate Sub Form_Current()
Dim MyDate
Dim MyTime
MyDate = Date
MyTime = Time
IF MyTime >=#12:00:00 AM# and MyTime <=#6:00:00 am# THEN
Me.TempDate.DefaultValue = MyDate-1
ELSE
Me.TempDate.DefaultValue = MyDate
End If
End Sub

The field's default value is 12/31/1899 (or something close to that). Why?
Does the fact that the form is in datasheet view figure in? Or the fact that
the form is a subform?

Many thanx,

Rip

 

[Douglas J. Steele]

Date/Times are stored as 8 byte floating point numbers, where the integer
portion represents the date as the number of days relative to 30 Dec, 1899,
and the decimal portion represents the time as a fraction of a day.

The fact that you're getting a default date of 30 Dec, 1899 means that your
default value is 0.

 

[RipperT]

Thank you, Douglas, this is important information to know, however it
doesn't help my situation. If MyDate = Date (current date) and
TempDate.DefaultValue = MyDate, clearly the default value is not zero, it
would be the current date. Why is it returning zero? Can you help, please?

Thank you,

Rip

 

[Klatuu]

I notice you do not specify data types in your Dim statement. Change it to:
Dim MyDate as Date
Dim MyTime as Date
Then step through your code to see what value the variables have.

 

[Michel]

Have you tried

Me.TempDate.DefaultValue = "#" & MyDate & "#"

??

Michel

 

[RipperT]

Michel and Klatuu, both good ideas, I will try.

Thank you!

Rip

 

[Douglas J. Steele]

Default Values need to be strings (even if the value is a numeric or date
value)

Try using the Format function with your values.

 

[RipperT]

Klattu, can you tell me how to step thru the code to see what the value of
variables are?

Thank you,

Rip