Change Table in CreateQueryDef Object

[holgi123]

Hi there,

I would like to get some help on the following problem:

I have some code that allows an administrator to calculate commission. She selects the month and the system does the rest. So far so good. Now they changed the commission plan and I have 2 tables with 2 rate structures - basically the old reps and the new reps.

So far I created a Query via

========= only partial code ==============
Dim db As Database
Dim qryNew As QueryDef
Set db = CurrentDb
With db
Set qryNew = db.CreateQueryDef("QueryMaster", "SELECT [MasterTable].*, " _
& " IIf([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]>=0.6 And [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<0.7,[tblMarketingRates].[From60to6999Rate] FROM [MasterTable], [tblNAMRates], [tblNewHireRates], [tblMarketingRates], [tblLeasingRates]; ")
End With
============ only partial code ===========

Now the question is this:

I have a field that would determine if someone is an "old plan member" or "new plan member" so I can identify this. But I had so far no luck in changing the reference of [tblMarketingRate].[From60to69999Rate] to for example to [tblNewHireRates].[From60t06999Rate] . How would I do this? I tried to insert an If-Clause above Set Db section and defined a string to a different table within the Select Statement but this did not work and got me some strange error message.

Any help would be much appreciated, without re-writing the entire thing.

Many thanks and best regards

 

[JerryKlmns]

And where is that field that would determine if someone is an "old plan member" or "new plan member"?

Can`t you use the same old table to hold old and new representatives? One way would be to

Table structure
-------------------------
OldRepFee | NewRepFee
6% | 0%
No value here| 4%

Then your fee could be RepFee = Nz([OldRepFee])+ Nz([NewRepFee])