between two days and times

O

Omani

Hi,

plz help me to find a solution for this problem, I want to query my
data between two dates and two differnet times, for example the
starting time and date 5:30 Am 22/9/2005 and 5:29 Am 23/9/2005 ending
time and date

i Tried to use between and where function, but it said cann't use
aggrigate function in where clase or cluse.
 
A

Allen Browne

Open your query in SQL View (on View menu, in query design), and copy the
SQL statement, so we can see the problem with the WHERE clause.
 
O

Omani

Hi, this is the sql statments, i hope u understand what im trying to
get

PARAMETERS [Forms]![viewquery]![date1] DateTime,
[Forms]![viewquery]![date2] DateTime;
SELECT master.phone_number, Count(master.Num) AS CountOfNum,
master.notes, Min(master.time) AS MinOftime
FROM master
WHERE (((master.date) Between [Forms]![viewquery]![date1] and
((Min(master.time)) Between #12/30/1899 21:31:0# And #12/30/1899
23:59:0#) And [Forms]![viewquery]![date2]) and (Min(master.time))
Between #12/30/1899# And #12/30/1899 5:30:0# AND
((master.report_id)=4))
GROUP BY master.phone_number, master.notes

ORDER BY Min(master.time) DESC;
 
A

Allen Browne

Okay, I don't understand the WHERE clause either.

Did you mean to retrieve the records where the Date and Time fields in the
Master table yields a result between the 2 date/time values entered on the
form? That would be:
WHERE (((master.date + master.time) Between [Forms]![viewquery]![date1]
And [Forms]![viewquery]![date2])
AND (master.report_id = 4))
 
O

Omani

Aproximatly yes, but let me explain it more

The date and time are on the master tables but two differnet fields,
the two days will enter on the form and the time is constant on the
query or select statment, but the problem with times caz i want the
time to be in this interval from 5:30 in the startingdate and 5:30 on
the endingdate

How im going to do that, for example i dont want to have a data where
time = 7:00 am for example on endingdate
 
L

Larry Linson

Omani said:
Hi,

. . . to query my data between two dates
and two differnet times, for example the
starting time and date 5:30 Am 22/9/2005
and 5:29 Am 23/9/2005 ending
time and date

i Tried to use between and where function,
but it said cann't use aggrigate function in
where clase or cluse.
Click to expand...

Go to the upper left hand corner of the query toolbar, choose SQL view, copy
from there, and paste here. The information you gave is not sufficient for
us to assist you in debugging.

Larry Linson
Microsoft Access MVP
 
A

Allen Browne

Okay, so:

WHERE (((master.date + master.time) Between
DateAdd("n", 331, [Forms]![viewquery]![date1])
And DateAdd("n", 330, [Forms]![viewquery]![date2]))
AND (master.report_id = 4))
 
O

Omani

Ok, but what are the first two parmeters "n", 331 ?

Are they the time or what?

i really wanna thank u, u r very helpful
 
A

Allen Browne

DateAdd() adds a date/time value.
The first parameter is n of miNutes. (M would add Months.)
5 hours and 31 minutes = 331 minutes.

See help in the VBA window on DateAdd().
 
O

Omani

Thanks Allen,

Its really works, but i face another problem now, i have the data
where its entered and saved to the database 24 houers a day.

so i want to query my data in 3 time intervals, one called mornning
from 5:30 am to 1:30 pm, second called evenning from 1:31 pm to 9:30
am, and the third is night from 9:31 pm to the next day 5:29 am, yours
solution works perfect for single day.

but i want to query my data so that i want all the data from the
firstdate to enddate only the mornning interval, and its the same for
other intervals, For example i want all the data between 1/10/2005 to
10/10/2005 just for mornning period (5:30 am to 1:30 pm).
 
O

Omani

Thanks Allen,

Its really works, but i face another problem now, i have the data
where its entered and saved to the database 24 houers a day.

so i want to query my data in 3 time intervals, one called mornning
from 5:30 am to 1:30 pm, second called evenning from 1:31 pm to 9:30
am, and the third is night from 9:31 pm to the next day 5:29 am, yours
solution works perfect for single day.

but i want to query my data so that i want all the data from the
firstdate to enddate only the mornning interval, and its the same for
other intervals, For example i want all the data between 1/10/2005 to
10/10/2005 just for mornning period (5:30 am to 1:30 pm).
 
A

Allen Browne

Having selected the data using the query you have built, you can then
segment the day using the Choose() or Switch() function on the TimeValue()
of the date/time field.
 
Top