Changing WHERE statement with VBA

[retro]

I have a form, frmMailShot, which has an option group, grpMailShotType and two command buttons - cmdLaunchWord and cmdLaunchReport.

The option group is used to select whether the mail shot will be the initial mail shot (value = 1) or a follow-up (value = 2). The command buttons select whether you want to open a pre-written Word mail merge document or a label report. Both will have their results taken from a query, qryMailShot.

The query should differ in the WHERE statement depending on the value of the option group as follows:

If grpMailShotType = 1

WHERE (((tblClients.MergeNo)=0));

If grpMailShotType = 2

WHERE (((tblClients.MergeNo)=1) AND ((tblClients.Response1)=False));

This ensures that the only clients selected are either those who have not been contacted previously, or those who have only been sent the initial mail shot.

How do I ensure that the WHERE statement will have changed accordingly when I click either command button?

Thanks in advance for any assistance offered!

 

[namliam]

You can do this 2 ways...

1) Write your SQL in code
2) Make a smart SQL statement...

I think in this case 2 is preferable.

I am not 100% certain on this but something like:

WHERE (Forms("Yourform")!grpMailShotType = 1 and tblClients.MergeNo=0)  
   or (Forms("Yourform")!grpMailShotType = 2 and tblClients.MergeNo=1 AND tblClients.Response1=False);

Good luck!

 

[retro]

Thanks for the quick response, namliam!

I'm having problems here as a colleague has pinched my mouse and I had to replace it with a Wacom tablet (not the best thing for Access!), but on the brief glance I managed to have before windows started shooting all over the place due to an over-sensitive pen, it seemed to do the trick just fine!

Thanks again!