Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6

Thread: Im Over My Head

  1. #1
    New to the CF scene
    Join Date
    Nov 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Im Over My Head

    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,437
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Nov 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oops...my bad. I am using Access 2007.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,437
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    1. Compare [Created On] to [End Time] and extract records that have a [JS] of "CP" and are greater than 24 hours old.
    Code:
    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.
    Code:
    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.
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,437
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,437
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    Maybe give some example records and show what is desired. That would clarify the requirements enormously.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •