IIF Statement in query

[Guest]

Good Day everyone

I have a problem with iif statement. What I am trying to do is find out if a
box is under 24" then it gets 1 (one) door else it gets 2 (two) doors.

I have one table that has 3(three) columns
CabinetId,CabinetCode,CabinetWidth

I can get this to work in excel but not in access
=If(CabinetWidyth<24,CabinetWidth-.1250,CabinetWidth/2-.1250)

this is the syntax that I have for this (in a query)

Door
WidthIIF([CabinetWidth]<24,[CabinetWidth]-.1250,[CabinetWidth]/2-.1250))
It produces an error# in Door Width column

What am I missing???

Thank You in advance

 

[Allen Browne]

If you open your table in design view, what is the Data Type of the
CabinetWidth field? What you have should be okay if the field is a Number
type, not a Text type.

The name Width has special meaning in some contexts. Try a different name.

If lots of the records have a null CabinetWidth, JET may get confused.

Try:
TheWidth: IIF([CabinetWidth] < 24, [CabinetWidth] - 0.1250,
[CabinetWidth] / 2 - 0.1250)

 

[Guest]

Allen Thank You very much I did have it as a Text
something so simple can really throw a wrench into it all

now my next question is why do I get a enter parameter box very time I run
the query or form??

If you open your table in design view, what is the Data Type of the
CabinetWidth field? What you have should be okay if the field is a Number
type, not a Text type.

The name Width has special meaning in some contexts. Try a different name.

If lots of the records have a null CabinetWidth, JET may get confused.

Try:
TheWidth: IIF([CabinetWidth] < 24, [CabinetWidth] - 0.1250,
[CabinetWidth] / 2 - 0.1250)


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Good Day everyone

I have a problem with iif statement. What I am trying to do is find out if
a
box is under 24" then it gets 1 (one) door else it gets 2 (two) doors.

I have one table that has 3(three) columns
CabinetId,CabinetCode,CabinetWidth

I can get this to work in excel but not in access
=If(CabinetWidyth<24,CabinetWidth-.1250,CabinetWidth/2-.1250)

this is the syntax that I have for this (in a query)

Door
WidthIIF([CabinetWidth]<24,[CabinetWidth]-.1250,[CabinetWidth]/2-.1250))
It produces an error# in Door Width column

What am I missing???

Thank You in advance

 

[Guest]

Allen never mind it does not seem to this parameter any more
I do not know what I did ?? weird

So once again thank you very much for your time on this matter

have a good day

 

[David F Cox]

Please tell me that you are not seeing ############# because you column is
not wide enough -<)

 

[Guest]

Welcome back David

No the reason I was getting the error# was because the format for
ColumnWidth was Text and not Number as Allen pointed out

Please tell me that you are not seeing ############# because you column is
not wide enough -<)

Good Day everyone

I have a problem with iif statement. What I am trying to do is find out if
a
box is under 24" then it gets 1 (one) door else it gets 2 (two) doors.

I have one table that has 3(three) columns
CabinetId,CabinetCode,CabinetWidth

I can get this to work in excel but not in access
=If(CabinetWidyth<24,CabinetWidth-.1250,CabinetWidth/2-.1250)

this is the syntax that I have for this (in a query)

Door
WidthIIF([CabinetWidth]<24,[CabinetWidth]-.1250,[CabinetWidth]/2-.1250))
It produces an error# in Door Width column

What am I missing???

Thank You in advance