Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help with some VBA Code in Access 2013

Posted on 2014-11-20
22
Medium Priority
?
229 Views
Last Modified: 2015-01-13
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
Comment
Question by:Mwvarner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 3
22 Comments
 
LVL 85
ID: 40455830
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
 

Author Comment

by:Mwvarner
ID: 40455875
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
 
LVL 85
ID: 40455900
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 Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 39

Expert Comment

by:PatHartman
ID: 40456045
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
 

Author Comment

by:Mwvarner
ID: 40458288
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
 

Author Comment

by:Mwvarner
ID: 40458295
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
 
LVL 85
ID: 40459287
I believe that should be:

iRemainingMinutes = iMinutes MOD 60
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 40462644
Thanks, I got carried away with the //////////////////////////////////'s
0
 

Author Comment

by:Mwvarner
ID: 40462861
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
 
LVL 39

Expert Comment

by:PatHartman
ID: 40462910
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
 

Author Comment

by:Mwvarner
ID: 40463030
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
 
LVL 39

Expert Comment

by:PatHartman
ID: 40463065
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
 

Author Comment

by:Mwvarner
ID: 40463299
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
 
LVL 39

Expert Comment

by:PatHartman
ID: 40464834
Did you remove the formatting for the Elapsed time control?
0
 

Author Comment

by:Mwvarner
ID: 40465098
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
 
LVL 39

Expert Comment

by:PatHartman
ID: 40465254
The Format(...) should be making the minutes field two digits.
0
 

Author Comment

by:Mwvarner
ID: 40465340
That's what I thought but the format causes the type mismatch error.
0
 

Author Comment

by:Mwvarner
ID: 40465342
If I try to set the format on the field itself it has no effect.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 40465376
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
 

Author Comment

by:Mwvarner
ID: 40465442
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
 

Author Comment

by:Mwvarner
ID: 40465446
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
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40465484
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question