Converting MS Excel Database to Access

[waholtmn]

I've nearly completed a conversion from an Excel database to an Access one as I've got only one more field to convert. However, this one seems to be the trickiest to figure out.

I cannot convert this Excel expression to one that works in Access. Please help.

Excel
=IF(C1>0,IF(OR(A1<>0,B1<>0)=TRUE,"Answer A","Answer B"),"Answer C")

Access
IIf(Field 3>0,IIf(Field 1<>"" Or Field 2<>""=True,"Answer A","Answer B"),"Answer C")


The Access expression is very close but I am getting a few incorrect responses.

Any help is appreciated!

 

[Beetle]

What's the data type?

In your Excel expression you're comparing A1 and B1 to 0. In your Access expression you're comparing Field1 and Field2 to an empty string ("").

Also, if the any of the fields can be Null, you'll need to wrap in the Nz function. Example;

IIf(Nz([Field 3], 0)>0,IIf(Nz([Field 1], 0)<>0 Or Nz([Field 2], 0)<>,"Answer A","Answer B"),"Answer C")

 

[plog]

This portion of your code looks weird:

Field 2<>""=True

The =True portion is unecessary. Now if you are getting errors other than that my guess would be that the data in your fields isn't the type you expect. Based on your comparisons, Field 3 should be numeric and Field 1 and Field 2 should contain text.

If that doesn't help post some sample input data and what you expect the formula to return along with what it is actually returning. Also a brief description in english of how the formula should function.

 

[waholtmn]

Thanks much Beetle, I'll give that a try.

Here are some more details. Field 3 is a number and fields 1 & 2 are both text.

The expression in Excel does this.

If field 3 is greater than zero then if field 1 has text or field 2 has text write "Answer A"; if field three is greater than zero and both field 1 and field 2 are blank "" then "Answer B"; if all fields are blank (either "" or null) then "Answer C"

 

[Beetle]

It wasn't that I didn't understand the Excel expression, I was just questioning why you were comparing to 0 in one case and to an empty string in the other.

 

[plog]

According to your explanation, this is the formula you want:

=IIF([Field 3]>0, IIF(IsNull([Field 1])=False OR IsNull([Field 2])=False, "Answer A", "Answer B"), "Answer C")

 

[waholtmn]

Beetle --

The Nz function didn't solve my predicament. Let me give some more information to see if a solution can be found.

Proposed Solution:
IIf(Nz([Field 3], 0)>0,IIf(Nz([Field 1], 0)<>0 Or Nz([Field 2], 0)<>,"Answer A","Answer B"),"Answer C")

Changed to accommodate the text fields 1 & 2:

IIf(Nz([Field 3], 0)>0,IIf(Nz([Field 1], 0)<>"" Or Nz([Field 2], 0)<>"","Answer A","Answer B"),"Answer C")

Field 3 will either house a number or a null value. Fields 1 and 2 will either have text values or an empty string.

I am not getting "Answer B" at all when both fields 1 and 2 are empty strings and the field 3 is greater than zero as "Answer A" is being given instead. Also, "Answer C" is being given for a few instances where "Answer A" should be seen but not all, this is where field 3 is Null but field 1 or field 2 has text values. However, if Field 3 is greater than zero and either field 1 or field 2 have text values then "Answer A" is given which is correct and there are no deviations from this result.

Basically I need this...

If field 1 or field 2 have text strings then write "Answer A". If field 1 and field 2 are empty strings and field 3 is greater than zero then write "Answer B". If all fields are either null or empty strings then write "Answer C".

I think the resolution is close but not quite there. I do not know VBA or SQL so an expression is what I am looking for.

 

[waholtmn]

PLOG -- I validated the results more closely and your solution is what I needed. Much thanks!!!