Converting Excel Formula To Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an If And statement that works in Excel, but I can't get it to work in Access. Can someone tell me how I would write the following Excel statement in Access? =If (And(C2+D2=B2, I2>=A2), "Yes", " ")
 
As an expression in a form:

= IIf(txtC+txtD = txtB AND txtI>=txtA, "Yes","")

In a query:

Expr1: IIf(ColC+ColD = ColB AND ColI>=ColA, "Yes","")

In code (feed values to C, D, B, I, and A) :

Function Whatever(C, D, B, I, A) As String

If ColC+ColD = ColB AND ColI>=ColA Then
Whatever = "Yes"
Else
Whatever = ""
End If

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Coleen Eastham said:
I have an If And statement that works in Excel, but I can't get it to work
Click to expand...
in Access. Can someone tell me how I would write the following Excel
statement in Access? =If (And(C2+D2=B2, I2>=A2), "Yes", " ")
 
As a general rule, you don't want to think of a database system as a
spreadsheet.

However, you can use:


iif( ((C2+d2) = b2) and (i2 >= a2),"Yes","")

I would think that you are going to use much more descriptive field names,
as "d2" in ms-access does not mean column d, and row 2.

So, d2 will have to be a single field name like InvoiceAmount, or
whatever...since we can't reference other rows in a formula.

In fact, the fields would have to be c, d, b, and I and a.....


You would get:

iif( ((C+d) = b) and (i >= a),"Yes","")
 
The formula ( =If (And (C2 + D2 = B2, I2 >= A2), "Yes", " ") that I had previously given is the Excel formula. The field names are not C2, D2, B2, etc. They have descriptive field names. I've tried your formula below, but it still is just giving me a shaded box instead of a check box. Here is what my Access formula looks like
iif(([One] + [Two] = [Tri Day Hrs Req'd]) and ([Total] >= [Hrs]), "Yes","")
Any other ideas

----- Albert D. Kallal wrote: ----

As a general rule, you don't want to think of a database system as
spreadsheet

However, you can use


iif( ((C2+d2) = b2) and (i2 >= a2),"Yes",""

I would think that you are going to use much more descriptive field names
as "d2" in ms-access does not mean column d, and row 2

So, d2 will have to be a single field name like InvoiceAmount, o
whatever...since we can't reference other rows in a formula

In fact, the fields would have to be c, d, b, and I and a....


You would get

iif( ((C+d) = b) and (i >= a),"Yes",""


-
Albert D. Kallal (MVP
Edmonton, Alberta Canad
(e-mail address removed)
http://www.attcanada.net/~kallal.ms
 
Coleen Eastham said:
I have tried your formula, and it doesn't seem to work. I'm using it in a
Click to expand...
form. In the field's properties, I'm typing the formula in the Control
Source. I just get a shaded box instead of a check. Any ideas?

You'll probably need to use this format in a property sheet:

=(IIf([Forms]![FormName]![txtControlName]+[Forms]![FormName]![txtControlName
2] = [Forms]![FormName]![Tri Day Hrs Req'd]) AND ([Forms]![FormName]![Total]
= [Forms]![FormName]![Hrs]), "Yes",""))
Click to expand...

You might get away with:

=(IIf([txtControlName]+[txtControlName2] = [Tri Day Hrs Req'd]) AND [Total]
= [Hrs]), "Yes",""))
Click to expand...

You may have a problem with the single quote in "[Tri Day Hrs Req'd]",
although the square breackets should be sufficient. ControlNames are NOT
fieldnames, they are the names of the textboxes (or other controls) which
contain the values of the fields.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Here is what my Access formula looks like:
iif(([One] + [Two] = [Tri Day Hrs Req'd]) and ([Total] >= [Hrs]),
Click to expand...
"Yes","")

While it *may* not your problem, you REALLY want to avoid fields with things
like spaces, and quotes. I would not use them at all! Spaces, and funny
characters in field names is a formula for trouble. I would consider
changing that before you have too much code, reports, forms etc built.

However, the expression you would type into the text box that you place on
the screen should look like:

=iif( (([One] + [Two]) = [Tr Day Hrs Req'd]) and ([Total] >=
[Hrs]),"Yes","")


So many statements and commands use spaces and things like " or ' as
delimiters...that you don't want to use those in your field names.

Also, try building up the expression one thing at a time. For example, try:

=iif( (([One + Two]) = [Tri Day Hrs Req'd]),"Yes","")

Get the above working...and then add the extra "and". Build it up in small
bits...and get each part working.

You could also try to make the following work:

=iif( ([Total] >= [Hrs]) , "Yes","")

Get the above working....and then try expanding the expression to include
all of the above.

It is of no use to try and get one large expression working until you can
get the most simple expression working.
 
The formula ( =If (And (C2 + D2 = B2, I2 >= A2), "Yes", " ") that I had previously given is the Excel formula. The field names are not C2, D2, B2, etc. They have descriptive field names. I've tried your formula below, but it still is just giving me a shaded box instead of a check box. Here is what my Access formula looks like:
iif(([One] + [Two] = [Tri Day Hrs Req'd]) and ([Total] >= [Hrs]), "Yes","")
Click to expand...

If the control is a Checkbox, you can't set it to text strings "Yes"
or an empty string. Instead, use -1 for True and 0 for False:

iif(([One]+[Two]=[Tri Day Hrs Req'd]) and ([Total] >= [Hrs]), -1, 0)
 
Back
Top