Return records within time range

T

Telobamipada

Can anyone look at this and tell me what I am doing wrong please?
I am simply trying to return all records within two time fields (Medium
Time). My query looks like this and returns nothing... I have looked through
some of the responses to no avail.

SELECT [Master Data].Account, [Master Data].[Last Name], [Master
Data].[First Name], [Master Data].[In OR], [Master Data].[Out OR / In PACU],
[Master Data].PROCEDURE
FROM [Master Data]
GROUP BY [Master Data].Account, [Master Data].[Last Name], [Master
Data].[First Name], [Master Data].[In OR], [Master Data].[Out OR / In PACU],
[Master Data].PROCEDURE, [Master Data].Date, [Master Data].[Add / Call],
[Master Data].SURGEON, [Master Data].[Scheduled In OR]
HAVING ((([Master Data].PROCEDURE)<>"Case Cancelled") AND (([Master
Data].Date) Between [Enter Beginning Date, mm/dd/yy] And [Enter Ending Date,
mm/dd/yy]) AND (([Master Data].[Add / Call])<>"CB") AND (([Master
Data].SURGEON)=[Enter surgeons name exactly as it appears in the database])
AND (([Master Data].[Scheduled In OR])>#12/30/1899 7:30:0# Or ([Master
Data].[Scheduled In OR])<#12/30/1899 15:30:0#));
 
M

MGFoster

Telobamipada said:
Can anyone look at this and tell me what I am doing wrong please?
I am simply trying to return all records within two time fields (Medium
Time). My query looks like this and returns nothing... I have looked through
some of the responses to no avail.

SELECT [Master Data].Account, [Master Data].[Last Name], [Master
Data].[First Name], [Master Data].[In OR], [Master Data].[Out OR / In PACU],
[Master Data].PROCEDURE
FROM [Master Data]
GROUP BY [Master Data].Account, [Master Data].[Last Name], [Master
Data].[First Name], [Master Data].[In OR], [Master Data].[Out OR / In PACU],
[Master Data].PROCEDURE, [Master Data].Date, [Master Data].[Add / Call],
[Master Data].SURGEON, [Master Data].[Scheduled In OR]
HAVING ((([Master Data].PROCEDURE)<>"Case Cancelled") AND (([Master
Data].Date) Between [Enter Beginning Date, mm/dd/yy] And [Enter Ending Date,
mm/dd/yy]) AND (([Master Data].[Add / Call])<>"CB") AND (([Master
Data].SURGEON)=[Enter surgeons name exactly as it appears in the database])
AND (([Master Data].[Scheduled In OR])>#12/30/1899 7:30:0# Or ([Master
Data].[Scheduled In OR])<#12/30/1899 15:30:0#));
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

First off, change the HAVING clause to the WHERE clause.

What are you trying to accomplish w/ the following statement?

([Master Data].[Scheduled In OR] > #12/30/1899 7:30:0#
Or ([Master Data].[Scheduled In OR] < #12/30/1899 15:30:0#)

If you want [Scheduled In OR] to be between 7:30am and 3:30pm, excluding
the boundary values, you can do it like this:

TimeValue([Scheduled In OR]) BETWEEN #7:31# AND #14:59#

This will return True for any date w/ times between 7:31 and 14:59, not
just 12/30/1899. If you've set up "special" records that use 12/30/1899
you'd do this:

[Scheduled In OR] BETWEEN #12/30/1899 7:31# AND #12/30/1899 14:59#

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQw9z54echKqOuFEgEQLDBgCg4bJ/a4Ld4cn6yP5zqLDW3ygzcYEAoNcT
4/GhSdA35OPxizVvX9Abdmae
=SGxm
-----END PGP SIGNATURE-----
 
Top