Printing different data in a report

[TonyF]

Hi,

I am fairly new to Access and have inherited a database which I must manage in my new post

I have a report (Rpt_LargeLabel)that is based on a query (Qry_LargeLabel)

I have been asked to change a field (type) that prints on the report to a different name if it is for a certain customer, but it must leave the underlying type in the table as it was originally.

Is it possible to do this using vba ?

I have tried using the following on the on open and on activate events in the report but it has no effect

Private Sub Report_Activate()
If Me.customer = "Multek" Then
Me.TYPE = "TEST"
End If

Any help greatly appreciated
Many thanks

 

[vbaInet]

Don't you have a CustomerID? You should be using that instead of the name.

You can perform that check in your query instead:

IIF([CustomerID] = 12345, "Test", "Something Else")

 

[TonyF]

Thanks for the reply vbaInet

Yes the customer field is actually a customer code eg XD-0060 which is unique to each customer it has just been named incorrectly i suppose

Where do i put the code in the query ?

I have tried criteria but it just brings back no data, I can create an expression but that doesn't change the original type value.

many thanks

 

[vbaInet]

In a new column in your query. Just place it in there.

 

[TonyF]

But that just creates a new field that I can then put on the report

and leaves the original value for TYPE the same, I need it to change the original type value to a new value if the customer = XD-0060

is that possible ?

 

[vbaInet]

That's is the idea. Preserve the original value, calculate the differences on the fly.

You don't save calculated values.

Replace your original control with this new field in your report.

 

[TonyF]

I see what you mean but I don't want both TYPES to print on the report

Just either the original or if the custumer = XD-0060 then the other type,

if I put the new control on the report and the customer doesn't = XD-0060

Won't the value be blank ? or am i missing something

Sorry if I come across as stupid but I am new to all this

 

[vbaInet]

if I put the new control on the report and the customer doesn't = XD-0060

Won't the value be blank ? or am i missing something

You haven't tried it so I would advise you give it a try.

Then read up on the IIF() function:

http://www.techonthenet.com/access/functions/advanced/iif.php

 

[TonyF]

Will do and thanks for your time, it is much appreciated

 

[vbaInet]

Let us know how it goes.

 

[TonyF]

Will do,

Thanks again

 

[TonyF]

Hi VbaInet,

This has worked perfectly,

I have used

TYPE2: IIf([NEWBOX.CUSTOMER]=XD-0060,"ES140",[NEWBOX.TYPE])

and this gives me ES140 if the customer is XD-0060 or just whatever the original type is if the customer is not XD-0060

and just replaced the control on the report.

For future reference would I still be able to use this method if they asked me to change the type for multiple customers or will that have to be a different solution ?

Many thanks again for your help with this

 

[vbaInet]

Good to hear!

If you have to do it for multiple customers, i.e. more than 2, then it will be better for you to create a separate table for that, link it up to this field with a LEFT JOIN. The first field will be the Customer and the second the replacement Type. Or you can create a function. However, if they are asking you to do this for multiple customers then I would question why they just don't want to update the Type instead.

By the way, you should use this:

TYPE2: IIf([NEWBOX.CUSTOMER]="XD-0060","ES140",[NEWBOX.TYPE])

 

[TonyF]

I will remember that in case they do ask in future,

I have put the missing quotation marks in now as well,

Cheers, have a great weekend

 

[vbaInet]

You too Tony!