I am stumped....TimeValue problem

[Evi]

"

together.

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...... :-(

Try making the Dlookup less complex just for test purposes
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# AND TimeValue([EndofShift]) >= #" & TimeValue(Now()) &
"#")

=DLookUp("[ShiftName]","ShiftName") (it should just look up a shift name)

then
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# )

It'll look up any shift start time which comes before the current time

is your table (or query) name actually "ShiftName"?

I wonder if you have a date hidden in your shift times (although I had hoped
that TimeValue would sort that out). Make sure that *in the Table Design*,
on the Format line for StartOfShift and EndOfShift you type

hh:nn:ss


Just for test purposes, change the shift times so that one includes the time
when you are testing

Do you have anything else in StartOfShift, EndOfShift other than the time?
Are they actually Date/Time fields?

Record 1 should be:

StartOfShift: 07:00:00
EndOfShift: 16:30:00

Record 2 should be

StartOfShift: 17:45:00
EndOfShift: 03:15:00
The text box, of course, will be blank if you test it at any time other than
those in your table.
Make sure your Timer Interval is no less than 5000. (Make it 20000 to start
with, just for testing)

Try testing Now() and TimeValue on your PC by having a text box say

=#17:00:00#>TimeValue(Now())

Depending on when you do this, the answer should be either 0 for untrue
or -1 for true

I've got this working so I know it is possible.
Evi

 

[Opal]

"
<SNIP>
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
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:

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......  :-(

Try making the Dlookup less complex just for test purposes
 =DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# AND TimeValue([EndofShift]) >= #" & TimeValue(Now()) &
"#")

 =DLookUp("[ShiftName]","ShiftName") (it should just look up a shift name)

then
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# )

It'll look up any shift start time which comes before the current time

is your table (or query) name actually "ShiftName"?

I wonder if you have a date hidden in your shift times (although I had hoped
that TimeValue would sort that out). Make sure that *in the Table Design*,
on the Format line for StartOfShift and EndOfShift you type

hh:nn:ss

Just for test purposes, change the shift times so that one includes the time
when you are testing

Do you have anything else in StartOfShift, EndOfShift other than the time?
Are they actually Date/Time fields?

Record 1 should be:

StartOfShift: 07:00:00
EndOfShift: 16:30:00

Record 2 should be

StartOfShift: 17:45:00
EndOfShift: 03:15:00
The text box, of course, will be blank if you test it at any time other than
those in your table.
Make sure your Timer Interval is no less than 5000. (Make it 20000 to start
with, just for testing)

Try testing Now() and TimeValue on your PC by having a text box say

=#17:00:00#>TimeValue(Now())

Depending on when you do this, the answer should be either 0 for untrue
or -1 for true

I've got this working so I know it is possible.
Evi- Hide quoted text -

- Show quoted text -

Thank you Evi. I couldn't get it working at home last night. Saved
it to a
USB key and took it work. Saved it on my computer on work, made no
changes
whatsoever before opening the file and it came up just fine. Spent
the
afternoon at work working on the database, tweaking things here and
there,
quitting time came, resaved to my USB key and saved it back to my
computer
at home and its not working again. So, obviously the problem is my
computer
and not what you provided. Thank you so much for all your help!

 

[Evi]

"

<SNIP>
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
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

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:

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...... :-(

Try making the Dlookup less complex just for test purposes
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# AND TimeValue([EndofShift]) >= #" & TimeValue(Now()) &
"#")

=DLookUp("[ShiftName]","ShiftName") (it should just look up a shift name)

then
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# )

It'll look up any shift start time which comes before the current time

is your table (or query) name actually "ShiftName"?

I wonder if you have a date hidden in your shift times (although I had hoped
that TimeValue would sort that out). Make sure that *in the Table Design*,
on the Format line for StartOfShift and EndOfShift you type

hh:nn:ss

Just for test purposes, change the shift times so that one includes the time
when you are testing

Do you have anything else in StartOfShift, EndOfShift other than the time?
Are they actually Date/Time fields?

Record 1 should be:

StartOfShift: 07:00:00
EndOfShift: 16:30:00

Record 2 should be

StartOfShift: 17:45:00
EndOfShift: 03:15:00
The text box, of course, will be blank if you test it at any time other than
those in your table.
Make sure your Timer Interval is no less than 5000. (Make it 20000 to start
with, just for testing)

Try testing Now() and TimeValue on your PC by having a text box say

=#17:00:00#>TimeValue(Now())

Depending on when you do this, the answer should be either 0 for untrue
or -1 for true

I've got this working so I know it is possible.
Evi- Hide quoted text -

- Show quoted text -

Thank you Evi. I couldn't get it working at home last night. Saved
it to a
USB key and took it work. Saved it on my computer on work, made no
changes
whatsoever before opening the file and it came up just fine. Spent
the
afternoon at work working on the database, tweaking things here and
there,
quitting time came, resaved to my USB key and saved it back to my
computer
at home and its not working again. So, obviously the problem is my
computer
and not what you provided. Thank you so much for all your help!

Hi Opal.

I don't think it's likely to be a problem with your PC so don't start
re-installing anything yet. It's far more likely that either:

1. You have a different version of Access at home which objects to something
about the function.

I'm having a problem here because the code for a Report Footer OnFormat
Event on my machine (Acc2000) starts with the line
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
A Converted database sent to me by someone with a highter version of Access
starts with:
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
(the word Section is missing)
Debug did not show an error but it didn't work as expected. I had to import
his database objects into a blank copy on my machine before I could make it
work and Heaven knows if it will work when it gets back to him!
You could try this with the database you have brought from work and see if
that makes a difference. Then also see below.



2. You have a missing Reference in Access on your PC.

Open any code Module. Go to Tools, References. If you are lucky, you will
see the word Missing. Tick that item and you will probably be fine. If you
are less lucky, you will need to find out which references you ought to have
on your version of Access.


Evi

 

[Opal]

"
<SNIP>
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
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.- Hide quoted text -
- Show quoted text -

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

Try making the Dlookup less complex just for test purposes
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# AND TimeValue([EndofShift]) >= #" & TimeValue(Now()) &

"#")

=DLookUp("[ShiftName]","ShiftName") (it should just look up a shift name)

then
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# )

It'll look up any shift start time which comes before the current time

is your table (or query) name actually "ShiftName"?

I wonder if you have a date hidden in your shift times (although I had hoped
that TimeValue would sort that out). Make sure that *in the Table Design*,
on the Format line for StartOfShift and EndOfShift you type

Just for test purposes, change the shift times so that one includes the time
when you are testing

Do you have anything else in StartOfShift, EndOfShift other than the time?
Are they actually Date/Time fields?

Record 1 should be:

StartOfShift: 07:00:00
EndOfShift: 16:30:00

Record 2 should be

StartOfShift: 17:45:00
EndOfShift: 03:15:00
The text box, of course, will be blank if you test it at any time other than
those in your table.
Make sure your Timer Interval is no less than 5000. (Make it 20000 to start
with, just for testing)

Try testing Now() and TimeValue on your PC by having a text box say

Depending on when you do this, the answer should be either 0 for untrue
or -1 for true

I've got this working so I know it is possible.
Evi- Hide quoted text -

- Show quoted text -

Thank you Evi.  I couldn't get it working at home last night.  Saved
it to a
USB key and took it work.  Saved it on my computer on work, made no
changes
whatsoever before opening the file and it came up just fine.  Spent
the
afternoon at work working on the database, tweaking things here and
there,
quitting time came, resaved to my USB key and saved it back to my
computer
at home and its not working again.  So, obviously the problem is my
computer
and not what you provided.  Thank you so much for all your help!

Hi Opal.

I don't think it's likely to be a problem with your PC so don't start
re-installing anything yet. It's far more likely that either:

1. You have a different version of Access at home which objects to something
about the function.

I'm having a problem here because  the code for a Report Footer OnFormat
Event on my machine (Acc2000) starts with the line
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
A Converted database sent to me by someone with a highter version of Access
starts with:
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
(the word Section is missing)
Debug did not show an error but it didn't work as expected. I had to import
his database objects into a blank copy on my machine before I could make it
work and Heaven knows if it will work when it gets back to him!
You could try this with the database you have brought from work and see if
that makes a difference. Then also see below.

2. You have a missing Reference in Access on your PC.

Open any code Module. Go to Tools, References. If you are lucky, you will
see the word Missing. Tick that item and you will probably be fine. If you
are less lucky, you will need to find out which references you ought to have
on your version of Access.

Evi- Hide quoted text -

- Show quoted text -

Nope, the problem was the computer....sort of.

My hubby was in the process of configuring a new one for me at home
and when
I opened the database on the new computer the DLookup
worked......but....
its now the time of day where it should the second shift, but the
DLookup
is still saying its the first shift.....

 

[Opal]

Hi Evi,

I tried all of your testing suggestions, above:

=DLookUp("[ShiftName]","ShiftName") (it should just look up a shift
name)

then
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# )

both produced the first shift despite the fact that the current time
of day
I should show the second shift.

I changed the format of the "StartofShift" and "EndofShift" fields in
the table from
long time to hh:nn:ss

The table name is ShiftTime. The two fields, above are indeed Date/
Time fields.

I just put the following code in a text box, as you suggested:

=#17:00:00#>TimeValue(Now())

and got a "0" value response at 7:02pm EDT

So.....am I back to square one?

 

[Evi]

"
<SNIP>
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
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.- Hide

quoted
text -

- Show quoted text -

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

Try making the Dlookup less complex just for test purposes
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# AND TimeValue([EndofShift]) >= #" &

TimeValue(Now())

&

"#")

=DLookUp("[ShiftName]","ShiftName") (it should just look up a shift

name)

then
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# )

It'll look up any shift start time which comes before the current time

is your table (or query) name actually "ShiftName"?

I wonder if you have a date hidden in your shift times (although I had hoped
that TimeValue would sort that out). Make sure that *in the Table Design*,
on the Format line for StartOfShift and EndOfShift you type

Just for test purposes, change the shift times so that one includes the time
when you are testing

Do you have anything else in StartOfShift, EndOfShift other than the time?
Are they actually Date/Time fields?

Record 1 should be:

StartOfShift: 07:00:00
EndOfShift: 16:30:00

Record 2 should be

StartOfShift: 17:45:00
EndOfShift: 03:15:00
The text box, of course, will be blank if you test it at any time other than
those in your table.
Make sure your Timer Interval is no less than 5000. (Make it 20000 to start
with, just for testing)

Try testing Now() and TimeValue on your PC by having a text box say

Depending on when you do this, the answer should be either 0 for untrue
or -1 for true

I've got this working so I know it is possible.
Evi- Hide quoted text -

- Show quoted text -

Thank you Evi. I couldn't get it working at home last night. Saved
it to a
USB key and took it work. Saved it on my computer on work, made no
changes
whatsoever before opening the file and it came up just fine. Spent
the
afternoon at work working on the database, tweaking things here and
there,
quitting time came, resaved to my USB key and saved it back to my
computer
at home and its not working again. So, obviously the problem is my
computer
and not what you provided. Thank you so much for all your help!

Hi Opal.

I don't think it's likely to be a problem with your PC so don't start
re-installing anything yet. It's far more likely that either:

1. You have a different version of Access at home which objects to something
about the function.

I'm having a problem here because the code for a Report Footer OnFormat
Event on my machine (Acc2000) starts with the line
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
A Converted database sent to me by someone with a highter version of Access
starts with:
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
(the word Section is missing)
Debug did not show an error but it didn't work as expected. I had to import
his database objects into a blank copy on my machine before I could make it
work and Heaven knows if it will work when it gets back to him!
You could try this with the database you have brought from work and see if
that makes a difference. Then also see below.

2. You have a missing Reference in Access on your PC.

Open any code Module. Go to Tools, References. If you are lucky, you will
see the word Missing. Tick that item and you will probably be fine. If you
are less lucky, you will need to find out which references you ought to have
on your version of Access.

Evi- Hide quoted text -

- Show quoted text -

Nope, the problem was the computer....sort of.

My hubby was in the process of configuring a new one for me at home
and when
I opened the database on the new computer the DLookup
worked......but....
its now the time of day where it should the second shift, but the
DLookup
is still saying its the first shift.....

Hi Opal
There is a thought that has struck me.
There is one time when this function will always fail - if you are testing
it for a time across midnight when Now() is past midnight and you may well
be doing that at home!

So if you have created a shift that starts at 23:00 and ends at 01:00, it
will work until the clock goes 1 second past midnight and then it won't find
it. That is because there isn't a record where the Start Time is less than
Now() (if the start time is 23:00hrs it is greater than Now if Now() is
00:05 hrs, if it is 01:00 hrs then it is greater than Now() and the first
criteria for the DLookup is that the start time has to be less than or equal
to now. I'm trying to find a way around that using a Function but I haven't
got it yet.


Evi

 

[Opal]

"
<SNIP>
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
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 -
Opal" <[email protected]> wrote in message

Something strange....when I use Dlookup....I have tried several ina
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.- Hide quoted
text -
- Show quoted text -
Nope, didn't work...... :-(
Try making the Dlookup less complex just for test purposes
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #"&
TimeValue(Now()) & "# AND TimeValue([EndofShift]) >= #" &

TimeValue(Now())

&
"#")
=DLookUp("[ShiftName]","ShiftName") (it should just look up a shift

name)

then
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #"&
TimeValue(Now()) & "# )
It'll look up any shift start time which comes before the current time
is your table (or query) name actually "ShiftName"?
I wonder if you have a date hidden in your shift times (although I had hoped
that TimeValue would sort that out). Make sure that *in the Table Design*,
on the Format line for StartOfShift and EndOfShift you type
hh:nn:ss
Just for test purposes, change the shift times so that one includes the time
when you are testing
Do you have anything else in StartOfShift, EndOfShift other than the time?
Are they actually Date/Time fields?
Record 1 should be:
StartOfShift: 07:00:00
EndOfShift: 16:30:00
Record 2 should be
StartOfShift: 17:45:00
EndOfShift: 03:15:00
The text box, of course, will be blank if you test it at any time other than
those in your table.
Make sure your Timer Interval is no less than 5000. (Make it 20000 to start
with, just for testing)
Try testing Now() and TimeValue on your PC by having a text box say
=#17:00:00#>TimeValue(Now())
Depending on when you do this, the answer should be either 0 for untrue
or -1 for true
I've got this working so I know it is possible.
Evi- Hide quoted text -
- Show quoted text -

"Opal" <[email protected]> wrote in message news:a80c7763-dbff-41e3-b986

Thank you Evi. I couldn't get it working at home last night. Saved
it to a
USB key and took it work. Saved it on my computer on work, made no
changes
whatsoever before opening the file and it came up just fine. Spent
the
afternoon at work working on the database, tweaking things here and
there,
quitting time came, resaved to my USB key and saved it back to my
computer
at home and its not working again. So, obviously the problem is my
computer
and not what you provided. Thank you so much for all your help!

Hi Opal.

I don't think it's likely to be a problem with your PC so don't start
re-installing anything yet. It's far more likely that either:

1. You have a different version of Access at home which objects to something
about the function.

I'm having a problem here because the code for a Report Footer OnFormat
Event on my machine (Acc2000) starts with the line
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
A Converted database sent to me by someone with a highter version of Access
starts with:
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
(the word Section is missing)
Debug did not show an error but it didn't work as expected. I had to import
his database objects into a blank copy on my machine before I could make it
work and Heaven knows if it will work when it gets back to him!
You could try this with the database you have brought from work and see if
that makes a difference. Then also see below.

2. You have a missing Reference in Access on your PC.

Open any code Module. Go to Tools, References. If you are lucky, you will
see the word Missing. Tick that item and you will probably be fine. If you
are less lucky, you will need to find out which references you ought to have
on your version of Access.

Evi- Hide quoted text -

- Show quoted text -

Nope, the problem was the computer....sort of.

My hubby was in the process of configuring a new one for me at home
and when
I opened the database on the new computer the DLookup
worked......but....
its now the time of day where it should the second shift, but the
DLookup
is still saying its the first shift.....

Hi Opal
There is a thought that has struck me.
There is one time when this function will always fail - if you are testing
it for a time across midnight when Now() is past midnight and you may well
be doing that at home!

So if you have created a shift that starts at 23:00 and ends at 01:00, it
will work until the clock goes 1 second past midnight and then it won't find
it. That is because there isn't a record where the Start Time is less than
Now() (if the start time is 23:00hrs it is greater than Now if Now() is
00:05 hrs, if it is 01:00 hrs then it is greater than Now() and the first
criteria for the DLookup is that the start time has to be less than or equal
to now. I'm trying to find a way around that using a Function but I haven't
got it yet.

Evi- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Nope...don't work past midnight myself, but the database
will be worked past midnight once it goes live where I work.
The shift times 7am to 4:30pm and 5:45pm to 3:15am are
standard shift times.

 

[Evi]

"
<SNIP>
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
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 -
Opal" <[email protected]> wrote in message

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.- Hide quoted
text -
- Show quoted text -
Nope, didn't work...... :-(
Try making the Dlookup less complex just for test purposes
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# AND TimeValue([EndofShift]) >= #" &

TimeValue(Now())

&
"#")
=DLookUp("[ShiftName]","ShiftName") (it should just look up a shift

name)

then
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# )
It'll look up any shift start time which comes before the current time
is your table (or query) name actually "ShiftName"?
I wonder if you have a date hidden in your shift times (although I had hoped
that TimeValue would sort that out). Make sure that *in the Table Design*,
on the Format line for StartOfShift and EndOfShift you type
hh:nn:ss
Just for test purposes, change the shift times so that one includes

the
time

when you are testing
Do you have anything else in StartOfShift, EndOfShift other than the time?
Are they actually Date/Time fields?
Record 1 should be:
StartOfShift: 07:00:00
EndOfShift: 16:30:00
Record 2 should be
StartOfShift: 17:45:00
EndOfShift: 03:15:00
The text box, of course, will be blank if you test it at any time

other
than

those in your table.
Make sure your Timer Interval is no less than 5000. (Make it 20000 to start
with, just for testing)
Try testing Now() and TimeValue on your PC by having a text box say
=#17:00:00#>TimeValue(Now())
Depending on when you do this, the answer should be either 0 for untrue
or -1 for true
I've got this working so I know it is possible.
Evi- Hide quoted text -
- Show quoted text -

Thank you Evi. I couldn't get it working at home last night. Saved
it to a
USB key and took it work. Saved it on my computer on work, made no
changes
whatsoever before opening the file and it came up just fine. Spent
the
afternoon at work working on the database, tweaking things here and
there,
quitting time came, resaved to my USB key and saved it back to my
computer
at home and its not working again. So, obviously the problem is my
computer
and not what you provided. Thank you so much for all your help!

Hi Opal.

I don't think it's likely to be a problem with your PC so don't start
re-installing anything yet. It's far more likely that either:

1. You have a different version of Access at home which objects to something
about the function.

I'm having a problem here because the code for a Report Footer OnFormat
Event on my machine (Acc2000) starts with the line
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
A Converted database sent to me by someone with a highter version of Access
starts with:
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
(the word Section is missing)
Debug did not show an error but it didn't work as expected. I had to import
his database objects into a blank copy on my machine before I could make it
work and Heaven knows if it will work when it gets back to him!
You could try this with the database you have brought from work and see if
that makes a difference. Then also see below.

2. You have a missing Reference in Access on your PC.

Open any code Module. Go to Tools, References. If you are lucky, you will
see the word Missing. Tick that item and you will probably be fine. If you
are less lucky, you will need to find out which references you ought to have
on your version of Access.

Evi- Hide quoted text -

- Show quoted text -

Nope, the problem was the computer....sort of.

My hubby was in the process of configuring a new one for me at home
and when
I opened the database on the new computer the DLookup
worked......but....
its now the time of day where it should the second shift, but the
DLookup
is still saying its the first shift.....

Hi Opal
There is a thought that has struck me.
There is one time when this function will always fail - if you are testing
it for a time across midnight when Now() is past midnight and you may well
be doing that at home!

So if you have created a shift that starts at 23:00 and ends at 01:00, it
will work until the clock goes 1 second past midnight and then it won't find
it. That is because there isn't a record where the Start Time is less than
Now() (if the start time is 23:00hrs it is greater than Now if Now() is
00:05 hrs, if it is 01:00 hrs then it is greater than Now() and the first
criteria for the DLookup is that the start time has to be less than or equal
to now. I'm trying to find a way around that using a Function but I haven't
got it yet.

Evi- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Nope...don't work past midnight myself, but the database
will be worked past midnight once it goes live where I work.
The shift times 7am to 4:30pm and 5:45pm to 3:15am are
standard shift times.

I'm stumped on that one so far.
I suggest you start a new thread asking how to do this. I thought about
adding a date as well as a time to the Shift Field and adding that same date
to Now() in a function. I thought that I could add the next day to the shift
after midnight but it still doesn't work. Time for bed now (02:12am here!)

Evi


Evi

 

[Opal]

"
<SNIP>
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
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 -

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
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.- Hide quoted
text -
- Show quoted text -
Nope, didn't work...... :-(
Try making the Dlookup less complex just for test purposes
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# AND TimeValue([EndofShift]) >= #" & TimeValue(Now())
&
"#")
=DLookUp("[ShiftName]","ShiftName") (it should just look up a shift name)

then
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# )
It'll look up any shift start time which comes before the current time
is your table (or query) name actually "ShiftName"?
I wonder if you have a date hidden in your shift times (although I had
hoped
that TimeValue would sort that out). Make sure that *in the Table Design*,
on the Format line for StartOfShift and EndOfShift you type
hh:nn:ss
Just for test purposes, change the shift times so that one includes the
time
when you are testing
Do you have anything else in StartOfShift, EndOfShift other than the time?
Are they actually Date/Time fields?
Record 1 should be:
StartOfShift: 07:00:00
EndOfShift: 16:30:00
Record 2 should be
StartOfShift: 17:45:00
EndOfShift: 03:15:00
The text box, of course, will be blank if you test it at any time other
than
those in your table.
Make sure your Timer Interval is no less than 5000. (Make it 20000 to
start
with, just for testing)
Try testing Now() and TimeValue on your PC by having a text box say
=#17:00:00#>TimeValue(Now())
Depending on when you do this, the answer should be either 0 for untrue
or -1 for true
I've got this working so I know it is possible.
Evi- Hide quoted text -
- Show quoted text -
"Opal" <[email protected]> wrote in message

Thank you Evi. I couldn't get it working at home last night. Saved
it to a
USB key and took it work. Saved it on my computer on work, made no
changes
whatsoever before opening the file and it came up just fine. Spent
the
afternoon at work working on the database, tweaking things here and
there,
quitting time came, resaved to my USB key and saved it back to my
computer
at home and its not working again. So, obviously the problem is my
computer
and not what you provided. Thank you so much for all your help!
Hi Opal.
I don't think it's likely to be a problem with your PC so don't start
re-installing anything yet. It's far more likely that either:
1. You have a different version of Access at home which objects to something
about the function.
I'm having a problem here because the code for a Report Footer OnFormat
Event on my machine (Acc2000) starts with the line
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
A Converted database sent to me by someone with a highter version of Access
starts with:
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
(the word Section is missing)
Debug did not show an error but it didn't work as expected. I had to import
his database objects into a blank copy on my machine before I could make it
work and Heaven knows if it will work when it gets back to him!
You could try this with the database you have brought from work and see if
that makes a difference. Then also see below.
2. You have a missing Reference in Access on your PC.
Open any code Module. Go to Tools, References. If you are lucky, you will
see the word Missing. Tick that item and you will probably be fine. If you
are less lucky, you will need to find out which references you ought to have
on your version of Access.
Evi- Hide quoted text -
- Show quoted text -

Nope, the problem was the computer....sort of.

My hubby was in the process of configuring a new one for me at home
and when
I opened the database on the new computer the DLookup
worked......but....
its now the time of day where it should the second shift, but the
DLookup
is still saying its the first shift.....

Hi Opal
There is a thought that has struck me.
There is one time when this function will always fail - if you are testing
it for a time across midnight when Now() is past midnight and you may well
be doing that at home!

So if you have created a shift that starts at 23:00 and ends at 01:00, it
will work until the clock goes 1 second past midnight and then it won't find
it. That is because there isn't a record where the Start Time is less than
Now() (if the start time is 23:00hrs it is greater than Now if Now() is
00:05 hrs, if it is 01:00 hrs then it is greater than Now() and the first
criteria for the DLookup is that the start time has to be less than or equal
to now. I'm trying to find a way around that using a Function but I haven't
got it yet.

Evi- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Nope...don't work past midnight myself, but the database
will be worked past midnight once it goes live where I work.
The shift times 7am to 4:30pm and 5:45pm to 3:15am are
standard shift times.

I'm stumped on that one so far.
I suggest you start a new thread asking how to do this. I thought about
adding a date as well as a time to the Shift Field and adding that same date
to Now() in a function. I thought that I could add the next day to the shift
after midnight but it still doesn't work. Time for bed now (02:12am here!)

Evi

Evi

thanks Evi, will do...

 

[Evi]

OK Opal, Im Top Posting because this is just too long now

I have found an answer to the problem but am hoping that there is a better
one

Look for my message:
Look up between 2 times over midnight - is there an easier way than
recordset code?

Paste the function there into a new module page (ie not one attached to a
form) Under the 'Dims' change the table and field names to suit your own -
I've tried to stick to variables to make this easier.

Go to Debug, Compile and see if it sticks anywhere before trying to use it

Because it uses Recordset code, you may have to find a few references or the
code may have to be adjusted.

If it works, your text boxes and queries, you can then use

=ShiftFind(Now())

to get the times.

Test it out and let me know what problems arise at home and at work



Evi

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...... :-(
Try making the Dlookup less complex just for test purposes
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# AND TimeValue([EndofShift]) >= #" & TimeValue(Now())
&
"#")
=DLookUp("[ShiftName]","ShiftName") (it should just look up a shift name)

then
=DLookUp("[ShiftName]","ShiftName","TimeValue([StartofShift])<= #" &
TimeValue(Now()) & "# )
It'll look up any shift start time which comes before the current

time

make
it

see

if to
have

Nope, the problem was the computer....sort of.

My hubby was in the process of configuring a new one for me at home
and when
I opened the database on the new computer the DLookup
worked......but....
its now the time of day where it should the second shift, but the
DLookup
is still saying its the first shift.....

Hi Opal
There is a thought that has struck me.
There is one time when this function will always fail - if you are testing
it for a time across midnight when Now() is past midnight and you may well
be doing that at home!

So if you have created a shift that starts at 23:00 and ends at 01:00, it
will work until the clock goes 1 second past midnight and then it won't find
it. That is because there isn't a record where the Start Time is less than
Now() (if the start time is 23:00hrs it is greater than Now if Now() is
00:05 hrs, if it is 01:00 hrs then it is greater than Now() and the first
criteria for the DLookup is that the start time has to be less than or equal
to now. I'm trying to find a way around that using a Function but I haven't
got it yet.

Evi- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Nope...don't work past midnight myself, but the database
will be worked past midnight once it goes live where I work.
The shift times 7am to 4:30pm and 5:45pm to 3:15am are
standard shift times.

I'm stumped on that one so far.
I suggest you start a new thread asking how to do this. I thought about
adding a date as well as a time to the Shift Field and adding that same date
to Now() in a function. I thought that I could add the next day to the shift
after midnight but it still doesn't work. Time for bed now (02:12am here!)

Evi

Evi

thanks Evi, will do...