Access formulas

  • Thread starter Thread starter Isa
  • Start date Start date
I

Isa

Hi,

I am trying to reproduce an excel formula in an Access query.

I have a figure in cell A1:
20/2

20 is the number of weeks and 2 is the number of days

I want to convert that field in order to calculate the total number of days.
My formula in excel is:

=LEFT(A1,SEARCH("/",A1)-1))*7)

Converts number of weeks into days.

Do you know if and what is the equivalent formula to use in an access query?

Thank you.

Regards,
 
Isa,
Check out String Manipulation in Access Help (Left, Right, and Mid)

Given a text control named WDays with a value of 20/2...
(all on one line)

= (Left([WDays], InStr([Wdays], "/")-1) * 7) + Mid([WDays], InStr([WDays],
"/") + 1)

will yield 142.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Isa,

Val([NameOfField])*7

That will do what you specifically asked, and will return 140 in the example
you gave. However, I notice you do not take account of the additional 2
days. If, indeed, you want the answer to be 142 instead, then try this:
Val([NameOfField])*7+Right([NameOfField],1)
 
Back
Top