Emergency

[Guest]

I have a pharmacy refill request report, based on a query. Here are the
criteria I'm using.

Order Date
<=Date()-21

Issue Date

=Date()-3

<Date()-3 Where [Issue Date]-[Order Date]<18

It's this last one that's been giving me trouble. I need to query all
prescriptions over 20 days old with Issue Dates within the last 3 days,
except where the Issue Date occured before the Order Date met it's criteria,
in which case the Issue Date could be greater than 3 days old. Please help!
The orders have to go out ASAP

 

[Duncan Bachen]

I have a pharmacy refill request report, based on a query. Here are the
criteria I'm using.

Order Date
<=Date()-21

Issue Date

=Date()-3

<Date()-3 Where [Issue Date]-[Order Date]<18


It's this last one that's been giving me trouble. I need to query all
prescriptions over 20 days old with Issue Dates within the last 3 days,
except where the Issue Date occured before the Order Date met it's criteria,
in which case the Issue Date could be greater than 3 days old. Please help!
The orders have to go out ASAP

(>= DateAdd("d", -3, Date()) AND >= [OrderDate]) OR (< [OrderDate])

This should return:
1) All Issue dates within the last three days, as long as the issue date
is the same as, or greater than the Order Date
2) All Issues date which occurred before the order date

 

[John Spencer]

The WHERE clause should look like

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

If you don't know how to use the above, Please copy and post the SQL of
your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

I have a pharmacy refill request report, based on a query. Here are the
criteria I'm using.

Order Date
<=Date()-21

Issue Date

=Date()-3

<Date()-3 Where [Issue Date]-[Order Date]<18

It's this last one that's been giving me trouble. I need to query all
prescriptions over 20 days old with Issue Dates within the last 3 days,
except where the Issue Date occured before the Order Date met it's
criteria,
in which case the Issue Date could be greater than 3 days old. Please
help!
The orders have to go out ASAP

 

[Guest]

Hi John,

Thanks for the feedback. I realized, my question wasn't that clear. The
first 2 parts of the "where" clause are good, but let me rephrase the rest of
it.

should read: except where the Issue date exceeded it's criteria (i.e.,
became 4 days old), before the Issue Date met it's criteria (i.e., became 21
days old). The issue date would never occur before the order date, so that
last criterion would be irrelevant. My bad. Any ideas?

thanks,
Mike

The WHERE clause should look like

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

If you don't know how to use the above, Please copy and post the SQL of
your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

I have a pharmacy refill request report, based on a query. Here are the
criteria I'm using.

Order Date
<=Date()-21

Issue Date

=Date()-3

<Date()-3 Where [Issue Date]-[Order Date]<18

It's this last one that's been giving me trouble. I need to query all
prescriptions over 20 days old with Issue Dates within the last 3 days, Please
help!
The orders have to go out ASAP

 

[John Spencer]

Mike,
I'm sorry, but I don't understand your statement of the criteria. It is
simply not clear to me what you are trying to do.

Where the issue date exceeded its criteria (become 4 days old) before the
Issue Date met its criteria (i.e., became 21 days old).

What does each instance of "its" refer to? Grammatically, "its" refers to
Issue Date in both cases. Logically, that makes no sense since you have two
different "ages" for the same basic comparison.

Perhaps you could give a few examples of the date pairs and an explanation
of whether you want the record selected or not and the reason why.

Hi John,

Thanks for the feedback. I realized, my question wasn't that clear. The
first 2 parts of the "where" clause are good, but let me rephrase the rest
of
it.

should read: except where the Issue date exceeded it's criteria (i.e.,
became 4 days old), before the Issue Date met it's criteria (i.e., became
21
days old). The issue date would never occur before the order date, so
that
last criterion would be irrelevant. My bad. Any ideas?

thanks,
Mike

The WHERE clause should look like

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

If you don't know how to use the above, Please copy and post the SQL of
your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

I have a pharmacy refill request report, based on a query. Here are the
criteria I'm using.

Order Date
<=Date()-21

Issue Date
=Date()-3
<Date()-3 Where [Issue Date]-[Order Date]<18

It's this last one that's been giving me trouble. I need to query all
prescriptions over 20 days old with Issue Dates within the last 3 days, Please
help!
The orders have to go out ASAP

 

[Guest]

My bad.

Where the Issue Date exceeded its criteria (become 4 days old) before the
*Order Date* met its criteria (i.e., became 21 days old).

The second "issue date" should be "order date".

Mike,
I'm sorry, but I don't understand your statement of the criteria. It is
simply not clear to me what you are trying to do.


What does each instance of "its" refer to? Grammatically, "its" refers to
Issue Date in both cases. Logically, that makes no sense since you have two
different "ages" for the same basic comparison.

Perhaps you could give a few examples of the date pairs and an explanation
of whether you want the record selected or not and the reason why.

Hi John,

Thanks for the feedback. I realized, my question wasn't that clear. The
first 2 parts of the "where" clause are good, but let me rephrase the rest
of
it.

except where the Issue Date occured before the Order Date met it's
criteria,
in which case the Issue Date could be greater than 3 days old.

should read: except where the Issue date exceeded it's criteria (i.e.,
became 4 days old), before the Issue Date met it's criteria (i.e., became
21
days old). The issue date would never occur before the order date, so
that
last criterion would be irrelevant. My bad. Any ideas?

thanks,
Mike

The WHERE clause should look like

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

If you don't know how to use the above, Please copy and post the SQL of
your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


I have a pharmacy refill request report, based on a query. Here are the
criteria I'm using.

Order Date
<=Date()-21

Issue Date
=Date()-3
<Date()-3 Where [Issue Date]-[Order Date]<18

It's this last one that's been giving me trouble. I need to query all
prescriptions over 20 days old with Issue Dates within the last 3 days, Please
help!
The orders have to go out ASAP

 

[Chris2]

I have a pharmacy refill request report, based on a query. Here are the
criteria I'm using.

Order Date
<=Date()-21

Issue Date

=Date()-3

<Date()-3 Where [Issue Date]-[Order Date]<18

It's this last one that's been giving me trouble. I need to query all
prescriptions over 20 days old with Issue Dates within the last 3 days,
except where the Issue Date occured before the Order Date met it's criteria,
in which case the Issue Date could be greater than 3 days old. Please help!
The orders have to go out ASAP

Mike,

I don't know the structure of your tables, so the following is a
guess you will need to adapt to your situation.

(It is also untested.)

SELECT P1.*
FROM Prescriptions AS P1
WHERE P1.[Order Date] <= (Date() - 20)
AND P1.[Issue Date] ??

?? must be replaced by whatever is meant by "except where the Issue
Date occurred before the Order Date met it's criteria,". I don't
know how to determine when the [Order Date] met its criteria. I do
see the above expression that you provided for [Issue Date], but
since that is what you already have, and it is not working
(apparently), it does not help.


Sincerely,

Chris O.