Between x and x not working 1

[shart00]

I tried the following and also tried using Between [x] and [x]. But both result in 0 records and when I check the base table there are records.
[Monday], [Tuesday], ect are field names in the table DSRDATES that have dates for the corresponding day of the week.

IIf(Weekday(Now())=2,([Base Table].[Date])=[Monday] Or ([Base Table].[Date])=[Tuesday] Or ([Base Table].[Date])=[Wednesday] Or ([Base Table].[Date])=[Thursday] Or ([Base Table].[Date])=[Friday] Or ([Base Table].[Date])=[Saturday] Or ([Base Table].[Date])=[Sunday],IIf(Weekday(Now())=3,[Monday],IIf(Weekday(Now())=4,([Base Table].[Date])=[Monday] Or ([Base Table].[Date])=[Tuesday],IIf(Weekday(Now())=5,([Base Table].[Date])=[Monday] Or ([Base Table].[Date])=[Tuesday] Or ([Base Table].[Date])=[Wednesday],IIf(Weekday(Now())=6,([Base Table].[Date])=[Monday] Or ([Base Table].[Date])=[Tuesday] Or ([Base Table].[Date])=[Wednesday] Or ([Base Table].[Date])=[Thursday],IIf(Weekday(Now())=7,([Base Table].[Date])=[Monday] Or ([Base Table].[Date])=[Tuesday] Or ([Base Table].[Date])=[Wednesday] Or ([Base Table].[Date])=[Thursday] Or ([Base Table].[Date])=[Friday]))))))


Any suggestions?

 

[lespaul]

your iif statement is screwy!

iif(Weekday(now()) = 2, what to do if true, what to do if false)

your what to do if true statement is:

[Base Table].[Date])=[Monday] Or ([Base Table].[Date])=[Tuesday] Or ([Base Table].[Date])=[Wednesday] Or ([Base Table].[Date])=[Thursday] Or ([Base Table].[Date])=[Friday] Or ([Base Table].[Date])=[Saturday] Or ([Base Table].[Date])=[Sunday]

that's not something to do, that's another comparison.

What are you trying to accomplish with this statement (or the between statement that didn't work)?


Leslie

 

[shart00]

This is a Make Table Query.
The 2 tables involved are: Base Table (Has all the data) and DSRDATES (Simply is a table with the field names Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, etc.)
What I need is to make a table (Reporting Table) that has all the data from base table but with one criteria:

If weekday = 2 (monday), take all the data from Base table with dates equal to the dates in the fields: monday, tuesday, wedensday, thursdsay, friday, saturday or sunday,
OR
if weekday = 3 (tuesday), take all data with dates equal to monday's date
OR
if weekday = 4 (wednesday), take all data with dates equal to: Monday or Tuesday
OR
if weekday = 5 (Thursday), take all data with dates equal to: Monday or Tuesday or Wednesday
OR
if weekday = 6 (Friday), take all data with dates equal to: Monday or Tuesday or Wednesday or Thursday
OR
if weekday = 7 (Saturday), take all data with date equal to: Monday or Tuesday or Wednesday or Thursday or Friday


This would allow the query to automatically say: Since I am running on Wednesday, and Access automatically assigns Wednesday 4,
I need to make the reporting table from the base table with records where [Date] = Monday or Tuesday.

Here is the Between statement that did not work:
IIf(Weekday(Now())=2,([Base Table].[Date]) Between [monday] And [sunday],IIf(Weekday(Now())=3,[monday],IIf(Weekday(Now())=4,([Base Table].[Date]) Between [monday] And [tuesday],IIf(Weekday(Now())=5,([Base Table].[Date]) Between [monday] And [wednesday],IIf(Weekday(Now())=6,([Base Table].[Date]) Between [monday] And [thursday],IIf(Weekday(Now())=7,([Base Table].[Date]) Between [monday] And [Friday]))))))

Any suggestions? This database is growing so much that this is needed so that people do not need to go into the query's and modify criteria. The last time people did that, it took me all day to fix it (However, it was quicker than calling for a tape recovery)

 

[lespaul]

I guess I'm confused because you say:

take all data with dates equal to Monday or Tuesday.

Monday isn't a DATE it is a Day of the Week, 1/25/05 is a date.

You want to find all the data where the date has a day of the week equal to a monday or a tuesday? All the Mondays & Tuesdays or just the last monday & Tuesday?

And what is WEEKDAY? Is that a field in your table?

Can you post the SQL you are using, a sample of what the tables contain, and what you want your final result set to look like?


Leslie

 

[shart00]

[monday], [Tuesday], etc are fields in the table DSRDATES.
Those fields are populated with actual dates such as 1/24/05 is currently in the field [Monday] and 1/25/05 is currently in [Tuesday] and so on.

What I am looking for the Make table query to do is look through Base table and make a new table with the records that their [Date] field is equal to [Monday] or [Tuesday].

This is so that when the query runs on a date that falls on a monday the query will look through the Base table and pull all records who's [Date] is equal to either [monday] or [tuesday] ..... or [sunday] from the DSRDATES and make a new table with those records only.

Sample from base:
[field1] [date]
X 1/2/04
c 12/3/04
d 1/24/05
e 1/25/05
f 1/26/05
g 1/27/05
h 1/28/05

Sample from DSRDATES:
[Monday]=1/24/05
[Tuesday]=1/25/05
[Wednesday]=1/26/05
[Thursday]=1/27/05
[Friday]=1/28/05
[Saturday]=1/29/05
[Sunday]=1/30/05

Since Access assigns each day of the week a number starting with sunday=1 and this is symbolized by weekday(now))=1 and Monday = weekday(now())=2 and so on.

when the query runs on Wednesday, weekday(now))=3

so when the query runs on a wedensday it should look through base and pick up only those records whose [Date]=1/24/05 or 1/25/05.

or when it runs on a Thrusday it should look and pick up [date]=1/24/05 or 1/25/05 or 1/26/05

and so on....

I would post the entire SQL but there are so many formulas in expressions that it would be a small novel. Here is the portion related to the criteria:

WHERE ((([Base Table].Date)=IIf(Weekday(Now())=2,([Base Table].[Date]) Between [monday] And [sunday],IIf(Weekday(Now())=3,[monday],IIf(Weekday(Now())=4,([Base Table].[Date]) Between [monday] And [tuesday],IIf(Weekday(Now())=5,([Base Table].[Date]) Between [monday] And [wednesday],IIf(Weekday(Now())=6,([Base Table].[Date]) Between [monday] And [thursday],IIf(Weekday(Now())=7,([Base Table].[Date]) Between [monday] And [Friday]))))))))

 

[lespaul]

I think you are going to have to create your query in VBA on the fly. What you really want to do is JOIN these two tables on the dates in the fields so that only specific dates show up.
So if it's a Tuesday your query would look like this:

SELECT F1, F2, F3
FROM BASE_TABLE B
INNER JOIN DSRDATES D on B.DateField = D.Monday

Now if it's a Wednesday, you would need to also join in to the DSRDATES.Tuesday field.

(although I'm not sure if you need to use an AND in the JOIN like this:
INNER JOIN DSRDATES D on B.DateField = D.Monday AND B.DateField = D.Tuesday

or if you would need a second INNER JOIN statement on another instance of DSRDATES)

So, for each day you will need a different SQL statement based on your criteria for each day's report.




Leslie

 

[JonFer]

Create a "DateRange" table like this and use it instead of your DSRDATES table:

WeekDayNum   DaysToFirst
  2           7        
  3           1           
  4           2           
  5           3           
  6           4           
  7           5

Use a Where clause like this:

Where B.DateField Between Date()-[DaysToFirst] And Date()-1
And WeekDay(Date())=[WeekDayNum]


John

http://www.interautomateconsulting.com

 

[lespaul]

what a nice solution! very clever. I usually do my manipulation in Delphi, so if I can just get the data, I do what I need to then. I would have never thought of a pure SQL solution to this problem.

Have a star!!

les

 

[shart00]

FYI... Code fixed!

>=IIf(Weekday(Now())=2,DateValue(Now())-7,DateValue(Now())-Weekday(Now())+2) And <=IIf(Weekday(Now())=2,DateValue(Now())-1,DateValue(Now())-1)

Had to separate the between to different parts of the code but it seems to work so far. The real test will be on Monday when Weekday(now())=2

I really could not change the table because there are other queries and reports that depend upone the actual date in DSRDATES, especially since Base has data from 1/1/04 - Current. This is so that all one has to do is update the date range in DSRDATES and they can pull any week of the past year.

Thank you all very much for you help and input!

 

[JonFer]

This is a little simpler and should also work:

Between DateValue(Now()-IIf(Weekday(Now())=2,7,Weekday(Now())-2)) And DateValue(Now()-1)


John

http://www.interautomateconsulting.com