'IF' function

[Guest]

Is there an 'IF' function in Access, similar to the one in Excel, that i can
use in either a table or a query?

My aim is to calculate whether a required number of parts ordered, have all
been delivered. The data is stored in two columns. the first "Quantity
required", the second "Quantity delivered".

If the quantity ordered is NOT equal to the quantity delivered, then i want
a 'LIVE' text to show in a third column.

Any help is very welcome

Chris

 

[Guest]

You can use IIf

In SQL:
Select [quantity ordered], [quantity delivered] , IIf([quantity ordered]=
[quantity delivered], "LIVE","") As ThirdFieldName From TableName

Or in query design, add another field and write

ThirdFieldName: IIf([quantity ordered]= [quantity delivered], "LIVE","")

 

[BruceM]

Not in a table. In a new query field you can use IIf, which is similar to
IF in Excel.
NewField: IIf([Required] <> [Delivered], "Live","")

Use whatever field name you like, and use the actual field names for
[Required] and [Delivered]. On a report, you can bind a control to
NewField.

You can also use IIf in the control source for a text box on a form or
report:
= IIf([Required] <> [Delivered], "Live","")

 

[Guest]

Sorry, not equal and I gave you an example with equal

In SQL:
Select [quantity ordered], [quantity delivered] , IIf([quantity ordered] <>
[quantity delivered], "LIVE","") As ThirdFieldName From TableName

Or in query design, add another field and write

ThirdFieldName: IIf([quantity ordered] <> [quantity delivered], "LIVE","")

--
\\// Live Long and Prosper \\//
BS"D

You can use IIf

In SQL:
Select [quantity ordered], [quantity delivered] , IIf([quantity ordered]=
[quantity delivered], "LIVE","") As ThirdFieldName From TableName

Or in query design, add another field and write

ThirdFieldName: IIf([quantity ordered]= [quantity delivered], "LIVE","")

--
\\// Live Long and Prosper \\//
BS"D

Is there an 'IF' function in Access, similar to the one in Excel, that i can
use in either a table or a query?

My aim is to calculate whether a required number of parts ordered, have all
been delivered. The data is stored in two columns. the first "Quantity
required", the second "Quantity delivered".

If the quantity ordered is NOT equal to the quantity delivered, then i want
a 'LIVE' text to show in a third column.

Any help is very welcome

Chris

 

[Guest]

Thank you for your help.

i have used your formula, but i keep beeing told:

"The expression you have entered has an invalid . (dot) or ! operator or
invlaid parentheses. You may have entered an invalid identifier or typed
parentheses following the null constant"

My new field is called "Live", and the formula as i have entered it is as
follows:

Live: IIf([Qty Required]<>[Qty Sourced],"Live","")

What have i done wrong? Where should i insert the formula? At present i have
it in the "Criteria" box.

Thanks

Crimbo

Sorry, not equal and I gave you an example with equal

In SQL:
Select [quantity ordered], [quantity delivered] , IIf([quantity ordered] <>
[quantity delivered], "LIVE","") As ThirdFieldName From TableName

Or in query design, add another field and write

ThirdFieldName: IIf([quantity ordered] <> [quantity delivered], "LIVE","")

--
\\// Live Long and Prosper \\//
BS"D

You can use IIf

In SQL:
Select [quantity ordered], [quantity delivered] , IIf([quantity ordered]=
[quantity delivered], "LIVE","") As ThirdFieldName From TableName

Or in query design, add another field and write

ThirdFieldName: IIf([quantity ordered]= [quantity delivered], "LIVE","")

--
\\// Live Long and Prosper \\//
BS"D

Is there an 'IF' function in Access, similar to the one in Excel, that i can
use in either a table or a query?

My aim is to calculate whether a required number of parts ordered, have all
been delivered. The data is stored in two columns. the first "Quantity
required", the second "Quantity delivered".

If the quantity ordered is NOT equal to the quantity delivered, then i want
a 'LIVE' text to show in a third column.

Any help is very welcome

Chris

 

[Guest]

Thank you for your help.

i have used your formula, but i keep beeing told:

"The expression you have entered has an invalid . (dot) or ! operator or
invlaid parentheses. You may have entered an invalid identifier or typed
parentheses following the null constant"

My new field is called "Live", and the formula as i have entered it is as
follows:

Live: IIf([Qty Required]<>[Qty Sourced],"Live","")

What have i done wrong? Where should i insert the formula? At present i have
it in the "Criteria" box.

Thanks

Crimbo

Not in a table. In a new query field you can use IIf, which is similar to
IF in Excel.
NewField: IIf([Required] <> [Delivered], "Live","")

Use whatever field name you like, and use the actual field names for
[Required] and [Delivered]. On a report, you can bind a control to
NewField.

You can also use IIf in the control source for a text box on a form or
report:
= IIf([Required] <> [Delivered], "Live","")

Is there an 'IF' function in Access, similar to the one in Excel, that i
can
use in either a table or a query?

My aim is to calculate whether a required number of parts ordered, have
all
been delivered. The data is stored in two columns. the first "Quantity
required", the second "Quantity delivered".

If the quantity ordered is NOT equal to the quantity delivered, then i
want
a 'LIVE' text to show in a third column.

Any help is very welcome

Chris

 

[BruceM]

In Query design view, the top row is labeled Field. That's where you put
the expression. Criteria is where you would put something to limit the
query results. For instance, if the table on which the query is based has a
number field, you could add that field to the query and specify >10 as the
criteria in order to limit the query results to those records in which that
field is greater than 10.

Thank you for your help.

i have used your formula, but i keep beeing told:

"The expression you have entered has an invalid . (dot) or ! operator or
invlaid parentheses. You may have entered an invalid identifier or typed
parentheses following the null constant"

My new field is called "Live", and the formula as i have entered it is as
follows:

Live: IIf([Qty Required]<>[Qty Sourced],"Live","")

What have i done wrong? Where should i insert the formula? At present i
have
it in the "Criteria" box.

Thanks

Crimbo

Not in a table. In a new query field you can use IIf, which is similar
to
IF in Excel.
NewField: IIf([Required] <> [Delivered], "Live","")

Use whatever field name you like, and use the actual field names for
[Required] and [Delivered]. On a report, you can bind a control to
NewField.

You can also use IIf in the control source for a text box on a form or
report:
= IIf([Required] <> [Delivered], "Live","")

Is there an 'IF' function in Access, similar to the one in Excel, that
i
can
use in either a table or a query?

My aim is to calculate whether a required number of parts ordered, have
all
been delivered. The data is stored in two columns. the first "Quantity
required", the second "Quantity delivered".

If the quantity ordered is NOT equal to the quantity delivered, then i
want
a 'LIVE' text to show in a third column.

Any help is very welcome

Chris

 

[Guest]

Move it from the criteria box to the field box

--
\\// Live Long and Prosper \\//
BS"D

Thank you for your help.

i have used your formula, but i keep beeing told:

"The expression you have entered has an invalid . (dot) or ! operator or
invlaid parentheses. You may have entered an invalid identifier or typed
parentheses following the null constant"

My new field is called "Live", and the formula as i have entered it is as
follows:

Live: IIf([Qty Required]<>[Qty Sourced],"Live","")

What have i done wrong? Where should i insert the formula? At present i have
it in the "Criteria" box.

Thanks

Crimbo

Sorry, not equal and I gave you an example with equal

In SQL:
Select [quantity ordered], [quantity delivered] , IIf([quantity ordered] <>
[quantity delivered], "LIVE","") As ThirdFieldName From TableName

Or in query design, add another field and write

ThirdFieldName: IIf([quantity ordered] <> [quantity delivered], "LIVE","")

--
\\// Live Long and Prosper \\//
BS"D

You can use IIf

In SQL:
Select [quantity ordered], [quantity delivered] , IIf([quantity ordered]=
[quantity delivered], "LIVE","") As ThirdFieldName From TableName

Or in query design, add another field and write

ThirdFieldName: IIf([quantity ordered]= [quantity delivered], "LIVE","")

--
\\// Live Long and Prosper \\//
BS"D


:

Is there an 'IF' function in Access, similar to the one in Excel, that i can
use in either a table or a query?

My aim is to calculate whether a required number of parts ordered, have all
been delivered. The data is stored in two columns. the first "Quantity
required", the second "Quantity delivered".

If the quantity ordered is NOT equal to the quantity delivered, then i want
a 'LIVE' text to show in a third column.

Any help is very welcome

Chris