IIF Statement in query

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

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
Width:(IIF([CabinetWidth]<24,[CabinetWidth]-.1250,[CabinetWidth]/2-.1250))
It produces an error# in Door Width column

What am I missing???

Thank You in advance
 
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 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??

Allen Browne said:
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.

hellZg8 said:
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
Width:(IIF([CabinetWidth]<24,[CabinetWidth]-.1250,[CabinetWidth]/2-.1250))
It produces an error# in Door Width column

What am I missing???

Thank You in advance
Click to expand...
Click to expand...
 
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
 
Please tell me that you are not seeing ############# because you column is
not wide enough (:-<)
 
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

David F Cox said:
Please tell me that you are not seeing ############# because you column is
not wide enough (:-<)


hellZg8 said:
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
Width:(IIF([CabinetWidth]<24,[CabinetWidth]-.1250,[CabinetWidth]/2-.1250))
It produces an error# in Door Width column

What am I missing???

Thank You in advance
Click to expand...
Click to expand...
 
Back
Top