Help with WHERE part of SQL statement

[Paul B.]

I am not sure I have the correct formatting for the following part of my SQL
statement:


WHERE
(((tblEpistry.DateOfService)>=[Forms]![frmQuery_BCAS_Crew_Reports]![txtStartDate])
AND
((tblEpistry.DateOfService)<=[Forms]![frmQuery_BCAS_Crew_Reports]![txtEndDate]) AND ((tblUnit1.Region)="3") OR ((tblUnit1.Region)="4"));


I need to ensure that all records are found where tblUnit1.Region is 3 or 4.
These are text fields.


Thanks in advance.

 

[Michel Walsh]

Hi,


Sounds you need an OR in the third line, or may be better:


WHERE ((tblUnit1.Region)="3") OR ((tblUnit1.Region)="4")) OR
((tblEpistry.DateOfService)>=[Forms]![frmQuery_BCAS_Crew_Reports]![txtStartDate])



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

Assumption:
You want records where the region is 3 or 4 AND the date of service is
between two dates. That can be written in Access SQL as:

WHERE tblEpistry.DateOfService Between
[Forms]![frmQuery_BCAS_Crew_Reports]![txtStartDate]
AND [Forms]![frmQuery_BCAS_Crew_Reports]![EndDate]
AND TblUnit1.Region In ("3","4")

That can be written in generic SQL as

WHERE tblEpistry.DateOfService >=
[Forms]![frmQuery_BCAS_Crew_Reports]![txtStartDate]
AND tblEpistry.DateOfService
<=[Forms]![frmQuery_BCAS_Crew_Reports]![EndDate]
AND (TblUnit1.Region="3" Or tblUnit1.Region ="4")

Your where clause seems to get records for region 3 that are within the
specified date range and ALL records for region 4 (no date range)

If you want all records for region 3 and region 4 plus all records within
the date range, then you need to change the where clause to
WHERE tblEpistry.DateOfService Between
[Forms]![frmQuery_BCAS_Crew_Reports]![txtStartDate]
AND [Forms]![frmQuery_BCAS_Crew_Reports]![EndDate]
OR TblUnit1.Region In ("3","4")

Again generically
WHERE tblEpistry.DateOfService >=
[Forms]![frmQuery_BCAS_Crew_Reports]![txtStartDate]
AND tblEpistry.DateOfService
<=[Forms]![frmQuery_BCAS_Crew_Reports]![EndDate]
OR (TblUnit1.Region="3" Or tblUnit1.Region ="4")