Code to auto fill txt/cbo boxes

[Opal]

I am trying to create a database whereby certain information is
automatically filled in for the user based on the day of the week, the
week in the year and the time of the day. However, I wonder if
there is a way to do this that will not involve lots and lots of
lines of code.....

For example, I have added the following to the on load event of my
main form:

Private Sub Form_Load()
If Me.txtTime >= #6:30:00 AM# And Me.txtTime <= #5:00:00 PM# Then
Me.cboShiftTime = 1
Else: Me.cboShiftTime = 2
End If
ShiftName
End Sub

The Shift time being Days (1) or Afternoons (2). Now I want the the
code
to go to the sub named "ShiftName" whereby based on what week it is
in the year, will determine which shift is on days and which shift is
on
afternoons: i.e. Week 1 and 2, Shift 2 is on days; Week 3 and 4, Shift
1;
Week 5 and 6, Shift 2; and so on...

I started to write the following:

Sub ShiftName()

If Forms![frmMain]!txtWeekNo = 1 And Forms![frmMain]!txtShiftTime = 1
Then
Forms![frmMain]!cboShiftName = 2
Else: Forms![frmMain]!cboShiftName = 1
End If
If Forms![frmMain]!txtWeekNo = 2 And Forms![frmMain]!txtShiftTime = 1
Then
Forms![frmMain]!cboShiftName = 2
Else: Forms![frmMain]!cboShiftName = 1
End If
If Forms![frmMain]!txtWeekNo = 3 And Forms![frmMain]!txtShiftTime = 1
Then
Forms![frmMain]!cboShiftName = 1
Else: Forms![frmMain]!cboShiftName = 2
End If
If Forms![frmMain]!txtWeekNo = 4 And Forms![frmMain]!txtShiftTime = 1
Then
Forms![frmMain]!cboShiftName = 1
Else: Forms![frmMain]!cboShiftName = 2
End If

..... and started to wonder if I needed to do this 52 times.....does
anyone have
any advice? Is there a simpler way to accomplish my goal? Thanks!

 

[Beetle]

Just a thought here, don't know if it will work for you, but if the shift
changes
every two weeks, how about adding a field to your (Shift?) table to use
as a date flag, then use code to determine if it's been two weeks since
the last time it was flagged. Something like;

If DateDiff("ww", [Me.DateFlag], Date) >=2 Then
If Me.ShiftTime = 1 Then
Me.ShitTime = 2
Else
Me.ShiftTime = 1
End If
Me.DateFlag = Now 'reset the date flag
End If

 

[Beetle]

oops. sorry about the unfortunate mispelling of "ShiftTime" there.

 

[Opal]

I am trying to create a database whereby certain information is
automatically filled in for the user based on the day of the week, the
week in the year and the time of the day.  However, I wonder if
there is a way to do this that will not involve lots and lots of
lines of code.....

For example, I have added the following to the on load event of my
main form:

Private Sub Form_Load()
   If Me.txtTime >= #6:30:00 AM# And Me.txtTime <= #5:00:00 PM# Then
   Me.cboShiftTime = 1
   Else: Me.cboShiftTime = 2
   End If
ShiftName
End Sub

The Shift time being Days (1) or Afternoons (2).  Now I want the the
code
to go to the sub named "ShiftName" whereby based on what week it is
in the year, will determine which shift is on days and which shift is
on
afternoons: i.e. Week 1 and 2, Shift 2 is on days; Week 3 and 4, Shift
1; Week 5 and 6, Shift 2; and so on...

If you know the week number, then you can calculate the
shift:

Forms![frmMain]!cboShiftName = _
                                        2 - (((Forms![frmMain]!txtWeekNo - 1) \ 2) Mod 2)

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thank you Marsh.....I do know the week number...but I'm unclear
as to how this code will "know" to "switch" a week from Monday
when the shift changes again....Do you mind explaining the
logic and can I use the same logic to determine which
hour in each shift we are in?

 

[Opal]

Opal wrote:
I am trying to create a database whereby certain information is
automatically filled in for the user based on the day of the week, the
week in the year and the time of the day.  However, I wonder if
there is a way to do this that will not involve lots and lots of
lines of code.....
For example, I have added the following to the on load event of my
main form:
Private Sub Form_Load()
   If Me.txtTime >= #6:30:00 AM# And Me.txtTime <= #5:00:00 PM# Then
   Me.cboShiftTime = 1
   Else: Me.cboShiftTime = 2
   End If
ShiftName
End Sub
The Shift time being Days (1) or Afternoons (2).  Now I want the the
code
to go to the sub named "ShiftName" whereby based on what week it is
in the year, will determine which shift is on days and which shift is
on
afternoons: i.e. Week 1 and 2, Shift 2 is on days; Week 3 and 4, Shift
1; Week 5 and 6, Shift 2; and so on...
If you know the week number, then you can calculate the
shift:
Forms![frmMain]!cboShiftName = _
             2 - (((Forms![frmMain]!txtWeekNo - 1) \ 2) Mod 2)

Thank you Marsh.....I do know the week number...but I'm unclear
as to how this code will "know" to "switch" a week from Monday
when the shift changes again....Do you mind explaining the
logic and can I use the same logic to determine which
hour in each shift we are in?

Why do you care about monday?  Given the week number, the
shift is determined as you explained
        "Week 1 and 2, Shift 2 is on days; " etc.

The expression breaks down:
WeekNo - 1                                                                     will be 0,1,2,3,4,5,6,7, ...
(WeekNo - 1) \ 2                                                        will be 0,0,1,1,2,2,3,3, ...
((WeekNo - 1) \ 2) Mod 2                        will be 0,0,1,1,0,0,1,1, ...
2 - (((WeekNo - 1) \ 2) Mod 2)  will be 2,2,1,1,2,2,1,1, ...

which, I believe, is the day shift number for any given
week.

I don't understand what you mean by: "can I use the same
logic to determine which hour in each shift we are in?"  You
already know the day shift is 6:30 to 17:00, etc.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thank you Marsh... what I needed to understand was how:

2 - (((WeekNo - 1) \ 2) Mod 2) will be 2,2,1,1,2,2,1,1, ...

broke down.

As for the other question. Further to my other question, in
another text box I want to auto fill in the "hour" of the shift,
i.e. first hour, second hour, etc as reports will need to be
generated at the end of each hour. I wanted to understand
the logic you employed to determine if similar logic was
possible for the other text box I have.

 

[Opal]

On Mar 30, 9:47 am, Marshall Barton wrote:
Opal wrote:
I am trying to create a database whereby certain information is
automatically filled in for the user based on the day of the week, the
week in the year and the time of the day.  However, I wonder if
there is a way to do this that will not involve lots and lots of
lines of code.....
For example, I have added the following to the on load event of my
main form:
Private Sub Form_Load()
   If Me.txtTime >= #6:30:00 AM# And Me.txtTime <= #5:00:00PM# Then
   Me.cboShiftTime = 1
   Else: Me.cboShiftTime = 2
   End If
ShiftName
End Sub
The Shift time being Days (1) or Afternoons (2).  Now I want the the
code
to go to the sub named "ShiftName" whereby based on what week it is
in the year, will determine which shift is on days and which shift is
on
afternoons: i.e. Week 1 and 2, Shift 2 is on days; Week 3 and 4, Shift
1; Week 5 and 6, Shift 2; and so on...
If you know the week number, then you can calculate the
shift:
Forms![frmMain]!cboShiftName = _
             2 - (((Forms![frmMain]!txtWeekNo - 1) \ 2)Mod 2)
Thank you Marsh.....I do know the week number...but I'm unclear
as to how this code will "know" to "switch" a week from Monday
when the shift changes again....Do you mind explaining the
logic and can I use the same logic to determine which
hour in each shift we are in?

Why do you care about monday?  Given the week number, the
shift is determined as you explained
        "Week 1 and 2, Shift 2 is on days; " etc.

The expression breaks down:
WeekNo - 1                                                                      will be 0,1,2,3,4,5,6,7, ...
(WeekNo - 1) \ 2                                                       will be 0,0,1,1,2,2,3,3, ...
((WeekNo - 1) \ 2) Mod 2                        will be 0,0,1,1,0,0,1,1, ...
2 - (((WeekNo - 1) \ 2) Mod 2)  will be 2,2,1,1,2,2,1,1, ...

which, I believe, is the day shift number for any given
week.

I don't understand what you mean by: "can I use the same
logic to determine which hour in each shift we are in?"  You
already know the day shift is 6:30 to 17:00, etc.

- Show quoted text -

Thank you Marsh... what I needed to  understand was how:

2 - (((WeekNo - 1) \ 2) Mod 2)  will be 2,2,1,1,2,2,1,1, ...

broke down.

As for the other question.  Further to my other question, in
another text box I want to auto fill in the "hour" of the shift,
i.e. first hour, second hour, etc as reports will need to be
generated at the end of each hour.  I wanted to understand
the logic you employed to determine if similar logic was
possible for the other text box I have.- Hide quoted text -

- Show quoted text -

Sorry Marsh.... still confused.... Shift 1 is on afternoons (Shift
Time = 2) this
week and Shift 2 on days (Shift Time = 1)......I don't think this
formula captures
that change since its currently after 5:00pm and my database is still
showing
Shift 2 when it should show Shift 1.

 

[Opal]

On Mar 30, 4:42 pm, Marshall Barton wrote:
Opal wrote:
On Mar 30, 9:47 am, Marshall Barton wrote:
Opal wrote:
I am trying to create a database whereby certain information is
automatically filled in for the user based on the day of the week, the
week in the year and the time of the day.  However, I wonder if
there is a way to do this that will not involve lots and lots of
lines of code.....
For example, I have added the following to the on load event of my
main form:
Private Sub Form_Load()
   If Me.txtTime >= #6:30:00 AM# And Me.txtTime <= #5:00:00 PM# Then
   Me.cboShiftTime = 1
   Else: Me.cboShiftTime = 2
   End If
ShiftName
End Sub
The Shift time being Days (1) or Afternoons (2).  Now I want the the
code
to go to the sub named "ShiftName" whereby based on what week it is
in the year, will determine which shift is on days and which shift is
on
afternoons: i.e. Week 1 and 2, Shift 2 is on days; Week 3 and 4, Shift
1; Week 5 and 6, Shift 2; and so on...
If you know the week number, then you can calculate the
shift:
Forms![frmMain]!cboShiftName = _
             2 - (((Forms![frmMain]!txtWeekNo - 1) \2) Mod 2)
Thank you Marsh.....I do know the week number...but I'm unclear
as to how this code will "know" to "switch" a week from Monday
when the shift changes again....Do you mind explaining the
logic and can I use the same logic to determine which
hour in each shift we are in?
Why do you care about monday?  Given the week number, the
shift is determined as you explained
        "Week 1 and 2, Shift 2 is on days; " etc.
The expression breaks down:
WeekNo - 1                           will be 0,1,2,3,4,5,6,7, ...
(WeekNo - 1) \ 2                    will be 0,0,1,1,2,2,3,3, ...
((WeekNo - 1) \ 2) Mod 2        will be 0,0,1,1,0,0,1,1, ...
2 - (((WeekNo - 1) \ 2) Mod 2) will be 2,2,1,1,2,2,1,1, ...
which, I believe, is the day shift number for any given
week.
Thank you Marsh... what I needed to  understand was how:
2 - (((WeekNo - 1) \ 2) Mod 2)  will be 2,2,1,1,2,2,1,1, ...
broke down.-

Sorry Marsh.... still confused....  Shift 1 is on afternoons (Shift
Time = 2) this
week and Shift 2 on days (Shift Time = 1)......I don't think this
formula captures
that change since its currently after 5:00pm and my database is still
showing Shift 2 when it should show Shift 1.

No, it tells you which shift is on Days that week.  If you
want to know which shift is currently working, then you need
to use the ShiftTime (based on the time of day) value that
you calculated earlier.  One way to look at it is a little
grid:
            ShiftTime
                                |       1      2
------------|---------------
Day   1 |   1      2     Shift on
Shift  2 |   2      1     duty now

An obscure, but very simple, expression can be used to
calculate the on duty shift value in the above grid.
txtDayShift is the value we calculated earlier using the
week number.

        txtOnDuty = (2 + (txtShiftTime = txtDayShift))

The way that works relies on the facts that the comparison
will either be True or False and that True is represented as
-1 and False as 0.  So, if the shift time and the day shift
are equal, the expression will boil down to
2 - 1 and if they are different 2 - 0

If you have bo need to display the txtDayShift result, then
you could either make the txtDayShift text box invisible or
get rid of it and substitute that expression into this one.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thanks for your help, Marsh.

I tried the "txtOnDuty = (2 + ([txtShiftTime] = [txtDayShift]))"

and when txtShiftTime = 2, txOnDuty =2, but txtDayShift
=2 and shift time is afternoons when it = 2....?

Basically, ShiftName 1 should be on ShiftTime 2
and ShiftName 2 should be on ShiftTime 1 but I have
both Shift 1 and 2 on on Shift time 2.

 

[Opal]

Opal wrote:
On Mar 30, 4:42 pm, Marshall Barton wrote:
Opal wrote:
On Mar 30, 9:47 am, Marshall Barton wrote:
Opal wrote:
I am trying to create a database whereby certain information is
automatically filled in for the user based on the day of the week, the
week in the year and the time of the day.  However, I wonderif
there is a way to do this that will not involve lots and lots of
lines of code.....
For example, I have added the following to the on load event of my
main form:
Private Sub Form_Load()
   If Me.txtTime >= #6:30:00 AM# And Me.txtTime <= #5:00:00 PM# Then
   Me.cboShiftTime = 1
   Else: Me.cboShiftTime = 2
   End If
ShiftName
End Sub
The Shift time being Days (1) or Afternoons (2).  Now I wantthe the
code
to go to the sub named "ShiftName" whereby based on what week it is
in the year, will determine which shift is on days and which shift is
on
afternoons: i.e. Week 1 and 2, Shift 2 is on days; Week 3 and 4, Shift
1; Week 5 and 6, Shift 2; and so on...
If you know the week number, then you can calculate the
shift:
Forms![frmMain]!cboShiftName = _
             2 - (((Forms![frmMain]!txtWeekNo - 1) \ 2) Mod 2)
Thank you Marsh.....I do know the week number...but I'm unclear
as to how this code will "know" to "switch" a week from Monday
when the shift changes again....Do you mind explaining the
logic and can I use the same logic to determine which
hour in each shift we are in?
Why do you care about monday?  Given the week number, the
shift is determined as you explained
        "Week 1 and 2, Shift 2 is on days; " etc.
The expression breaks down:
WeekNo - 1                           will be 0,1,2,3,4,5,6,7, ...
(WeekNo - 1) \ 2                    will be 0,0,1,1,2,2,3,3, ...
((WeekNo - 1) \ 2) Mod 2        will be 0,0,1,1,0,0,1,1, ....
2 - (((WeekNo - 1) \ 2) Mod 2) will be 2,2,1,1,2,2,1,1, ...
which, I believe, is the day shift number for any given
week.
Thank you Marsh... what I needed to  understand was how:
2 - (((WeekNo - 1) \ 2) Mod 2)  will be 2,2,1,1,2,2,1,1, ...
broke down.-
Sorry Marsh.... still confused....  Shift 1 is on afternoons (Shift
Time = 2) this
week and Shift 2 on days (Shift Time = 1)......I don't think this
formula captures
that change since its currently after 5:00pm and my database is still
showing Shift 2 when it should show Shift 1.
No, it tells you which shift is on Days that week.  If you
want to know which shift is currently working, then you need
to use the ShiftTime (based on the time of day) value that
you calculated earlier.  One way to look at it is a little
grid:
            ShiftTime
                                |       1      2
------------|---------------
Day   1 |   1      2     Shift on
Shift  2 |   2      1     duty now
An obscure, but very simple, expression can be used to
calculate the on duty shift value in the above grid.
txtDayShift is the value we calculated earlier using the
week number.
        txtOnDuty = (2 + (txtShiftTime = txtDayShift))
The way that works relies on the facts that the comparison
will either be True or False and that True is represented as
-1 and False as 0.  So, if the shift time and the day shift
are equal, the expression will boil down to
2 - 1 and if they are different 2 - 0
If you have bo need to display the txtDayShift result, then
you could either make the txtDayShift text box invisible or
get rid of it and substitute that expression into this one.

I tried the "txtOnDuty = (2 + ([txtShiftTime] = [txtDayShift]))"

and when txtShiftTime = 2, txOnDuty =2, but txtDayShift
=2 and shift time is afternoons when it = 2....?

Basically, ShiftName 1 should be on ShiftTime 2
and ShiftName 2 should be on ShiftTime 1 but I have
both Shift 1 and 2 on on Shift time 2.

That's confusing to me.

Seems like if txtDayShift=2 and txtShiftTime=2
then txtOnDuty=1, which is what I thought you wanted.

Double check to make sure all the other calculations are
done before the txtOnDuty calculation.  You will get odd
results if they are done in the wrong order.  Or, if they
are done in the right order, maybe one value is changing but
some of the other calculations are not being redone.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Okay....let me see if I can break this down a little better because
I am only about half way to where I want to be with this:

I have a txtTodaysDate (unbound) and txtTime (unbound) with
the following:

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

I have a txtWeekNo (unbound) with the following control source
=DatePart("ww",Date())

I have a txtDayofWeek (unbound) with the following control source
=DatePart("w",[txtTodaysDate])

I have a txtDayShift (unbound) with the following as you provided
=2-(((Forms!frmMain!txtWeekNo-1)\2) Mod 2)
It is currently showing shift 2 as on Days which is correct.

I have a txtShiftTime (unbound) which I cannot get working properly
to reveal the correct current shift (Days or Afternoons)

The code I was trying is as follows:

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

But I get an invalid use of null error. It has been suggested that I
use Dlookup to my table "ShiftTime"
But isn't Dlookup slow and full of errors.....?

Finally, I have a txtShiftName (unbound) which I need to show which
shift is currently on based on the ShiftTime......i.e. ShiftName 1
should be on ShiftTime2 and ShiftName
2 should be on ShiftTime1 this week.

Any help would be appreciated. Thank you.

 

[Opal]

Okay....let me see if I can break this down a little better because
I am only about half way to where I want to be with this:

I have a txtTodaysDate (unbound) and txtTime (unbound) with
the following:

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

I have a txtWeekNo (unbound) with the following control source
=DatePart("ww",Date())

I have a txtDayofWeek (unbound) with the following control source
=DatePart("w",[txtTodaysDate])

I have a txtDayShift (unbound) with the following as you provided
=2-(((Forms!frmMain!txtWeekNo-1)\2) Mod 2)
It is currently showing shift 2 as on Days which is correct.

I have a txtShiftTime (unbound) which I cannot get working properly
to reveal the correct current shift (Days or Afternoons)

The code I was trying is as follows:

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

But I get an invalid use of null error.  It has been suggested that I
use Dlookup to my table "ShiftTime"
But isn't Dlookup slow and full of errors.....?

Finally, I have a txtShiftName (unbound) which I need to show which
shift is currently on based on the ShiftTime......i.e. ShiftName 1
should be on ShiftTime2 and ShiftName
2 should be on ShiftTime1 this week.

I think a major discrepency in what you/ve explained above
is that you are mixing text box control source expression
calculations and VBA code calculations.  This can not work
in general because two different (Windows) tasks deal with
the two separate types of calculations and they can not be
synchronized.  All that confusing stuff means that you need
to all this in VBA code (in your form's Timer event?) or do
it all in text box expressions (might get messy).

I think(?) doing it all in VBA would be something like:

Private Sub Form_Timer()
   txtTime = Time
   txtTodaysDate = Date

        txtWeekNo = DatePart("ww", txtTodaysDate )
        txtDayofWeek = DatePart("w", txtTodaysDate)

        If txtTime >=  #6:30# And txtTime <= #17:00# Then
                txtShiftTime = 1
        ElseIf txtTime >= #17:30# Or txtTime <= #3:45#Then
                txtShiftTime = 2
        End If

        txtDayShift = 2-(((txtWeekNo-1)\2) Mod 2)
        txtShiftName = (2 + (txtShiftTime = txtDayShift))
End Sub

If all these text boxes are unbound, then I am wondering if
this form is just an elaborate clock/calendar??  Not that it
isn't a good choice, but I am also wondering why you are
using a database sytem like Access to do this job?

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Good question....there is more to the database than
just an elaborate clock/calendar. If I can get these all
working it will make things easier for the users. They
will be entering hourly repair data into the database and
I want to also set it up to auto email reports to management
on repair status. The users time is limited and the less
items they need to enter into fields the easier it is for them
but at the same time with give management the data they
need to analyze repair times.

The code you gave above works great with one exception...
txtDayShift is Shift 2 which is correct. However, current
txtShiftTime is Afternoons but txtShiftName is coming up
as shift 2, it should be shift 1. How can we get the correct
shift name to show for the current shift time?

 

[Opal]

On Apr 2, 9:14 pm, Marshall Barton wrote: []

Finally, I have a txtShiftName (unbound) which I need to show which
shift is currently on based on the ShiftTime......i.e. ShiftName 1
should be on ShiftTime2 and ShiftName
2 should be on ShiftTime1 this week.
I think a major discrepency in what you/ve explained above
is that you are mixing text box control source expression
calculations and VBA code calculations.  This can not work
in general because two different (Windows) tasks deal with
the two separate types of calculations and they can not be
synchronized.  All that confusing stuff means that you need
to all this in VBA code (in your form's Timer event?) or do
it all in text box expressions (might get messy).
I think(?) doing it all in VBA would be something like:
Private Sub Form_Timer()
   txtTime = Time
   txtTodaysDate = Date
        txtWeekNo = DatePart("ww", txtTodaysDate )
        txtDayofWeek = DatePart("w", txtTodaysDate)
        If txtTime >=  #6:30# And txtTime <= #17:00# Then
                txtShiftTime = 1
        ElseIf txtTime >= #17:30# Or txtTime <= #3:45#Then
                txtShiftTime = 2
        End If
        txtDayShift = 2-(((txtWeekNo-1)\2) Mod 2)
        txtShiftName = (2 + (txtShiftTime = txtDayShift))
End Sub
If all these text boxes are unbound, then I am wondering if
this form is just an elaborate clock/calendar??  Not that it
isn't a good choice, but I am also wondering why you are
using a database sytem like Access to do this job?

[]
The code you gave above works great with one exception...
txtDayShift is Shift 2 which is correct.  However, current
txtShiftTime is Afternoons but txtShiftName is coming up
as shift 2, it should be shift 1.  How can we get the correct
shift name to show for the current shift time?

If shifttime = 2 and dayshift = 2, then I don't see how the
shift name expression (2 + (txtShiftTime = txtDayShift)) can
be anything other than 1.

Just in case your code is somehow different from mine, post
a Copy/Paste of what you currently have.  Also, double check
that all these text box's have nothing in their control
source.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thanks, Marsh,
Private Sub Form_Timer()
txtTime = Time
txtTodaysDate = Date


txtWeekNo = DatePart("ww", txtTodaysDate)
txtDayofWeek = DatePart("w", txtTodaysDate)


If txtTime >= #6:30:00 AM# And txtTime <= #5:00:00 PM# Then
txtShiftTime = 1
ElseIf txtTime >= #5:30:00 PM# Or txtTime <= #3:45:00 AM# Then
txtShiftTime = 2
End If


txtDayShift = 2 - (((txtWeekNo - 1) \ 2) Mod 2)
txtShiftName = (2 + (txtShiftTime = txtDayShift))
End Sub

And I have confirmed that

txtTime, txtDayofWeek, txtWeekNo, txtTodaysDate, txtShiftTime,
txtShiftName and txtDayShift
are all unbound with nothing in their control source.

 

[Opal]

On Apr 2, 9:14 pm, Marshall Barton wrote: []
Finally, I have a txtShiftName (unbound) which I need to show which
shift is currently on based on the ShiftTime......i.e. ShiftName 1
should be on ShiftTime2 and ShiftName
2 should be on ShiftTime1 this week.
I think a major discrepency in what you/ve explained above
is that you are mixing text box control source expression
calculations and VBA code calculations.  This can not work
in general because two different (Windows) tasks deal with
the two separate types of calculations and they can not be
synchronized.  All that confusing stuff means that you need
to all this in VBA code (in your form's Timer event?) or do
it all in text box expressions (might get messy).
I think(?) doing it all in VBA would be something like:
Private Sub Form_Timer()
   txtTime = Time
   txtTodaysDate = Date
        txtWeekNo = DatePart("ww", txtTodaysDate )
        txtDayofWeek = DatePart("w", txtTodaysDate)
        If txtTime >=  #6:30# And txtTime <= #17:00# Then
                txtShiftTime = 1
        ElseIf txtTime >= #17:30# Or txtTime <= #3:45#Then
                txtShiftTime = 2
        End If
        txtDayShift = 2-(((txtWeekNo-1)\2) Mod 2)
        txtShiftName = (2 + (txtShiftTime = txtDayShift))
End Sub
If all these text boxes are unbound, then I am wondering if
this form is just an elaborate clock/calendar??  Not that it
isn't a good choice, but I am also wondering why you are
using a database sytem like Access to do this job?

[]
The code you gave above works great with one exception...
txtDayShift is Shift 2 which is correct.  However, current
txtShiftTime is Afternoons but txtShiftName is coming up
as shift 2, it should be shift 1.  How can we get the correct
shift name to show for the current shift time?

If shifttime = 2 and dayshift = 2, then I don't see how the
shift name expression (2 + (txtShiftTime = txtDayShift)) can
be anything other than 1.

Just in case your code is somehow different from mine, post
a Copy/Paste of what you currently have.  Also, double check
that all these text box's have nothing in their control
source.

- Show quoted text -

Thanks, Marsh,
Private Sub Form_Timer()
     txtTime = Time
     txtTodaysDate = Date

        txtWeekNo = DatePart("ww", txtTodaysDate)
        txtDayofWeek = DatePart("w", txtTodaysDate)

        If txtTime >= #6:30:00 AM# And txtTime <= #5:00:00 PM#Then
                txtShiftTime = 1
        ElseIf txtTime >= #5:30:00 PM# Or txtTime <= #3:45:00 AM# Then
                txtShiftTime = 2
        End If

        txtDayShift = 2 - (((txtWeekNo - 1) \ 2) Mod 2)
        txtShiftName = (2 + (txtShiftTime = txtDayShift))
End Sub

And I have confirmed that

txtTime, txtDayofWeek, txtWeekNo, txtTodaysDate, txtShiftTime,
txtShiftName and txtDayShift
are all unbound with nothing in their control source.- Hide quoted text -

- Show quoted text -

Now this is interesting..... the following code you gave me for
shift hour....(on March 30th)

If Me.txtTime >= #7:00:00 AM# And Me.txtTime <= #4:30:00 PM# Then
Me.txtShiftHour = DateDiff("n", #7:30:00 AM#, Me.txtTime) \ 60
+ 1
Else
Me.txtShiftHour = DateDiff("n", #5:45:00 PM#, Me.txtTime) \ 60
+ 1
End If

perhaps I have put it in the wrong event (on Timer) but the result is
12:00am..... I don't understand....?

 

[Opal]

On Apr 4, 12:44 am, Marshall Barton wrote:
Opal wrote:
On Apr 2, 9:14 pm, Marshall Barton wrote: []
I think(?) doing it all in VBA would be something like:
Private Sub Form_Timer()
   txtTime = Time
   txtTodaysDate = Date
        txtWeekNo = DatePart("ww", txtTodaysDate )
        txtDayofWeek = DatePart("w", txtTodaysDate)
        If txtTime >=  #6:30# And txtTime <= #17:00#Then
                txtShiftTime = 1
        ElseIf txtTime >= #17:30# Or txtTime <= #3:45#Then
                txtShiftTime = 2
        End If
        txtDayShift = 2-(((txtWeekNo-1)\2) Mod 2)
        txtShiftName = (2 + (txtShiftTime = txtDayShift))
End Sub
[]
The code you gave above works great with one exception...
txtDayShift is Shift 2 which is correct.  However, current
txtShiftTime is Afternoons but txtShiftName is coming up
as shift 2, it should be shift 1.  How can we get the correct
shift name to show for the current shift time?
If shifttime = 2 and dayshift = 2, then I don't see how the
shift name expression (2 + (txtShiftTime = txtDayShift)) can
be anything other than 1.
Just in case your code is somehow different from mine, post
a Copy/Paste of what you currently have.  Also, double check
that all these text box's have nothing in their control
source.
Thanks, Marsh,
Private Sub Form_Timer()
     txtTime = Time
     txtTodaysDate = Date
        txtWeekNo = DatePart("ww", txtTodaysDate)
        txtDayofWeek = DatePart("w", txtTodaysDate)
        If txtTime >= #6:30:00 AM# And txtTime <= #5:00:00 PM# Then
                txtShiftTime = 1
        ElseIf txtTime >= #5:30:00 PM# Or txtTime <= #3:45:00 AM# Then
                txtShiftTime = 2
        End If
        txtDayShift = 2 - (((txtWeekNo - 1) \ 2) Mod 2)
        txtShiftName = (2 + (txtShiftTime = txtDayShift))
End Sub

Now this is interesting.....  the following code you gave me for
shift hour....(on March 30th)

If Me.txtTime >= #7:00:00 AM# And Me.txtTime <= #4:30:00 PM# Then
       Me.txtShiftHour = DateDiff("n", #7:30:00 AM#, Me.txtTime) \ 60
+ 1
       Else
       Me.txtShiftHour = DateDiff("n", #5:45:00 PM#, Me.txtTime) \ 60
+ 1
       End If

perhaps I have put it in the wrong event (on Timer) but the result is
12:00am.....  I don't understand....?

I thought that you wanted the shift hour to be 1, 2, 3, ...
for first, second. etc. hour of the shift (when you format
an integer value as as if it were a time value, you will get
midnight).  Remove whatever you have in txtShiftHour's
format property and see if you get a more reasonable
display.  If I misunderstood what you wanted for
txtShiftHour, please explain it to me again.

Did you make any progress on figuring out how you getting
the wrong txtShiftName?  I just see how it's possible to
have txtShiftTime=2 and txtDayShift=2 and
get txtShiftName=2  ???

Maybe it's related to all the different times you have used
for the start and end of shifts.  Including the times you
are using in your other thread with Evi, I see at least
three different sets of shift start and end times.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Hi Marsh,

If I remove the format from the txt box I get "949051" at 1:30pm
I have been trying a Dlookup function in this instead but will run
into a problem with the shift change, I think.....

I know I showed different times with the thread with Evi...some
confusion
there. I have fixed them to show only 7:00am to 4:30pm Day shift
(shift #1)
and 5:45pm to 3:15am Afternoon shift (shift #2)

 

[Opal]

If I remove the format from the txt box I get "949051" at 1:30pm
I have been trying a Dlookup function in this instead but will run
into a problem with the shift change, I think.....

I know I showed different times with the thread with Evi...some
confusion
there.  I have fixed them to show only 7:00am to 4:30pm Day shift
(shift #1)
and 5:45pm to 3:15am Afternoon shift (shift #2)

You will get that kind of weird result if you set txtTime to
Now instead of Time.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

So I changed the txtTime box from Now to Time and I still get
949051

 

[Opal]

Please post the code that you used.

I'm getting the impression that you have more than one
version and I am having trouble tracking what you are using
when you get these strange results.

Hi Marsh!

I think I have been too many things at once with this database
but now I think I have a handle on it all.....I have the following
in the On Current event for the form:

If Me.txtTime >= #7:00:00 AM# And Me.txtTime <= #4:30:00 PM# Then
Me.txtShiftHour = DateDiff("n", #7:30:00 AM#, Me.txtTime) \ 60
+ 1
Else
Me.txtShiftHour = DateDiff("n", #5:45:00 PM#, Me.txtTime) \ 60
+ 1
End If

And its working now.....I was trying too many other options -
DLookups, etc

Thank you so much for all your help!

 

[Opal]

Hi Marsh!

I think I have been too many things at once with this database
but now I think I have a handle on it all.....I have the following
in the On Current event for the form:

 If Me.txtTime >= #7:00:00 AM# And Me.txtTime <= #4:30:00 PM# Then
        Me.txtShiftHour = DateDiff("n", #7:30:00 AM#, Me.txtTime) \ 60
+ 1
        Else
        Me.txtShiftHour = DateDiff("n", #5:45:00 PM#, Me.txtTime) \ 60
+ 1
        End If

And its working now.....I was trying too many other options -
DLookups, etc

Thank you so much for all your help!- Hide quoted text -

- Show quoted text -

Hmmm... further thought on this one....Monday to Thursday shift starts
@ 5:45pm but
on Friday it starts @ 4:45pm....would this just be a matter of an
additional line of
code?

 

[Opal]

Well, it could be a few more lines of code that check the
time and the day of the week, but IMO, it really is time to
put the shift schedure in a table.  I think your other
thread on this aspect has reached a breakthrough, but you
will need to add a DOW (day of week) field to the table so
you can have different schedules for different days.
Graham's function will have to be modified to deal with this
issue.

My thoughts exactly. I have put the schedule in a table and
as soon as I saw Graham's code I started to think that this
could just be modified to work for the schedule too!

I need to throw another variable into Graham's code to
account for the day of the week as you said I'm just not sure
how and where....