Complex Query on Current Date and time

[growleruk]

Hi all,

I am trying to run a query that will QUERY a table by selecting the current date and time. The fields in my table as follows:

"1"Backup_Name
"2"Week
"3"Day
"4"Start_Time
"5"Start_Date
"6"Finish_Time
"7"Finish_Date

eg:

"1" "2" "3" "4" "5" "6" "7"
SQL BACKUP 1 Tuesday 16:00 20/01/2003 12:00 21/12/2003

The table will be completed spanning over a year.

The query needs to be able to take current date and time and query between start time/date and finish time/date to return the the correct day and week speccified.

If query done on 01:00hrs on 21/01/2002 the query ould return week 1 Tuesday SQL backup.

Hope someone can help. This one is doing my head in.

 

[r937]

what are the datatypes of the date and time columns? the reason i ask is because microsoft access always stores both date and time in datetime fields

if you were to combine your date and time columns, then you could say

where start_datetime <= now()
and end_datetime >= now()


rudy

 

[growleruk]

The Start_date and finish_date are both short date format and start_time and finish_time are both short time format.

I think I see what you are saying. Combine the 4 columns into 2 using date and time format.

I will give this ago thanks and post results...thanks

 

[growleruk]

As simple as that, and theres me syaing it was complex. I think I was looking a bit too deeply into the problem.

Thanks for help does exactly what I want it to do

Thanks