if statment in query

[Guest]

if this is posted already forgive me

I have a query whichright now the syntax for is as follows

Door Width:[Cabinet Width]-.1250

I'm new in access ( i'm okay with excel and vba)

in excel i could right the if statement as follows
=if(Cabinet Width<24,Cabinet Width-.1250,Cabinet Width/2-.1250)

my question is is it possible to do the same in a query.

I have also created a form based on the query and tried to do a event
procedure to in vba the syntax worked (did not produce an error) but did not
change my form at all.

thank you in advance

 

[Guest]

Hi Hellzg8,

what you need is an Iif statement in access, rather than If. Like excel, the
syntax remains the same - evaluation part , true part, false part. In help
under Imediate If (hence the 2 i's)

TonyT..

 

[David F Cox]

So close, it is IIF(test, iftrue, iffalse)

DoorWidth: IIF([Cabinet Width]<24,[Cabinet Width]-.1250,[Cabinet
Width]/2-.1250)

the [] brackets help identify fields, and are essential if you have blanks
in the field names. Ii really is not a good idea to have blanks in filed
names.

 

[Guest]

Tony And David
Thank you both for a quick response

i have seen this Iif statement and tried it
all it produced was a error in door width column
and on form

this is the syntax that i have in the query
Door Width: IIf([CabinetWidth]<24,[CabinetWidth]-0.125,[CabinetWidth]/2-0.125)

now i had earlier tried just
Door Width:[CabinetWidth]-.1250 which gave me the correct numbers but i need
to have the number divide in two if it is large than 24

maybe i missed typed something I don't know

thanks in advance

 

[David F Cox]

Before you posted Cabinet Width as the field name, which is why I
said you had to enclose it thus [Cabinet Width].
now it appears as CabinetWidth. You have to match the name exactly to that
specified in the source, which is probably a table.

 

[Guest]

my apologies David the source is CabinetWidth the first time I typed this out
was incorrect

when I typed in the code that you had written I had put it as it should be
[CabinetWidth]

again sorry for the type o my fault

Before you posted Cabinet Width as the field name, which is why I
said you had to enclose it thus [Cabinet Width].
now it appears as CabinetWidth. You have to match the name exactly to that
specified in the source, which is probably a table.

Tony And David
Thank you both for a quick response

i have seen this Iif statement and tried it
all it produced was a error in door width column
and on form

this is the syntax that i have in the query
Door Width:
IIf([CabinetWidth]<24,[CabinetWidth]-0.125,[CabinetWidth]/2-0.125)

now i had earlier tried just
Door Width:[CabinetWidth]-.1250 which gave me the correct numbers but i
need
to have the number divide in two if it is large than 24

maybe i missed typed something I don't know

thanks in advance

 

[Guest]

David
I have one table with three columns in it
CabinetId, CabinetCode, CabinetWidth

Now in my query I have those three columns plus 5 more
Door Code, Door Width, Door Height,SqIn,SqFt

Door Code:"LD" & [CabinetWidth]
Door Width: This the code I need help with
Door Height:24.6250
SqIn:[Door Width]*[Door Height]
SqFt:SqIn/144
all the above code does work with the exception of Door Width

 

[David F Cox]

So I installed a cabinet in my classroom and used

UPDATE tblclass SET tblclass.doorwidth =
IIf([cabinetwidth]<24,[cabinetwidth]-0.125,[cabinetwidth]/2-0.125);

and it worked for me.

If it still does not work paste the SQL you are using from SQL view. I do
not think that you are far away.

my apologies David the source is CabinetWidth the first time I typed this
out
was incorrect

when I typed in the code that you had written I had put it as it should be
[CabinetWidth]

again sorry for the type o my fault

Before you posted Cabinet Width as the field name, which is
why I
said you had to enclose it thus [Cabinet Width].
now it appears as CabinetWidth. You have to match the name exactly to
that
specified in the source, which is probably a table.

Tony And David
Thank you both for a quick response

i have seen this Iif statement and tried it
all it produced was a error in door width column
and on form

this is the syntax that i have in the query
Door Width:
IIf([CabinetWidth]<24,[CabinetWidth]-0.125,[CabinetWidth]/2-0.125)

now i had earlier tried just
Door Width:[CabinetWidth]-.1250 which gave me the correct numbers but i
need
to have the number divide in two if it is large than 24

maybe i missed typed something I don't know

thanks in advance

 

[Guest]

Hi David
I am not sure on where to place this does in go into vba or do I build an
event with this on the form
The only table that I have is the a Cabinet Table which gives me the
CabinetId,CabinetCode,and the CabinetWidth

every thing else is in a query or form
I have one form for Cabinets, and one base on the query for the doors

So I installed a cabinet in my classroom and used

UPDATE tblclass SET tblclass.doorwidth =
IIf([cabinetwidth]<24,[cabinetwidth]-0.125,[cabinetwidth]/2-0.125);

and it worked for me.

If it still does not work paste the SQL you are using from SQL view. I do
not think that you are far away.

my apologies David the source is CabinetWidth the first time I typed this
out
was incorrect

when I typed in the code that you had written I had put it as it should be
[CabinetWidth]

again sorry for the type o my fault

Before you posted Cabinet Width as the field name, which is
why I
said you had to enclose it thus [Cabinet Width].
now it appears as CabinetWidth. You have to match the name exactly to
that
specified in the source, which is probably a table.


Tony And David
Thank you both for a quick response

i have seen this Iif statement and tried it
all it produced was a error in door width column
and on form

this is the syntax that i have in the query
Door Width:
IIf([CabinetWidth]<24,[CabinetWidth]-0.125,[CabinetWidth]/2-0.125)

now i had earlier tried just
Door Width:[CabinetWidth]-.1250 which gave me the correct numbers but i
need
to have the number divide in two if it is large than 24

maybe i missed typed something I don't know

thanks in advance

 

[David F Cox]

I am going to believe that we have one part of the problem solved, the
syntax of the update query. After a hard night I am not able to visualise
your user interface, what you are entering and what result you wish to
achieve. I know it might seem obvious to you, being immersed in the
situation. Could you please start another thread about the layout of your
forms, what information you want to input using which controls, and what
results you want to get, so everybody trying to help can be clear about the
situation.

Hi David
I am not sure on where to place this does in go into vba or do I build an
event with this on the form
The only table that I have is the a Cabinet Table which gives me the
CabinetId,CabinetCode,and the CabinetWidth

every thing else is in a query or form
I have one form for Cabinets, and one base on the query for the doors

So I installed a cabinet in my classroom and used

UPDATE tblclass SET tblclass.doorwidth =
IIf([cabinetwidth]<24,[cabinetwidth]-0.125,[cabinetwidth]/2-0.125);

and it worked for me.

If it still does not work paste the SQL you are using from SQL view. I do
not think that you are far away.

my apologies David the source is CabinetWidth the first time I typed
this
out
was incorrect

when I typed in the code that you had written I had put it as it should
be
[CabinetWidth]

again sorry for the type o my fault

:

Before you posted Cabinet Width as the field name, which is
why I
said you had to enclose it thus [Cabinet Width].
now it appears as CabinetWidth. You have to match the name exactly to
that
specified in the source, which is probably a table.


Tony And David
Thank you both for a quick response

i have seen this Iif statement and tried it
all it produced was a error in door width column
and on form

this is the syntax that i have in the query
Door Width:
IIf([CabinetWidth]<24,[CabinetWidth]-0.125,[CabinetWidth]/2-0.125)

now i had earlier tried just
Door Width:[CabinetWidth]-.1250 which gave me the correct numbers
but i
need
to have the number divide in two if it is large than 24

maybe i missed typed something I don't know

thanks in advance

 

[Guest]

Hi David

What I want to be able to achieve is that when my form moves to the next entry
which would give me a new cabinet size that I getthe info for the doors that
are needed for the cabinet.

example: cabinets from 9" to 21" in width come with one door, cabinets 24"
to 45"
have two doors.So I need to be able to split the cabinet width in halve -
1/8" for each door

is there a way I can post or send this file for people to see to help??
the file is 732 kb

on the form I have the following ;

CabinetId, Cabinet Code, CabinetWidth which all comes from my table (the
only table so far)
LDoor Code, Door Width, Door Height, SqIn, SqFt

CabinetId I get from table, along with CabinetCode, CabinetWidth
every thing else is base upon the query

any cabinet 24" and above I need the cabinet width / 2 - 1/8"

I hope this help out a bit more

thanks again David for all your time on this matter

Thanks in advance

 

[David F Cox]

I suggested that you started a new thread on this because the experts tend
tolook at the newsgroups in "group by conversation" mode, and do not know to
step in if someone trying to help gets stuck. I have just had major issues
arise at home and at work, and really regret that I have not been able to
spend the time required to finalise this for you. I have got as far as a
query that will do the update job, the "if statement in query bit" but I
have not had the time or the information required to work on integrating
that query with a form to use to get the desired result.

My guess is that basing a form on a query that uses a similar technique to
this update query will do the job.

UPDATE tblclass SET tblclass.doorwidth =
IIf([cabinetwidth]<24,[cabinetwidth]-0.125,[cabinetwidth]/2-0.125);

If I get time I will work on this further, but I regret it is down my list
of priorities now. If you statrt a new thread in this group with a new title
I am sure you will attract fresh help that will achieve a result before I
can. Sorry.

David

 

[Guest]

Thank you David for all your time on thois matter