CONVERT Function

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

CONVERT Function

Post by 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

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: CONVERT Function

Post by silverback »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: CONVERT Function

Post by 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
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: CONVERT Function

Post by HansV »

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

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: CONVERT Function

Post by silverback »

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

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: CONVERT Function

Post by 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. :scratch: 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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: CONVERT Function

Post by 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.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: CONVERT Function

Post by HansV »

It follows from my previous reply that you could also use

cc2cup = cc / 236.588
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: CONVERT Function

Post by Leesha »

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

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: CONVERT Function

Post by SpeakEasy »

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