Date field

[thomas_kemp]

I am working on a database and a form that has a set date field. I need that field to reflect the week commencing date for the week the users are currently working on ie they are inputting data today-now- (25th April) but I would like the date field to be showing (21st April)-week commencing date.

any help would be appreciated.

 

[Mile-O]

I don't know how you set the dates but this code would go on your form - where it is most relevant.

    If WeekDay(Date) = 2 Then
        txtWeek = Date
    Else
        txtWeek = RevertWeek(txtWeek)
    End If

And this code can go in a module, so that it can be called from elsewhere within your database.

Public Function RevertWeek(ByVal dteCurrentDate As Date) As Date

    On Error GoTo Err_RevertWeek

    Dim intCounter As Integer
        
    For intCounter = 7 To 1 Step -1
        dteCurrentDate = dteCurrentDate - 1
        If WeekDay(dteCurrentDate) = 2 Then Exit For
    Next intCounter
    
    RevertWeek = dteCurrentDate
    
Exit_RevertWeek:
    Exit Function
    
Err_RevertWeek:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_RevertWeek
    
End Function

 

[raskew]

Try copying/pasting this to a new module then following the instructions for invoking the function.

Function LastNDay(pMyDate As Date, pLastNDay As Integer) As Date
'*******************************************
'Name:      LastNDay (Function)
'Purpose:   Display the start date of the week in which pMyDate falls.
'Goal:      Six or less working lines of code
'Note:      pLastNDay: 1 (Sunday) to 7 (Saturday)
'Inputs:    from debug (immediate) window
'           ? lastNday(#4/25/03#, 2)<enter>
'Output:    4/21/03
'*******************************************

LastNDay = IIf(WeekDay(pMyDate) = pLastNDay, pMyDate, pMyDate - (WeekDay(pMyDate) + IIf(WeekDay(pMyDate) <= pLastNDay, 7, 0) - pLastNDay))

End Function

 

[thomas_kemp]

Thanks for your help so far.

I have to admit my knowledge of VBA is very limited, if you could give me a step by step guide I would be most grateful.

At the moment the form is pulling data from a field called “date” in a table, its format is “short date” and it is structured in the English date syntax - DDMMYY.

I just require that date field to display the week commencing (the Monday of the week) every time they input data or convert it once they have put the date in.

Hope you can help me some more

Thanks.

 

[Rich]

You need to rename your date field, Date is a reserved word in Access and will cause you problems