Query to exclude weekends....

[jufranpnoy]

Hello I am new to this site as well as MS Access and SQL. So any help would be appreciated. Sorry if this is too drawn out. I have this query that returns the previous business day's(Monday through Friday) number of docs sent from my department to another department. I would manually go into the SQL view in Access and change the date to yesterday. The easy part was having the code do the previous day. The problem comes Monday morning when the code does the previous day. It will do Sunday which will return nothing since I really want Friday's numbers. I am trying to figure out how to take into consideration if the query is run on Monday morning and to automatically choose Friday. I checked out DayOfWeek() and Case When condition statements but really unsure about syntax and whatnot. Here is the SQL view of the query...

SELECT Count(WS_FORM_TEMP.FORM_TEMP_ID) AS CountOfFORM_TEMP_ID, WS_FORM_TEMP.FILENET_DOC_ID, WS_FORM_TEMP.LINKED_FILENET_DOC_ID, WS_FORM_TEMP.SSN, WS_FORM_TEMP.FORM_TYPE, WS_FORM_TEMP.RECEIVED_TYPE, WS_FORM_TEMP.DET_ACCT_NUM, WS_FORM_TEMP.DET_ACCT_NUM_CORR, WS_FORM_TEMP.SEQUENCE_NUM, WS_FORM_TEMP.CLAIM_EFFECTIVE_DATE, WS_FORM_TEMP.EMPLOYEE_START_DATE, WS_FORM_TEMP.EMPLOYEE_END_DATE, WS_FORM_TEMP.VACATION_PAY_FLAG, WS_FORM_TEMP.RETIREMENT_BENEFITS_FLAG, WS_FORM_TEMP.SEVERANCE_PAY_FLAG, WS_FORM_TEMP.SIGNED_RELEASE_FLAG, WS_FORM_TEMP.EMPLOYER_CONTACT_NAME, WS_FORM_TEMP.EMPLOYER_CONTACT_PHONE, WS_FORM_TEMP.EMPLOYER_CONTACT_PHONE_EXT, WS_FORM_TEMP.EMPLOYER_CONTACT_FAX, WS_FORM_TEMP.DUE_DATE, WS_FORM_TEMP.COMMENTS_FLAG, WS_FORM_TEMP.ATTACHMENTS_FLAG, WS_FORM_TEMP.POSTMARK_DATE, WS_FORM_TEMP.RECEIVED_DATE, WS_FORM_TEMP.CREATED_DATE, WS_FORM_TEMP.WAGE1_END_DATE, WS_FORM_TEMP.WAGE1_WAGE, WS_FORM_TEMP.WAGE2_END_DATE, WS_FORM_TEMP.WAGE2_WAGE, WS_FORM_TEMP.WAGE3_END_DATE, WS_FORM_TEMP.WAGE3_WAGE, WS_FORM_TEMP.WAGE4_END_DATE, WS_FORM_TEMP.WAGE4_WAGE, WS_FORM_TEMP.WAGE5_END_DATE, WS_FORM_TEMP.WAGE5_WAGE, WS_FORM_TEMP.WAGE6_END_DATE, WS_FORM_TEMP.WAGE6_WAGE, WS_FORM_TEMP.WAGE7_END_DATE, WS_FORM_TEMP.WAGE7_WAGE, WS_FORM_TEMP.WAGE8_END_DATE, WS_FORM_TEMP.WAGE8_WAGE, WS_FORM_TEMP.SEP_LAIDOFF_FLAG, WS_FORM_TEMP.SEP_LAIDOFF_RECALL_DATE, WS_FORM_TEMP.SEP_QUIT_FLAG, WS_FORM_TEMP.SEP_DISCHARGED_FLAG, WS_FORM_TEMP.SEP_SUSPENDED_FLAG, WS_FORM_TEMP.SEP_CONVICTION_FLAG, WS_FORM_TEMP.SEP_PERFORMANCE_FLAG, WS_FORM_TEMP.SEP_STRIKE_FLAG, WS_FORM_TEMP.SEP_ABSENCE_FLAG, WS_FORM_TEMP.SEP_ASSURANCE_FLAG, WS_FORM_TEMP.SEP_EMPLOYED_FLAG, WS_FORM_TEMP.FORM_ID, WS_FORM_TEMP.STATUS
FROM WS_FORM_TEMP
GROUP BY WS_FORM_TEMP.FILENET_DOC_ID, WS_FORM_TEMP.LINKED_FILENET_DOC_ID, WS_FORM_TEMP.SSN, WS_FORM_TEMP.FORM_TYPE, WS_FORM_TEMP.RECEIVED_TYPE, WS_FORM_TEMP.DET_ACCT_NUM, WS_FORM_TEMP.DET_ACCT_NUM_CORR, WS_FORM_TEMP.SEQUENCE_NUM, WS_FORM_TEMP.CLAIM_EFFECTIVE_DATE, WS_FORM_TEMP.EMPLOYEE_START_DATE, WS_FORM_TEMP.EMPLOYEE_END_DATE, WS_FORM_TEMP.VACATION_PAY_FLAG, WS_FORM_TEMP.RETIREMENT_BENEFITS_FLAG, WS_FORM_TEMP.SEVERANCE_PAY_FLAG, WS_FORM_TEMP.SIGNED_RELEASE_FLAG, WS_FORM_TEMP.EMPLOYER_CONTACT_NAME, WS_FORM_TEMP.EMPLOYER_CONTACT_PHONE, WS_FORM_TEMP.EMPLOYER_CONTACT_PHONE_EXT, WS_FORM_TEMP.EMPLOYER_CONTACT_FAX, WS_FORM_TEMP.DUE_DATE, WS_FORM_TEMP.COMMENTS_FLAG, WS_FORM_TEMP.ATTACHMENTS_FLAG, WS_FORM_TEMP.POSTMARK_DATE, WS_FORM_TEMP.RECEIVED_DATE, WS_FORM_TEMP.CREATED_DATE, WS_FORM_TEMP.WAGE1_END_DATE, WS_FORM_TEMP.WAGE1_WAGE, WS_FORM_TEMP.WAGE2_END_DATE, WS_FORM_TEMP.WAGE2_WAGE, WS_FORM_TEMP.WAGE3_END_DATE, WS_FORM_TEMP.WAGE3_WAGE, WS_FORM_TEMP.WAGE4_END_DATE, WS_FORM_TEMP.WAGE4_WAGE, WS_FORM_TEMP.WAGE5_END_DATE, WS_FORM_TEMP.WAGE5_WAGE, WS_FORM_TEMP.WAGE6_END_DATE, WS_FORM_TEMP.WAGE6_WAGE, WS_FORM_TEMP.WAGE7_END_DATE, WS_FORM_TEMP.WAGE7_WAGE, WS_FORM_TEMP.WAGE8_END_DATE, WS_FORM_TEMP.WAGE8_WAGE, WS_FORM_TEMP.SEP_LAIDOFF_FLAG, WS_FORM_TEMP.SEP_LAIDOFF_RECALL_DATE, WS_FORM_TEMP.SEP_QUIT_FLAG, WS_FORM_TEMP.SEP_DISCHARGED_FLAG, WS_FORM_TEMP.SEP_SUSPENDED_FLAG, WS_FORM_TEMP.SEP_CONVICTION_FLAG, WS_FORM_TEMP.SEP_PERFORMANCE_FLAG, WS_FORM_TEMP.SEP_STRIKE_FLAG, WS_FORM_TEMP.SEP_ABSENCE_FLAG, WS_FORM_TEMP.SEP_ASSURANCE_FLAG, WS_FORM_TEMP.SEP_EMPLOYED_FLAG, WS_FORM_TEMP.FORM_ID, WS_FORM_TEMP.STATUS
HAVING (((WS_FORM_TEMP.CREATED_DATE)=to_char(sysdate-1,'DD-Mon-YYYY')) AND ((WS_FORM_TEMP.RECEIVED_TYPE='1') or (WS_FORM_TEMP.RECEIVED_TYPE='2')));

Thanks in advance.

 

[Jon K]

In Access, if CREATED_DATE is a date/time field (i.e. not a text field), you can use Format() to test whether the system date Date() returns a Monday or not. If it does, -3 days, otherwise -1 day from Date():-

HAVING (((WS_FORM_TEMP.CREATED_DATE)=IIf(Format(Date(),"ddd")="Mon",Date()-3,Date()-1)) AND ((WS_FORM_TEMP.RECEIVED_TYPE='1') or (WS_FORM_TEMP.RECEIVED_TYPE='2')));
.

 

[jufranpnoy]

Thanks I will definitely try that. Right now I am working on another freaking problem. While doing my research online for that query, one of the sites I went to installed some malicious software on my PC. Now I am battling all these pop ups and whatnot. It's so annoying. I knew I should have gone into a different profession. I am starting to really dislike the IT field : ) Thanks again.

 

[neileg]

Try Spybot and Ad-Aware to combat your popups. Both are free downloads.

 

[jufranpnoy]

I d/led Adware and Spysweeper. I will d/l Spybot next. I am also looking into this prog called HijackThis.

 

[jufranpnoy]

Jon, I did try your changes. But I am getting an error message: "ORA-00920: invalid relational operator".

In Access, if CREATED_DATE is a date/time field (i.e. not a text field), you can use Format() to test whether the system date Date() returns a Monday or not. If it does, -3 days, otherwise -1 day from Date():-

HAVING (((WS_FORM_TEMP.CREATED_DATE)=IIf(Format(Date(),"ddd")="Mon",Date()-3,Date()-1)) AND ((WS_FORM_TEMP.RECEIVED_TYPE='1') or (WS_FORM_TEMP.RECEIVED_TYPE='2')));
.

 

[EMP]

I have no problem running Jon's expression in Access 2K.

Today is Friday July 1 here. When I ran the query in the attached database, it returned the record of Thursday June 30. No error popped up.

As Jon said, the expression works only in Access. You may need to make changes to it if you use it in SQL Server.

^

 

[jufranpnoy]

First, thanks for your help. But crap. Feel so stupid. I could have sworn this db was in 2000. But it's Access 97. IIf command won't work in 97 right? I vaguely remember IIf not being supported by one of the versions. And since you got it to work in 2000, then it must be 97. Is there an equivalent in 97? I will try to search more in the mean time.

I have no problem running Jon's expression in Access 2K.

Today is Friday July 1 here. When I ran the query in the attached database, it returned the record of Thursday June 30. No error popped up.

As Jon said, the expression works only in Access. You may need to make changes to it if you use it in SQL Server.

^

 

[ScottGem]

First, thanks for your help. But crap. Feel so stupid. I could have sworn this db was in 2000. But it's Access 97. IIf command won't work in 97 right? I vaguely remember IIf not being supported by one of the versions. And since you got it to work in 2000, then it must be 97. Is there an equivalent in 97? I will try to search more in the mean time.

IIF() is supported in all versions.

 

[raskew]

The following will return the previous workday (presuming that Saturday and Sunday are the non-workdays):

dteMydate = #7/3/05#
? dteMyDate - IIf(WeekDay(dteMyDate) < 3, 1 + WeekDay(dteMyDate), 1)
7/1/05

HTH - Bob

 

[RV]

Jon, I did try your changes. But I am getting an error message: "ORA-00920: invalid relational operator".

Well that means you're running your query against an ORACLE database.
ORACLE comes with SQL that's different from Access SQL and doesn't come with an IFF function....

Can't tell you what you would need to do to run queries from within Access against ORACLE databases.
Perhaps someone else can.

RV