Date Problem in Acess Query- Need Help

[LALTIZER]

Access: I am trying to complete a criteria in a query to pull prior day data OR if it is a Monday then to pull in Friday, Saturday, Sunday data. (Note: The main table is link to external source)

Below expession pulls in the prior day data, but every Monday, I need to pull in three days of data (Friday, Sat and Sun).

Is there a expression to allow this to happen without me having to manual change the expression every Monday morning in the data base? (I have 15 excel worksheets link to this Query)

Field: Invoice Creation Date
Table: Unbilled Table
Sort:
Show:
Criteria: Date()-1

Thank you
Laura

[alansidman]

Laura, this is untested as I didn't want to re-create your data base, but logically it should work. In your criteria for the Invoice Creation Date put this expression:

Please Login or Register  to view this content.

Here is the syntax on the Weekday function
http://www.techonthenet.com/access/f...te/weekday.php

If this doesn't work for you, then I have two more ideas to try.

Alan

[LALTIZER]

Alan, Thank you very much. I have pasted the expression and it work to pull yesterday data, the big test will be on Monday. I will keep for posted.

[LALTIZER]

Alan,

Sad news, the formula did not work, no data for Friday or Sat or Sunday. I have exported the query into excel file for you. On Monday only, I would like to see Friday, Sat and Sunday data(example - June 1,2 and 3rd), but on Tues thru Friday I would like to view the only the prior day data.

Thanks,
Laura

[LALTIZER]

Alan,

Sad news, the formula did not work, no data for Friday or Sat or Sunday. I have exported the query into excel file for you. On Monday only, I would like to see Friday, Sat and Sunday data(example - June 1,2 and 3rd), but on Tues thru Friday I would like to view the only the prior day data.

Thanks,
Laura

[alansidman]

Laura; I wasn't able to work on this until tonight. Give this SQL statement a try. I added a new field to the query based upon the excel spreadsheet I imported to a db. In the criteria of this query, you could put Not null.

SELECT Pic_tix___DS_Query.ID, Pic_tix___DS_Query.[Invoice Creation Date], Pic_tix___DS_Query.Source, Pic_tix___DS_Query.[Invoice Number], Pic_tix___DS_Query.[Sales Order], Pic_tix___DS_Query.[Customer PO], Pic_tix___DS_Query.[Contract Type], Pic_tix___DS_Query.[Billing Specialist], IIf(Weekday(Date())<>2 And [Invoice Creation Date]=Date()-1,Date()-1,IIf(Weekday(Date())=2 And [Invoice Creation Date]>Date()-4,[Invoice Creation Date],Null)) AS Expr1
FROM Pic_tix___DS_Query;

Alan

[LALTIZER]

Alan,

Thank you again for your help and I try this SQL statment.

[LALTIZER]

Alan,

The SQL statement work perfect. Thank you

Laura