I want to change 5'5", 5'10", etc to the equivalent inches in another column using SetValue. I've looked for answer but have had no luck. Is it possible?
I want to change 5'5", 5'10", etc to the equivalent inches in another column using SetValue. I've looked for answer but have had no luck. Is it possible?
As long as the db is in a trusted location, the record/field is updatable and the field will accept the type of data you want to put in, it shouldn't be a problem. Your question reads like you're asking if it's possible, but not how to do it so I haven't elaborated. To be honest, I'm not a big macro user - many of us here aren't but we do our best.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
You are right. I wasn't clear. I know I can use SetValue but my problem is the quote marks. I don't know how to get Access to accept heights like 5'5" or 5'10" because of the single and double quote marks used when measure heights. If I put the height in quotes it looks like this "5'10"" and Access doesn't like it.
An expression or user defined function will be needed. If the values you show are consistently formatted that way (e.g. always feet then apostrophe then inches) you might try
where strValue is wherever your values are coming from, but the value would have to look like 5'10 exactly. If it doesn't, I'll go back to the drawing board since I presume that expression won't handle anything else.Code:Val(strValue) * 12 + Mid(strValue, InStr(1, strValue, "'") + 1)
EDIT: in a few minutes, I found that adding four " onto your value has the effect of appending one ", so "5'10" becomes "5'10"" which is what you need (5'10" wrapped in double quotes). In which case, this expression works
if in code I set strValue = ("5'10"). Alternatively, one could set strvalue as strValue = ("5'10") & """" and go back to the first expression format.Code:Val(strValue & """") * 12 + Val(Mid(strValue & """", InStr(1, strValue & """", "'") + 1))
It's beginning to look like a function call from your macro would be cleaner.
Last edited by Micron; 04-10-2017 at 09:59 PM. Reason: removed questions & posted expressions
Here's a function if you care to try it out and can figure out how to pass the height value to it (maybe it's a form control reference or DLookup?). You might invoke it on your SetValue macro step as ConvertHeight(Forms!frmMyForm.txtMyTextbox), using your own form/control names of course. If you were to use the actual measurement, you'd make the call as ConvertHeight("5'10")
This function would have to be in a standard module (i.e. not part of a form's code).Code:Public Function ConvertHeight(strVal As String) As Long strVal = strVal & """" ConvertHeight = Val(strVal) * 12 + Val(Mid(strVal, InStr(1, strVal, "'") + 1)) End Function