Date conditions

[7 of 9]

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

 
SELECT cono, whse, prod, module, transtype, stkqtyship, phyadjexcp, operinit, postdt, transtm 
 
FROM tblLcet
 
WHERE  ((tblLcet.postdt)>=Now()-1);
 
 

but what do i do when on monday when Now()-1 returns nothing because we are closed on sunday?

[nico5038]

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

 
SELECT cono, whse, prod, module, transtype, stkqtyship, phyadjexcp, operinit, postdt, transtm 
 
FROM tblLcet
 
WHERE  ((tblLcet.postdt)>=Now()-1);
 
 

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)

[7 of 9]

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?

[nico5038]

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)

[7 of 9]

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.