New at Access and need help with an expression

[iplay10us2]

I have a form that is a combination of 2 tables: Bill To and Ship To. In my Bill To table, I have a column "Bill To =Ship To" and that appears on the form. What I am trying to do is build an expression that would fill in the shipping address, shipping city, etc automatically when Y is the input in "Bill to = Ship To.

I am not sure if I would use and if expression or what.

Any help is greatly appreciated. Thanks.

[BradHodge]

I'm not sure exactly how your form is set-up, but here is an example of how it could be done. If you have the Parent form ("frmBillShip") recordsource as "Bill To" and there is a subform ("subShipTo") with the recordsource of "Ship To", and there is a checkbox called "chkBillShip", then your code could look something like this...

Private Sub chkBillShip_AfterUpdate()
If Me.chkBillShip = True Then
Forms![frmBillShip]![subShipTo].Form![ShipName] = Me.BillName
Forms![frmBillShip]![subShipTo].Form![ShipAdd] = Me.BillAdd
Forms![frmBillShip]![subShipTo].Form![ShipCity] = Me.BillCity
Forms![frmBillShip]![subShipTo].Form![ShipState] = Me.BillState
Else:
DoCmd.SetWarnings False
Forms![frmBillShip]![subShipTo].SetFocus
RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
End If
End Sub

Hope that gets you going.

Brad.

[iplay10us2]

OK...I really don't understand that much of what you said, sorry. Let me try to explain what I have and am trying to do a little bit better.

I have a table called "Customers and it includes billing address, etc and a column "billto=shipto" where i can enter a "y" if it does. I have another table "ShipTo and it includes shipping address, etc. The tables are linked by a customer ID. I created a form and a query by combing the 2 tables. What I am trying to do is have the shipping address,etc filled in automatically when the "billto=shipto" is marked with a "y." I don't know anything about code, but I was thinking I could build a relationship and put it as a default value in the design view of either the form or the query. Something along the lines of If [customers!billto=shipto] =y then [ShipTo!shippingaddress] = [Customers!billingaddress]

I am a little more comfortable with building this kind of expression but I know I am not bracketing where I need to. Will anything like this work for me and where do I put it.

Sorry for my ignorance.

[mlcampeau]

OK...I really don't understand that much of what you said, sorry. Let me try to explain what I have and am trying to do a little bit better.

I have a table called "Customers and it includes billing address, etc and a column "billto=shipto" where i can enter a "y" if it does. I have another table "ShipTo and it includes shipping address, etc. The tables are linked by a customer ID. I created a form and a query by combing the 2 tables. What I am trying to do is have the shipping address,etc filled in automatically when the "billto=shipto" is marked with a "y." I don't know anything about code, but I was thinking I could build a relationship and put it as a default value in the design view of either the form or the query. Something along the lines of If [customers!billto=shipto] =y then [ShipTo!shippingaddress] = [Customers!billingaddress]

I am a little more comfortable with building this kind of expression but I know I am not bracketing where I need to. Will anything like this work for me and where do I put it.

Sorry for my ignorance.

If you have a field named billto=shipto, you may want to change that. The equal sign might or probably will give you troubles. Try BillToEqualsShipTo or something instead. Then in your query you could have
ShippingAddress: IIF([Customers.BillToEqualsShipTo]="Y", [Customers.BillingAddress], [ShipTo.ShippingAddress])
Meaning, if [Customers.BillToEqualsShipTo]="Y" then [Customers.BillingAddress] else [ShipTo.ShippingAddress]

[iplay10us2]

If you have a field named billto=shipto, you may want to change that. The equal sign might or probably will give you troubles. Try BillToEqualsShipTo or something instead. Then in your query you could have
ShippingAddress: IIF([Customers.BillToEqualsShipTo]="Y", [Customers.BillingAddress], [ShipTo.ShippingAddress])
Meaning, if [Customers.BillToEqualsShipTo]="Y" then [Customers.BillingAddress] else [ShipTo.ShippingAddress]

Thanks. I will change the field and get rid of the "=" . One more question: What is that character before the IF in your formula? It looks like a capital I, but I am not sure.

[mlcampeau]

Thanks. I will change the field and get rid of the "=" . One more question: What is that character before the IF in your formula? It looks like a capital I, but I am not sure.

it is an i so it is iif. Not sure why, but that's how access wants if written

[iplay10us2]

it is an i so it is iif. Not sure why, but that's how access wants if written

Thanks so much for all of your help.

[NeoPa]

it is an i so it is iif. Not sure why, but that's how access wants if written

Why is IIf() so called I wonder? Good question.

Nice going anyway MLCampeau. Your status change is pending by the way - we haven't forgotten about you :)