Results 1 to 9 of 9
  1. #1
    Gregm66 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Posts
    36

    Convert function

    Hi everyone,



    Is it possible in access to use the convert function?

    I want to convert Liquid Measurements and Temperatures, using their abrevetion IE Celsius = cel. Cup= cup

    I have 2 combo boxes each one populated by a table called tblConversion.
    2 text boxes that are linked to each combo these show the measurement abreveation. IE: cel

    a Third text box to type in an amount. IE: 2

    a fourth text box that I want to show the converted amount.
    So the convert would have to be converting,Textbox1 and Textbox2 with the Value in Textbox3

    I hope this is easy to understand.
    Any help with abit of code to do this would be appreciated.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Is it possible in access to use the convert function?
    AFAIK, there is no such thing - at least not in the manner in which you're referring to. There are conversion functions, but they are solely for converting data types, e.g. numbers to strings, strings to numbers, integers to longs, etc.
    This makes no sense to me: "So the convert would have to be converting,Textbox1 and Textbox2 with the Value in Textbox3"
    If 1 and 2 are the short version descriptions for a measurement, there is nothing to convert. Nor would I convert the input value in #3 and lose what was there - rather I'd provide the converted value in the textbox I want to show it in (4th) based on what was entered into 3rd.
    The 4th could be a bound textbox that could get the necessary formula from a table based on the combo choices, or it could be unbound and you'd programmatically apply the formula. OR in code you could write a whole lot of statements in a Select Case block and again, update the textbox via code. Personally, I'd go with the bound control since one of the main ideas of database design is to minimize code changes when options for an existing feature are added, plus there's no need to write code to control the contents of a textbox when a query can do it. All you'd have to do is add table rows as you expand the conversion feature.

    When user clicks your convert button, I'd use a select query that finds the formula in the table where the cmbTo and cmbFrom choices (combos To and From) match the from and to values in the related table fields (I would not use those reserved words). The formula for converting from Celsius to Fahrenheit would look like cmbFrom*9/5+32 and txtResults would be bound to a query that returns the formula. Requery the result control as part of the process and the value should show up IF the formula uses the names of the form controls (cmbFrom or cmbTo or whatever you call them). The table might look like
    FromUnits ToUnits Formula
    Celsius Fahrenheit cmbFrom*9/5+32
    Fahrenheit Kelvin (cmbFrom + 459.67) × 5/9

    Edit: not sure I agree with extra textboxes just to show short forms. If those need to be shown only during choosing, they could be part of the combo (a second column). If always, labels might make a better choice. I guess it's a matter of design preference.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Gregm66 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Posts
    36
    Hi Micron and thankyou for your reply.

    So if I have this right I add to my current table called tblConversions is this correct?

    So my table would look like the following:
    recipeDB.zip
    ConvertID.............Convert...........Abreviatio n.........FromUnits................ToUnits........ ....Formula
    1 .........................Celsius............cel ...................Celsius.................... Fahrenheit...... cmbFrom*9/5+32


    would that be correct?

  4. #4
    Gregm66 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Posts
    36
    Hi Micron please see attached file in my post called recipeDB.zip
    at line 4

    it might give you an idea on what I'm trying to do.
    Please PM Me for user name and password

  5. #5
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not quite. Abbreviations don't belong in tblFormulas IMO. Look at your example - who's to say that C (cel??) belongs to the value in field 2 and not 5? For that matter, where is the sf for Farenheit? You should have a tblUnits if you want short forms, in which case I'd modify my original suggestion and use the PK values from tblUnits instead of unit names:
    tblUnits
    UnitID UnitName Abbrv.
    1 Celsius C
    2 Fahrenheit F
    3 Kelvin K
    4 gram g
    5 kilometer km

    tblFormulas
    FromUnits ToUnits Formula
    1 2 cmbFrom*9/5+32
    2 3 (cmbFrom + 459.67) × 5/9
    If the logic of this escapes you, you need to read up on normalization.
    The combos should then be 2 column (or 3 if that's where you want to show the sf), column 1 being bound, getting data as
    SELECT * FROM tblUnits for the 1st combo. The second combo should be cascading (its list depending on the choice in the first so that the same value cannot be chosen) such as SELECT * FROM tblUnits WHERE tblUnits.ToUnits <> Me.cmbFromUnits OR you code to trap that the user has selected the same values twice. Again, IMO smart designers avoid code where simple design will suffice. HOWEVER, I just noticed a faux pas in my first response. A calculated control cannot also be bound but it can retrieve your formula using DLookup, which IIRC, can then be evaluated; e.g. DLookup("Formula", "tblFormulas", "FromUnits = Me.cmbFrom AND ToUnits = Me.cmbTo"). Hopefully I am close on that, and if you know about DLookups with criteria, it is a good reason to use id numbers, not unit names as I'm saying. If I'm wrong about how to get the value returned to the textbox, there's always code

    Just notice your reference to the other thread. I'll check it out.
    BTW, I did not use the reserved word "Convert" in my reply. Suggest you bookmark this reference http://allenbrowne.com/AppIssueBadWord.html#C

    P.S. would not surprise me if someone has not already created an add-in that you can use.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My apologies - getting late. The formula should reference the textbox that holds the value to be converted - not the combo box.
    DUH!
    I'm having a bad day - you cannot use Me as a reference in a sql statement like that. It would have to be Forms!frmName.ControlName

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK, by looking at your db, I see that you wish to incorporate this function into a form that has a record source having nothing to do with conversions whereas I thought you were starting from scratch. I've modified your form and played with the tables but didn't remove any. It's not robust enough to prevent anyone from trying to convert temperature to ounces. For that, you'd need a sort of "measurement type" field if you want to put the work into it, so that inappropriate values in the 2nd combo don't show. For now, I've got it so that such an attempt will not find a conversion formula, thus it will provide a message. There are also other checks that I mentioned before. A couple of things you had in code have been commented out because there was no such control or whatever the issue was. You should always compile your db before posting it. Natch, your image paths produced an error that I ignored. Check it out and see what you think. You should keep a copy of your original db to fall back on. I did a compact/repair; the db seems large given what I see in it.
    recipeDB.zip
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Gregm66 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Posts
    36
    Hi Micron,

    Thanks for all of your help, I see what you mean with the table that you added, I am working on updating that table with all formulas to convert with.

    although I just tried to add kilograms to the conversion table and I can not see that now in the combo boxes, any ideas why. I saved my DB shut it down and opened it again and still I can not see kilograms.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Likely you are adding it to the wrong table. You will have to either emulate my approach using your own tables, thus linking the controls to those tables, or use the ones I created. IIRC, I did not use your conversions table.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-18-2015, 09:17 AM
  2. Replies: 8
    Last Post: 11-04-2014, 10:44 PM
  3. Replies: 26
    Last Post: 07-31-2014, 11:04 AM
  4. CDate function to convert from dd.mm.yyyy format
    By GregTheSquarePeg in forum Queries
    Replies: 7
    Last Post: 10-11-2013, 07:11 AM
  5. Replies: 9
    Last Post: 12-20-2010, 08:05 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