Solved

Need help with some VBA Code in Access 2013

Posted on 2014-11-20
22
219 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
  • 11
  • 8
  • 3
22 Comments
 
LVL 84
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 84
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
 
LVL 34

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 84
ID: 40459287
I believe that should be:

iRemainingMinutes = iMinutes MOD 60
0
 
LVL 34

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 34

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 34

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 34

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 34

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 34

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 34

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now