Calculations using Days of the week (1 Viewer)

[Jarichardusa]

This may be more complex then I think. I'm no expert and have just recently started learning how some of this stuff work. I'm building a fairly simple DB to keep track of my work and pay. I'm a gig worker and jobs I complete Tu-Sa pay out on the following Monday, and Jobs worked Su and Mo pay out on the following Wednesday. Each job has a set wage which is tallied the day the job is completed and tips for that work can come in at any time. I have a table, tblDeliveries, which keeps all the information for each job. The relevant fields I believe would be 'DeliveryDate', 'Wage', 'TipDate', 'TipAmount' I have a form, 'frmPayDay' with a textbox, 'txtDateEntry' and a btn, 'btnDateSelect' There are two other textboxes. 'txtPayDate' and 'txtPayAmount' txtDateEntry will just be set for Date(). When btnDateSelect is clicked, The end goal is for the code to automatically determine the next payday, whether it be the the next Monday or Wednesday, depending on what the current day is when it runs, put that date in txtPayDate, and then determine how much that pay will be based on the entries in wage, DeliveryDate, TipDate and TipAmount. Obviously it will only be calculating based on entries that were made after the last paydate. For instance, if its a thursday and I want to go in and check what I should be getting paid the following monday, then the code will look at all DeliveryDate and TipDate that fell on Tu, We, and Th, since the Sunday and Monday jobs and received tips were paid out the day before, that Wednesday, add the amounts together in Wage and TipAmount and return a value into TxtPayAmount of exactly what I should be expecting paid out to me whether the next pay date is Monday or Wedneday from when I click the button.

Maybe its too much to figure out, not sure if its even possible, that's why I'm coming to the experts for help. My guess it would require someone with a lot of knowledge and time on their hands. Or maybe its something really simple. I'm hoping for the latter. Anyhow, thank you for any assitance in advance for helping me or pointing me in the right direction.

 

[bob fitz]

Perhaps you could use Select Case or If/Then with Weekday() function to determine what day today is and DateAdd() to calculate the pay date.

 

[plog]

Words often get in the way of ideas on this forum. I suggest you demonstrate your issue with data. Show us what data you will be inputting and then show us what you expect calculated.

 

[moke123]

As Bob pointed out you could use select case to add the number of days to either monday or wednesday

Private Function CalcPayDate(dte As Date) As Date

    Select Case Weekday(dte)

    Case 1    'Sunday
        CalcPayDate = DateAdd("d", 3, dte)    '3 days to Wed

    Case 2    'Monday
        CalcPayDate = DateAdd("d", 2, dte)    '2 days to Wed

    Case 3    'Tuesday
        CalcPayDate = DateAdd("d", 6, dte)    '6 days to Mon

    Case 4    'Wednesday
        CalcPayDate = DateAdd("d", 5, dte)    '5 days to Mon

    Case 5    'Thursday
        CalcPayDate = DateAdd("d", 4, dte)    '4 days to Mon

    Case 6    'Friday
        CalcPayDate = DateAdd("d", 3, dte)    '3 days to Mon

    Case 7    'Saturday
        CalcPayDate = DateAdd("d", 2, dte)    '2 days to Mon

    End Select

End Function

By default the weekday function returns 1 for a sunday, 2 for monday . . . 7 for saturday