Syntax Error in CreateQueryDef

[holgi123]

Hi everyone,

in a previous thread I posted a question about changing a table reference within a CreateQueryDef code. I managed to do so by defining various string variables and than include an Iif-Clause within the CreateQueryDef which defines which str variable to pull.

Now I get an error message, which I can't seem to solve. any help would be much appreciated. I only pasted the relevant part of the code.

The CreateQueryDef is like this:
===================
Set qryNew = db.CreateQueryDef("QueryMaster", "SELECT [MasterTable].* , Iif ([MasterTable].[NewTyp] Alike " & "'NAM'" & ", " & strnam & ", Iif([MasterTable].[NewTyp] Alike " & "'OLD'" & ", " & strold & ", " & strnew & ")) ")
===================
For example strnam ( strold and strnew are defined similar with just a different table reference) is defined as this:
===================
strnam = "([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "LeaseAct]+[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "NoneLeaseAct]+[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "BacklogLeaseNoHold]+[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "BacklogNoneLeaseNoHold]) as " & [Forms]![MainSwitchboard]!cmdmonth.Value & "Achievment, " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Bud] <>0,[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Achievment]/[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Bud],0) as " & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach, " _
& " [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "LeaseAct] +[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "NoneLeaseAct]+[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "BacklogLeaseNoHold]+[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "BacklogNoneLeaseNoHold] as " & [Forms]![MainSwitchboard]!cmdmonth.Value & "MarketingBase, " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<0.6,0," _
& " IIf([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]>=0.6 And [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<0.7,[tblNAMRates].[From60to6999Rate], 0)) as " & [Forms]![MainSwitchboard]!cmdmonth.Value & "Rate into tblmaster " _
& " FROM [MasterTable], [tblNAMRates], [tblNewHireRates], [tblMarketingRates], [tblLeasingRates]; "
=====================
I think the problem is in the CreateQuery within the Iif-Clause and in the above example with the - Alike " & "'NAM'" & " - structure. Error says "syntax error, missing operator....".

Many thanks for any help on this.

 

[PHV]

Anyway, Alike is not a valid SQL operator.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[holgi123]

Hi PHV,

I played around with this already and tried and replace it with Like or = but still no luck.

Would you mind helping me further with an appropriate alternative to Alike?

Many thanks again

 

[holgi123]

Hi,

I'm still struggling with this and get really frustrated.

This is how far I got but still showing <<syntax error>>

========

strnam = "([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "LeaseAct]+[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "NoneLeaseAct]+[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "BacklogLeaseNoHold]+[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "BacklogNoneLeaseNoHold]) as " & [Forms]![MainSwitchboard]!cmdmonth.Value & "Achievment, " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Bud] <> 0,[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Achievment]/[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Bud],0) as " & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach, " _
& " [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "LeaseAct] +[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "NoneLeaseAct]+[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "BacklogLeaseNoHold]+[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "BacklogNoneLeaseNoHold] as " & [Forms]![MainSwitchboard]!cmdmonth.Value & "MarketingBase, " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<0.6,0," _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]>=0.6 And [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<0.7,[tblMarketingRates].[From60to6999Rate], " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]>=0.7 And [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<0.8,[tblMarketingRates].[From70to7999Rate], " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]>=0.8 And [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<0.9,[tblMarketingRates].[From80to8999Rate], " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]>=0.9 And [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<1,[tblMarketingRates].[From90to9999Rate], " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]>=1 And [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<1.1,[tblMarketingRates].[From100to10999Rate]," _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]>=1.1 And [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<1.3,[tblMarketingRates].[From110to12999Rate], " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]>=1.3 And [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<1.5,[tblMarketingRates].[From130to14999Rate], " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]>=1.5 And [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<1.6,[tblMarketingRates].[From150to15999Rate], " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]>=1.6 And [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<1.7,[tblMarketingRates].[From160to16999Rate], " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]>=1.7 And [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<2,[tblMarketingRates].[From170to19999Rate], " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]>=2 And [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]<2.5,[tblMarketingRates].[From200to24999Rate], " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach]>=2.5 And [MasterTable].[Typ] Like " & "'AM%'" & " Or [MasterTable].[Typ] Like " & "'T%'" & ", " _
& " [tblMarketingRates].[Above250Rate],[tblMarketingRates].[From200to24999Rate]))))))))))))) as " & [Forms]![MainSwitchboard]!cmdmonth.Value & "Rate, " _
& " [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Achievment]*[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Rate] as " & [Forms]![MainSwitchboard]!cmdmonth.Value & "MarketingPay, " _
& " [" & [Forms]![MainSwitchboard]!cmdmonth.Value & "LeaseAct]+[" & [Forms]![MainSwitchboard]!cmdmonth.Value & "BacklogLeaseNoHold] as " & [Forms]![MainSwitchboard]!cmdmonth.Value & "LeaseBase, " _
& " Iif([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "Reach] >= 0.6,(NZ([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "LeaseAct])+NZ([" & [Forms]![MainSwitchboard]!cmdmonth.Value & "BacklogLeaseNoHold]))*[tblLeasingRates].[AchieveRate],0) as " & [Forms]![MainSwitchboard]!cmdmonth.Value & "LeasePay " _



With db
Set qryNew = db.CreateQueryDef("QueryMaster", "SELECT [MasterTable].*, Iif ([MasterTable].[NewTyp] Like " & "'NAM'" & " , " & strnam & "," & strnew & ") into tblmaster from [MasterTable], [tblMarketingRates], [tblNamRates],[tblNewHireRates];")

End With
DoCmd.OpenQuery "QueryMaster", acViewNormal
===============

The variable strnew is exactly the same as strnam only refering to another table.
When I run the query I get an error and the set qryNew line is yellow. Upon hoovering above - strnam - I see the correct code, which I would think tells me, that it picks up the variable correctly. I would think this further tells me, that the error is in the other part, being the LIKE section from above.

As I'm desperately running out of time, I would appreciate it for anyone who can help me here.

Many thanks and best regards