Conditional formatting with dates.

[fakiane]

I'm currently making a stock control database, within the 'Supplier' table I have the days that they can deliver, eg, Monday, Friday etc, which is in text format.
Is there any way I can make another field in the table which is date/time format that corresponds to the next available delivery date? eg if it was Monday then the field would correspond and would say 23/01/2017. This would be easier for me as it would allow me to do conditional formatting on the order form so if the supplier couldn't deliver on a certain day it would be highlighted.

Thanks

 

[static]

Add this to a standard (not form) module.

Public Function GetNextDate(day As String, Optional dt As Date) As String
    Dim i As VbDayOfWeek
    If dt = 0 Then dt = Date
    Select Case LCase(day)
    Case "sunday": i = VbDayOfWeek.vbSunday
    Case "monday": i = VbDayOfWeek.vbMonday
    Case "tuesday": i = VbDayOfWeek.vbTuesday
    Case "wednesday": i = VbDayOfWeek.vbWednesday
    Case "thursday": i = VbDayOfWeek.vbThursday
    Case "friday": i = VbDayOfWeek.vbFriday
    Case "saturday": i = VbDayOfWeek.vbSaturday
    End Select
    If i Then
        GetNextDate = DateAdd("d", 8 - Weekday(dt, i), dt)
    Else
        GetNextDate = "Day not recognised"
    End If
End Function

Base a query on the table and create a calculated field

NextDate: GetNextDate([dayname])

Replace dayname with your field name.

 

[fakiane]

Add this to a standard (not form) module.

Public Function GetNextDate(day As String, Optional dt As Date) As String
    Dim i As VbDayOfWeek
    If dt = 0 Then dt = Date
    Select Case LCase(day)
    Case "sunday": i = VbDayOfWeek.vbSunday
    Case "monday": i = VbDayOfWeek.vbMonday
    Case "tuesday": i = VbDayOfWeek.vbTuesday
    Case "wednesday": i = VbDayOfWeek.vbWednesday
    Case "thursday": i = VbDayOfWeek.vbThursday
    Case "friday": i = VbDayOfWeek.vbFriday
    Case "saturday": i = VbDayOfWeek.vbSaturday
    End Select
    If i Then
        GetNextDate = DateAdd("d", 8 - Weekday(dt, i), dt)
    Else
        GetNextDate = "Day not recognised"
    End If
End Function

Base a query on the table and create a calculated field

NextDate: GetNextDate([dayname])

Replace dayname with your field name.

how do I make a standard module

 

[Sweetu]

how do I make a standard module

On create check out for code/module then copy/paste above code to VBE...name it and call however you need

 

[sneuberg]

how do I make a standard module

1. Go to the VBE editor by clicking on Visual Basic in the CREATE tab or Alt F11 will get you there in some versions.
2. In the Menu along the top click Insert and then Module
3. The module will be given a name like Module1 but you will be prompted to give it a name when it's save or you can rename it later in the object view.
4. When you name the module make sure its name is not the same as any other public procedure, e.g. "GetNextDate" would be a very bad choice for the module name.