How to convert Excel 2010 If statement into Access 2010?

[wendikunz]

Hello,
Thank you for your time and assistance.
I am building a database based from a daily Excel sheet. I'm working on Windows 7 and Excel and Access 2010.
One of the cells has an extensive If formula that I don't know how to convert into Access.
I have changed the cell references to text box names, i.e. [FEDD] and [Weekday]. But I get a "#Name?" error.
What am I missing?

=IF([FEDD]="",””,(-0.3434*[FEDD]^3)+(42.041*[FEDD]^2)+(1131.8*[FEDD])+(([FYEDD]-[FEDD])*600)+(0.14*[Customers])+IF([Weekday]="Monday",500)+IF([Weekday]="Tuesday",500)+IF([Weekday]="Wednesday",500)+IF([Weekday]="Thursday",500)-IF([Weekday]="Friday",250)-IF([Weekday]="Saturday",250)-IF([Weekday]="Sunday",250))

 

[Micron]

The error you're getting generally means Access cannot resolve a name you have used for an object or field reference. Three things jump out to me. First is that the Access equivalent of the Excel IF statement is IIF. Another is that Weekday is a reserved Access word and you should not use these words for object or field names. The other is that the Access Weekday function returns a number, so it cannot be "Tuesday" unless you nest within your expression a function to return the text day value. Check out 'reserved Access words' to get a list.

 

[HiTechCoach]

Hello,
Thank you for your time and assistance.
I am building a database based from a daily Excel sheet. I'm working on Windows 7 and Excel and Access 2010.
One of the cells has an extensive If formula that I don't know how to convert into Access.
I have changed the cell references to text box names, i.e. [FEDD] and [Weekday]. But I get a "#Name?" error.
What am I missing?

=IF([FEDD]="",””,(-0.3434*[FEDD]^3)+(42.041*[FEDD]^2)+(1131.8*[FEDD])+(([FYEDD]-[FEDD])*600)+(0.14*[Customers])+IF([Weekday]="Monday",500)+IF([Weekday]="Tuesday",500)+IF([Weekday]="Wednesday",500)+IF([Weekday]="Thursday",500)-IF([Weekday]="Friday",250)-IF([Weekday]="Saturday",250)-IF([Weekday]="Sunday",250))

Welcome to the world of database programming!

Are you using this as the control source for a textbox on a form/report or in a query?

What you are trying to do might work in Excel but it really is not a good way with a database, including Access.


TIP: Avoid hard coding any values. Use lookup tables.

For example:

IF([Weekday]="Wednesday",500)+IF([Weekday]="Thursday",500)-IF([Weekday]="Friday",250)-IF([Weekday]="Saturday",250)-IF([Weekday]="Sunday",250))

This is hard coding values and should be avoid. Even in Excel the values for each weekday should be a cell reference.

I would expect that you have a lookup table that has a record for each weekday and the value. Do you have that already?

Getting your tables all build will give you a good foundation to build on. It will also make things a lot easier.