Solved: between date and time

[wibbers2000]

Hello,

I am new to access and have a problem with a query;

I have a table with a 4 columns. Date, Time, Material, Qty
example;

01/12/05 04:00 steel 35
01/12/05 06:00 steel 60
01/12/05 23:40 steel 40
01/12/05 08:00 iron 25
02/12/05 05:00 steel 20
02/12/05 06:00 steel 50


I want to be able to look for matching rows that have a start & end date and start & end time.

i.e start date 01/12/05 time >= 06:00
end date 02/12/05 time <= 05:59

The times are aways static. I ideally would like to see one row returned that summed the last column and combined the different dates like;
01/12/05 steel 120
01/12/05 iron 25

however if I searched for 01/12/05 and 03/12/05 I would see;
01/12/05 steel 120
01/12/05 iron 25
02/12/05 steel 50

Any help or guidance will be appreciated.

regards
Wibbers

[geekgirlau]

IMHO it's easiest to combine the date and time in a single value. So your query might have a new calculated field like the following:

FullTime:cdate(format([Date] & " " & [Time],"dd/mm/yyyy hh:nn:ss"))

Then your criteria for the calculated field is:

Between #01/12/05 6:00# and #02/12/05 5:59:59#