Extracting record from business hours...say M-F 8-5. 2

[INTP]

Goodness.. I'm not even sure where to begin. This really isn't about business hours but it was the best example I could give to fit in the Subject.

I am looking to build a query to extract records between given hours and given days. The actual times needed (in case it is relevant) would be Monday through Saturday between the hours of 7AM and 7PM. Our business is a 24/7 business and our software tracks data entered by employees. I am interested in looking at the data between the hours I gave.

I am using an ODBC connection to the database but am accessing it through MS Access at tis point. The time-stamps I am looking at are stored in a Time/Date format so I will also need to learn how to extract days of the week from this as well.

As a working example I will say I want all records for the month of March 2004 that occured between Monday and Saturday and between the hours of 7am and 7pm.

Any hints would be appreciated. Please let me know if you need more specifics about my situation.

Thanks.

 

[smedvid]

'-- Display All Except Sunday.
SELECT Table1.F1, Table1.F2
FROM Table1
WHERE ((Weekday([f2])<>7) AND (((Format([f2],"h"))<=18 AND (Format([f2],"h"))>=7)));
'-- f2 is a Date/Time Field.

htwh,



Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[SgtJarrow]

I would create a function like this:

Public Function CheckDate(dteDate As Date) As Boolean

    If Format(dteDate, "dddd") = "Sunday" Then
        CheckDate = False
    Else
        Select Case Hour(dteDate)
            Case 7 To 19: CheckDate = True
            Case Else: CheckDate = False
        End Select
    End If
    

End Function

Just paste that into a global module and save it.

I would then create a query based on the table in question. Add the following into the first blank column:

Review: =CheckDate([name of date field in table])

Then set the Criteria to:

True

You will now see in the query only those records that meet your specs above....

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[INTP]

Thanks mstrmage1768,

Quick question. I tried your approach and it appears that it may work. However, I will need someway to identify the dates with their day of the week. The DB I am working with does not have a separate table that identifies a date with the day of the week. Is there a good way to do this?

 

[SgtJarrow]

The built in function I used:

Format(dteDate, "dddd")

will return the "day" depending on the date....check out the Format function in help to see more ways you can reformat a date based on format.

The code I supplied will first check each date...if it is sunday, it doesn't meet your needs and is therefore automatically set to false. If it is any other day, we use the Hour function to detemine what in what hour the record is....any record between 7 and 19 (7 PM for you non-military or 24 hr challenged) is defined as true because it meets what you are looking for.

We then use the true in the criteria of the query in order to "remove from display" any record that is false...and therefore does not meet your requirements.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[INTP]

Hmmm... looks like I forgot to set the criteria to True. When I do though it returns a blank dynaset. (is this the right word?)

 

[SgtJarrow]

As long as some of the data meets the criteria, it should not return an empty set.....can you post the SQL for yuor query as well as maybe a couple lines of your data???

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[INTP]

here is the SQL for the querry:

SELECT informix_reports_data.sc_dt, informix_reports_data.sc_id, informix_reports_data.re_tm, CheckDate([sc_dt]) AS Review
FROM informix_reports_data
WHERE (((informix_reports_data.sc_dt) Between #3/1/2004# And #3/31/2004#) AND ((informix_reports_data.sc_id)<600000) AND ((CheckDate([sc_dt]))=True));

here are a couple of lines of the data without the criteria set to true (since it returns nothing when I set it to tue):

sc_dt sc_id re_tm Review
3/1/04 16 3/1/04 4:01:10 AM 0
3/1/04 21 3/1/04 5:18:20 AM 0

 

[BobJacksonNCI]

Sorry to protrude...

Does sc_dt include the time, or do you need to change:

CheckDate([sc_dt]))=True));

to:

CheckDate([re_tm]))=True));

HTH,
Bob

 

[SgtJarrow]

Bob is right....I understood your date/time field to include both date and time. It seems your sc_dt is date only and your re_tm is both.....change the CheckDate argument as Bob suggested.

Thanks Bob!

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[INTP]

Oh... my bad. I did say that and I was incorrect. I changed the field to re_tm... however, when I run th querry and start scrolling through the data I receive and error that says "Data type mismatch in criteria expression" . When I click 'OK', it changes all of the field values to #Name? .

 

[SgtJarrow]

do you have any re_tm fields that do no contain dates/times??? Any that are blank?? I did not supplement my code to allow for these.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[INTP]

Yes I did. I just notices that myself when asking myself what the error message was saying. I changed the re_tm field criteria to 'Is Not Null' and it worked like a charm. Thanks to the both of you. You have bene a tremendous help!

 

[INTP]

quick question... how would I change it to filter out Saturdays and Sundays? I tried

Public Function CheckDate(dteDate As Date) As Boolean

If Format(dteDate, "dddd") = "Sunday" or
If Format(dteDate, "dddd") = "Saturday" Then
CheckDate = False
Else
Select Case Hour(dteDate)
Case 7 To 19: CheckDate = True
Case Else: CheckDate = False
End Select
End If


End Function

It didn't work for me.

 

[BobJacksonNCI]

INTP,

Always useful here to state exactly what happened. I.E., did you not get the result you wanted or did you get an error message? If you got an error message, what was it?

That said, If statements have a structure. For each If there has to eventually be an End If. If you have more than one If in a row, that's called Nested If statements. That's what you have, but not what you want. There are multiple ways to get what you want. The smallest change to your code that should work is:

Public Function CheckDate(dteDate As Date) As Boolean

If Format(dteDate, "dddd") = "Sunday" or Format(dteDate, "dddd") = "Saturday" Then
CheckDate = False
Else
Select Case Hour(dteDate)
Case 7 To 19: CheckDate = True
Case Else: CheckDate = False
End Select
End If

End Function



HTH,
Bob

 

[INTP]

I think I might get it... So had I wanted to have two nested Ifs I would have to add a second End If? Kind of like nested Ifs in excel where you add an extra parenthesis for each nested if.

If I am way off please let me know because I'm learning a lot about access here and don't want to learn something incorrectly

Thanks also for the alternative that you posted. It looks like I was neglecting to add the second "Format(dteDate, "dddd") =" Does this prefix thing have an official name?

 

[BobJacksonNCI]

INTP,

Glad to help ... we all had to start ... in my case it was on an IBM 360/20 learning assembler on a "mainframe" that had 8K of storage.

No, I didn't have to fight dinosaurs on my way to class!

Format is a built-in function. Where you need to do some calculation and include the results in a query, for example, you can write your own function.

Most importantly, are you getting the desired result?

Bob

 

[INTP]

Thanks Bob! I like the idea of having multiple ways to achieve a desired result.