Date Time Query

[Firemansam]

Hi,

I have a table that shows when a Patient was admitted using a time and date field, what I am trying to do is create a query that will show me how many patients were admitted over a 12 month period between the hours of 10am and 11am.

I have tried the between syntax ie Between #10/01/2010 10:00:00AM# and #10/01/2011 11:00:00AM# however this obviously show me all the patients when in this range not all the patients with in this range between 10am and 11am. I then tried removing the date segment and replacing it with a wild card* but this just came up with and error. Any ideas would be helpful, and greatly appreciated.

Cheers

Andrew

 

[pbaldy]

Try

WHERE TimeValue(FieldName) Between #10:00:00 AM# and #11:00:00 AM#

 

[Firemansam]

Cheers for the reply pbaldy, but I am still getting an error
this is what I have as the SQL query what am I doing wrong.

SELECT ICU_Daily_statistics.[Patient ID], ICU_Daily_statistics.[Admission Datetime, ICU DateTime]
FROM ICU_Daily_statistics
WHERE TimeValue([Admission Datetime, ICU DateTime]) Between #10:00:00 AM and #11:00:00 AM#;

Cheers

Andrew

 

[pbaldy]

You missed the closing # on the first value.

 

[Firemansam]

I have added the closing # now I'm getting an error saying "Data type mismatch in criteria expression."

Any ideas?

Cheers for all your help

Andrew

 

[pbaldy]

What is the data type of that field? I just tested this on a date/time field:

WHERE TimeValue([ReqDateTime]) Between #13:00:00# And #15:00:00#

The mismatch error implies that the field isn't date/time.

 

[Firemansam]

I have attached a screen shot of the table design, I have higlights the field that I am working on. It definitely show as a Date/Time field. I can't work out why it is coming up with this error.

http://s291.photobucket.com/albums/ll313/FiremanSam1/Access Screen Shots/

Cheers

Andrew

 

[pbaldy]

It certainly is. Can you post the db, or a representative sample (ie one that demonstrates the error)?

Oh, is there a possibility of that field being Null? That might be causing the error.

 

[Firemansam]

G'day Paul,

I have just checked the table that this query references and there is some null values. Is there a way of writing the SQL statement to exclude these?

Cheers

Andrew

 

[pbaldy]

You can try adding this to the WHERE clause:

AND [ThatFieldNameWithTheInadvisableSpacesAndSymbol] Is Not Null