Date and Time Query 2

[rayna]

I am wondering if there is a way to do this, and I cannot believe there is not:

I have a table for a month, with several dates, and several times within each date, all in chronological order.

I want to get, say, the block of rows between 03/22/00 at 9 AM and 03/27/00 at 7 PM. I have tried several queries that involve the dates and times, but if I use 9 AM and 7 PM as time parameters, I get all the dates, but only the rows that fall between 9 AM and 7 PM, each day. The query drops everything from 7:01 PM and 8:59 AM.

Now, there has to be a way to do this...has anyone got a solution? I have tried several ways, but nothing returns the results I want.

Can anyone help?

Regards,
Rayna

 

[tempclerk]

Combine your two fields into an actual date/time (if they are already being interpretted as a date and a time all you have to do is add them) and in your criteria for this field put BETWEEN date/time1 and date/time2.

 

[rayna]

The data is coming from an Excel spreadsheet that is spewed out by some other code on a machine somewhere (I am only presented with the spreadsheet file for analysis). The date and time are already seperate entities in the spreadsheet.

Plus, the resulting queries are exported [bold]back[/bold] to Excel for graphing. Do you think this would make a difference?

If not, what's the easiest way to accomplish this?

 

[Sawatzky]

Dear Rayna,

You have to excuse, that my query relies on 24h timestandard and German date order which is dd/mm/yyyy.
But as you did your query so far I think you can change it to your needs.

I asume you have 2 fields, one for the date (actdate) and one for the time (acttime).

SELECT Table1.actdate, Table1.acttime
FROM Table1
WHERE ((([Tabelle1]![actdate]+[Tabelle1]![acttime]) Between #21/1/2000 8:31:0# And #31/1/2000 22:31:0#));


HTH
Astrid

 

[rayna]

Dear Astrid,

Thanks so much for this information. It works perfectly. But now I need to take it one step further: I need for whoever is using the database to be able to enter both dates and both times as parameters.

I have written it this way, thinking it should work, but it does not:

SELECT [Facility Heating Data].Date, [Facility Heating Data].Time
FROM [Facility Heating Data]
WHERE ((([Facility Heating Data]![Date]+[Facility Heating Data]![Time]) Between ([Enter Start Date]+[Enter Start Time]) And ([Enter End Date]+[Enter End Time])));

It does run, but with 0 rows returned when there should be hundreds.

Neither do the dates with the pound symbol (#) work. I also tried concatenating spaces between the date and time and that has no effect either.

Am I missing something crucial? I guess I must be, else it would work!!

Thanks,
Rayna

 

[tempclerk]

The Start and End Dates and times are probably not being interpretted as such. Try using the CDate function on each of them in your criteria.

 

[rayna]

Thank you, thank you! I can't tell you how much time I have spent trying to figure this out. It seemed so simple but the query is quite a bit more complex than I would have expected.

My query now works like a charm! Thank you, Astrid and tempclerk.

Rayna