Results 1 to 5 of 5
  1. #1
    Ceedy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2017
    Posts
    2

    Changing height to inches in Macro

    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?

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  3. #3
    Ceedy is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2017
    Posts
    2
    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.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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
    Code:
    Val(strValue) * 12 + Mid(strValue, InStr(1, strValue, "'") + 1)
    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.

    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
    Code:
    Val(strValue & """") * 12 + Val(Mid(strValue & """", InStr(1, strValue & """", "'") + 1))
    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.
    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

  5. #5
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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")

    Code:
    Public Function ConvertHeight(strVal As String) As Long
    strVal = strVal & """"
    ConvertHeight = Val(strVal) * 12 + Val(Mid(strVal, InStr(1, strVal, "'") + 1))
    End Function
    This function would have to be in a standard module (i.e. not part of a form's code).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Displaying Height as Feet and Inches
    By beaverx37 in forum Forms
    Replies: 6
    Last Post: 11-12-2014, 12:12 PM
  2. Change Height of Box to height of textBox
    By oxicottin in forum Reports
    Replies: 7
    Last Post: 03-15-2013, 09:54 PM
  3. Replies: 4
    Last Post: 09-15-2012, 07:31 PM
  4. Replies: 0
    Last Post: 03-17-2009, 11:25 AM
  5. Replies: 0
    Last Post: 12-23-2008, 01:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums