By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

Date conditions

7 of 9
P: 23
Hello All,

Here is my problem I have i set of queries (4) that I run manualy every morning to check all transactions from the previous day. I am in the process of creating the macro to run them automatically every morning but i have run into a slight problem. My first query is

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT cono, whse, prod, module, transtype, stkqtyship, phyadjexcp, operinit, postdt, transtm 
  3.  
  4. FROM tblLcet
  5.  
  6. WHERE  ((tblLcet.postdt)>=Now()-1);
  7.  
  8.  
but what do i do when on monday when Now()-1 returns nothing because we are closed on sunday?
May 23 '08 #1
Share this Question
Share on Google+
4 Replies


nico5038
Expert 2.5K+
P: 3,072
Hello All,

Here is my problem I have i set of queries (4) that I run manualy every morning to check all transactions from the previous day. I am in the process of creating the macro to run them automatically every morning but i have run into a slight problem. My first query is

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT cono, whse, prod, module, transtype, stkqtyship, phyadjexcp, operinit, postdt, transtm 
  3.  
  4. FROM tblLcet
  5.  
  6. WHERE  ((tblLcet.postdt)>=Now()-1);
  7.  
  8.  
but what do i do when on monday when Now()-1 returns nothing because we are closed on sunday?
And not to mention the holidays like X-mas, etc...
Probably you want always the last date holding the transactions of the previous business day.
I would just select the MAX() tblLcet.postdt being not today like:

WHERE tblLcet.postdt = (select max(B.postdt) from tblLcet B WHERE B.postdt < Date())

Getting the idea ?

Nic;o)
May 23 '08 #2

7 of 9
P: 23
And not to mention the holidays like X-mas, etc...
Probably you want always the last date holding the transactions of the previous business day.
I would just select the MAX() tblLcet.postdt being not today like:

WHERE tblLcet.postdt = (select max(B.postdt) from tblLcet B WHERE B.postdt < Date())

Getting the idea ?

Nic;o)
Thanks Nico I tried it and it works but what if someone comes in on saturday to catch up with his/ her work? The report (That runs mon - friday) would never show the transactions from friday. How would resolve this?
May 23 '08 #3

nico5038
Expert 2.5K+
P: 3,072
Just schedule the job every day (use the windows scheduler) and save the file with the Date() in the name. The size will tell you or something has been found and the dates or all jobs did run.

Nic;o)
May 23 '08 #4

7 of 9
P: 23
Just schedule the job every day (use the windows scheduler) and save the file with the Date() in the name. The size will tell you or something has been found and the dates or all jobs did run.

Nic;o)

Thanks again ill give it a try this weekend.
May 23 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.