Formula in Access

  • Thread starter Thread starter MichaelR
  • Start date Start date
M

MichaelR

Hi,

I've used Access before although not extensively. If I'm not mistaken, I
can't put formulas into cells in the same way as I can in Excel but I was
wondering if there was another way for me to accomplish the same thing.

Specifically, I get data that comes in on a monthly basis and I need to use
two of the columns to determine whether the sale was in the US, Canada or
Mexico. If it were in excel, I would use an IF statement nested in another IF
statement that would look like something like:
=IF(A2=7,"Canada",IF(P2="-","Mex","US"))

Any ideas for how I can do this in Access?

Thanks!
Michael
 
Hi,

I've used Access before although not extensively. If I'm not mistaken, I
can't put formulas into cells in the same way as I can in Excel but I was
wondering if there was another way for me to accomplish the same thing.

Specifically, I get data that comes in on a monthly basis and I need to use
two of the columns to determine whether the sale was in the US, Canada or
Mexico. If it were in excel, I would use an IF statement nested in another IF
statement that would look like something like:
=IF(A2=7,"Canada",IF(P2="-","Mex","US"))

Any ideas for how I can do this in Access?

Thanks!
Michael
Click to expand...

No problem. You just do NOT do this in a table.
Create a query. Add a new column.
SaleCountry:IIf([FieldName] = 7,"Canada",IIf([FieldName] =
"-","Mex","US"))

In a report or on a form, you would use an unbound control:
=IIf([FieldName] = 7,"Canada",IIf([FieldName] = "-","Mex","US"))

There are other methods as well. What ever method you use, there is no
need to save this value.
 
Hi Fred,

Thanks for your help. I tried to translate my formula into your format but
when I put it into the field row of my query it didn't work for some reason.

The statement looks like this: Geography: IIf([CSCO]=7 Or
[CSCO]=8,IIf([CSSTATE]="-","US","Mexico")).

Any ideas?

Thanks!
Michael

fredg said:
Hi,

I've used Access before although not extensively. If I'm not mistaken, I
can't put formulas into cells in the same way as I can in Excel but I was
wondering if there was another way for me to accomplish the same thing.

Specifically, I get data that comes in on a monthly basis and I need to use
two of the columns to determine whether the sale was in the US, Canada or
Mexico. If it were in excel, I would use an IF statement nested in another IF
statement that would look like something like:
=IF(A2=7,"Canada",IF(P2="-","Mex","US"))

Any ideas for how I can do this in Access?

Thanks!
Michael
Click to expand...

No problem. You just do NOT do this in a table.
Create a query. Add a new column.
SaleCountry:IIf([FieldName] = 7,"Canada",IIf([FieldName] =
"-","Mex","US"))

In a report or on a form, you would use an unbound control:
=IIf([FieldName] = 7,"Canada",IIf([FieldName] = "-","Mex","US"))

There are other methods as well. What ever method you use, there is no
need to save this value.
Click to expand...
 
Hi Fred,

Thanks for your help. I tried to translate my formula into your format but
when I put it into the field row of my query it didn't work for some reason.

The statement looks like this: Geography: IIf([CSCO]=7 Or
[CSCO]=8,IIf([CSSTATE]="-","US","Mexico")).

Any ideas?

Thanks!
Michael

fredg said:
Hi,

I've used Access before although not extensively. If I'm not mistaken, I
can't put formulas into cells in the same way as I can in Excel but I was
wondering if there was another way for me to accomplish the same thing.

Specifically, I get data that comes in on a monthly basis and I need to use
two of the columns to determine whether the sale was in the US, Canada or
Mexico. If it were in excel, I would use an IF statement nested in another IF
statement that would look like something like:
=IF(A2=7,"Canada",IF(P2="-","Mex","US"))

Any ideas for how I can do this in Access?

Thanks!
Michael
Click to expand...

No problem. You just do NOT do this in a table.
Create a query. Add a new column.
SaleCountry:IIf([FieldName] = 7,"Canada",IIf([FieldName] =
"-","Mex","US"))

In a report or on a form, you would use an unbound control:
=IIf([FieldName] = 7,"Canada",IIf([FieldName] = "-","Mex","US"))

There are other methods as well. What ever method you use, there is no
need to save this value.
Click to expand...
Click to expand...


You're not giving us enough useful information so I'll guess this is
the logic what you want.

either....
If ([CSCO] = 7 or 8) AND [CSSTATE] = "-" then "US" will be displayed,
otherwise "Mexico".

or perhaps you mean ....
If [CSCO] = 7 OR ([CSCO] = 8 and [CSSTATE] = "-") then "US" will be
displayed, otherwise "Mexico".

Note the different positions of the parenthesis.
Each will result in different values being returned.

In your query, using the first parenthesis grouping as an example,
write

Grography:
IIf(([CSCO] = 7 Or [CSCO] = 8) AND [CSSTATE] "-","US","Mexico")

Please try this before re-posting.
If the parenthesis do not return the correct values, try placing them
as in the second example.
If you still need help, give more details.
 
Fred,

Sorry for not having explained the relationship well enough. It goes as
follows:

If ([CSCO] = 7 or 8) is true then the geography is canada

If ([CSCO] = 7 or 8) is false then If [CSSTATE] = "-" then geography is
"US" otherwise, geography is mexico.

The problem is that when I put Geography: IIf(([CSCO]=7 Or [CSCO]=8) And
[CSSTATE]="-","US","Mexico") into the query, an error message pops up saying
that there is an extra ) in the query expression.

Any ideas? I'm sorry to keep bothering you. Thanks for all of your help so
far.

Michael


fredg said:
Hi Fred,

Thanks for your help. I tried to translate my formula into your format but
when I put it into the field row of my query it didn't work for some reason.

The statement looks like this: Geography: IIf([CSCO]=7 Or
[CSCO]=8,IIf([CSSTATE]="-","US","Mexico")).

Any ideas?

Thanks!
Michael

fredg said:
On Tue, 17 Jun 2008 14:51:01 -0700, MichaelR wrote:

Hi,

I've used Access before although not extensively. If I'm not mistaken, I
can't put formulas into cells in the same way as I can in Excel but I was
wondering if there was another way for me to accomplish the same thing.

Specifically, I get data that comes in on a monthly basis and I need to use
two of the columns to determine whether the sale was in the US, Canada or
Mexico. If it were in excel, I would use an IF statement nested in another IF
statement that would look like something like:
=IF(A2=7,"Canada",IF(P2="-","Mex","US"))

Any ideas for how I can do this in Access?

Thanks!
Michael

No problem. You just do NOT do this in a table.
Create a query. Add a new column.
SaleCountry:IIf([FieldName] = 7,"Canada",IIf([FieldName] =
"-","Mex","US"))

In a report or on a form, you would use an unbound control:
=IIf([FieldName] = 7,"Canada",IIf([FieldName] = "-","Mex","US"))

There are other methods as well. What ever method you use, there is no
need to save this value.
Click to expand...
Click to expand...


You're not giving us enough useful information so I'll guess this is
the logic what you want.

either....
If ([CSCO] = 7 or 8) AND [CSSTATE] = "-" then "US" will be displayed,
otherwise "Mexico".

or perhaps you mean ....
If [CSCO] = 7 OR ([CSCO] = 8 and [CSSTATE] = "-") then "US" will be
displayed, otherwise "Mexico".

Note the different positions of the parenthesis.
Each will result in different values being returned.

In your query, using the first parenthesis grouping as an example,
write

Grography:
IIf(([CSCO] = 7 Or [CSCO] = 8) AND [CSSTATE] "-","US","Mexico")

Please try this before re-posting.
If the parenthesis do not return the correct values, try placing them
as in the second example.
If you still need help, give more details.
Click to expand...
 
Fred,

Sorry for not having explained the relationship well enough. It goes as
follows:

If ([CSCO] = 7 or 8) is true then the geography is canada

If ([CSCO] = 7 or 8) is false then If [CSSTATE] = "-" then geography is
"US" otherwise, geography is mexico.

The problem is that when I put Geography: IIf(([CSCO]=7 Or [CSCO]=8) And
[CSSTATE]="-","US","Mexico") into the query, an error message pops up saying
that there is an extra ) in the query expression.

Any ideas? I'm sorry to keep bothering you. Thanks for all of your help so
far.

Michael
Click to expand...

*** snipped ***

OK. Let's try again.

Logically....
If ([CSCO] = 7 or 8) "canada",If it's not 7 or 8 AND [CSState] = "-",
"US", otherwise "Mexico"

Geography:IIf([CSCO] = 7 or [CSCO] = 8, "Canada",IIf([CSState]=
"-","US","Mexico"))
 
Fred,

The last query worked great! Thank you for all of your help and patience.

Michael
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query to lookup and multiply by exchange rates 1
access formula 3
If/And Statement 2
How do I count the number of times an entry occurs in a field? 2
Access Reports 1
IIF QUERY SYNTAX QUESTION IN ACCESS 5
Increase cell B by one for every 3 in cell A up to 50? 3
What Formula Can I use 2

Back
Top