Query to select time range and selected day (1 Viewer)

[nick941741]

Hi, I have an access DB that is a large phone bill for multiple mobile accounts.

I need to create a query that will return calls mode out of hours, out of hours being anything after 7pm and between 7am Monday to Saturday and anything on a Sunday.

The fields are simply Time and Date

Any help appreciated

Thanks

Nick

 

[Brianwarnock]

You are going to need to use the weekday function to return the day of the week, then if it is Sunday return the record, if it is not Sunday and the time is not between 07:00 and 19:00 return the record.

Brian

 

[nick941741]

OK thanks Brian, not sure if I made it clear. I need all the calls that are made on a Sunday as this is considered out of hours, whereas Monday - Saturday only calls made between 19:00 - 07:00 are out of hours.

I have a formula that works for out of hours, but its just getting the Sunday bit to work I can't seem to do.


Nick

 

[Brianwarnock]

Posting your present formula would be helpfull.

Brian

 

[nick941741]

Sorry, of course, current formula is

Between #19:00:00# And #23:59:59# Or Between #00:01:00# And #07:00:00#

There is probably a much better way of writing that.

Nick

 

[Brianwarnock]

Well Nick you are not attempting to consider the day, did you look up weekday?

Perhaps we should use Format to filter the day giving a where clause of

WHERE (Format([mydate],"dddd")="sunday") Or ((Table1.mytime Not Between #7:0:0# And #16:59:59#) And Format([mydate],"dddd")<>"sunday")

you my need to tweak the times to retieve exactly the ones required eg is 07:00:00 to be retrieved or not.
remember to use your field names, hoefully not Date or Time which are access function names.
Don't worry if in the SQL Access adds 12/30/1899 to the time fields, that is effectively a Null date.

Brian

 

[nick941741]

Ok, so I'm not 100% sure where to enter this. The fields I have are usage date and usage time.

Nick

 

[Brianwarnock]

Tip don't use spaces and special characters in object names it makes syntax errors more likely , you have to always use [] around the names and if you use VBA then it compromises intellisense, use an underscore instead of a space.

Switch you query to SQL view, you will see your Where clause, replace it with the one I typed, modified for your names, switch back to design grid vie to see how to do it there.

Brian

 

[nick941741]

Thanks Brian, I managed to sort it out just before I got your reply.

Very much appreciated

 

[Brianwarnock]

HTH you work it out.

Brian