query criteria

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

Guest

I have query criteria as follows

WHERE [Order Date] <= Date()-21 AND [Issue Date] Between Date()-3 and Date()
AND [Refills]<>0

I need to query all prescriptions with Order Dates over 20 days old with
Issue Dates within the last 3 days, except where the Issue Date exceeded its
criteria (i.e., became 4 days old) before the Order Date met its criteria
(i.e., became 21 days old), in which case the Issue Date could be greater
than 3 days old. It's this last exception clause I'm having trouble with.
Any help? I know the syntax is wrong on this, but here's what I'd like to
put in there

AND [Issue Date]<Date()-3 IIf([Issue Date]-[Order Date]<18)
 
If I understand your request then try something like

WHERE (([Order Date] <= Date()-21 AND [Issue Date] Between Date()-3 And
Date()) Or ([Issue Date] < Date()-3)) AND [Refills]<>0
 
Close, but no cigar. :) The Issue Date criteria "superfluousize" each other.
The second criterion is missing the condition under which it would be
applicable - i.e., if [Issue Date]-[Order Date]<18

Ofer Cohen said:
If I understand your request then try something like

WHERE (([Order Date] <= Date()-21 AND [Issue Date] Between Date()-3 And
Date()) Or ([Issue Date] < Date()-3)) AND [Refills]<>0


--
Good Luck
BS"D


Mike said:
I have query criteria as follows

WHERE [Order Date] <= Date()-21 AND [Issue Date] Between Date()-3 and Date()
AND [Refills]<>0

I need to query all prescriptions with Order Dates over 20 days old with
Issue Dates within the last 3 days, except where the Issue Date exceeded its
criteria (i.e., became 4 days old) before the Order Date met its criteria
(i.e., became 21 days old), in which case the Issue Date could be greater
than 3 days old. It's this last exception clause I'm having trouble with.
Any help? I know the syntax is wrong on this, but here's what I'd like to
put in there

AND [Issue Date]<Date()-3 IIf([Issue Date]-[Order Date]<18)
Click to expand...
Click to expand...
 
Not sure, but ry

WHERE (([Order Date] <= Date()-21 AND [Issue Date] Between Date()-3 And
Date()) Or ([Issue Date] < Date()-3) Or DateDiff("d",[Issue Date],[Order
Date])<18) AND [Refills]<>0


--
Good Luck
BS"D


Mike said:
Close, but no cigar. :) The Issue Date criteria "superfluousize" each other.
The second criterion is missing the condition under which it would be
applicable - i.e., if [Issue Date]-[Order Date]<18

Ofer Cohen said:
If I understand your request then try something like

WHERE (([Order Date] <= Date()-21 AND [Issue Date] Between Date()-3 And
Date()) Or ([Issue Date] < Date()-3)) AND [Refills]<>0


--
Good Luck
BS"D


Mike said:
I have query criteria as follows

WHERE [Order Date] <= Date()-21 AND [Issue Date] Between Date()-3 and Date()
AND [Refills]<>0

I need to query all prescriptions with Order Dates over 20 days old with
Issue Dates within the last 3 days, except where the Issue Date exceeded its
criteria (i.e., became 4 days old) before the Order Date met its criteria
(i.e., became 21 days old), in which case the Issue Date could be greater
than 3 days old. It's this last exception clause I'm having trouble with.
Any help? I know the syntax is wrong on this, but here's what I'd like to
put in there

AND [Issue Date]<Date()-3 IIf([Issue Date]-[Order Date]<18)
Click to expand...
Click to expand...
Click to expand...
 
Already tried the DateDiff thing a couple different ways, but it didn't work.
Thanks anyway though.

Ofer Cohen said:
Not sure, but ry

WHERE (([Order Date] <= Date()-21 AND [Issue Date] Between Date()-3 And
Date()) Or ([Issue Date] < Date()-3) Or DateDiff("d",[Issue Date],[Order
Date])<18) AND [Refills]<>0


--
Good Luck
BS"D


Mike said:
Close, but no cigar. :) The Issue Date criteria "superfluousize" each other.
The second criterion is missing the condition under which it would be
applicable - i.e., if [Issue Date]-[Order Date]<18

Ofer Cohen said:
If I understand your request then try something like

WHERE (([Order Date] <= Date()-21 AND [Issue Date] Between Date()-3 And
Date()) Or ([Issue Date] < Date()-3)) AND [Refills]<>0


--
Good Luck
BS"D


:

I have query criteria as follows

WHERE [Order Date] <= Date()-21 AND [Issue Date] Between Date()-3 and Date()
AND [Refills]<>0

I need to query all prescriptions with Order Dates over 20 days old with
Issue Dates within the last 3 days, except where the Issue Date exceeded its
criteria (i.e., became 4 days old) before the Order Date met its criteria
(i.e., became 21 days old), in which case the Issue Date could be greater
than 3 days old. It's this last exception clause I'm having trouble with.
Any help? I know the syntax is wrong on this, but here's what I'd like to
put in there

AND [Issue Date]<Date()-3 IIf([Issue Date]-[Order Date]<18)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top