Need help with some VBA Code in Access 2013
I have a database that and I have a macro to calculate elapsed time. Not and easy thing in Access. The Macro I created was working fine but I needed to expand it beyond the 255 character limit. I used access to convert the macro but it isn't working. I get a type mismatch when I try to run the code.
Macro to setproperty value.
MACRO Value: =((IIf([PressFinish]>[Pres
VBA Code: DoCmd.SetProperty "ManHrs", , ((IIf(PressFinish > PressMakeReady, PressFinish - PressMakeReady, (PressFinish + 0.5) - PressMakeReady) * 1440) \ 60) & Format((IIf(PressFinish > PressMakeReady, PressFinish - PressMakeReady, (PressFinish + 0.5) - PressMakeReady) * 1440) Mod 60, "\:00")
End Sub
Macro to setproperty value.
MACRO Value: =((IIf([PressFinish]>[Pres
VBA Code: DoCmd.SetProperty "ManHrs", , ((IIf(PressFinish > PressMakeReady, PressFinish - PressMakeReady, (PressFinish + 0.5) - PressMakeReady) * 1440) \ 60) & Format((IIf(PressFinish > PressMakeReady, PressFinish - PressMakeReady, (PressFinish + 0.5) - PressMakeReady) * 1440) Mod 60, "\:00")
End Sub
ASKER
I using the set Property Value on an elapsed time field. I couldn't get DateDiff to work without much more coding because I only delivers one value. Either Day's, Hours, Minutes. I need hours and minutes. I have used set property in several other places without problems but now I getting a type mismatch.
See this MSDN article on SetProperty:
http://msdn.microsoft.com/en-us/library/office/ff192301(v=office.15).aspx
I don't think you're using it correctly. If your goal is to set the VALUE of your ManHrs to the result of your calculation, then you'd do this:
Me.ManHrs = "Your Calc Here"
http://msdn.microsoft.com/en-us/library/office/ff192301(v=office.15).aspx
I don't think you're using it correctly. If your goal is to set the VALUE of your ManHrs to the result of your calculation, then you'd do this:
Me.ManHrs = "Your Calc Here"
I couldn't get DateDiff to work without much more coding because I only delivers one value.True, but it's much more reliable when working with DateTime values in Access. And delivering a single value is not a big deal - you'd just have to do the same sort of divisions and such as you're doing now, except that you can assured that the initial calcs (i.e. the subtraction and such) are done correctly. Using your methods, you may or may not start out with valid data.
Dim iMinutes as Integer
Dim iRemainingMinutes as Integer
Dim iHours as Integer
iMinutes= DateDiff("n", [PressFinish], [PressMakeReady])
iHours = iMinutes / 60
iRemainingMinutes = iMinutes mod /60
Me.ManHours = iHours & ":" & Format(iRemainingMinutes, "00")ASKER
That looks much better to me than what I'm using but I get a syntax error on this line when I run the code.
iRemainingMinutes = iMinutes mod /60
Marshall
iRemainingMinutes = iMinutes mod /60
Marshall
ASKER
Sorry,
The error is on this line, iRemainingMinutes = iMinutes mod /60. Not the one above it.
The error message actually says compile error syntax error.
Marshall
The error is on this line, iRemainingMinutes = iMinutes mod /60. Not the one above it.
The error message actually says compile error syntax error.
Marshall
I believe that should be:
iRemainingMinutes = iMinutes MOD 60
iRemainingMinutes = iMinutes MOD 60
Thanks, I got carried away with the //////////////////////////
ASKER
I've gotten the code corrected but now I get another error. Type Mismatch. What should the table be set to for this field. I've tried short date, and Short text but I still get the error.
Man hours is a string so the field should be short text. Short Date is a format not a data type. Don't confuse how a field is formatted with how it is stored in the table.
ASKER
I'm not sure what I could be doing wrong, I've entered the code exactly as shown and I continue to get a type mismatch error. The filed in the table is set to short text and the format is Short Time.
You can't format this text field. The ":" is already embedded in the string. Remove the format.
Please post your version of the code and tell us which line is failing.
Please post your version of the code and tell us which line is failing.
ASKER
The Error is Runtime Error 13, The text says Type Mismatch.
The line that begins with Me is the highlighted line in the debug screen.
Private Sub Command136_Click()
Dim iMinutes As Integer
Dim iRemainingMinutes As Integer
Dim iHours As Integer
iMinutes = DateDiff("n", [PressMakeReady], [PressFinish])
iHours = iMinutes / 60
iRemainingMinutes = iMinutes Mod 60
Me.ElapsedTime = iHours & ":" & Format(iRemainingMinutes, "00")
End Sub
The line that begins with Me is the highlighted line in the debug screen.
Private Sub Command136_Click()
Dim iMinutes As Integer
Dim iRemainingMinutes As Integer
Dim iHours As Integer
iMinutes = DateDiff("n", [PressMakeReady], [PressFinish])
iHours = iMinutes / 60
iRemainingMinutes = iMinutes Mod 60
Me.ElapsedTime = iHours & ":" & Format(iRemainingMinutes, "00")
End Sub
Did you remove the formatting for the Elapsed time control?
ASKER
I removed the formatting and when I do it works without any errors but if the iRemainingminutes is a single digit that is all I get in the output. For example I get 1:9 vs 1:09.
The Format(...) should be making the minutes field two digits.
ASKER
That's what I thought but the format causes the type mismatch error.
ASKER
If I try to set the format on the field itself it has no effect.
The format I told you to remove was the format that was defined on the table or the control. You NEED the Format() FUNCTION to get the leading zero.
ASKER
I removed both and added them back one at a time. When I removed all formatting it works with no errors. However for 9 minutes or less I get a single digit. For example 1:9 vs. 1.09. Adding formatting in the table didn't make any difference. I removed the formatting from the table and added it to the code and I get the type mismatch error every time.
The variables in the code are being declared at integers. But the field in the table is short text. Do I not have to convert the integer to text? Could that be why I'm getting the type mismatch error?
The variables in the code are being declared at integers. But the field in the table is short text. Do I not have to convert the integer to text? Could that be why I'm getting the type mismatch error?
ASKER
Another problem I noticed in using DateDiff the way you have specified is that if the time crossed midnight the elapsed times are wrong.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
FWIW: Elapsed time in VBA is best handled with the DateDiff methods. If you have two DateTime values, you can get the difference between those two like this:
MsgBox DateDiff("m", Date1, Date2)