If then help

[ChuckW]

Hi,

I have a query called YesterdayTRX that list the Customer
that came into our client and treatment code. We have
four treatment codes: BEA, BTA, FAC and MAS. There is a
separate table called TreatmentType that list these codes
and their the TreatmentNames. Using a cross tab query I
also created a summary of all of our customers and the
count of all treatment codes (including yesterdays)called
Coupon1. I then create some if then logic to identify all
people who had an MAS but not a FAC, a FAC but not an
MAS, a BTA but not a MAS and a BEA but not a FAC called
Coupon2. I created a field called SendCoupon and have
everyone who meets these conditions be assigned a value
of -1.

What I want to do now is to send offers to everyone who
satisfy these conditions. I have two coupons that are
JPEG files: FAC.JPEG and MAS.JPEG. I want to create a
letter using an Access Report that says something like:

----------------------------------------------------
Dear XXXXX,

We'd like to thank you for choosing us for your
[TreatmentName].

In appreciation we are sending you this voucher.
--------------------------------------------------

At the bottom of the letter one of the two types of
vouchers would appear. FAC.JPEG for people who had a MAS
or BEA and the MAS.JPEG for people who had a FAC or BTA.

For example, if someone came in for an MAS yesterday and
never had a facial - based on the Coupon2 query, they
would get a letter thanking them for coming in for the
massage and they would receive a coupon for a facial.

The JPEG file could print on the bottom of the Access
report as part of the letter. Can anyone help me with
how to do this one?

Thanks,

Chuck

 

[Graham Mandeno]

Hi Chuck

It sounds like you're most of the way there.

I suggest you modify your SendCoupon field slightly, so that it gets one
value (say 1) if a FAC coupon is required, and another value (say 2) if a
MAS is required.

Now you can add an empty image control to your letter report. In the Format
event procedure for the section containing the image, load the appropriate
picture.

Select case me.SendCoupon
case 1
imgCoupon.Picture = "C:\...\FAC.jpeg"
case 2
imgCoupon.Picture = "C:\...\MAS.jpeg
End select

In a report, unlike a form, you cannot refer to a field unless it is bound
to a control on your report, so add a hidden textbox named and bound to
SendCoupon.

 

[ChuckW]

Graham,

Thanks for your help. I have a few follow up questions
about placing the image on the report. I was thinking
about placing the jpegs in the Detail section of my
letter. When I click on the detail properties, do I
choose the Event Tab and the On format? If so, it looks
like you have VBA code. Do I click on Code Builder and
insert the VBA code into it? And the me.Sendcoupon -
what is that referring to?

Thanks,

Chuck

-----Original Message-----
Hi Chuck

It sounds like you're most of the way there.

I suggest you modify your SendCoupon field slightly, so that it gets one
value (say 1) if a FAC coupon is required, and another value (say 2) if a
MAS is required.

Now you can add an empty image control to your letter report. In the Format
event procedure for the section containing the image, load the appropriate
picture.

Select case me.SendCoupon
case 1
imgCoupon.Picture = "C:\...\FAC.jpeg"
case 2
imgCoupon.Picture = "C:\...\MAS.jpeg
End select

In a report, unlike a form, you cannot refer to a field unless it is bound
to a control on your report, so add a hidden textbox named and bound to
SendCoupon.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I have a query called YesterdayTRX that list the Customer
that came into our client and treatment code. We have
four treatment codes: BEA, BTA, FAC and MAS. There is a
separate table called TreatmentType that list these codes
and their the TreatmentNames. Using a cross tab query I
also created a summary of all of our customers and the
count of all treatment codes (including yesterdays) called
Coupon1. I then create some if then logic to identify all
people who had an MAS but not a FAC, a FAC but not an
MAS, a BTA but not a MAS and a BEA but not a FAC called
Coupon2. I created a field called SendCoupon and have
everyone who meets these conditions be assigned a value
of -1.

What I want to do now is to send offers to everyone who
satisfy these conditions. I have two coupons that are
JPEG files: FAC.JPEG and MAS.JPEG. I want to create a
letter using an Access Report that says something like:

----------------------------------------------------
Dear XXXXX,

We'd like to thank you for choosing us for your
[TreatmentName].

In appreciation we are sending you this voucher.
--------------------------------------------------

At the bottom of the letter one of the two types of
vouchers would appear. FAC.JPEG for people who had a MAS
or BEA and the MAS.JPEG for people who had a FAC or BTA.

For example, if someone came in for an MAS yesterday and
never had a facial - based on the Coupon2 query, they
would get a letter thanking them for coming in for the
massage and they would receive a coupon for a facial.

The JPEG file could print on the bottom of the Access
report as part of the letter. Can anyone help me with
how to do this one?

Thanks,

Chuck

.

 

[Graham Mandeno]

Hi Chuck

Yes, you choose the code builder in the OnFormat property cell. It will
open the VBA code window with a "skeleton" event procedure already created
for you, like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
| < cursor
End Sub

Insert your code where the cursor is flashing.

Me.SendCoupon refers to the hidden textbox I mentioned, bound to the
SendCoupon calculated field in your query.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Thanks for your help. I have a few follow up questions
about placing the image on the report. I was thinking
about placing the jpegs in the Detail section of my
letter. When I click on the detail properties, do I
choose the Event Tab and the On format? If so, it looks
like you have VBA code. Do I click on Code Builder and
insert the VBA code into it? And the me.Sendcoupon -
what is that referring to?

Thanks,

Chuck

-----Original Message-----
Hi Chuck

It sounds like you're most of the way there.

I suggest you modify your SendCoupon field slightly, so that it gets one
value (say 1) if a FAC coupon is required, and another value (say 2) if a
MAS is required.

Now you can add an empty image control to your letter report. In the Format
event procedure for the section containing the image, load the appropriate
picture.

Select case me.SendCoupon
case 1
imgCoupon.Picture = "C:\...\FAC.jpeg"
case 2
imgCoupon.Picture = "C:\...\MAS.jpeg
End select

In a report, unlike a form, you cannot refer to a field unless it is bound
to a control on your report, so add a hidden textbox named and bound to
SendCoupon.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I have a query called YesterdayTRX that list the Customer
that came into our client and treatment code. We have
four treatment codes: BEA, BTA, FAC and MAS. There is a
separate table called TreatmentType that list these codes
and their the TreatmentNames. Using a cross tab query I
also created a summary of all of our customers and the
count of all treatment codes (including yesterdays) called
Coupon1. I then create some if then logic to identify all
people who had an MAS but not a FAC, a FAC but not an
MAS, a BTA but not a MAS and a BEA but not a FAC called
Coupon2. I created a field called SendCoupon and have
everyone who meets these conditions be assigned a value
of -1.

What I want to do now is to send offers to everyone who
satisfy these conditions. I have two coupons that are
JPEG files: FAC.JPEG and MAS.JPEG. I want to create a
letter using an Access Report that says something like:

----------------------------------------------------
Dear XXXXX,

We'd like to thank you for choosing us for your
[TreatmentName].

In appreciation we are sending you this voucher.
--------------------------------------------------

At the bottom of the letter one of the two types of
vouchers would appear. FAC.JPEG for people who had a MAS
or BEA and the MAS.JPEG for people who had a FAC or BTA.

For example, if someone came in for an MAS yesterday and
never had a facial - based on the Coupon2 query, they
would get a letter thanking them for coming in for the
massage and they would receive a coupon for a facial.

The JPEG file could print on the bottom of the Access
report as part of the letter. Can anyone help me with
how to do this one?

Thanks,

Chuck

.