Nested If statement with conditional expressions

[Guest]

Hi,
I am trying to figure out how to create a query with a newly created field
called "Price" whereby the price will be $7.00 if the manually entered text
in the Color field is "black" or if the field is left blank OR the price will
be $10.00 if the manually entered text in the Color field is "color".

My queries below only work for fields with text entered in them (ie black or
color).
Neither one of these functions will show the record with the color field
left blank, but they all give same result:

Price: IIf(IsNull([Color]) Or [Color]="black",7,10)

Price: IIf([Color]=" " Or [Color]="black",7,10)

Price: IIf([Color]='''' Or [Color]="black",7,10)

Price: IIf(IsMissing([Color]) Or [Color]="black",7,10)

I have reviewed some postings with respect to null values and empty strings,
and have tried the following query as well. However, I get an error message
that there is the wrong number of arguments.

Price: IIf(IsNull([Color]),"",IIf([Color])="","",If([Color])="black",7,10))

Your help would be greatly appreciated.

Thanks!

 

[Guest]

You should be able to cover all bases with the following single IIf call:

Price: IIf(Trim([Color] & "")="" Or [Color] = "Black",7,10)

The expression Color & "" will produce a zero length string if Color is
already a zero length string or is Null. If it’s a string of one or more
spaces then the Trim function will return a zero length string. So for all
of these three possibilities you just have to check for a zero length string.
You then OR this criterion with [Color] = "Black".

BTW there is not really such a thing as a 'null value'. Null is the absence
of a value. That's why we have the IsNull function in VBA and IS NULL in
SQL, because asking if Something = NULL always returns an answer of NULL,
even ? NULL = NULL returns an answer of NULL, which sounds illogical at
first, but if you think of NULL as 'unknown' then it makes more sense as each
'unknown' might be the same value or they might be completely different
values, so the answer is also 'unknown'. The Value *property* of a control
or field object can be NULL, however!

Forget the IsMissing function in this situation. That's used for testing
whether an optional argument of Variant data type has been passed into a
function.

Ken Sheridan
Stafford, England

 

[Guest]

Hi Ken,
Thanks so much for your speedy response! My only problem is that the query
still will not show the records for which the field "Color" is left blank.
(It is important that this field is left blank only if a customer does not
order a photo with their text design. There are only 2 choices for photos,
black/white or color).

I am also using "Group By" to avoid duplicates in my query. (just so that
you are aware)

Thanks so much!

You should be able to cover all bases with the following single IIf call:

Price: IIf(Trim([Color] & "")="" Or [Color] = "Black",7,10)

The expression Color & "" will produce a zero length string if Color is
already a zero length string or is Null. If it’s a string of one or more
spaces then the Trim function will return a zero length string. So for all
of these three possibilities you just have to check for a zero length string.
You then OR this criterion with [Color] = "Black".

BTW there is not really such a thing as a 'null value'. Null is the absence
of a value. That's why we have the IsNull function in VBA and IS NULL in
SQL, because asking if Something = NULL always returns an answer of NULL,
even ? NULL = NULL returns an answer of NULL, which sounds illogical at
first, but if you think of NULL as 'unknown' then it makes more sense as each
'unknown' might be the same value or they might be completely different
values, so the answer is also 'unknown'. The Value *property* of a control
or field object can be NULL, however!

Forget the IsMissing function in this situation. That's used for testing
whether an optional argument of Variant data type has been passed into a
function.

Ken Sheridan
Stafford, England

Hi,
I am trying to figure out how to create a query with a newly created field
called "Price" whereby the price will be $7.00 if the manually entered text
in the Color field is "black" or if the field is left blank OR the price will
be $10.00 if the manually entered text in the Color field is "color".

My queries below only work for fields with text entered in them (ie black or
color).
Neither one of these functions will show the record with the color field
left blank, but they all give same result:

Price: IIf(IsNull([Color]) Or [Color]="black",7,10)

Price: IIf([Color]=" " Or [Color]="black",7,10)

Price: IIf([Color]='''' Or [Color]="black",7,10)

Price: IIf(IsMissing([Color]) Or [Color]="black",7,10)

I have reviewed some postings with respect to null values and empty strings,
and have tried the following query as well. However, I get an error message
that there is the wrong number of arguments.

Price: IIf(IsNull([Color]),"",IIf([Color])="","",If([Color])="black",7,10))

Your help would be greatly appreciated.

Thanks!

 

[Guest]

If the rows without any value in the Color column are not being returned then
that's more likely to be due to either a criterion in the query or to the
lack of a match in the join between two tables. Can you post the SQL for the
query here; that might help the dog see the rabbit.

Ken Sheridan
Stafford, England

Hi Ken,
Thanks so much for your speedy response! My only problem is that the query
still will not show the records for which the field "Color" is left blank.
(It is important that this field is left blank only if a customer does not
order a photo with their text design. There are only 2 choices for photos,
black/white or color).

I am also using "Group By" to avoid duplicates in my query. (just so that
you are aware)

Thanks so much!

You should be able to cover all bases with the following single IIf call:

Price: IIf(Trim([Color] & "")="" Or [Color] = "Black",7,10)

The expression Color & "" will produce a zero length string if Color is
already a zero length string or is Null. If it’s a string of one or more
spaces then the Trim function will return a zero length string. So for all
of these three possibilities you just have to check for a zero length string.
You then OR this criterion with [Color] = "Black".

BTW there is not really such a thing as a 'null value'. Null is the absence
of a value. That's why we have the IsNull function in VBA and IS NULL in
SQL, because asking if Something = NULL always returns an answer of NULL,
even ? NULL = NULL returns an answer of NULL, which sounds illogical at
first, but if you think of NULL as 'unknown' then it makes more sense as each
'unknown' might be the same value or they might be completely different
values, so the answer is also 'unknown'. The Value *property* of a control
or field object can be NULL, however!

Forget the IsMissing function in this situation. That's used for testing
whether an optional argument of Variant data type has been passed into a
function.

Ken Sheridan
Stafford, England

Hi,
I am trying to figure out how to create a query with a newly created field
called "Price" whereby the price will be $7.00 if the manually entered text
in the Color field is "black" or if the field is left blank OR the price will
be $10.00 if the manually entered text in the Color field is "color".

My queries below only work for fields with text entered in them (ie black or
color).
Neither one of these functions will show the record with the color field
left blank, but they all give same result:

Price: IIf(IsNull([Color]) Or [Color]="black",7,10)

Price: IIf([Color]=" " Or [Color]="black",7,10)

Price: IIf([Color]='''' Or [Color]="black",7,10)

Price: IIf(IsMissing([Color]) Or [Color]="black",7,10)

I have reviewed some postings with respect to null values and empty strings,
and have tried the following query as well. However, I get an error message
that there is the wrong number of arguments.

Price: IIf(IsNull([Color]),"",IIf([Color])="","",If([Color])="black",7,10))

Your help would be greatly appreciated.

Thanks!

 

[Guest]

Hi Ken,
I was actually able to do my query as follows:

iif([colour]="colour",10,7)

I then placed a validation rule in the Colour field such that a user can
only enter "b/w", "colour" or "n/a".

This works perfect. Thanks so much for responding to me again, Ken. Have a
great day!

If the rows without any value in the Color column are not being returned then
that's more likely to be due to either a criterion in the query or to the
lack of a match in the join between two tables. Can you post the SQL for the
query here; that might help the dog see the rabbit.

Ken Sheridan
Stafford, England

Hi Ken,
Thanks so much for your speedy response! My only problem is that the query
still will not show the records for which the field "Color" is left blank.
(It is important that this field is left blank only if a customer does not
order a photo with their text design. There are only 2 choices for photos,
black/white or color).

I am also using "Group By" to avoid duplicates in my query. (just so that
you are aware)

Thanks so much!

You should be able to cover all bases with the following single IIf call:

Price: IIf(Trim([Color] & "")="" Or [Color] = "Black",7,10)

The expression Color & "" will produce a zero length string if Color is
already a zero length string or is Null. If it’s a string of one or more
spaces then the Trim function will return a zero length string. So for all
of these three possibilities you just have to check for a zero length string.
You then OR this criterion with [Color] = "Black".

BTW there is not really such a thing as a 'null value'. Null is the absence
of a value. That's why we have the IsNull function in VBA and IS NULL in
SQL, because asking if Something = NULL always returns an answer of NULL,
even ? NULL = NULL returns an answer of NULL, which sounds illogical at
first, but if you think of NULL as 'unknown' then it makes more sense as each
'unknown' might be the same value or they might be completely different
values, so the answer is also 'unknown'. The Value *property* of a control
or field object can be NULL, however!

Forget the IsMissing function in this situation. That's used for testing
whether an optional argument of Variant data type has been passed into a
function.

Ken Sheridan
Stafford, England

:

Hi,
I am trying to figure out how to create a query with a newly created field
called "Price" whereby the price will be $7.00 if the manually entered text
in the Color field is "black" or if the field is left blank OR the price will
be $10.00 if the manually entered text in the Color field is "color".

My queries below only work for fields with text entered in them (ie black or
color).
Neither one of these functions will show the record with the color field
left blank, but they all give same result:

Price: IIf(IsNull([Color]) Or [Color]="black",7,10)

Price: IIf([Color]=" " Or [Color]="black",7,10)

Price: IIf([Color]='''' Or [Color]="black",7,10)

Price: IIf(IsMissing([Color]) Or [Color]="black",7,10)

I have reviewed some postings with respect to null values and empty strings,
and have tried the following query as well. However, I get an error message
that there is the wrong number of arguments.

Price: IIf(IsNull([Color]),"",IIf([Color])="","",If([Color])="black",7,10))

Your help would be greatly appreciated.

Thanks!

 

[Jamie Collins]

BTW there is not really such a thing as a 'null value'.

Well, there is certainly the concept of "the null value", as used in
the SQL-92 standard, the SQL literature, etc.

asking if Something = NULL always returns an answer of NULL,
even ? NULL = NULL returns an answer of NULL

Actually, the answer in both cases is UNKNOWN. You seem to have missed
the distinction between NULL being a data value and UNKNOWN being a
logical value. This is important to understanding why a UNKNOWN
logical value will not fail a Validation Rule.

Jamie.

--