Solved

Need help with some VBA Code in Access 2013

Posted on 2014-11-20
22
226 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 38

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 38

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 38

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 38

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 38

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 38

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 38

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 38

Accepted Solution

by:
PatHartman earned 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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: …

626 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