IIf statements with AND criteria

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

Guest

I have the following line in a query:

Type: IIf([SelectedQuantity]=0 And [CorrectQuantity]>0,"SKIP","NO SKIP")

[SelectedQuantity] and [CorrectQuantity] are numeric fields but I want the
Type field to return text. I have done this in other queries but for some
reason I can't get this one to work. I get a field value of #Error for every
record.

Can someone tell me what I am doing wrong?
 
Type is a reserved word in ACCESS, so change it to TypeText or something
like that.
 
Thank you for the response.

I changed the name to TypeText and SkipError but I still get the same
results. Does everything else look ok or is there maybe another way of doing
this?

SkipError: IIf([SelectedQuantity]=0 And [CorrectQuantity]<>0,"SKIP","")




Ken Snell (MVP) said:
Type is a reserved word in ACCESS, so change it to TypeText or something
like that.

--

Ken Snell
<MS ACCESS MVP>


AccessIM said:
I have the following line in a query:

Type: IIf([SelectedQuantity]=0 And [CorrectQuantity]>0,"SKIP","NO SKIP")

[SelectedQuantity] and [CorrectQuantity] are numeric fields but I want the
Type field to return text. I have done this in other queries but for some
reason I can't get this one to work. I get a field value of #Error for
every
record.

Can someone tell me what I am doing wrong?
Click to expand...
Click to expand...
 
Interesting and frustrating I bet...

First, are we talking about the "same thing?"

-- are you describing what you type in a Field row
in the query grid?

Second, split up 2 fields to test

TestSel: [SelectedQuantity]=0

TestCor: [CorrectQuantity]<>0

-- do both or just one or none give #ERROR?

Third, could you provide full SQL of query?

thanks,

gary

AccessIM said:
Thank you for the response.

I changed the name to TypeText and SkipError but I still get the same
results. Does everything else look ok or is there maybe another way of
doing
this?

SkipError: IIf([SelectedQuantity]=0 And [CorrectQuantity]<>0,"SKIP","")




Ken Snell (MVP) said:
Type is a reserved word in ACCESS, so change it to TypeText or something
like that.

--

Ken Snell
<MS ACCESS MVP>


AccessIM said:
I have the following line in a query:

Type: IIf([SelectedQuantity]=0 And [CorrectQuantity]>0,"SKIP","NO
SKIP")

[SelectedQuantity] and [CorrectQuantity] are numeric fields but I want
the
Type field to return text. I have done this in other queries but for
some
reason I can't get this one to work. I get a field value of #Error for
every
record.

Can someone tell me what I am doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
OK -

Is it possible for SelectedQuantity and/or CorrectQuantity to have Null
value? If yes, try this:

SkipError: IIf(Nz([SelectedQuantity],0)=0 And
Nz([CorrectQuantity],0)<>0,"SKIP","")

--

Ken Snell
<MS ACCESS MVP>


AccessIM said:
Thank you for the response.

I changed the name to TypeText and SkipError but I still get the same
results. Does everything else look ok or is there maybe another way of
doing
this?

SkipError: IIf([SelectedQuantity]=0 And [CorrectQuantity]<>0,"SKIP","")




Ken Snell (MVP) said:
Type is a reserved word in ACCESS, so change it to TypeText or something
like that.

--

Ken Snell
<MS ACCESS MVP>


AccessIM said:
I have the following line in a query:

Type: IIf([SelectedQuantity]=0 And [CorrectQuantity]>0,"SKIP","NO
SKIP")

[SelectedQuantity] and [CorrectQuantity] are numeric fields but I want
the
Type field to return text. I have done this in other queries but for
some
reason I can't get this one to work. I get a field value of #Error for
every
record.

Can someone tell me what I am doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top