And , between and or

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

SELECT dbo_log_driverlogs.mpp_id, dbo_manpowerprofile.mpp_otherid,
dbo_manpowerprofile.mpp_firstname, dbo_manpowerprofile.mpp_lastname,
dbo_log_driverlogs.log_date, dbo_log_driverlogs.off_duty_hrs,
dbo_log_driverlogs.sleeper_berth_hrs, dbo_log_driverlogs.driving_hrs,
dbo_log_driverlogs.on_duty_hrs, dbo_manpowerprofile.mpp_type4,
[dbo_log_driverlogs]![on_duty_hrs]+[dbo_log_driverlogs]![driving_hrs] AS
Expr1, dbo_log_driverlogs.log
FROM dbo_log_driverlogs INNER JOIN dbo_manpowerprofile ON
dbo_log_driverlogs.mpp_id = dbo_manpowerprofile.mpp_id
WHERE (((dbo_log_driverlogs.driving_hrs)>11) AND
((dbo_manpowerprofile.mpp_type4)="hstn") AND
(([dbo_log_driverlogs]![log_date]) Between [Enter the begining date] And
[Enter the ending date])) OR
((([dbo_log_driverlogs]![on_duty_hrs]+[dbo_log_driverlogs]![driving_hrs])>14));

Heres the query, it does ok until I put the Or statement in, then it returns
data outside my date range as well as other incorrect info. Im new to this
and the boss is all I got and he is yelling. Help.. Im sure there are
questions.
 
Hi,


your where clause is

(((x)>11) AND ((y)='hstm") AND ((z) BETWEEN a AND b )) OR (((m+n)>14))

If we remove un-necessary ( ) to help reading that:

((x>11) AND (y="hstm") AND ( z BETWEEN a AND b))
OR
( m+n > 14 )



so, since an OR means EITHER one, in your case, either m+n > 14 is enough
to keep the record, either x>11 and u="hstm" and z betweeness is required to
keep the record.


It is a matter to write your logical expression appropriately, if something
else (something you didn't mention) is desirable.



Remember that AND and OR are evaluate with the same "priority", unless there
is a use of parentheses, that is. Like here, the left argument of the OR,
implying two ANDs, is evaluated before the OR.



Hoping it may help,
Vanderghast, Access MVP
 
By adding the final OR clause, you have asked for all the records
where the driving time is greater than 14 **regardless of the date
when it occurred**. That is the nature of OR.

Did you really mean "AND" -- the records that fall within the date
range AND the driving time was over 14?



SELECT dbo_log_driverlogs.mpp_id, dbo_manpowerprofile.mpp_otherid,
dbo_manpowerprofile.mpp_firstname, dbo_manpowerprofile.mpp_lastname,
dbo_log_driverlogs.log_date, dbo_log_driverlogs.off_duty_hrs,
dbo_log_driverlogs.sleeper_berth_hrs, dbo_log_driverlogs.driving_hrs,
dbo_log_driverlogs.on_duty_hrs, dbo_manpowerprofile.mpp_type4,
[dbo_log_driverlogs]![on_duty_hrs]+[dbo_log_driverlogs]![driving_hrs] AS
Expr1, dbo_log_driverlogs.log
FROM dbo_log_driverlogs INNER JOIN dbo_manpowerprofile ON
dbo_log_driverlogs.mpp_id = dbo_manpowerprofile.mpp_id
WHERE (((dbo_log_driverlogs.driving_hrs)>11) AND
((dbo_manpowerprofile.mpp_type4)="hstn") AND
(([dbo_log_driverlogs]![log_date]) Between [Enter the begining date] And
[Enter the ending date])) OR
((([dbo_log_driverlogs]![on_duty_hrs]+[dbo_log_driverlogs]![driving_hrs])>14));

Heres the query, it does ok until I put the Or statement in, then it returns
data outside my date range as well as other incorrect info. Im new to this
and the boss is all I got and he is yelling. Help.. Im sure there are
questions.
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
I only want the data that falls within the date range and is in HSTN that
either the driving time was over 1 or the EXP1 was over 14

Jack MacDonald said:
By adding the final OR clause, you have asked for all the records
where the driving time is greater than 14 **regardless of the date
when it occurred**. That is the nature of OR.

Did you really mean "AND" -- the records that fall within the date
range AND the driving time was over 14?



SELECT dbo_log_driverlogs.mpp_id, dbo_manpowerprofile.mpp_otherid,
dbo_manpowerprofile.mpp_firstname, dbo_manpowerprofile.mpp_lastname,
dbo_log_driverlogs.log_date, dbo_log_driverlogs.off_duty_hrs,
dbo_log_driverlogs.sleeper_berth_hrs, dbo_log_driverlogs.driving_hrs,
dbo_log_driverlogs.on_duty_hrs, dbo_manpowerprofile.mpp_type4,
[dbo_log_driverlogs]![on_duty_hrs]+[dbo_log_driverlogs]![driving_hrs] AS
Expr1, dbo_log_driverlogs.log
FROM dbo_log_driverlogs INNER JOIN dbo_manpowerprofile ON
dbo_log_driverlogs.mpp_id = dbo_manpowerprofile.mpp_id
WHERE (((dbo_log_driverlogs.driving_hrs)>11) AND
((dbo_manpowerprofile.mpp_type4)="hstn") AND
(([dbo_log_driverlogs]![log_date]) Between [Enter the begining date] And
[Enter the ending date])) OR
((([dbo_log_driverlogs]![on_duty_hrs]+[dbo_log_driverlogs]![driving_hrs])>14));

Heres the query, it does ok until I put the Or statement in, then it returns
data outside my date range as well as other incorrect info. Im new to this
and the boss is all I got and he is yelling. Help.. Im sure there are
questions.
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
Click to expand...
 
I only want the data that falls within the date range and is in HSTN that
either the driving time was over 1 or the EXP1 was over 14
Click to expand...

I think that you got exactly what you asked for -- which may be not
what you meant!! Check parentheses and follow the order of operation.
This is how Access evaluates it:

(within the date range and is in HSTN that
either the driving time was over 1) or (the EXP1 was over 14)

when perhaps you want:

(within the date range) and (is in HSTN) AND (the driving time was
over 1 or the EXP1 was over 14)

Notice the different placement of the parentheses -- it is critical to
getting the correct result. This level of control is possible using
the query grid, but I often find it easier to view the SQL and modify
it manually. White space (ie, spaces or line breaks) are not
significant in the sql view, so you can add line breaks to make it
easier to read. It is sometimes helpful to place the AND and OR
operators on their own lines to make it clear what two operands are
being ANDed/ ORed.

Then you can place parentheses to control the order of operation. And
and Or have the same precedence level, so the first one encountered
(sans parentheses) is the first one that will be executed.


Jack MacDonald said:
By adding the final OR clause, you have asked for all the records
where the driving time is greater than 14 **regardless of the date
when it occurred**. That is the nature of OR.

Did you really mean "AND" -- the records that fall within the date
range AND the driving time was over 14?



SELECT dbo_log_driverlogs.mpp_id, dbo_manpowerprofile.mpp_otherid,
dbo_manpowerprofile.mpp_firstname, dbo_manpowerprofile.mpp_lastname,
dbo_log_driverlogs.log_date, dbo_log_driverlogs.off_duty_hrs,
dbo_log_driverlogs.sleeper_berth_hrs, dbo_log_driverlogs.driving_hrs,
dbo_log_driverlogs.on_duty_hrs, dbo_manpowerprofile.mpp_type4,
[dbo_log_driverlogs]![on_duty_hrs]+[dbo_log_driverlogs]![driving_hrs] AS
Expr1, dbo_log_driverlogs.log
FROM dbo_log_driverlogs INNER JOIN dbo_manpowerprofile ON
dbo_log_driverlogs.mpp_id = dbo_manpowerprofile.mpp_id
WHERE (((dbo_log_driverlogs.driving_hrs)>11) AND
((dbo_manpowerprofile.mpp_type4)="hstn") AND
(([dbo_log_driverlogs]![log_date]) Between [Enter the begining date] And
[Enter the ending date])) OR
((([dbo_log_driverlogs]![on_duty_hrs]+[dbo_log_driverlogs]![driving_hrs])>14));

Heres the query, it does ok until I put the Or statement in, then it returns
data outside my date range as well as other incorrect info. Im new to this
and the boss is all I got and he is yelling. Help.. Im sure there are
questions.
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
Click to expand...
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Back
Top