IF Then Else

[Bill]

Hi,
Can someone point out to me what I have wrong with this IF statement. No
matter what I change or where I place commas I still get a Syntax Error. I
have a Form with one text box for an input number and a command button to run
my query. I want to put in this IF statement in the query. It is suppose to
do this. When a number is put into the text box the query checks for match in
the dwFirstNumber field. If there is not any then to look in the
dwSecondNumber field and then the dwThirdNumber for matches. This is what I
entered on the criteria line of my query:
If(([Forms]![tblBoxed_Form]![Text0] = “dwFirstNumberâ€) then (“dwFirstNumberâ€))

Else
([Forms]![tblBoxed_Form]![Text0] = “dwSecondNumberâ€)

Else
([Forms]![tblBoxed_Form]![Text0] = “dwThirdNumberâ€)

Else
([Forms]![tblBoxed_Form]![Text0] = “â€) “Number Not Pickedâ€

End If

Just what I am I doing wrong? And what can I do to fix it. I want to thank
anyone who helps me out. I am just learning so I have to feel my way around
and ask some simple questions I should know.
Thanks,
Bill

 

[John W. Vinson]

Hi,
Can someone point out to me what I have wrong with this IF statement. No
matter what I change or where I place commas I still get a Syntax Error. I
have a Form with one text box for an input number and a command button to run
my query. I want to put in this IF statement in the query. It is suppose to
do this. When a number is put into the text box the query checks for match in
the dwFirstNumber field. If there is not any then to look in the
dwSecondNumber field and then the dwThirdNumber for matches. This is what I
entered on the criteria line of my query:
If(([Forms]![tblBoxed_Form]![Text0] = “dwFirstNumber”) then (“dwFirstNumber”))

Else
([Forms]![tblBoxed_Form]![Text0] = “dwSecondNumber”)

Else
([Forms]![tblBoxed_Form]![Text0] = “dwThirdNumber”)

Else
([Forms]![tblBoxed_Form]![Text0] = “”) “Number Not Picked”

End If

Just what I am I doing wrong? And what can I do to fix it. I want to thank
anyone who helps me out. I am just learning so I have to feel my way around
and ask some simple questions I should know.
Thanks,
Bill

Lots of things wrong here. For one thing, an IF/ELSE block works in VBA
program code; it doesn't work in a query.

In VBA, Else doesn't take arguments. An IF block is of the form

IF <true or false expression> THEN
<statements to execute if it is TRUE>
ELSE
<statements to execute if it is FALSE>
END IF

or, for a simple one-liner,

IF <true or false expressioni> THEN <single statement to run if true>

You're mixing these two and trying to multibranch as well.

Finally, your "statement to execute if true" isn't a statement, it's just a
text string. You can't execute a text string! What do you want to do with

Could you explain the context? What specifically do you want to happen?

 

[KenSheridan via AccessMonster.com]

Bill:

Are you saying that you want the query to return rows where either the
dwFirstNumber, dwSecondNumber or dwThirdNumber columns contain the number
entered in the text box on the form? If so then the query would be something
like this:

SELECT *
FROM [YourTable]
WHERE ([dwFirstNumber] = [Forms]![tblBoxed_Form]![Text0]
OR [dwSecondNumber] = [Forms]![tblBoxed_Form]![Text0]
OR [dwThirdNumber] = [Forms]![tblBoxed_Form]![Text0]);

However, the fact that you appear to have three columns in the table
representing different values of the same attribute sets my antennae
twitching. It suggests that the table might not be normalized as each row in
a table should contain only one value of each attribute. The correct way of
recording multiple values of one attribute is to store each as separate rows
in a related table. For instance you might have a table Customers where each
customer can make one or more orders. Instead of having columns OrderNumber1,
OrderNumber2, OrderNumber3 etc you'd have separate tables Customers and
Orders, Customers would have columns CustomerID (its primary key), along
with columns for the customer's name, address etc. Orders would have columns
OrderNumber (its primary key), CustomerID (a foreign key referencing the
primary key of customers), OrderDate etc. As well s allowing as few or as
many orders per customer as necessary this makes querying by OrderNumber very
easy as only one column is involved, e.g.

SELECT Customer, OrderNumber, OrderDate
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE OrderNumber = [Forms]![MyForm]![txtOrderNumber];

Ken Sheridan
Stafford, England

Hi,
Can someone point out to me what I have wrong with this IF statement. No
matter what I change or where I place commas I still get a Syntax Error. I
have a Form with one text box for an input number and a command button to run
my query. I want to put in this IF statement in the query. It is suppose to
do this. When a number is put into the text box the query checks for match in
the dwFirstNumber field. If there is not any then to look in the
dwSecondNumber field and then the dwThirdNumber for matches. This is what I
entered on the criteria line of my query:
If(([Forms]![tblBoxed_Form]![Text0] = “dwFirstNumberâ€) then (“dwFirstNumberâ€))

Else
([Forms]![tblBoxed_Form]![Text0] = “dwSecondNumberâ€)

Else
([Forms]![tblBoxed_Form]![Text0] = “dwThirdNumberâ€)

Else
([Forms]![tblBoxed_Form]![Text0] = “â€) “Number Not Pickedâ€

End If

Just what I am I doing wrong? And what can I do to fix it. I want to thank
anyone who helps me out. I am just learning so I have to feel my way around
and ask some simple questions I should know.
Thanks,
Bill