If [FIELD] equals X then enter "TEST", If [FIELD] does not equal X then lookup ....

[tanyapeila]

I'm hoping someone can help me out.


I have attached a copy of a report.
I need to create a query (I think) to add to this report.

Here is what I would like it to do.

If the [CARRIER NAME] is equal to the [SCAC] then I would like it to enter "[SCAC] ~ Same as Consignee"
If the [CARRIER NAME] is not equal to the [SCAC] then I would like it to look up the [ConsigneeName], [Address1], etc from a table and enter "[SCAC] ~ [ConsigneeName], [Address 1]

All of the tables these fields are in are related properly just not sure how to set this up.

Thanks!

[orange]

What is the recordsource of the Form?
Could you post the associated SQL?
Please explain SCAC, and where exactly do you want to enter the text strings?

[tanyapeila]

Oh Orange my friend!!!
So the record source is a Query pulling records where [SELECT] is true, basically a check box selecting which records to include on the report.

SCAC - This is basically a transportation company code. Each transportation company has a unique code. This specific report is for Germany shipments. And the text strings would go where it says "(9) Also Notify"

Thank you in advance for your help

[orange]

Please post the SQL of the recordsource.

[tanyapeila]

Oops sorry forgot that part

SELECT OUTBOUND.[SELECT], OUTBOUND.SHIPPER, OUTBOUND.GBL, OUTBOUND.RDD, OUTBOUND.GROSS, OUTBOUND.CUBE, OUTBOUND.[PCS X OF], OUTBOUND.[TOTAL PCS], OUTBOUND.SCAC, OUTBOUND.REMARKS, OUTBOUND.[CONT#], OUTBOUND.VESSEL, OUTBOUND.VOYAGE, OUTBOUND.TO, OUTBOUND.FROM, OUTBOUND.[SEAL#], OUTBOUND.ETD, OUTBOUND.ETA, OUTBOUND.[CUT OFF], OUTBOUND.SIZE, OUTBOUND.SHIPLINE, OUTBOUND.[ALSO NOTIFY], OUTBOUND.[BK#], OUTBOUND.NET, [INTERNATIONAL CARRIER].[CARRIER NAME], [Copy Of Consignee].ConsigneeName, [CONSIGNEE ADDRESSES].[Address 1], [CONSIGNEE ADDRESSES].[Address 2], [CONSIGNEE ADDRESSES].[Address 3], [CONSIGNEE ADDRESSES].City, [CONSIGNEE ADDRESSES].State, [CONSIGNEE ADDRESSES].Field1, [CONSIGNEE ADDRESSES].PostalCode, [CONSIGNEE ADDRESSES].WorkPhone, OUTBOUND.[ETA PORT], OUTBOUND.[ETD PORT]
FROM ((OUTBOUND INNER JOIN [INTERNATIONAL CARRIER] ON OUTBOUND.SCAC = [INTERNATIONAL CARRIER].SCAC) INNER JOIN [Copy Of Consignee] ON (OUTBOUND.TO = [Copy Of Consignee].Country) AND (OUTBOUND.SCAC = [Copy Of Consignee].SCAC)) LEFT JOIN [CONSIGNEE ADDRESSES] ON [Copy Of Consignee].ConsigneeName = [CONSIGNEE ADDRESSES].ConsigneeName
WHERE (((OUTBOUND.[SELECT])=True));

[orange]

My first thought was to use an immediate iif statement, But I don't know how your recordsource fields map to your form.
Here's info on IIF
Which field in the recordsource goes to which field in the report?