Replies

Back again! Seem to be running into one little problem. If I try to change update/change only the manager field for an associate, I get a run-time error…unless I put in an InactiveDt. I can then update the records and go back remove the InactiveDt and update again. Any ideas why this is happening? The db is now in Access 97 format.

Thanks

Thanks again! It works beautifully, and with that I am calling it a night. I really appreciate all of the help that you give me.

nite

Hans,

Can that same If IsNull stmt be inserted in the code corrected code that you originally gave me?

Case “&Update”
sSQL = “UPDATE tblAssociateList ” & _
“SET username = ‘” & Me.txtOperator.Value & “‘, ” & _
“Manager='” & Me.cboManager.Value & “‘, ” & _
“AssocType='” & Me.cboAssocType.Value & “‘, ” & _
“ShiftHrs=” & Me.txtShiftHours.Value & “, ” & _
“ActiveDt=#” & Me.txtActiveDt.Value & “#, ” & _
“InActiveDt=#” & Me.txtInactiveDt.Value & “# ” & _
“WHERE username = ‘” & Me.lstOperators & “‘”

Had to fix those typos again. It is working correctly now. How much modification is needed to leave the InactiveDT field blank? the reason that I ask is because I have another simular form that has this same type of field (see code below) and this same field can remain blank and not cause any errors.

Select Case Me.cmdAction.Caption
Case “&Add”
sSQL = “INSERT INTO tblUserLog (UserID, UserName, NmFirst, NmLast, ActiveDt, AccessLevel, Password) VALUES (”
sSQL = sSQL & Me.txtUserID & “, ‘”
sSQL = sSQL & Me.txtUsername & “‘, ‘”
sSQL = sSQL & Me.txtFirstName & “‘, ‘”
sSQL = sSQL & Me.txtLastName & “‘, ‘”
sSQL = sSQL & Me.txtActiveDate & “‘, ”
sSQL = sSQL & Me.cboAccessLevel & “, ‘howitzer1’)”
Case “&Update”
sSQL = “UPDATE tblUserLog SET username = ‘” & Me.txtUsername & “‘, ”
sSQL = sSQL & “NmFirst = ‘” & Me.txtFirstName & “‘, ”
sSQL = sSQL & “NmLast = ‘” & Me.txtLastName & “‘, ”
sSQL = sSQL & “InActiveDt = ‘” & Me.txtInactiveDate & “‘, ”
sSQL = sSQL & “AccessLevel = ” & Me.cboAccessLevel
sSQL = sSQL & ” WHERE UserID = ” & Me.lstUsers
End Select

thanks!

Hans,

I am getting a Compile error, Method or data member not found. It seems to be hanging up on Me.txtShiftHours. Any ideas?

“VALUES (‘” & Me.txtOperator & “‘, ‘” & Me.cboManager & “‘, ‘” & Me.cboAssocType & _
“‘, ” & Me.txtShiftHours & “, #” & Me.txtActvieDt & “#, #” & Me.txtInactiveDt & “#)”

Thanks for finding that typo

Fabulous!!!! Thanks for letting me pick those brains

Hopefully this will be my last post of the day

I am using the curent formulas to determine the the BOM(Beginning of the month) =WORKDAY(EOMONTH(RDate,-1)+1,-1)
, and EOM(End of Month) =WORKDAY(EOMONTH(RDate,0)+1,-2)

This worked great until “RDate” became 04/29/05, which would be the first funding day of the next month. Is there a way to modify the formulas so that they will look at “RDate” and then calculate what the beginning and end of month would be?

Marie

That was the ticket!

Thanks again!!!

Just the average (not including *FDIM) should come to 54, and I am getting 36? Do you think that this has something to do with the blank cells?

Perfect! Thanks again!

You are good!!!

Thanks again!!

Sorry Hans!

It is K1

Hans,

Thanks so much for the help with this. Wondering if you could help with another calculation. I have in Cell I1, a calculation to provide me with a QTD total:

=IF(MONTH(Date)>=10,DATE(YEAR(Date),10,1),IF(MONTH(Date)>=7,DATE(YEAR(Date),7,1),IF(MONTH(Date)>=4,DATE(YEAR(Date),4,1),DATE(YEAR(Date),1,1))))

This worked well until I found out that funding days do not include the last working day of the current month, but do include the last working day of the previous month.

Is there a way that this QTD calculation can be modified to work in this manner?

Thanks again for any assistance that you can provide.

Marie

Ok, I have had some time to think about this. I had taken a some code from another post to help calculate workdays. When I looked at it for the one hundredth time, I finally saw were the code rounded down days to the previous workday instead of up to the next workday. I have made the modifecation and it works like it should. Modified code:

Public Function GetNextWorkday(ByVal StartDate As Date, _
ByVal lngInterval As Long) As Date

Dim lngWeeks As Long
Dim lngDays As Long

If lngInterval = 0 Then
GetNextWorkday = StartDate
ElseIf lngInterval > 0 Then
‘ Make sure StartDate is a workday (round up):
If Weekday(StartDate) = vbSunday Then
StartDate = StartDate + 1
ElseIf Weekday(StartDate) = vbSaturday Then
StartDate = StartDate + 2
End If

‘ Calculate lngWeeks and lngDays:
lngWeeks = lngInterval 5 ‘ Integer division operator
lngDays = lngInterval – (lngWeeks * 5) ‘ remainder
StartDate = StartDate + (lngWeeks * 7)
‘ Account for weekends:
If (DatePart(“w”, StartDate) + lngDays) > 6 Then
StartDate = StartDate + lngDays + 2
Else
StartDate = StartDate + lngDays
End If

Else ‘ lngInterval < 0
lngInterval = lngInterval * -1 ' Make positive & subtract later
' Make sure StartDate is a workday (round up):
If Weekday(StartDate) = vbSunday Then
StartDate = StartDate + 1
ElseIf Weekday(StartDate) = vbSaturday Then
StartDate = StartDate + 2
End If

lngWeeks = lngInterval 5
lngDays = lngInterval – (lngWeeks * 5)
StartDate = StartDate – (lngWeeks * 7)

If (DatePart("w", StartDate) – lngDays) < 2 Then
StartDate = StartDate – lngDays – 2
Else
StartDate = StartDate – lngDays
End If
End If

GetNextWorkday = StartDate

End Function

Hans,

I have looked over the functions in the link that you provided, and do not see a specific one that would fit my need.