Adding Week days only in a calculated field, based on a value of another field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a query or a form, I want to add only week days to a date field based on the value of another field. Simaliar to this: IIF([complaintType]="Quality",[FollowUp]+7weekdays,else,10) Obviously my syntax is wrong. I have tried DateAdd "W", DateSerial,Year Month,day.
 
Try to use functions from my example:
Add new module to your database. Copy my functions and
paste it in new module. Test it!

Sub AddDay() 'test procedure
MsgBox AddWeekDay(Date + 1) 'Add first WeekDay
End Sub

Function AddWeekDay(mday As Date) As Date
Dim tmpDate As Date

tmpDate = mday 'set temporary date
Do While Not IsWeekDay(tmpDate) 'test for weekday
tmpDate = DateAdd("d", 1, tmpDate) 'add 1 to date...
Loop 'and test it again

AddWeekDay = tmpDate 'yet tmpDate = first week day

End Function

'test for week day
'true: tested date is week day
'false: tested date is NOT week day

Private Function IsWeekDay(mday As Date) As Boolean
Select Case WeekDay(mday)
Case vbMonday To vbFriday
IsWeekDay = True
Case Else
IsWeekDay = False
End Select
End Function

You can use AddWeekDay([fieldWithDate]) function in your
forms or queries.
Form: FieldNextWeekDay.DefaultValue = AddWeekDay
([fieldWithDate])
Query:
SELECT Query1.FieldWithDate, AddWeekDay([Query1].
[FieldWithDate]) AS FieldNextWeekDay
FROM Query1;
-----Original Message-----
In a query or a form, I want to add only week days to a
Click to expand...
date field based on the value of another field. Simaliar
to this: IIF([complaintType]="Quality",[FollowUp]
+7weekdays,else,10) Obviously my syntax is wrong. I have
tried DateAdd "W", DateSerial,Year Month,day.
 
Do something like this

Function MyNextWeekDay(aDate as Date) as Date
Select case WeekDay(aDate)
case vbFriday
aDate = aDate +3
case vbSaturDay
aDate= aDate +2
case else
aDate = aDate+1
End Select
End Function

--
Kindest regards
HC Francke
www.master-office.com

losmac said:
Try to use functions from my example:
Add new module to your database. Copy my functions and
paste it in new module. Test it!

Sub AddDay() 'test procedure
MsgBox AddWeekDay(Date + 1) 'Add first WeekDay
End Sub

Function AddWeekDay(mday As Date) As Date
Dim tmpDate As Date

tmpDate = mday 'set temporary date
Do While Not IsWeekDay(tmpDate) 'test for weekday
tmpDate = DateAdd("d", 1, tmpDate) 'add 1 to date...
Loop 'and test it again

AddWeekDay = tmpDate 'yet tmpDate = first week day

End Function

'test for week day
'true: tested date is week day
'false: tested date is NOT week day

Private Function IsWeekDay(mday As Date) As Boolean
Select Case WeekDay(mday)
Case vbMonday To vbFriday
IsWeekDay = True
Case Else
IsWeekDay = False
End Select
End Function

You can use AddWeekDay([fieldWithDate]) function in your
forms or queries.
Form: FieldNextWeekDay.DefaultValue = AddWeekDay
([fieldWithDate])
Query:
SELECT Query1.FieldWithDate, AddWeekDay([Query1].
[FieldWithDate]) AS FieldNextWeekDay
FROM Query1;
-----Original Message-----
In a query or a form, I want to add only week days to a
Click to expand...
date field based on the value of another field. Simaliar
to this: IIF([complaintType]="Quality",[FollowUp]
+7weekdays,else,10) Obviously my syntax is wrong. I have
tried DateAdd "W", DateSerial,Year Month,day.
.
Click to expand...
Click to expand...
 
Back
Top