Reputation: 531
I have a SELECT query that contains a date field. I need to SELECT only certain dates depending on which day of the week the query is run. If the day the query is run is a Monday then I need the date field to be equal to Friday, Saturday, or Sundays date. If the query is run on any other day then I need it the date field to equal the previous days date. Below is the query before I have incorporated any WHERE clauses for the dates. Not sure if this can be done with just SQL or if I need to incorporate any vba. Thank you in advance!
SELECT qryCourses.[App ID] AS Application, "Course" AS [Revision Type], qryRevisionLogCourseLevel.Category AS Category, qryRevisionLogCourseLevel.[Revision Date] AS [Revision Date], qryRevisionLogCourseLevel.[File Name] AS [Asset/Course/Track Title], qryRevisionLogCourseLevel.[File Name] AS [Used In], qryRevisionLogCourseLevel.[Revision Notes] AS [Revision Notes], qryRevisionLogCourseLevel.[Revised By] AS [Revised By], qryRevisionLogCourseLevel.Impact AS Impact, qryRevisionLogCourseLevel.[Communication Method] AS [Communication Method], qryRevisionLogCourseLevel.[Communication Date] AS [Communication Date], qryRevisionLogCourseLevel.[CR Number] AS [CR Number] FROM qryRevisionLogCourseLevel INNER JOIN qryCourses ON qryRevisionLogCourseLevel.[Course ID] = qryCourses.[Course ID] WHERE qryCourses.[App ID] <> "AAAA"
UNION ALL SELECT qryTracks.App AS Application, "Track" AS [Revision Type], qryRevisionLogTrackLevel.Category, qryRevisionLogTrackLevel.[Revision Date], qryTracks.[Track Name] AS [Asset/Course/Track Title], qryTracks.[Track Name] AS [Used In], qryRevisionLogTrackLevel.[Revision Notes], qryRevisionLogTrackLevel.[Revised By], qryRevisionLogTrackLevel.Impact, qryRevisionLogTrackLevel.[Communication Method], qryRevisionLogTrackLevel.[Communication Date], qryRevisionLogTrackLevel.[CR Number] FROM qryRevisionLogTrackLevel INNER JOIN qryTracks ON qryRevisionLogTrackLevel.[Track ID] = qryTracks.[Track ID] WHERE qryTracks.[App] <> "AAAA"
UNION ALL SELECT qryCMRevLogConcatCourseName.App AS Application, qryCMRevLogConcatCourseName.qryCMRevLogMaterialLevel.RevType AS [Revision Type], qryCMRevLogConcatCourseName.qryCMRevLogMaterialLevel.Category AS Category, qryCMRevLogConcatCourseName.[Revision Date] AS [Revision Date], qryCMRevLogConcatCourseName.[Deliverable Title] AS [Asset/Course/Track Title], qryCMRevLogConcatCourseName.[Used In] AS [Used In], qryCMRevLogConcatCourseName.[Revision Notes] AS [Revision Notes], qryCMRevLogConcatCourseName.qryCMRevLogMaterialLevel.[Revised By] AS [Revised By], qryCMRevLogConcatCourseName.qryCMRevLogMaterialLevel.Impact AS Impact, qryCMRevLogConcatCourseName.qryCMRevLogMaterialLevel.[Communication Method] AS [Communication Method], qryCMRevLogConcatCourseName.qryCMRevLogMaterialLevel.[Communication Date] AS [Communication Date], qryCMRevLogConcatCourseName.qryCMRevLogMaterialLevel.[CR Number] AS [CR Number] FROM qryCMRevLogConcatCourseName WHERE qryCMRevLogConcatCourseName.App <>"AAAA" ORDER BY Application, [Revision Type];
Upvotes: 0
Views: 658
Reputation: 55806
You can use this criteria:
Where
[Revision Date] Between
DateAdd("d", IIf(Weekday(Date()) = 2, -3, -1), Date()) And
DateAdd("d", -1, Date())
Upvotes: 2
Reputation: 7107
Select Case Weekday(Now)
Case is = 2
vbSql = "SELECT THIS FROM TBL WHERE Date>=#" & DateAdd("d", -3, NOW()) & "# AND Date<=#" & DateAdd("d", -1, NOW()) & "#;"
Case else
vbSql = "SELECT THIS FROM TBL WHERE Date=#" & DateAdd("d", -1, NOW()) & "#;"
End Select
DoCmd.RunSql vbSql
Edit to help you out on syntax instead of psuedo code
Upvotes: 0