Condtional Parameter

[SDS100UK]

Hi,

I have a Make Tablequery that performs on yesterdays date.

This is OK except on a Monday I want it to query Friday & Saturday. I have the code using IIF that will do this but what I cant seem to get it to do is give me a Parameter that asks for the Friday or Saturday date so that I can print out a report for each day, but ONLY prompt me on a Monday.

The rest of the week I want the ELSE part of IIF to take 1 day off DATE()

Can anyone help me??

Many thanks

Steven

 

[winbod]

If you put this in the criteria against the date column:

IIf(Weekday(Date()) In (6,7),[Please enter date to query],Date())

It works perfectly apart from the fact that it keeps prompting for the date whether it needs it ot not

 

[UncleJack]

Have you considered using two queries and then using the day of the week to trigger the appropriate query automatically? Not very elegant, but effective.


Uncle Jack

 

[MichaelRed]

I am mystified as to why you would want your users to have to enter a date on some days but not others. I am also not at all sure that I fully understand your issue. In light of my confusion, you should consider the following carefully, but it may be of some use.

Public Function basPrevWeekDay(Optional DtIn) As Date

    If (IsMissing(DtIn)) Then
        DtIn = Date
    End If

    Select Case Weekday(DtIn)
        Case Is = vbSaturday, vbSunday, vbMonday
            basPrevWeekDay = DateAdd("d", Weekday(DtIn) - 5, DtIn)

        Case Else
            basPrevWeekDay = DateAdd("d", -1, DtIn)
    End Select

End Function

Of course, the function should return the previous day's date unless the current day is Sat, Sun, or Mon, in which instances it should return the previous Fri's date. I am pretty sure that you will have SOME issues with holidays, bubt since you haven't mentioned these, I will for-go the tedium of this explination.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over