Query Problem

[xmeb]

I cannot seem to get the following query to work. It runs but returns "0" as a result.

SELECT Count(DataTable.StartTime) AS CountOfStartTime1500, Count(DataTable.StartTime) AS CountOfStartTime1600
FROM DataTable
WHERE ((Hour([StartTime])=15) AND (Hour([StartTime])=16));

I would also like to add an additionl time period of 1700.

Thanks

 

[dhookom]

Make sense to me since the starttime can't equal 2 different values. It's like count the red lifesavers that are also green.

You might want to try something like:

SELECT Hour(StartTime) as TheHour, Count(*) As TheCount
FROM DataTable
WHERE Hour(StartTime) IN (15,16)
GROUP BY Hour(StartTime)

If you really need the values across, try:

SELECT Sum(Abs(Hour(StartTime) = 15)) as CountOfStartTime1500, Sum(Abs(Hour(StartTime) = 16)) as CountOfStartTime1600
FROM DataTable
WHERE Hour(StartTime) IN (15,16)

Duane
Hook'D on Access
MS Access MVP

 

[xmeb]

Duane:

What you provided is exactly what I needed and it works great. Thank you so much. You are always so very helpful.

Thanks again,

xmeb

 

[xmeb]

Duane:

Just two other things.

Is there a way for it to return "1500" instead of "15" and only show results when the count is greater than five?

Thanks again,

xmeb

 

[xmeb]

I have been trying to make something like "=Format([StartTime],"hh:nn)" work in the text box on the report but when I click out of the Format box a bunch of quotes (") appear randomly in what I entered.

Thanks.

 

[SkipVought]

please copy exactly what you coded in the Format box and paste here.

What is the Data Type of [Start Time]?

Assuming that [Start Time] is a Date/Time value, I think that you may want something like...

=Hour([Start Time]) & ":" & Minute([Start Time])

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xmeb]

=Format([StartTime],"hh:nn)

Date/Time

I tried what you suggested and when I click out of the Format box a bunch of quotes (") appear randomly in what I entered.

 

[dhookom]

Not sure I follow what you are attempting to accomplish. I do know this won't work
=Format([StartTime],"hh:nn)

You could try
=Format([StartTime],"hh:nn")
or
=Format([StartTime],"hhnn")


Duane
Hook'D on Access
MS Access MVP