DateAdd (1 Viewer)

[Simon_MT]

I was wondering how to do DateAdd in Vba, here my unsuccessful attempt:

Function DayofWeekLookup()

Dim MyControl As Control
        Set MyControl = Screen.ActiveControl

    With CodeContextObject
        Dim IntervalType As String
        Dim StartDate As Date
        IntervalType = "d"
        StartDate = .[Start Date]
        MyControl.RowSource = "SELECT DateAdd(IntervalType,[dayofweekoffset],StartDate) AS TimeSheetDate FROM tblDaysofWeek;"
    End With
End Function

Needless to say I was prompted for IntervalType and StartDate. Bother!

Simon

 

[pbaldy]

Not surprising actually. The VBA variables would not be available to the row source SQL. You'd have to concatenate them in. Try:

MyControl.RowSource = "SELECT DateAdd('" & IntervalType & "',[dayofweekoffset],#" & StartDate & "#) AS TimeSheetDate FROM tblDaysofWeek;"

which is a bit of a shot-in-the-dark.

 

[MarkK]

But looking at the bigger picture I'm suspicious of a table named 'DaysOfWeek'. VBA offers a WeekdayName() function that returns the full name of the day or an abbreviated name, and my guess is that you might find it easier to use some of the existing tools for handling dates, particularly the Weekday() and Day() functions.
If that interests you, perhaps say more about what your ultimate objective is.
HTH

 

[Simon_MT]

Thanks pbaldy, it was get late, all I had to do was to remove the hashes to get to European date formats.

This is just trying to put some sort of Input control on data entry of Timesheets. You select your week and the provide a range of valid dates for that week. This is an usual financial year starting on the 29/03/10.

Simon

 

[pbaldy]

Glad you got it sorted out.

 

[Simon_MT]

Thanks again, I've always had problems with Dates, in VBA, so thanks very much for your help, pbaldy. I now understand why there are so many questions related to dates particularly if you use European formats.

Simon