populate time field based on field

J

John Powell

I have three set due times that I am trying to auto populate. If it
is "1st" shift the due time should populate at 3:00, "2nd" shift at
11:00, and "3rd" shift at 0700.

This is a "Key Checkout" database that is set up on a barcode reader.
The idea is that using a static Due_Time, the issuer of the key will
know who is late in returning the key - by use of an "Overdue"
reminder. I am a novice at this but the powers that be have faith
that this can be done.

Once scanned, the employee is selected in a list box - Name, title,
employee Id. A "shift" combo box is auto filled based upon the list
box selection. I have the selected shift (1st, 2nd, 3rd) set up on
the following and it doesn't work, Due Date is set up on the Date &
Time format (General).

Private Sub Checked_Out_To_AfterUpdate()
Select Case Me.[Checked Out To].Value
Case "1st"
Me.[Due_Date].Value = 1700
Case "2nd"
Me.[Due_Date].Value = 1100
Case "3rd"
Me.[Due_Date].Value = 0700
End Select

End Sub

I would much rather set it up on a time format, meaning if key is
checked out at 7am it automatically knows it must be returned by 5pm
or a reminder will appear.

Any help would be appreciated,

John
 
J

John Spencer

Well, dates and times (datetime fields) require that you use # delimiters
Me.DueDate = #17:00#



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
J

John W. Vinson

Once scanned, the employee is selected in a list box - Name, title,
employee Id. A "shift" combo box is auto filled based upon the list
box selection. I have the selected shift (1st, 2nd, 3rd) set up on
the following and it doesn't work, Due Date is set up on the Date &
Time format (General).

Private Sub Checked_Out_To_AfterUpdate()
Select Case Me.[Checked Out To].Value
Case "1st"
Me.[Due_Date].Value = 1700
Case "2nd"
Me.[Due_Date].Value = 1100
Case "3rd"
Me.[Due_Date].Value = 0700
End Select
Click to expand...

The reason it isn't working is because of how Date/Time values are stored: a
Double Float number, a count of days and fractions of a day since midnight,
December 30, 1899. So when you put an integer 1700 or 1100 or 700 into the
Due_Date field, it's storing midnight on some day in the first few years of
the 20th century!

Use a date literal, which Access will recognize if it's delimited with #
marks, instead:

Me.Due_Date = #17:00#

and simillarly for the others.

You don't need the .Value property, it's the default, nor the brackets since
you don't have blanks in the name - the don't hurt but they're unnecessary.
 
J

John Powell

Once scanned, the employee is selected in a list box - Name, title,
employee Id.  A "shift" combo box is auto filled based upon the list
box selection.  I have the selected shift (1st, 2nd, 3rd) set up on
the following and it doesn't work, Due Date is set up on the Date &
Time format (General).
Click to expand...
Private Sub Checked_Out_To_AfterUpdate()
Select Case Me.[Checked Out To].Value
   Case "1st"
       Me.[Due_Date].Value = 1700
   Case "2nd"
       Me.[Due_Date].Value = 1100
Case "3rd"
       Me.[Due_Date].Value = 0700
End Select
Click to expand...

The reason it isn't working is because of how Date/Time values are stored: a
Double Float number, a count of days and fractions of a day since midnight,
December 30, 1899. So when you put an integer 1700 or 1100 or 700 into the
Due_Date field, it's storing midnight on some day in the first few years of
the 20th century!

Use a date literal, which Access will recognize if it's delimited with #
marks, instead:

Me.Due_Date = #17:00#

and simillarly for the others.

You don't need the .Value property, it's the default, nor the brackets since
you don't have blanks in the name - the don't hurt but they're unnecessary.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -
Click to expand...



More Info:

The list box "CheckedOutTo" is populated using SELECT [Contacts
Extended].ID, [Contacts Extended].[Contact Name] & ", " & [Shift] FROM
[Contacts Extended] ORDER BY [Contacts Extended].[Shift];

I want the list box selection to auto fill the Due_Date text box with
the corresponding end of "Shift" times as follows:

Private Sub CheckedOutTo_AfterUpdate()
Select Case Me.[CheckedOutTo_]

Case "1st"
Me.Due_Date = #5:00:00 PM#
Case "2nd"
Me.Due_Date = #11:00:00 PM#
Case "3rd"
Me.Due_Date = #7:00:00 AM#

End Select
End Sub

I can't make this work, it looks like it should - I don't know if its
not reading the "SELECTt" data or not. If there is a better
option.....I use it.

Thanks,

John
 
J

John Spencer

Unless the bound column of the list box is the column that contains 1st, 2nd,
or 3rd this is going to fail because your code is looking at the value of the
bound column.

Private Sub CheckedOutTo_AfterUpdate()
Select Case Me.[CheckedOutTo_] '<<<<<<<
'The second column seems to be the one that has the SHIFT value in it.
'Columns are zero-based so the 2nd column is column(1)
'Assuming that the column looks like "Sam Jones, 2nd" then
'Try this code

Select Case Me.CheckedOutTo.Column(1)

Case Like "*1st"
Me.Due_Date = #5:00:00 PM#
Case "*2nd"
Me.Due_Date = #11:00:00 PM#
Case "*3rd"
Me.Due_Date = #7:00:00 AM#

End Select
End Sub

Personnally, I would just include SHIFT as another column (you could hide it
by setting its column width to zero) and then use

SELECT Case Me.CheckedOutTo.Column(2)
Case "1st"
Case "2nd"
Case "3rd"
End case

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John said:
Once scanned, the employee is selected in a list box - Name, title,
employee Id. A "shift" combo box is auto filled based upon the list
box selection. I have the selected shift (1st, 2nd, 3rd) set up on
the following and it doesn't work, Due Date is set up on the Date &
Time format (General).
Private Sub Checked_Out_To_AfterUpdate()
Select Case Me.[Checked Out To].Value
Case "1st"
Me.[Due_Date].Value = 1700
Case "2nd"
Me.[Due_Date].Value = 1100
Case "3rd"
Me.[Due_Date].Value = 0700
End Select
Click to expand...
The reason it isn't working is because of how Date/Time values are stored: a
Double Float number, a count of days and fractions of a day since midnight,
December 30, 1899. So when you put an integer 1700 or 1100 or 700 into the
Due_Date field, it's storing midnight on some day in the first few years of
the 20th century!

Use a date literal, which Access will recognize if it's delimited with #
marks, instead:

Me.Due_Date = #17:00#

and simillarly for the others.

You don't need the .Value property, it's the default, nor the brackets since
you don't have blanks in the name - the don't hurt but they're unnecessary.
--

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -
Click to expand...



More Info:

The list box "CheckedOutTo" is populated using SELECT [Contacts
Extended].ID, [Contacts Extended].[Contact Name] & ", " & [Shift] FROM
[Contacts Extended] ORDER BY [Contacts Extended].[Shift];

I want the list box selection to auto fill the Due_Date text box with
the corresponding end of "Shift" times as follows:

Private Sub CheckedOutTo_AfterUpdate()
Select Case Me.[CheckedOutTo_]

Case "1st"
Me.Due_Date = #5:00:00 PM#
Case "2nd"
Me.Due_Date = #11:00:00 PM#
Case "3rd"
Me.Due_Date = #7:00:00 AM#

End Select
End Sub

I can't make this work, it looks like it should - I don't know if its
not reading the "SELECTt" data or not. If there is a better
option.....I use it.

Thanks,

John
Click to expand...
 
J

John W. Vinson

The list box "CheckedOutTo" is populated using SELECT [Contacts
Extended].ID, [Contacts Extended].[Contact Name] & ", " & [Shift] FROM
[Contacts Extended] ORDER BY [Contacts Extended].[Shift];
Click to expand...

What's the bound column of the listbox? Your code seems to assume that the
combo box's value will be a text string "1st", "2nd", "3rd", but the Shift
field (which I'd guess contains those values) isn't even in the query other
than buried in a concatenated text string.

Try changing the rowsource to

SELECT [Contacts Extended].ID, [Contacts Extended].[Contact Name], [Shift]
FROM [Contacts Extended]
ORDER BY [Contacts Extended].[Shift];

and set its ColumnCount to 3; then use

Select Case Me.[CheckedOutTo_].Column(2)

to select the shift value rather than the ID.
 
N

newtoaccess

The list box "CheckedOutTo" is populated using SELECT [Contacts
Extended].ID, [Contacts Extended].[Contact Name] & ", " & [Shift] FROM
[Contacts Extended] ORDER BY [Contacts Extended].[Shift];
Click to expand...

What's the bound column of the listbox? Your code seems to assume that the
combo box's value will be a text string "1st", "2nd", "3rd", but the Shift
field (which I'd guess contains those values) isn't even in the query other
than buried in a concatenated text string.

Try changing the rowsource to

 SELECT [Contacts Extended].ID, [Contacts Extended].[Contact Name], [Shift]
FROM [Contacts Extended]
ORDER BY [Contacts Extended].[Shift];

and set its ColumnCount to 3; then use

Select Case Me.[CheckedOutTo_].Column(2)

to select the shift value rather than the ID.
Click to expand...

Back again.....
I have tried all recommendations, but none are working. I'm sure that
I have failed to mention something that would help you understand my
problem better. So here I go.....

The Form was pillaged from the "Lending Library" database, CHECK OUT.
The List box on the CHECK OUT form incorporates the following SELECT
clause:

SELECT [Contacts Extended].ID, [Contacts Extended].[Shift] & ", " &
[Contacts Extended].[Contact Name] & ", " & [Contacts Extended].[Job
Title] & ", " & [Contacts Extended].[Employee ID] FROM [Contacts
Extended] ORDER BY [Contacts Extended].[Shift];


Once a name is selected from the list box, I want the Due_Date control
on the form to auto fill based upon the shift of the employee (1st,
2nd, 3rd). The name of the list box is "CheckedOutTo", the Control is
Checked_Out_To. On the After Update of the Checked_Out_To list box, I
have entered:

Private Sub CheckedOutTo_AfterUpdate()
Select Case Me.CheckedOutTo.Column(1)
Case Like "*1st"
Me.Due_Date = #5:00:00 PM#
Case "*2nd"
Me.Due_Date = #11:00:00 PM#
Case "*3rd"
Me.Due_Date = #7:00:00 AM#
End Select
End Sub

The "Like" statement creates an error, if removed from statement the
error goes away. But still does not work. I am truly grateful to all
of you, it is a blessing to be able have access to the knowledge you
guys possess, I am just sorry that I have yet to properly explain my
issue, I hope knowing where I got the form template helps..

Thanks again,

John
 
J

John W. Vinson

Back again.....
I have tried all recommendations, but none are working. I'm sure that
I have failed to mention something that would help you understand my
problem better. So here I go.....

The Form was pillaged from the "Lending Library" database, CHECK OUT.
The List box on the CHECK OUT form incorporates the following SELECT
clause:

SELECT [Contacts Extended].ID, [Contacts Extended].[Shift] & ", " &
[Contacts Extended].[Contact Name] & ", " & [Contacts Extended].[Job
Title] & ", " & [Contacts Extended].[Employee ID] FROM [Contacts
Extended] ORDER BY [Contacts Extended].[Shift];


Once a name is selected from the list box, I want the Due_Date control
on the form to auto fill based upon the shift of the employee (1st,
2nd, 3rd). The name of the list box is "CheckedOutTo", the Control is
Checked_Out_To. On the After Update of the Checked_Out_To list box, I
have entered:

Private Sub CheckedOutTo_AfterUpdate()
Select Case Me.CheckedOutTo.Column(1)
Case Like "*1st"
Me.Due_Date = #5:00:00 PM#
Case "*2nd"
Me.Due_Date = #11:00:00 PM#
Case "*3rd"
Me.Due_Date = #7:00:00 AM#
End Select
End Sub

The "Like" statement creates an error, if removed from statement the
error goes away. But still does not work. I am truly grateful to all
of you, it is a blessing to be able have access to the knowledge you
guys possess, I am just sorry that I have yet to properly explain my
issue, I hope knowing where I got the form template helps..
Click to expand...

Let's try again.

Open the form in design view.
Select the listbox.
Open its Rowsource query in SQL view.
CHANGE the SQL from

SELECT [Contacts Extended].ID, [Contacts Extended].[Shift] & ", " &
[Contacts Extended].[Contact Name] & ", " & [Contacts Extended].[Job
Title] & ", " & [Contacts Extended].[Employee ID] FROM [Contacts
Extended] ORDER BY [Contacts Extended].[Shift];

to

SELECT [Contacts Extended].ID, [Contacts Extended].[Shift] & ", " &
[Contacts Extended].[Contact Name] & ", " & [Contacts Extended].[Job
Title] & ", " & [Contacts Extended].[Employee ID], [Shift] FROM [Contacts
Extended] ORDER BY [Contacts Extended].[Shift];

adding the Shift field. The listbox's Column Count property should also be
changed from 2 to 3 to accommodate the newly added field. You can use its
ColumnWidths property to conceal this field (since it's already included in
the concatenated calculated field).

Edit your code from

Private Sub CheckedOutTo_AfterUpdate()
Select Case Me.CheckedOutTo.Column(1)
Case Like "*1st"
Me.Due_Date = #5:00:00 PM#
Case "*2nd"
Me.Due_Date = #11:00:00 PM#
Case "*3rd"
Me.Due_Date = #7:00:00 AM#
End Select
End Sub

to

Private Sub CheckedOutTo_AfterUpdate()
Select Case Me.CheckedOutTo.Column(2)
Case "1st"
Me.Due_Date = #5:00:00 PM#
Case "2nd"
Me.Due_Date = #11:00:00 PM#
Case "3rd"
Me.Due_Date = #7:00:00 AM#
End Select
End Sub

to look in the *third* field of the rowsource (remember, it's zero based) to
pull the exact Shift value rather than packing it into a string and then
attempting to unpack it. SELECT CASE will not recognize LIKE; that's a SQL
operator, not a VBA operator.
 
N

newtoaccess

Back again.....
I have tried all recommendations, but none are working.  I'm sure that
I have failed to mention something that would help you understand my
problem better.  So here I go.....
Click to expand...
The Form was pillaged from the "Lending Library" database, CHECK OUT.
The List box on the CHECK OUT form incorporates the following SELECT
clause:
Click to expand...
SELECT [Contacts Extended].ID, [Contacts Extended].[Shift] & ", " &
[Contacts Extended].[Contact Name] & ", " & [Contacts Extended].[Job
Title] & ", " & [Contacts Extended].[Employee ID] FROM [Contacts
Extended] ORDER BY [Contacts Extended].[Shift];
Click to expand...
Once a name is selected from the list box, I want the Due_Date control
on the form to auto fill based upon the shift of the employee (1st,
2nd, 3rd).  The name of the list box is "CheckedOutTo", the Control is
Checked_Out_To.  On the After Update of the Checked_Out_To list box, I
have entered:
Click to expand...
Private Sub CheckedOutTo_AfterUpdate()
Select Case Me.CheckedOutTo.Column(1)
    Case Like "*1st"
        Me.Due_Date = #5:00:00 PM#
    Case "*2nd"
        Me.Due_Date = #11:00:00 PM#
    Case "*3rd"
        Me.Due_Date = #7:00:00 AM#
End Select
End Sub
Click to expand...
The "Like" statement creates an error, if removed from statement the
error goes away.  But still does not work.  I am truly grateful to all
of you, it is a blessing to be able have access to the knowledge you
guys possess, I am just sorry that I have yet to properly explain my
issue, I hope knowing where I got the form template helps..
Click to expand...

Let's try again.

Open the form in design view.
Select the listbox.
Open its Rowsource query in SQL view.
CHANGE the SQL from

SELECT [Contacts Extended].ID, [Contacts Extended].[Shift] & ", " &
[Contacts Extended].[Contact Name] & ", " & [Contacts Extended].[Job
Title] & ", " & [Contacts Extended].[Employee ID] FROM [Contacts
Extended] ORDER BY [Contacts Extended].[Shift];

to

SELECT [Contacts Extended].ID, [Contacts Extended].[Shift] & ", " &
[Contacts Extended].[Contact Name] & ", " & [Contacts Extended].[Job
Title] & ", " & [Contacts Extended].[Employee ID], [Shift] FROM [Contacts
Extended] ORDER BY [Contacts Extended].[Shift];

adding the Shift field. The listbox's Column Count property should also be
changed from 2 to 3 to accommodate the newly added field. You can use its
ColumnWidths property to conceal this field (since it's already included in
the concatenated calculated field).

Edit your code from

Private Sub CheckedOutTo_AfterUpdate()
Select Case Me.CheckedOutTo.Column(1)
     Case Like "*1st"
         Me.Due_Date = #5:00:00 PM#
     Case "*2nd"
         Me.Due_Date = #11:00:00 PM#
     Case "*3rd"
         Me.Due_Date = #7:00:00 AM#
End Select
End Sub

to

Private Sub CheckedOutTo_AfterUpdate()
Select Case Me.CheckedOutTo.Column(2)
     Case "1st"
         Me.Due_Date = #5:00:00 PM#
     Case "2nd"
         Me.Due_Date = #11:00:00 PM#
     Case "3rd"
         Me.Due_Date = #7:00:00 AM#
End Select
End Sub

to look in the *third* field of the rowsource (remember, it's zero based)to
pull the exact Shift value rather than packing it into a string and then
attempting to unpack it. SELECT CASE will not recognize LIKE; that's a SQL
operator, not a VBA operator.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you for your time, it works great. It worked even better once I
removed the Validation Rule I had preloaded in the Table....."Live and
Learn"

Thanks again,

John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Assigning shift to Time Intervals using IIF 6
Access Question 4
Outlook Macro to set Reminder on a Contact tomorro 9:00 4
Report Group Level On Open - Receiving Error 3
How to enter start and end date/time in excel based on some specific value. 7
Auto-populate date field based on two other fields. 7
IIf statement using a time field 3
Combo Box on Main Form and limits Combo Box selection on the Subfo 1

Top