If then statement syntax

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

Guest

I have a form with a subform. The main form finds a group of records and
allows the user to add a new price and effective date.
The subform finds corresponding PNs' prices without a price closing date and
closes the old price. At least that is the premise.

When completing the new price info the main form's last field sets focus to
the sub form's closing price field. Upon 'got focus', the closing date field
on the sub form = the effective date on the main form.
All would seem well....and does work unless there was no price to start
with. So, if the user is entering a price for the first time, the new price
that was just entered is the only price with a null value in the closing date
field. So, the sub-form closes the new price that was just entered.

I would like to put something like this in the sub form closing price
field....
Iif(form_main.price = me.price,null,form_main.EffectiveDate) But, I don't
know how to get the syntax right. Can anyone help me.
Thx.
 
I'm having a little difficulty understanding exactly what you want to do.
You use the word "field", but I think in this case you mean Text Box. Sorry,
but you have to use the correct verbage to get understood. If, you mean text
box, then put your line of code in the DefaultValue property of the text box.
The IIF statement requires an "=" in this case.

= Iif(form_main.price = me.price,null,form_main.EffectiveDate)
 
Thank you for your response. I'm sorry I'm using the wrong language. I did
not create a text box, but selected the a field from the field list when
creating the form - and am trying to have the date that is put in another
field or text box (not sure what to call this) fill in in this one also if
the conditions are right.

Can I use an IIf statement in a box from the field list. I tried putting
the "=" sign in front as you suggested, but the code editor gives me the
following error:

"Compile Error:
Expected: line number or label or statement or end of statement"
 
Sorry, I just realized you wrote to put it in the DefaultValue. I'll try this.
Thanks again
 
I put the following in the DefaultValue of the Text Box called BOMDollarClose:

=IIf([Form_BOMDollarAppend1].[BOMDollar]=[Me].[BOMDollar],Null,[Form_BOMDollarAppend1].[BomDollarEffec])

The open form now shows #NAME? in the Text Box BOMDollarClose

What I need is - If BomDollar on the Main Form and BOMDollar on the subform
are equal, I'd like BOMDollarClose to remain an empty box (null). If they
are not equal, I'd like BOMDollarClose to equal BomDollarEffec on the main
form.

I was originally trying to do this in the event "got focus" for the text box
BOMDollarClose. I couldn't get it to work there either.
Any additional suggests?
Thank you
 
Put it in the LostFocus event of the main form:
I'm not sure which text box is main, which is sub, so I'll psuedo code.
Just put in the correct names:

frmSub!BomDollarClose =
Iff[Form_BOMDollarAppend1].[BOMDollar]=[Me].[BOMDollar],Null,[Form_BOMDollarAppend1].[BomDollarEffec])

Hope this makes sense, if not, give me the following:
Name of Main Form
text box on main form where the data is to be replicated in the sub form
what you are comparing it to (text box, field, etc)
Name of Sub Form
name of text box on sub form where data goes.

neenmarie said:
I put the following in the DefaultValue of the Text Box called BOMDollarClose:

=IIf([Form_BOMDollarAppend1].[BOMDollar]=[Me].[BOMDollar],Null,[Form_BOMDollarAppend1].[BomDollarEffec])

The open form now shows #NAME? in the Text Box BOMDollarClose

What I need is - If BomDollar on the Main Form and BOMDollar on the subform
are equal, I'd like BOMDollarClose to remain an empty box (null). If they
are not equal, I'd like BOMDollarClose to equal BomDollarEffec on the main
form.

I was originally trying to do this in the event "got focus" for the text box
BOMDollarClose. I couldn't get it to work there either.
Any additional suggests?
Thank you

Klatuu said:
I'm having a little difficulty understanding exactly what you want to do.
You use the word "field", but I think in this case you mean Text Box. Sorry,
but you have to use the correct verbage to get understood. If, you mean text
box, then put your line of code in the DefaultValue property of the text box.
The IIF statement requires an "=" in this case.

= Iif(form_main.price = me.price,null,form_main.EffectiveDate)
Click to expand...
Click to expand...
 
I appreciate your help so much.
I still can't get it right. The info is as follows:

Main Form:
[BOMDollarAppend1]
Sub Form:
[BOMDollarChgClose]
Name of Main Form Text Box:
[BOMDollar] (Data to Compare to SubForm Data)
Sub Form Text Box is also :
[BOMDollar]
Sub Form Text Box to leave null if the two BOMDollar fields are equal or
Fill in if they are not:
[BOMDollarClose]
Main Form Text Box to replicate data from:
[BOMDollarEffec]

Klatuu said:
Put it in the LostFocus event of the main form:
I'm not sure which text box is main, which is sub, so I'll psuedo code.
Just put in the correct names:

frmSub!BomDollarClose =
Iff[Form_BOMDollarAppend1].[BOMDollar]=[Me].[BOMDollar],Null,[Form_BOMDollarAppend1].[BomDollarEffec])

Hope this makes sense, if not, give me the following:
Name of Main Form
text box on main form where the data is to be replicated in the sub form
what you are comparing it to (text box, field, etc)
Name of Sub Form
name of text box on sub form where data goes.

neenmarie said:
I put the following in the DefaultValue of the Text Box called BOMDollarClose:

=IIf([Form_BOMDollarAppend1].[BOMDollar]=[Me].[BOMDollar],Null,[Form_BOMDollarAppend1].[BomDollarEffec])

The open form now shows #NAME? in the Text Box BOMDollarClose

What I need is - If BomDollar on the Main Form and BOMDollar on the subform
are equal, I'd like BOMDollarClose to remain an empty box (null). If they
are not equal, I'd like BOMDollarClose to equal BomDollarEffec on the main
form.

I was originally trying to do this in the event "got focus" for the text box
BOMDollarClose. I couldn't get it to work there either.
Any additional suggests?
Thank you

Klatuu said:
I'm having a little difficulty understanding exactly what you want to do.
You use the word "field", but I think in this case you mean Text Box. Sorry,
but you have to use the correct verbage to get understood. If, you mean text
box, then put your line of code in the DefaultValue property of the text box.
The IIF statement requires an "=" in this case.

= Iif(form_main.price = me.price,null,form_main.EffectiveDate)

:

I have a form with a subform. The main form finds a group of records and
allows the user to add a new price and effective date.
The subform finds corresponding PNs' prices without a price closing date and
closes the old price. At least that is the premise.

When completing the new price info the main form's last field sets focus to
the sub form's closing price field. Upon 'got focus', the closing date field
on the sub form = the effective date on the main form.
All would seem well....and does work unless there was no price to start
with. So, if the user is entering a price for the first time, the new price
that was just entered is the only price with a null value in the closing date
field. So, the sub-form closes the new price that was just entered.

I would like to put something like this in the sub form closing price
field....
Iif(form_main.price = me.price,null,form_main.EffectiveDate) But, I don't
know how to get the syntax right. Can anyone help me.
Thx.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top