Access formulas

[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,

 

[Al Campagna]

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."

 

[Steve Schapel]

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)