I am stumped....TimeValue problem

[Opal]

Perhaps its due to my lack of sleep with my new
puppy but I just can't get my head around this...

I have the following in my forms on open
event to determine shift time:

If TimeValue(Me!txtTime) >= #6:30:00 AM# And TimeValue(Me!txtTime) <=
#5:00:00 PM# Then
Me!cboShiftTime = 1
ElseIf TimeValue(Me!txtTime) >= #5:30:00 PM# And TimeValue(Me!
txtTime) <= #3:45:00 AM# Then
Me!cboShiftTime = 2
End If

But I am getting an invalid use of Null error. Could someone
help me out? What have I messed up?

 

[Dirk Goldgar]

Perhaps its due to my lack of sleep with my new
puppy but I just can't get my head around this...

I have the following in my forms on open
event to determine shift time:

If TimeValue(Me!txtTime) >= #6:30:00 AM# And TimeValue(Me!txtTime) <=
#5:00:00 PM# Then
Me!cboShiftTime = 1
ElseIf TimeValue(Me!txtTime) >= #5:30:00 PM# And TimeValue(Me!
txtTime) <= #3:45:00 AM# Then
Me!cboShiftTime = 2
End If

But I am getting an invalid use of Null error. Could someone
help me out? What have I messed up?

Just guessing, but maybe the Open event is too early for txtTime to have a
value. That would certainly be the case if it's a bound control -- then I
wouldn't expect it to have any data until the form's Current event, though
maybe the Load event would work. If it's a calculated control, then it may
be that its value hasn't been calculated yet, in the Open event.

 

[Evi]

There may be null values in the table where you want to use this function

so begin it with

If Not IsNull(Me.txtTime)


or maybe there are times in the table which don't fall into either category.


You might want to consider putting these times as ShiftStart and ShiftEnd in
your Shift table rather than in a function - Shift times can change.

Evi

 

[Opal]

Just guessing, but maybe the Open event is too early for txtTime to have a
value.  That would certainly be the case if it's a bound control -- thenI
wouldn't expect it to have any data until the form's Current event, though
maybe the Load event would work.  If it's a calculated control, then it may
be that its value hasn't been calculated yet, in the Open event.

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

Yes, thank you Dirk. It needed to go in the On Current event.
Thank you so much!

 

[Opal]

Yes, thank you Dirk.  It needed to go in the On Current event.
Thank you so much!- Hide quoted text -

- Show quoted text -

Okay it worked and now its not....I think I need a nap....any other
suggestions?

 

[Dirk Goldgar]

Okay it worked and now its not....I think I need a nap....any other
suggestions?

I'd need to know more about what changed between "working" and "not
working", and about the details of your form. You didn't say whether this
was a bound control or a calculated one. What is the Control Source
property of txtTime?

Note that, as Evi pointed out, if it's a bound control or a calculation
based on a field in the form's recordsource, then the code as written will
fail -- with the error you reported -- if that field is Null (as, for
example, if the form is at a new record). Evi's suggestion to check for
Null first, with

If Not IsNull(Me.txtTime) Then

' ...

End If

seems like a very good one to me.

 

[Opal]

I'd need to know more about what changed between "working" and "not
working", and about the details of your form.  You didn't say whether this
was a bound control or a calculated one.  What is the Control Source
property of txtTime?

Note that, as Evi pointed out, if it's a bound control or a calculation
based on a field in the form's recordsource, then the code as written will
fail -- with the error you reported -- if that field is Null (as, for
example, if the form is at a new record).  Evi's suggestion to check for
Null first, with

    If Not IsNull(Me.txtTime) Then

        ' ...

    End If

seems like a very good one to me.

The form is not bound. All the text boxes and combo boxes are
unbound.
The txtTime field is unbound but has the following running on the
form's timer

Private Sub Form_Timer()
txtTime.Value = Now()
txtTodaysDate.Value = Date
End Sub

I have other text boxes and combo boxes that are calculated controls,
i.e DCount or DatePart.... Should I still be checking for null?

 

[Opal]

There may be null values in the table where you want to use this function

so begin it with

If Not IsNull(Me.txtTime)

or maybe there are times in the table which don't fall into either category.

You might want to consider putting these times as ShiftStart and ShiftEnd in
your Shift table rather than in a function - Shift times can change.

Evi









- Show quoted text -

Actually, I do have these times noted in my ShiftTime table as well.
However, the table is not bound to this form, so how would this be
re-written in that case?

 

[Opal]

The form is not bound.  All the text boxes and combo boxes are
unbound.
The txtTime field is unbound but has the following running on the
form's timer

Private Sub Form_Timer()
    txtTime.Value = Now()
    txtTodaysDate.Value = Date
End Sub

I have other text boxes and combo boxes that are calculated controls,
i.e DCount or DatePart....  Should I still be checking for null?- Hide quoted text -

- Show quoted text -

This is what I have now in the forms On Current event:

If Not IsNull(Me!txtTime) Then
If TimeValue(Me!txtTime) >= #6:30:00 AM# And TimeValue(Me!txtTime)
<= #5:00:00 PM# Then
Me!cboShiftTime = 1
ElseIf TimeValue(Me!txtTime) >= #5:30:00 PM# And TimeValue(Me!
txtTime) <= #3:45:00 AM# Then
Me!cboShiftTime = 2
End If
End If

However, now the cboShiftTime is blank...no value. I do not get the
invalid use of Null error though....

 

[Evi]

Opel, If the form is not bound and the only things in txtTime and
txtTodaysDate are Now() and Date

Why don't you refer directly to Now() and Date() in your code rather than to
the textboxes?

And if your start and end times are already in a table, would you be able to
use DLookup instead of typing in the values?

You may have explained this but I've missed it (no puppy, just wild party!)
Evi

The form is not bound. All the text boxes and combo boxes are
unbound.
The txtTime field is unbound but has the following running on the
form's timer

Private Sub Form_Timer()
txtTime.Value = Now()
txtTodaysDate.Value = Date
End Sub

I have other text boxes and combo boxes that are calculated controls,
i.e DCount or DatePart.... Should I still be checking for null?- Hide quoted text -

- Show quoted text -

This is what I have now in the forms On Current event:

If Not IsNull(Me!txtTime) Then
If TimeValue(Me!txtTime) >= #6:30:00 AM# And TimeValue(Me!txtTime)
<= #5:00:00 PM# Then
Me!cboShiftTime = 1
ElseIf TimeValue(Me!txtTime) >= #5:30:00 PM# And TimeValue(Me!
txtTime) <= #3:45:00 AM# Then
Me!cboShiftTime = 2
End If
End If

However, now the cboShiftTime is blank...no value. I do not get the
invalid use of Null error though....

 

[Opal]

Opel, If the form is not bound and the only things in txtTime and
txtTodaysDate are Now() and Date

Why don't you refer directly to Now() and Date() in your code rather than to
the textboxes?

And if your start and end times are already in a table, would you be able to
use DLookup instead of typing in the values?

You may have explained this but I've missed it (no puppy, just wild party!)
Evi





quoted text -


This is what I have now in the forms On Current event:

  If Not IsNull(Me!txtTime) Then
    If TimeValue(Me!txtTime) >= #6:30:00 AM# And TimeValue(Me!txtTime)
<= #5:00:00 PM# Then
    Me!cboShiftTime = 1
    ElseIf TimeValue(Me!txtTime) >= #5:30:00 PM# And TimeValue(Me!
txtTime) <= #3:45:00 AM# Then
    Me!cboShiftTime = 2
    End If
    End If

However, now the cboShiftTime is blank...no value.  I do not get the
invalid use of Null error though....

Hmm...not certain how to do that...will have to do some research....

 

[Evi]

Opel, If the form is not bound and the only things in txtTime and
txtTodaysDate are Now() and Date

Why don't you refer directly to Now() and Date() in your code rather than to
the textboxes?

And if your start and end times are already in a table, would you be able to
use DLookup instead of typing in the values?

You may have explained this but I've missed it (no puppy, just wild party!)


quoted text -


This is what I have now in the forms On Current event:

If Not IsNull(Me!txtTime) Then
If TimeValue(Me!txtTime) >= #6:30:00 AM# And TimeValue(Me!txtTime)
<= #5:00:00 PM# Then
Me!cboShiftTime = 1
ElseIf TimeValue(Me!txtTime) >= #5:30:00 PM# And TimeValue(Me!
txtTime) <= #3:45:00 AM# Then
Me!cboShiftTime = 2
End If
End If

However, now the cboShiftTime is blank...no value. I do not get the
invalid use of Null error though....

Hmm...not certain how to do that...will have to do some research....

Oh I think I see (dim bulb lights sulkily), you've rigged up a time clock
for clocking in and out and you want to include the information in the clock
which shift it is? Could you adapt the following formula for your use? (It
should all be one line, but the email will break it up, so paste it into
Word and delete the manual line breaks)


=DLookUp("[ShiftNum]","TblShift","TimeValue([ShiftStart])<= #" &
TimeValue(Now()) & "# AND TimeValue([ShiftEnd]) >=#" & TimeValue(Now()) &
"#")

I've made up the table and field names. I'm going to assume that you have a
table, TblShift, with ShiftNum (a number field showing the shift number),
ShiftStart (a date field
formatted as short time saying when the shift starts) and ShiftEnd (same
format).

Having said this, I see that using the Timer Event to Requery the form seems
to cause a blank space in the control. It seems to be that which is screwing
up Now()

Back to the drawing board.

Evi

 

[Evi]

Opel, If the form is not bound and the only things in txtTime and
txtTodaysDate are Now() and Date

Why don't you refer directly to Now() and Date() in your code rather

than

to
the textboxes?

And if your start and end times are already in a table, would you be

able

to
use DLookup instead of typing in the values?

You may have explained this but I've missed it (no puppy, just wild party!)


quoted text -


This is what I have now in the forms On Current event:

If Not IsNull(Me!txtTime) Then
If TimeValue(Me!txtTime) >= #6:30:00 AM# And TimeValue(Me!txtTime)
<= #5:00:00 PM# Then
Me!cboShiftTime = 1
ElseIf TimeValue(Me!txtTime) >= #5:30:00 PM# And TimeValue(Me!
txtTime) <= #3:45:00 AM# Then
Me!cboShiftTime = 2
End If
End If

However, now the cboShiftTime is blank...no value. I do not get the
invalid use of Null error though....

Hmm...not certain how to do that...will have to do some research....

Oh I think I see (dim bulb lights sulkily), you've rigged up a time clock
for clocking in and out and you want to include the information in the clock
which shift it is? Could you adapt the following formula for your use? (It
should all be one line, but the email will break it up, so paste it into
Word and delete the manual line breaks)


=DLookUp("[ShiftNum]","TblShift","TimeValue([ShiftStart])<= #" &
TimeValue(Now()) & "# AND TimeValue([ShiftEnd]) >=#" & TimeValue(Now()) &
"#")

I've made up the table and field names. I'm going to assume that you have a
table, TblShift, with ShiftNum (a number field showing the shift number),
ShiftStart (a date field
formatted as short time saying when the shift starts) and ShiftEnd (same
format).

Having said this, I see that using the Timer Event to Requery the form seems
to cause a blank space in the control. It seems to be that which is screwing
up Now()

Back to the drawing board.

Evi

OK, just had to set the Time Interval to a greater value (5000) and in the
form's timer event have

Me.txtShift.Requery (txtShift contains the dlookup)

But this still has the unfortunate effect of causing the control to blink in
an Epilepsy inducing manner. I wonder if there is some way of stopping this.
Evi

 

[Stuart McCall]

OK, just had to set the Time Interval to a greater value (5000) and in the
form's timer event have

Me.txtShift.Requery (txtShift contains the dlookup)

But this still has the unfortunate effect of causing the control to blink
in
an Epilepsy inducing manner. I wonder if there is some way of stopping
this.
Evi

Try this:

Application.Echo False
Me.txtShift.Requery
Application.Echo True

 

[Evi]

Opel, If the form is not bound and the only things in txtTime and
txtTodaysDate are Now() and Date

Why don't you refer directly to Now() and Date() in your code rather

than

to
the textboxes?

And if your start and end times are already in a table, would you be

able

to
use DLookup instead of typing in the values?

You may have explained this but I've missed it (no puppy, just wild party!)
Evi



The form is not bound. All the text boxes and combo boxes are
unbound.
The txtTime field is unbound but has the following running on the
form's timer

Private Sub Form_Timer()
txtTime.Value = Now()
txtTodaysDate.Value = Date
End Sub

I have other text boxes and combo boxes that are calculated controls,
i.e DCount or DatePart.... Should I still be checking for null?- Hide
quoted text -

- Show quoted text -

This is what I have now in the forms On Current event:

If Not IsNull(Me!txtTime) Then
If TimeValue(Me!txtTime) >= #6:30:00 AM# And TimeValue(Me!txtTime)
<= #5:00:00 PM# Then
Me!cboShiftTime = 1
ElseIf TimeValue(Me!txtTime) >= #5:30:00 PM# And TimeValue(Me!
txtTime) <= #3:45:00 AM# Then
Me!cboShiftTime = 2
End If
End If

However, now the cboShiftTime is blank...no value. I do not get the
invalid use of Null error though....

Hmm...not certain how to do that...will have to do some research....

Oh I think I see (dim bulb lights sulkily), you've rigged up a time clock
for clocking in and out and you want to include the information in the clock
which shift it is? Could you adapt the following formula for your use? (It
should all be one line, but the email will break it up, so paste it into
Word and delete the manual line breaks)


=DLookUp("[ShiftNum]","TblShift","TimeValue([ShiftStart])<= #" &
TimeValue(Now()) & "# AND TimeValue([ShiftEnd]) >=#" & TimeValue(Now()) &
"#")

I've made up the table and field names. I'm going to assume that you

have

a
table, TblShift, with ShiftNum (a number field showing the shift number),
ShiftStart (a date field
formatted as short time saying when the shift starts) and ShiftEnd (same
format).

Having said this, I see that using the Timer Event to Requery the form seems
to cause a blank space in the control. It seems to be that which is screwing
up Now()

Back to the drawing board.

Evi

OK, just had to set the Time Interval to a greater value (5000) and in the
form's timer event have

Me.txtShift.Requery (txtShift contains the dlookup)

But this still has the unfortunate effect of causing the control to blink in
an Epilepsy inducing manner. I wonder if there is some way of stopping this.
Evi

Although still blinking, it helps if I format the Shift Times as
hh:nn:ss
then I can have say ShiftEnd as 12:00:00
and the next ShiftStart as 12:00:01. This prevents a null reading in
txtShift between 12:00 and 12:01
Evi

 

[Evi]

Try this:

Application.Echo False
Me.txtShift.Requery
Application.Echo True

Thanks for the reply Stuart. So THAT's what Echo does!
It ought to work but I'm still getting the blink

Maybe it's something to do with Echo and the Timer not working together.

Private Sub Form_Timer()
Application.Echo False
Me.txtShift.Requery
Application.Echo True
End Sub

 

[Opal]

Thanks for the reply Stuart. So THAT's what Echo does!
It ought to work but I'm still getting the blink

Maybe it's something to do with Echo and the Timer not working together.

Private Sub Form_Timer()
Application.Echo False
Me.txtShift.Requery
Application.Echo True
End Sub- Hide quoted text -

- Show quoted text -

Something strange....when I use Dlookup....I have tried several in a
txt box
it comes up blank. I just tried, as you suggested Evi:

=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# AND TimeValue([EndofShift]) >= #" &
TimeValue(Now()) & "#")

and the box is blank.

When I tried the echo false and my main form disappeared and I could
not retrieve it. I had to close
out the database and re-open even if I commented out the lines of
code.

On what event did you put the Me.txtShiftName.Requery?

 

[Evi]

"

Thanks for the reply Stuart. So THAT's what Echo does!
It ought to work but I'm still getting the blink

Maybe it's something to do with Echo and the Timer not working together.

Private Sub Form_Timer()
Application.Echo False
Me.txtShift.Requery
Application.Echo True
End Sub- Hide quoted text -

- Show quoted text -

Something strange....when I use Dlookup....I have tried several in a
txt box
it comes up blank. I just tried, as you suggested Evi:

=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# AND TimeValue([EndofShift]) >= #" &
TimeValue(Now()) & "#")


and the box is blank.

When I tried the echo false and my main form disappeared and I could
not retrieve it. I had to close
out the database and re-open even if I commented out the lines of
code.

On what event did you put the Me.txtShiftName.Requery?

Hi Opal
Echo False must be followed by Echo True or it stays switched off.
This code is put in the On Timer Event.

Comment out the On Timer Event for now, until you get the text box working.
It works without the On Timer, it just doesn't update.

In the DLookup, remember to replace my field and table/Query names with
yours.

I see you've got ShiftName as the query or table name which the DLookup is
reading from. Is that what you have called your query or table which holds
the shift times?



The Syntax for DLookUp is

DLookup("[TheFieldinThe OtherTableOrQueryWhich you want to find]", "The
Table Or Query which has that field", "Any Criteria needed for finding that
field")

Make sure that the DLookup is all on one line (the email will almost
certainly break it up).

I've found for the StartOfShift and EndOFShift, it works best if you format
them *in your table* as

hh:nn:ss

You can use a different format in forms and reports but we want the seconds
value in your table

Make sure (if appropriate) that your times follow on from each other,
otherwise your text box will be blank if there is no current shift - which
is what you want.

eg

Shift1 09:00:01 to 12:00:00
Shift2 12:00:01 to 16:00:00
Shift3 16:00:01 to 20:00:00

Have I got the right idea about what you are trying to do?


I've commented out the Echo lines. It seems to make the whole form blink
whereas the Requery event just makes the text box itself blink

Ensure that your Timer Interval in Properties is no less than 5000 so that
it refreshes every 5 seconds. If you don't have to be that precise then have
it refresh less often, say 10000, to cut down on the 'blinks'.

Evi

 

[Opal]

"

Thanks for the reply Stuart. So THAT's what Echo does!
It ought to work but I'm still getting the blink

Maybe it's something to do with Echo and the Timer not working together.

Private Sub Form_Timer()
Application.Echo False
Me.txtShift.Requery
Application.Echo True
End Sub- Hide quoted text -

- Show quoted text -

Something strange....when I use Dlookup....I have tried several in a
txt box
it comes up blank.   I just tried, as you suggested Evi:

=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "#  AND TimeValue([EndofShift]) >= #" &
TimeValue(Now()) & "#")

and the box is blank.

When I tried the echo false and my main form disappeared and I could
not retrieve it.  I had to close
out the database and re-open even if I commented out the lines of
code.

On what event did you put the Me.txtShiftName.Requery?

Hi Opal
Echo False must be followed by Echo True or it stays switched off.
This code is put in the On Timer Event.

Comment out the On Timer Event for now, until you get the text box working..
It works without the On Timer, it just doesn't update.

In the DLookup, remember to replace my field and table/Query names with
yours.

I see you've got ShiftName as the query or table name which the DLookup is
reading from. Is that what you have called your query or table which holds
the shift times?

The Syntax for DLookUp is

DLookup("[TheFieldinThe OtherTableOrQueryWhich you want to find]", "The
Table Or Query which has that field", "Any Criteria needed for finding that
field")

Make sure that the DLookup is all on one line (the email will almost
certainly break it up).

I've found for the StartOfShift and EndOFShift, it works best if you format
them *in your table* as

hh:nn:ss

You can use a different format in forms and reports but we want the seconds
value in your table

Make sure (if appropriate) that your times follow on from each other,
otherwise your text box will be blank if there is no current shift - which
is what you want.

eg

Shift1 09:00:01 to 12:00:00
Shift2 12:00:01 to 16:00:00
Shift3 16:00:01 to 20:00:00

Have I got the right idea about what you are trying to do?

I've commented out the Echo lines. It seems to make the whole form blink
whereas the Requery event just makes the text box itself blink

Ensure that your Timer Interval in Properties is no less than 5000 so that
it refreshes every 5 seconds. If you don't have to be that precise then have
it refresh less often, say 10000, to cut down on the 'blinks'.

Evi- Hide quoted text -

- Show quoted text -

Hi Evi,

Lots of good advice. Okay...got a little confused yesterday.

The table name is ShiftTime. (I made an error yesterday)
Fields in the table are:
ShiftTimeID (autonumber field)
ShiftTime
StartofShift
EndofShift

My shift times follow the shifts in the plant which are
Start: 7:00am end: 4:30pm - Day Shift
Start: 5:45pm end: 3:15am - Aft. Shift

So my Dlookup looks like this:

=DLookUp("[ShiftTime]","ShiftTime","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# AND TimeValue([EndofShift]) >= #" &
TimeValue(Now()) & "#")

I have this Dlookup in the Control source of my txtShiftName in my
form, but, I am still getting a blank response to my text box. Has
this got to do with my start and end of shift times based on what you
wrote
above:

"eg

Shift1 09:00:01 to 12:00:00
Shift2 12:00:01 to 16:00:00
Shift3 16:00:01 to 20:00:00"

So, I will try to alter my shift times to see if it works.

 

[Opal]

"

Opal" <[email protected]> wrote in message news:15c02213-1dce-45de-

Something strange....when I use Dlookup....I have tried several in a
txt box
it comes up blank.   I just tried, as you suggested Evi:

=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "#  AND TimeValue([EndofShift]) >= #" &
TimeValue(Now()) & "#")

and the box is blank.

When I tried the echo false and my main form disappeared and I could
not retrieve it.  I had to close
out the database and re-open even if I commented out the lines of
code.

On what event did you put the Me.txtShiftName.Requery?

Hi Opal
Echo False must be followed by Echo True or it stays switched off.
This code is put in the On Timer Event.

Comment out the On Timer Event for now, until you get the text box working.
It works without the On Timer, it just doesn't update.

In the DLookup, remember to replace my field and table/Query names with
yours.

I see you've got ShiftName as the query or table name which the DLookup is
reading from. Is that what you have called your query or table which holds
the shift times?

The Syntax for DLookUp is

DLookup("[TheFieldinThe OtherTableOrQueryWhich you want to find]", "The
Table Or Query which has that field", "Any Criteria needed for finding that
field")

Make sure that the DLookup is all on one line (the email will almost
certainly break it up).

I've found for the StartOfShift and EndOFShift, it works best if you format
them *in your table* as

You can use a different format in forms and reports but we want the seconds
value in your table

Make sure (if appropriate) that your times follow on from each other,
otherwise your text box will be blank if there is no current shift - which
is what you want.

Shift1 09:00:01 to 12:00:00
Shift2 12:00:01 to 16:00:00
Shift3 16:00:01 to 20:00:00

Have I got the right idea about what you are trying to do?

I've commented out the Echo lines. It seems to make the whole form blink
whereas the Requery event just makes the text box itself blink

Ensure that your Timer Interval in Properties is no less than 5000 so that
it refreshes every 5 seconds. If you don't have to be that precise then have
it refresh less often, say 10000, to cut down on the 'blinks'.

Evi- Hide quoted text -

- Show quoted text -

Hi Evi,

Lots of good advice.  Okay...got a little confused yesterday.

The table name is ShiftTime. (I made an error yesterday)
Fields in the table are:
ShiftTimeID (autonumber field)
ShiftTime
StartofShift
EndofShift

My shift times follow the shifts in the plant which are
Start:  7:00am     end:  4:30pm  - Day Shift
Start:  5:45pm     end:  3:15am  - Aft. Shift

So my Dlookup looks like this:

=DLookUp("[ShiftTime]","ShiftTime","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "#  AND TimeValue([EndofShift]) >= #" &
TimeValue(Now()) & "#")

I have this Dlookup in the Control source of my txtShiftName in my
form, but, I am still getting a blank response to my text box.  Has
this got to do with my start and end of shift times based on what you
wrote
above:

"eg

Shift1 09:00:01 to 12:00:00
Shift2 12:00:01 to 16:00:00
Shift3 16:00:01 to 20:00:00"

So, I will try to alter my shift times to see if it works.- Hide quoted text -

- Show quoted text -

Nope, didn't work...... :-(