CONVERT Function

[Leesha]

Hi,
Is there a function in Access to convert measurements from 1 type to another? I have total cc's for ingredients in recipes. The user needs these converted to total cups, quarts, gallons etc. I saw that there is a CONVERT funtion for Excel but didn't find anything like it for Access.
Thanks!
Leesha

[silverback]

I found this but whether it's available in Access I don't know.
Silverback

[HansV]

No, Access does not have something similar. You could create your own conversion functions in VBA, for example:

Code: Select all

Function cc2cup(cc)
    If IsNull(cc) Then
        cc2cup = Null
    Else
        cc2cup = 0.00422675 * cc
    End If
End Function

Function cc2gallon(cc)
    If IsNull(cc) Then
        cc2gallon = Null
    Else
        cc2gallon = 0.000264172 * cc
    End If
End Function

Function cc2quart(cc)
    If IsNull(cc) Then
        cc2quart = Null
    Else
        cc2quart = 0.00105669 * cc
    End If
End Function

Function cc2tbsp(cc)
    If IsNull(cc) Then
        cc2tbsp = Null
    Else
        cc2tbsp = 0.067628 * cc
    End If
End Function

Function cc2tsp(cc)
    If IsNull(cc) Then
        cc2tsp = Null
    Else
        cc2tsp = 0.202884 * cc
    End If
End Function

[HansV]

@silverback: SQL Server's CONVERT function converts data types (date, number, currency) but not units of measurement.

[silverback]

Hans/Leesha. My apologies. That will teach me to read things fully.
Silverback

[Leesha]

Hi!

Thanks so much! I had looked at stuff similar to what Silverback posted but as you mentioned could fine nothing re units of measure. (Thanks anyway Silverback) When I came across the CONVERT code for excel I got so excited and when it didn't work I thought "Hans will know".

This will work great. I have a question. What is the rationale behind the math in the VBA? You know me, I like to understand what I'm doing vs just copy and paste. For example, I have that 1 cup = 236.588 cc. The sample code has cc2cup = 0.00422675 * cc. How did you arrive at the number? I'm sure it's probably a no brainer by my very tired brain isn't grasping it this morning as I was up until 2 AM trying to get this on my own and finally gave up and conceded defeat.
Thanks,
Leesha

[HansV]

It's simply the inverse: 0.00422675 = 1/236.588
I found the values by searching for convert cc to cups etc. in Google.

[HansV]

It follows from my previous reply that you could also use

cc2cup = cc / 236.588

[Leesha]

I missed the division piece in the previous reply. Thanks again! I am so appreciative!!

[SpeakEasy]

If you have Excel, then you can use Excel worksheet functions (such as CONVERT) in Access.