Code to Select an Alternate Field

[jwolsr]

Being new to Access I’m not aware of a query, control source expression or other method to select an alternate field based on the value of a third field so I assume code is required. I need this field selection operation for input to a report.

Each record has a primary field (Customer), an alternate field (Bill To) and a “group” field (Buy Group). IF the group field is blank then the alternate field is blank also. In other words, there is no Bill To if the record does not have a Buy Group. By default, the report (actually an invoice) must show the Customer and associated address, city, etc. if the Buy Group is blank. What is needed however is the replacement of the Customer field if and only if, the record has a Buy Group field with a value. If the Buy Group field has a value then there will be a Bill To field with a value. Then the Customer value must be replaced with the Bill To value and its associated address, city, etc.

The RAW logic is simple:
IF (ISNULL[Buy Group]) THEN
[Customer]
ELSE
[Bill To]
END IF
Obviously there’s an Address, and City-State-Zip that is tied to Customer and a different Address and City-State-Zip that is tied to Bill To.

The report/invoice is fed from a query which has the Customer, Buy Group and Bill To fields already determined. I assume this macro should run upon opening the report/invoice for a chosen record. I’m also not sure where to install the macro in order to implement/run it.

I’m using Access 2010 but I started with an Access 2007 template.

I hope someone will show me the code necessary to do this. Thanks

 

[CazB]

If I'm understanding your question properly, you don't need a macro. In the query source of your report, you will need to create a few new fields, something like

InvoiceTo: iif(isnull([buy group]),[customer],[bill to])
InvoiceAddress: iif(isnull([buy group]),[customer address],[bill to address])
InvoiceZip: iif(isnull([buy group]),[customerzip],[bill to zip])

(obviously, substitute your field names as appropriate)

and then use those fields in your report

Quick question, though - why are you storing two separate address fields, and two separate Zip code fields, if you are only ever using one of them on the invoice? If you are only using one of them on each record, you only need one of them in your table...

 

[jwolsr]

CazB:

Thank you for your recommendation and quick response. I sincerely appreciate your taking time to help.

Being new to Access I had to study your solution for a while but I now think I have an understanding and can try your suggestion. I'm glad you corrected my ignorance of needing a macro for this issue. My query source for the invoice (report) contains 7 tables so I'll have to carefully study where and how to add the new fields you suggest. I have the DB split and running on our internal network so I'll have to reassemble the front end and back end to experiment with.

The reason for the two different name, address, city-state-zip fields is that many of our customers belong to one of three different Buy Groups all with different addresses while others get invoiced directly. The Buy Group member's invoices go directly to the Buy Group but... we also need identical invoice data to be retained with the customer for reporting reasons AND because each customer has a different Sales Rep (who gets commissions based on customer invoice data). Sorry to bore you with a lengthy answer of your question.

I'll surely let you know as soon as I figure out how to implement your suggested solution. Thanks again CazB.

CazB I have done my best to make the changes you suggested. I added a field in the Customer table “Buying Group” to relate each customer to a Buying Group, or blank (null). Buy Group is the name of the table with 7 fields: Buying Group ID (KEY), Buying Group (name), Bill To, Bill Address, Bill City, Bill State, Bill Zip. I added the new fields to the Invoice (report) as you suggested and modified the SQL. Here it is:

SELECT Orders.[Order ID], Orders.[Ship Name], Orders.[Ship Address], Orders.[Ship City], Orders.[Ship State/Province], Orders.[Ship ZIP/Postal Code], Orders.[Ship Country/Region], Orders.[Customer ID], IIf(IsNull([Buying Group]),[Company],[Bill To]) AS InvoiceTo, IIf(IsNull([Buying Group]),[Address],[Bill Address]) AS InvoiceAddress, IIf(IsNull([Buying Group]),[City]+", "+[State]+" "+[Zip/Postal Code],[Bill City]+", "+[Bill State]+[Bill Zip]) AS InvoiceZip, Customers.EmployeeID AS Salesperson, Orders.[Order Date], Orders.[Shipped Date], Shippers.Company AS [Shipper Name], [Order Details].[Product ID], Products.ID AS [Product ID], [Order Details].[Unit Price], [Order Details].Quantity, [Order Details].Discount, CCur(Nz([Unit Price]*[Quantity]*(1-[Discount]),0)/100)*100 AS ExtendedPrice, Orders.[Shipping Fee], Products.[Product Name], Customers.EmployeeID, [Order Details].[Purchase Order], Products.[UPC Code]
FROM ((Shippers RIGHT JOIN (Customers RIGHT JOIN (Orders LEFT JOIN [Employees Extended] ON Orders.[Employee ID] = [Employees Extended].ID) ON Customers.ID = Orders.[Customer ID]) ON Shippers.ID = Orders.[Shipper ID]) LEFT JOIN ([Order Details] LEFT JOIN Products ON [Order Details].[Product ID] = Products.ID) ON Orders.[Order ID] = [Order Details].[Order ID]) LEFT JOIN [Buy Group] ON Customers.[Buying Group] = [Buy Group].[Buying Group];

When I attempt to print the Invoice (report) I get: ERROR: The specified field ‘[Buying Group]’ could refer to more than one table listed in the FROM clause of your SQL statement.

Any ideas where I goofed??? Incidentally I intend to clean up the names to eliminate spaces as suggested by Mihail. Also, why did the ';' appear at the end of the SQL? I didn't put it there.

 

[Mihail]

In the report create a new text box with this control source:
=NZ([Buy Group],[Bill To])

By the way: Stop to use spaces or special characters in the names.

 

[jwolsr]

Mihail I thank you for your suggestion and I will definitely look into it whenever I get the issue resolved as I think the "NZ()" is shorter/quicker that the "Iif()"

Also, I'm learning and will avoid using spaces.

I appreciate your help.

 

[CazB]

Glad to have been of some use

The problem you're having with the Error is that when you have two tables containing the same field name, it doesn't know which table to look at!

So, in IIf(IsNull([Buying Group])

you need to specify which table it's from - ie should it be

[customers].[Buying Group] or [buy group].[Buying Group]

in other words, should it be:

IIf(IsNull([customers].[Buying Group]) or IIf(IsNull[buy group].[Buying Group])

HTH

Oh, and don't worry about the ";" at the end, it just marks the end of the SQL, Access adds it for you if you don't do it yourself

 

[jwolsr]

CazB... IT WORKED! I'm sincerely grateful to you for taking time to educate me. It must be frustrating to deal with dummies like me. However, I have learned much much more than than to simply get this DB working and I appreciate it.
You're a very generous person.
Thanks
jw

 

[CazB]

Lol - we're all learning, I think I ask at least as many questions as I answer, if not more