• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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]>[PressMakeReady],[PressFinish]-[PressMakeReady],([PressFinish]+0.5)-[PressMakeReady])*1440)\60) & Format((IIf([PressFinish]>[PressMakeReady],[PressFinish]-[PressMakeReady],([PressFinish]+0.5)-[PressMakeReady])*1440) Mod 60,"\:00")

  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
0
Mwvarner
Asked:
Mwvarner
  • 11
  • 8
  • 3
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't use it, but I believe SetProperty sets a value like BackColor, ForeColor, etc. What exactly are you trying to set in Access with your forumula.

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)
0
 
MwvarnerAuthor Commented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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"

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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
PatHartmanCommented:
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")

Open in new window

0
 
MwvarnerAuthor Commented:
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
0
 
MwvarnerAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I believe that should be:

iRemainingMinutes = iMinutes MOD 60
0
 
PatHartmanCommented:
Thanks, I got carried away with the //////////////////////////////////'s
0
 
MwvarnerAuthor Commented:
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.
0
 
PatHartmanCommented:
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.
0
 
MwvarnerAuthor Commented:
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.
0
 
PatHartmanCommented:
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.
0
 
MwvarnerAuthor Commented:
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
0
 
PatHartmanCommented:
Did you remove the formatting for the Elapsed time control?
0
 
MwvarnerAuthor Commented:
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.
0
 
PatHartmanCommented:
The Format(...) should be making the minutes field two digits.
0
 
MwvarnerAuthor Commented:
That's what I thought but the format causes the type mismatch error.
0
 
MwvarnerAuthor Commented:
If I try to set the format on the field itself it has no effect.
0
 
PatHartmanCommented:
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.
0
 
MwvarnerAuthor Commented:
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?
0
 
MwvarnerAuthor Commented:
Another problem I noticed in using DateDiff the way you have specified is that if the time crossed midnight the elapsed times are wrong.
0
 
PatHartmanCommented:
Did you remove the formatting from the control?  You can't have ANY formatting of the control or the field in the table or in the query!!!!  The only formatting is done with the Format() function in code.

Why is the elapsed time wrong if the dates cross midnight?  Are you not including date in the date/time field?

Elapsed time is not a date/time data type.  It is a string as you have defined it so make sure the ElapsedTime field is defined as text.  Dates are NOT stored as strings.  They are stored as double precision numbers with the integer being the number of days elapsed since Dec 30, 1899.  And the decimal is time of day.  If you want to store Elapsed time for use in calculations (you shouldn't need to), then store it as minutes in a long integer.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 11
  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now