Find date from day name.

[Homegrownandy]

I have a time sheet submission system that I'm seeking to improve.



I'm hoping this can be done via a query but a little guidance is all I'm after right now.

Users submit a timesheet/activity log for a weeks worth of work. The relevant data on this is:

- week ending
- day (mon/tues/wed..etc)

I'm wondering whats the best way to assign a date to the day after submission.

example:

time sheet for week ending 11/Feb/2019

Monday = 04/02/2019
Tuesday = 05/02/2019
Wednesday = 06/02/2019

and so on..

I'm trying to avoid human error when invoicing for days. Thoughts and advice appreciated.

Andy.

[davegri]

datevalue(#02/11/19#)-1
will show the previous day

[ranman256]

format([dateFld],"dddd")
will give Monday, Tuesday, etc

format([dateFld],"ddd")
will give Mon, Tue, etc

[Gicu]

Can you show us how your data looks like? You can use simple subtraction to get your days from the EndOfWeek (always a Monday?):

Mon=[EndOfWeek]-7,Tue=[EndOfWeek]-6, etc.

Cheers,
Vlad

[ssanfu]

There are several methods you could use, depending on your form.

1) If you have a combo box to select the week ending? You could have code in the after update event to calculate and push the dates into the correct control for each day.

2) You enter the week ending date into a text box. The form Before Update event validates date entered is the correct week ending date (always a Mon). Part of the process would be to calculate the dates and push the date into the correct control.

[Homegrownandy]

Sorry, this is imported data. They don't have any interaction with the database at this stage.

data is coming in as shown here:



So, because we use Sunday as week ending date. If its a Monday its week ending -6.

I could set up some macros in excel possibly. what do you think?

Andy

basically, "Monday" is useless for historical records I just want to change this value to an actual date.

@gicu - your suggestion looks right but I'm confused where I would implement that.

[Gicu]

In Access after you import the Excel file. I suggest you import into a temporary table with one extra column called JobDate or something and use an update query to update it to the right date. The easiest that comes to mind would be using expression like: JobDate: IIF([Day]="Monday",[Week Ending]-6,[Day]="Tuesday",[Week Ending -5],........,[Week Ending]))))))).

Cheers,
Vlad

[ArviLaanemets]

Create a table
SomeTable: WeekdayName, DaysFromWeekend

with values
Monday 6
Tuesday 5
etc.

Now you can use this table in query (linked with weekday name) or in formula (using DLookup). The weekday date will be [WEEK ENDING] - DaysFromWeekend.

[ssanfu]

If I am understanding correctly, you want to have a field in an Access tale that has the date of each day in the record. (DOM is Day of Month in the image)
If the weekending is 10/2/2019 (10 Feb 2019), for the Monday record you want DOM = 4/2/2019, Tues = 5/2/2019, Wed = 6/2/2019, etc???

Code could handle that. Open a record set where DOM is NULL, then for each record, use the Week ending and the day to calculate the date. (BTW "Day" is a reserved word). Loop until all records have a date.

Untested ,but code might look something like

Sub CalcDate()
    Dim r As DAO.Recordset
    Dim sSQL As String
    Dim pDOW As String
    Dim DayNum As Integer

    sSQL = "SELECT DOW, DOM, WeekEnding FROM tblImport WHERE DOM IS NULL;"
    'DOW = day of week - Mon, Tue, etc
    'DOM = day of month -  4/2/2019
    Set r = CurrentDb.OpenRecordset(sSQL)

    If Not r.BOF And Not r.EOF Then
        r.MoveLast
        r.MoveFirst
        Do Until r.EOF
            pDOW = r!DOW
            Select Case pDOW
                Case "Monday"
                    DayNum = -6
                Case "Tuesday"
                    DayNum = -5
                Case "Wednesday"
                    DayNum = -4
                Case "Thursday"
                    DayNum = -3
                Case "Friday"
                    DayNum = -2
            End Select

            r.Edit
            r!DOM = DateAdd("d", DayNum, r!WeekEnding)
            r.Update

            r.MoveNext
        Loop
    End If

    r.Close
    Set r = Nothing
End Sub

[June7]

[Week Ending]-Switch([Day]="Monday",6, [Day]="Tuesday",5, [Day]="Wednesday",4, [Day]="Thursday",3, [Day]="Friday",2, [Day]="Saturday",1, [Day]="Sunday",0)

[ssanfu]

Or as June suggests (simpler)

Sub CalcDate2()
    Dim r As DAO.Recordset
    Dim sSQL As String
    Dim pDOW As String

    sSQL = "SELECT DOW, DOM, WeekEnding FROM tblImport WHERE DOM IS NULL;"
    'DOW = day of week - Mon, Tue, etc
    'DOM = day of month -  4/2/2019
    Set r = CurrentDb.OpenRecordset(sSQL)

    If Not r.BOF And Not r.EOF Then
        r.MoveLast
        r.MoveFirst
        Do Until r.EOF
            pDOW = r!DOW
            r.Edit
            r!DOM = r!WeekEnding - Switch([pDOW] = "Monday", 6, [pDOW] = "Tuesday", 5, [pDOW] = "Wednesday", 4, [pDOW] = "Thursday", 3, [pDOW] = "Friday", 2, [pDOW] = "Saturday", 1, [pDOW] = "Sunday", 0)
            r.Update

            r.MoveNext
        Loop
    End If

    r.Close
    Set r = Nothing
End Sub

why didn't I think of that??

[June7]

But why loop a recordset? Why not just an UPDATE action sql?

[ssanfu]

Ummmmm...... Ahhhhhhh........Ohhhhhhhh......... it was a teaching moment?????
Yeah, yeah... that is what I am going to stick with.

[Gicu]

Sorry guys, a learning moment for me too , would you please let me know what was wrong with the suggestion I made in post # 7?

Vlad

[June7]

It will work but why repeat [Week Ending] in expression when referencing it once will work. Also Switch() is just simpler structure than nested IIfs. Choose() is another function that can often replace nested IIfs. Shorter may be better. There is a limit on number of characters in query design grid cell. However, I think Switch and Choose are VBA functions and there might be a slight reduction in performance compared to IIf in query.

If this is a procedure that will be run periodically, VBA will likely be involved. So VBA can call an UPDATE query object that does the update or UPDATE SQL statement can be run in VBA.

Or don't update field and just calculate the date when needed.