Query where... and if no results

G

Guest

Hi,
I'm trying to do a query that returns entries with a specific date range.
In the case that the query returns no results that match. I would like it to
find the closest greater entry. In other words. If the query is asking to
bring back august 1,2004 and that day was a weekend. I would want it to
default to the following monday. Any help would be much appreciated.


SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
BOOKINGS.YEAR, BOOKINGS.[DAY OF MONTH], BOOKINGS.[MH SHIPPED] FROM BOOKINGS
WHERE (((BOOKINGS.DATE)>=DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![QUERIES DATE]),Month([Forms]![BOOKINGS DAILY REPORT]![QUERIES
DATE])-1,1) And (BOOKINGS.DATE)<DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![QUERIES DATE]),Month([Forms]![BOOKINGS DAILY REPORT]![QUERIES
DATE]),1)) AND ((BOOKINGS.[DAY OF MONTH])<=[Forms]![BOOKINGS DAILY
REPORT]![DAY]));
 
A

Al Campagna

ZigZag,
I would handle that before the query ran.
Enter a criteria date... (ex. [QueryDate]) and on the AfterUpdate event of that
field...

Private Sub QueryDate_AfterUpdate()
Select Case WeekDay(QueryDate)
Case 1
QueryDate = QueryDate + 1 'use Monday instead
Case 7
QueryDate = QueryDate - 1 'use Friday instead
End Select
'call the query here....
End Sub
 
G

Guest

I don't think that will work in this instance. There are 3 main queries
being ran off of a single inputed date. (1) brings back all entries that are
before that day, within the same month. (2) brings back all entries that are
before that day, in the previous month. And (3) brings back all entries that
are before that day, but the last year in the same month. Its a data base
for tracking sales & compairing them to other months. The problem I am
having is on the report. If you search for the first day of the month, or
sometimes even the second. Query #(2) and #(3) could end up with no results
due to being a weekend. This makes my fields on the report error. So I need
it to find the next closest day, so they have something to compare numbers
with. I hope this makes since. I still think there needs to be some kind of
If/then in the query....but don't know how. Any thoughts?

Al Campagna said:
ZigZag,
I would handle that before the query ran.
Enter a criteria date... (ex. [QueryDate]) and on the AfterUpdate event of that
field...

Private Sub QueryDate_AfterUpdate()
Select Case WeekDay(QueryDate)
Case 1
QueryDate = QueryDate + 1 'use Monday instead
Case 7
QueryDate = QueryDate - 1 'use Friday instead
End Select
'call the query here....
End Sub

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


ZigZagZak said:
Hi,
I'm trying to do a query that returns entries with a specific date range.
In the case that the query returns no results that match. I would like it to
find the closest greater entry. In other words. If the query is asking to
bring back august 1,2004 and that day was a weekend. I would want it to
default to the following monday. Any help would be much appreciated.


SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
BOOKINGS.YEAR, BOOKINGS.[DAY OF MONTH], BOOKINGS.[MH SHIPPED] FROM BOOKINGS
WHERE (((BOOKINGS.DATE)>=DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![QUERIES DATE]),Month([Forms]![BOOKINGS DAILY REPORT]![QUERIES
DATE])-1,1) And (BOOKINGS.DATE)<DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![QUERIES DATE]),Month([Forms]![BOOKINGS DAILY REPORT]![QUERIES
DATE]),1)) AND ((BOOKINGS.[DAY OF MONTH])<=[Forms]![BOOKINGS DAILY
REPORT]![DAY]));
Click to expand...
Click to expand...
 
G

Guest

I just had a second thought. Should I use the dateadd function to change
the queries date on different text boxes that are hidden, and have the query
look to them instead? That way your code could work, and may simplify it.

ZigZagZak said:
I don't think that will work in this instance. There are 3 main queries
being ran off of a single inputed date. (1) brings back all entries that are
before that day, within the same month. (2) brings back all entries that are
before that day, in the previous month. And (3) brings back all entries that
are before that day, but the last year in the same month. Its a data base
for tracking sales & compairing them to other months. The problem I am
having is on the report. If you search for the first day of the month, or
sometimes even the second. Query #(2) and #(3) could end up with no results
due to being a weekend. This makes my fields on the report error. So I need
it to find the next closest day, so they have something to compare numbers
with. I hope this makes since. I still think there needs to be some kind of
If/then in the query....but don't know how. Any thoughts?

Al Campagna said:
ZigZag,
I would handle that before the query ran.
Enter a criteria date... (ex. [QueryDate]) and on the AfterUpdate event of that
field...

Private Sub QueryDate_AfterUpdate()
Select Case WeekDay(QueryDate)
Case 1
QueryDate = QueryDate + 1 'use Monday instead
Case 7
QueryDate = QueryDate - 1 'use Friday instead
End Select
'call the query here....
End Sub

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


ZigZagZak said:
Hi,
I'm trying to do a query that returns entries with a specific date range.
In the case that the query returns no results that match. I would like it to
find the closest greater entry. In other words. If the query is asking to
bring back august 1,2004 and that day was a weekend. I would want it to
default to the following monday. Any help would be much appreciated.


SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
BOOKINGS.YEAR, BOOKINGS.[DAY OF MONTH], BOOKINGS.[MH SHIPPED] FROM BOOKINGS
WHERE (((BOOKINGS.DATE)>=DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![QUERIES DATE]),Month([Forms]![BOOKINGS DAILY REPORT]![QUERIES
DATE])-1,1) And (BOOKINGS.DATE)<DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![QUERIES DATE]),Month([Forms]![BOOKINGS DAILY REPORT]![QUERIES
DATE]),1)) AND ((BOOKINGS.[DAY OF MONTH])<=[Forms]![BOOKINGS DAILY
REPORT]![DAY]));
Click to expand...
Click to expand...
Click to expand...
 
A

Al Campagna

Very difficult to understand, but I think I get it now.
Let's set up a really "mechanical" logic system to start with...
I'll assume that you know when you input the ReportDate that IT is NOT on a Saturday or
Sunday... it's the "last month" and "last year/last month" that you're not sure of...

Imagine if you had an InputDate field called ReportDate, and 2 calculated fields on the
form called LastMonth and LastYearLastMonth (formatted ddd).
LastMonth = WeekDay(DateAdd("m",-1,ReportDate))
LastYearLastMonth = WeekDay(DateAdd("m",-13,ReportDate))

Private Sub ReportDate_AfterUpdate()
While LastMonth = 1 Or LastMonth = 7 Or LastYearLastMonth = 1 Or LastYearLastMonth = 7
ReportDate = ReportDate + 1
Wend
End Sub

That will force ReportDate to a value that always yields AT LEAST one Weekday for query
2 and 3.
Of course you don't really need the calculated fields when you "finally" code, but it
will help visualize what's going on during testing.
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


ZigZagZak said:
I just had a second thought. Should I use the dateadd function to change
the queries date on different text boxes that are hidden, and have the query
look to them instead? That way your code could work, and may simplify it.

ZigZagZak said:
I don't think that will work in this instance. There are 3 main queries
being ran off of a single inputed date. (1) brings back all entries that are
before that day, within the same month. (2) brings back all entries that are
before that day, in the previous month. And (3) brings back all entries that
are before that day, but the last year in the same month. Its a data base
for tracking sales & compairing them to other months. The problem I am
having is on the report. If you search for the first day of the month, or
sometimes even the second. Query #(2) and #(3) could end up with no results
due to being a weekend. This makes my fields on the report error. So I need
it to find the next closest day, so they have something to compare numbers
with. I hope this makes since. I still think there needs to be some kind of
If/then in the query....but don't know how. Any thoughts?

Al Campagna said:
ZigZag,
I would handle that before the query ran.
Enter a criteria date... (ex. [QueryDate]) and on the AfterUpdate event of that
field...

Private Sub QueryDate_AfterUpdate()
Select Case WeekDay(QueryDate)
Case 1
QueryDate = QueryDate + 1 'use Monday instead
Case 7
QueryDate = QueryDate - 1 'use Friday instead
End Select
'call the query here....
End Sub

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Hi,
I'm trying to do a query that returns entries with a specific date range.
In the case that the query returns no results that match. I would like it to
find the closest greater entry. In other words. If the query is asking to
bring back august 1,2004 and that day was a weekend. I would want it to
default to the following monday. Any help would be much appreciated.


SELECT BOOKINGS.[PT BOOKED], BOOKINGS.[MH BOOKED], BOOKINGS.DATE,
BOOKINGS.[TOTAL BOOKED], BOOKINGS.[PT TRANSFERED OUT], BOOKINGS.[MH
TRANSFERED OUT], BOOKINGS.[SHEAVES TRANSERED OUT], BOOKINGS.[TOTAL ORDERS],
BOOKINGS.[NUMBER PT ORDERS BOOKED], BOOKINGS.[NUMBER MH ORDERS BOOKED],
BOOKINGS.[TOTAL SHIPPED], BOOKINGS.[TOTAL INVOICE], BOOKINGS.MONTH,
BOOKINGS.YEAR, BOOKINGS.[DAY OF MONTH], BOOKINGS.[MH SHIPPED] FROM BOOKINGS
WHERE (((BOOKINGS.DATE)>=DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![QUERIES DATE]),Month([Forms]![BOOKINGS DAILY REPORT]![QUERIES
DATE])-1,1) And (BOOKINGS.DATE)<DateSerial(Year([Forms]![BOOKINGS DAILY
REPORT]![QUERIES DATE]),Month([Forms]![BOOKINGS DAILY REPORT]![QUERIES
DATE]),1)) AND ((BOOKINGS.[DAY OF MONTH])<=[Forms]![BOOKINGS DAILY
REPORT]![DAY]));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculated value in a query 2
Total appointments per agent 2
Help with relationships and reports 1
Query 2
Networkdays Conundrum 3
Setting up database 2
library inventory control 5
Amazon Returns 7

Top