Date/Time Question

[GoBrowns!]

I have a table in Access that is filled with info from Excel... the data
comes over as:
4/30/2009 6:01:00 AM into my Access table.

I need to take that date/time information and split it into two shifts - one
from 6AM - 3PM, the other from 3:01PM to midnight. How can this be achieved?

Thanks!

 

[John Spencer MVP]

Use the TimeValue function to extract the time from the dateTime field.

IIF(TimeValue([YourDateTimeField])>=#15:00:00#, "Shift Two",
IIF(TimeValue([YourDateTimeField])>=#06:00:00#, "Shift One", "Invalid shift"))

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

 

[GoBrowns!]

John;

I am getting an error - invalid syntax - for entering an operand without an
operator. It all seems to be centered around the second ":00" in each time.

Any ideas?

Thanks for the help!

Erin

Use the TimeValue function to extract the time from the dateTime field.

IIF(TimeValue([YourDateTimeField])>=#15:00:00#, "Shift Two",
IIF(TimeValue([YourDateTimeField])>=#06:00:00#, "Shift One", "Invalid shift"))

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

I have a table in Access that is filled with info from Excel... the data
comes over as:
4/30/2009 6:01:00 AM into my Access table.

I need to take that date/time information and split it into two shifts - one
from 6AM - 3PM, the other from 3:01PM to midnight. How can this be achieved?

Thanks!

 

[John Spencer MVP]

Try the following expression:

IIF(IsDate([YourDateTimeField]) = False,"Bad Date",
IIF(TimeValue([YourDateTimeField])>=#15:00:00#, "Shift Two",
IIF(TimeValue([YourDateTimeField])>=#06:00:00#, "Shift One", "Invalid")))

WHERE are you using the expression. It should work as a calculated field in a
query.

It can fail as a control's source if you have nulls or values that can't be
interpreted as a date.

Another possibility is that your time separator is not a colon, but is some
other character.

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

John;

I am getting an error - invalid syntax - for entering an operand without an
operator. It all seems to be centered around the second ":00" in each time.

Any ideas?

Thanks for the help!

Erin

Use the TimeValue function to extract the time from the dateTime field.

IIF(TimeValue([YourDateTimeField])>=#15:00:00#, "Shift Two",
IIF(TimeValue([YourDateTimeField])>=#06:00:00#, "Shift One", "Invalid shift"))

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

I have a table in Access that is filled with info from Excel... the data
comes over as:
4/30/2009 6:01:00 AM into my Access table.

I need to take that date/time information and split it into two shifts - one
from 6AM - 3PM, the other from 3:01PM to midnight. How can this be achieved?

Thanks!

 

[GoBrowns!]

Yes, I am using it as a calculated field in the query.... and I keep getting
the same error!

Any other ideas?

Thanks!!

Try the following expression:

IIF(IsDate([YourDateTimeField]) = False,"Bad Date",
IIF(TimeValue([YourDateTimeField])>=#15:00:00#, "Shift Two",
IIF(TimeValue([YourDateTimeField])>=#06:00:00#, "Shift One", "Invalid")))

WHERE are you using the expression. It should work as a calculated field in a
query.

It can fail as a control's source if you have nulls or values that can't be
interpreted as a date.

Another possibility is that your time separator is not a colon, but is some
other character.

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

John;

I am getting an error - invalid syntax - for entering an operand without an
operator. It all seems to be centered around the second ":00" in each time.

Any ideas?

Thanks for the help!

Erin

Use the TimeValue function to extract the time from the dateTime field.

IIF(TimeValue([YourDateTimeField])>=#15:00:00#, "Shift Two",
IIF(TimeValue([YourDateTimeField])>=#06:00:00#, "Shift One", "Invalid shift"))

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

GoBrowns! wrote:
I have a table in Access that is filled with info from Excel... the data
comes over as:
4/30/2009 6:01:00 AM into my Access table.

I need to take that date/time information and split it into two shifts - one
from 6AM - 3PM, the other from 3:01PM to midnight. How can this be achieved?

Thanks!

 

[John Spencer MVP]

The only thing I can suggest is to break this down one step at a time to see
what is causing the error.

First Step:
IIF(IsDate([YourTimeField]),"Date","Not Date"))

Second Step:
IIF(IsDate([YourTimeField]),TimeValue([YourTimeField]),Null))

Third Step:
IIF(IsDate([YourTimeField]),IIF(TimeValue([YourTimeField])>#00:00:00#,"Good","Bad"),Null))

etc.

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

 

[GoBrowns!]

The issue seems to be with #00:00:00# - that second colon seems to bother the
query....

The only thing I can suggest is to break this down one step at a time to see
what is causing the error.

First Step:
IIF(IsDate([YourTimeField]),"Date","Not Date"))

Second Step:
IIF(IsDate([YourTimeField]),TimeValue([YourTimeField]),Null))

Third Step:
IIF(IsDate([YourTimeField]),IIF(TimeValue([YourTimeField])>#00:00:00#,"Good","Bad"),Null))

etc.

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

Yes, I am using it as a calculated field in the query.... and I keep getting
the same error!

Any other ideas?

Thanks!

 

[GoBrowns!]

I am getting an error: that my statement includes a reserved word, or an
argument name is misspelled or that punctuation is incorrect.

Can you please help debug?

Thanks!!!!

Here is my SELECT query:

SELECT
, Production_Download.Work_Center
, Production_Download.Work_Center_Description
, Production_Download.IDH
, Production_Download.IDH_Description
, Production_Download.Quantity
, Production_Download.Start_Date
, Switch(TimeValue([Start_Date]) Between #06:00#
AND
#15:00#,"1st Shift",TimeValue([Start_Date]) Between #15:01#
AND
#00:00#,"2nd Shift",True,"Off Duty") AS Shift
FROM
Production_Download;

Here is a working example, using the Switch() function.

SELECT
tblTest5.Process
, tblTest5.StartTime
, Switch(TimeValue([StartTime]) Between # 06:00#
AND
#15:00#,"First",TimeValue([StartTime]) Between #15:01#
AND
#00:00#,"Second",True,"Off Duty") AS Shift
FROM
tblTest5;

...copy/paste to a new query, replacing it with your table/field
names.

HTH - Bob

Yes, I am using it as a calculated field in the query.... and I keep getting
the same error!

Any other ideas?

Thanks!!

Try the following expression:

[quoted text clipped - 45 lines]

Thanks!

 

[GoBrowns!]

That made the query work... however....

The only things deemed "2nd Shift" are things that happen between 5:00 AM
and 5:59 AM....


"1st Shift" looks correct...

All others.... from 3:01PM to 11:59PM are labeled "Off Duty," when they
really should be "2nd Shift".....

How to fix?!?

Thanks so much for all your help!!!!!!!!

Try removing the leading comma in
, Production_Download.Work_Center

The first line following SELECT

Bob

I am getting an error: that my statement includes a reserved word, or an
argument name is misspelled or that punctuation is incorrect.

Can you please help debug?

Thanks!!!!

Here is my SELECT query:

SELECT
, Production_Download.Work_Center
, Production_Download.Work_Center_Description
, Production_Download.IDH
, Production_Download.IDH_Description
, Production_Download.Quantity
, Production_Download.Start_Date
, Switch(TimeValue([Start_Date]) Between #06:00#
AND
#15:00#,"1st Shift",TimeValue([Start_Date]) Between #15:01#
AND
#00:00#,"2nd Shift",True,"Off Duty") AS Shift
FROM
Production_Download;

Here is a working example, using the Switch() function.

[quoted text clipped - 26 lines]

Thanks!

 

[GoBrowns!]

Changing the time seemed to fix the issue... thanks so much for all your help!!

Have you examined your dates imported from Excel. Many strange things can
happen.

Open your table and look at your date field(s).

Go into design view and check the datatype of your 'date' fields.

If you note problems that you're unable to resolve, please post back
with a description of what you've found.

Bob

OK -

You might try changing this line:
#00:00#,"2nd Shift",True,"Off Duty") AS Shift
to
#23:59#,"2nd Shift",True,"Off Duty") AS Shift

If that doesn't work, is it possible to download a short example of
your db?

Bob

That made the query work... however....

[quoted text clipped - 15 lines]

Thanks!