Im Over My Head

[ScottInTX]

I am over my head, but my boss has asked me to do something that I would think is simple. I was hoping the readers here could help...

I am trying to compare the data in [Created On], [End Time] and [JS]

The [Created On] and [End Time] fields are formatted in a mm/dd/yy hh:mm:ss (example: 11/13/2010 11:19:00 AM)

The [JS] field is a simple text field where the data is 2 char's (“CP”, “AS”, “IS”, or “ND”)

The data is exported from a program as text, but I have the Data Type for the [Created On] and [End Time] fields set as Date/Time.

Comparing these 3 fields I need to extract 3 sets of data:

1. Compare [Created On] to [End Time] and extract records that have a [JS] of "CP" and are greater than 24 hours old.

2. Compare [Created On] to [End Time] and extract records that have a [JS] not equal to "CP" where the [Created On] date/time is BEFORE 3pm of the previous day.

2. Compare [Created On] to [End Time] and extract records that have a [JS] not equal to "CP" where the [Created On] date/time is AFTER 3pm of the previous day.


Any help and advice you can give will be GREATLY appriciated.

[Old Pedant]

What are the data types of the [Created On] and [End Time] fields????

Did somebody foolishly make them text fields (e.g., VARCHAR) instead of DATETIME fields?

What kind of database is this? Your use of [...] delimiters would make me think it is Access? (Yes, it matters: Date and time functions are very very different among all the different database systems.)

Off to a tonsorial appointment. Back a while later.

***********

EDIT: DOH on me! I can't read.

I have the Data Type for the [Created On] and [End Time] fields set as Date/Time

Good for you! A wonderful first step! Will be easy once you tell us what DB you are using.

[ScottInTX]

Oops...my bad. I am using Access 2007.

[Old Pedant]

1. Compare [Created On] to [End Time] and extract records that have a [JS] of "CP" and are greater than 24 hours old.

SELECT * FROM table WHERE JS='CP' AND ([Created On] + [End Time]) > (Now()-1)

2. Compare [Created On] to [End Time] and extract records that have a [JS] not equal to "CP" where the [Created On] date/time is BEFORE 3pm of the previous day.

SELECT * FROM table WHERE JS<>'CP' AND [Created On] = (Date()-1) AND[End Time] < #3:00:00 PM#

3. Compare [Created On] to [End Time] and extract records that have a [JS] not equal to "CP" where the [Created On] date/time is AFTER 3pm of the previous day.

SELECT * FROM table WHERE JS<>'CP' AND [Created On] = (Date()-1) AND[End Time] > #3:00:00 PM#

But be careful: (2) and (3) together will *MISS* any records that happen to have been created *exactly* at 3PM on previous day. So I assume that *ONE* of them should include exactly 3PM. Whichever should, just change the operator from < to <= or > to >= as appropriate.

NOTE: I read requirements (2) and (3) to mean that the records should *ONLY* come from the previous day. That is "after 3PM of the previous day" would *NOT* include any time *today*. Or "before 3PM of the previous day" would not include any time on a day last week.

If that's not the right interpretation, tell us.

[Old Pedant]

Reading your requirements again, I think I've misinterpreted.

I was assuming that [Created On] is a date and [End Time] is a time.
And I further assumed that "3 PM of the previous day" literally meant "3PM yesterday", where yesterday really is the day before today.

But I think you meant that *each* is a Date+Time value.

So these records are describing a TIME SPAN. And "of the previous day" means "the day before the [End Time], whenever [End Time] occurs.

So if [End Time] is #14 Jan 2009 7:33:21PM#, then "3 PM of the previous day" means #13 Jan 2009 3:00:00PM#.

Is *THAT* what you meant???

**********

By the by... You stated:

The [Created On] and [End Time] fields are formatted in a mm/dd/yy hh:mm:ss

Well, yes and no. They may be formatted that way FOR DISPLAY PURPOSES, but internal to the database they are held in a *completely* different form! So the formatting is really irrelevant.


And wh

[Old Pedant]

Maybe give some example records and show what is desired. That would clarify the requirements enormously.