Syntax error: missing operator in query expression

[cmarucco]

Does anyone see the problem in the syntax below?!?

(Other than an overly complicated IIF statement that should be approached a bit differently)...but I'm not a very advanced user.



SELECT Inpatient_Item_Level.Patient_Account_Number, Inpatient_Item_Level.ID, IIF([Date_of_Service] BETWEEN '01012008' AND '03312008', NZ(AddendumB_2008_January.[Payment Rate],0) AS [Fee_Schedule_Amount], IIF([Date_of_Service] BETWEEN '04012008' AND '06302008', NZ(AddendumB_2008_April.[Payment Rate],0) AS [Fee_Schedule_Amount], IIF([Date_of_Service] BETWEEN '07012008' AND '09302008', NZ(AddendumB_2008_July.[Payment Rate],0) AS [Fee_Schedule_Amount], IIF([Date_of_Service] BETWEEN '10012008' AND '12312008', NZ(AddendumB_2008_October.[Payment Rate],0) AS [Fee_Schedule_Amount], '0' AS [Fee_Schedule_Amount])))), [Units]*[Fee_Schedule_Amount] AS Fee_Schedule_Total

Thanks!

 

[ByteMyzer]

Perhaps you could describe for us exactly what it is that you are attempting to do?

 

[cmarucco]

Basically...

I have a table 'Items' that contains many variables, two of which are 'Code' and 'Date_of_Service'.

I have four tables that contain corresponding 'codes' and a 'payment rate' for each. The four tables are broken into 3-month segments: Jan-Mar, April-June, July-Sept, Oct-Dec...each table contains different 'payment rates' for the 'codes'.

I want to pull the 'payment rate' from the correct table based on 'Date_of_Service' in the original table, and INSERT INTO another table.

I'll post the complete query SQL I've come up with below to be more clear, with the nested IIF statement in red. So far, I've only been getting errors, but conceptually you can get the picture. I'm sure theres a better way to do this, but like I said before I'm not too advanced with access/database tools. Thanks again!

INSERT INTO Temp_Table_Inpatient_Item_Level ( Patient_Account_Number, ID, Fee_Schedule_Amount, Fee_Schedule_Total )

SELECT Inpatient_Item_Level.Patient_Account_Number, Inpatient_Item_Level.ID, IIF([Date_of_Service] BETWEEN '01012008' AND '03312008', NZ(AddendumB_2008_January.[Payment Rate],0) AS [Fee_Schedule_Amount], IIF([Date_of_Service] BETWEEN '04012008' AND '06302008', NZ(AddendumB_2008_April.[Payment Rate],0) AS [Fee_Schedule_Amount], IIF([Date_of_Service] BETWEEN '07012008' AND '09302008', NZ(AddendumB_2008_July.[Payment Rate],0) AS [Fee_Schedule_Amount], IIF([Date_of_Service] BETWEEN '10012008' AND '12312008', NZ(AddendumB_2008_October.[Payment Rate],0) AS [Fee_Schedule_Amount], '0' AS [Fee_Schedule_Amount])))), [Units]*[Fee_Schedule_Amount] AS Fee_Schedule_Total

FROM (((Inpatient_Account_Level INNER JOIN (Inpatient_Item_Level INNER JOIN AddendumB_2008_October ON Inpatient_Item_Level.CPT = AddendumB_2008_October.[HCPCS Code]) ON Inpatient_Account_Level.Patient_Account_Number = Inpatient_Item_Level.Patient_Account_Number) INNER JOIN AddendumB_2008_January ON Inpatient_Item_Level.CPT = AddendumB_2008_January.[HCPCS Code]) INNER JOIN AddendumB_2008_July ON Inpatient_Item_Level.CPT = AddendumB_2008_July.[HCPCS Code]) INNER JOIN AddendumB_2008_April ON Inpatient_Item_Level.CPT = AddendumB_2008_April.[HCPCS Code];
​

 

[ByteMyzer]

I take it that Date_of_Service is a text field? In that case, try this:

INSERT INTO Temp_Table_Inpatient_Item_Level
    ( Patient_Account_Number, ID, Fee_Schedule_Amount, Fee_Schedule_Total )
SELECT Inpatient_Item_Level.Patient_Account_Number, Inpatient_Item_Level.ID,
Switch([Date_of_Service] BETWEEN '01012008' AND '03312008',
    NZ(AddendumB_2008_January.[Payment Rate],0),
    [Date_of_Service] BETWEEN '04012008' AND '06302008',
    NZ(AddendumB_2008_April.[Payment Rate],0),
    [Date_of_Service] BETWEEN '07012008' AND '09302008',
    NZ(AddendumB_2008_July.[Payment Rate],0),
    [Date_of_Service] BETWEEN '10012008' AND '12312008',
    NZ(AddendumB_2008_October.[Payment Rate],0),
    True, 0) AS [Fee_Schedule_Amount],
[Units]*[Fee_Schedule_Amount] AS [Fee_Schedule_Total]
FROM ((((Inpatient_Account_Level INNER JOIN Inpatient_Item_Level
    ON Inpatient_Account_Level.Patient_Account_Number
    = Inpatient_Item_Level.Patient_Account_Number)
LEFT JOIN AddendumB_2008_January ON Inpatient_Item_Level.CPT
    = AddendumB_2008_January.[HCPCS Code])
LEFT JOIN AddendumB_2008_April ON Inpatient_Item_Level.CPT
    = AddendumB_2008_January.[HCPCS Code])
LEFT JOIN AddendumB_2008_July ON Inpatient_Item_Level.CPT
    = AddendumB_2008_January.[HCPCS Code])
LEFT JOIN AddendumB_2008_October ON Inpatient_Item_Level.CPT
    = AddendumB_2008_January.[HCPCS Code];

 

[cmarucco]

Thanks ByteMyzer! I'll try it out...And read up on the Switch command!

 

[gemma-the-husky]

SELECT Inpatient_Item_Level.Patient_Account_Number, Inpatient_Item_Level.ID, IIF([Date_of_Service] BETWEEN '01012008' AND '03312008', NZ(AddendumB_2008_January.[Payment Rate],0) AS [Fee_Schedule_Amount], IIF([Date_of_Service] BETWEEN '04012008' AND '06302008', NZ(AddendumB_2008_April.[Payment Rate],0) AS [Fee_Schedule_Amount], IIF([Date_of_Service] BETWEEN '07012008' AND '09302008', NZ(AddendumB_2008_July.[Payment Rate],0) AS [Fee_Schedule_Amount], IIF([Date_of_Service] BETWEEN '10012008' AND '12312008', NZ(AddendumB_2008_October.[Payment Rate],0) AS [Fee_Schedule_Amount], '0' AS [Fee_Schedule_Amount])))), [Units]*[Fee_Schedule_Amount] AS Fee_Schedule_Total

when wrapping variables in this way, you need to be aware of this

1) sql can be uncompromising of spacing errors - put extra spaces between identifiers operators etc to make sure

2) values/variables have to be wrapped properly
NUMBER
so a number can be used as a number eg "testvalue=" & 4

TEXT
but a string has to be wrapped in " marks eg testtext="whatever"
-however you cant just use " directly, to get a ", you have to use "", which makes formatting tricky - to get around this i use chr(34), so you end "testtext= " &chr(34) & "whatever" & chr(34)

DATE
and a date has wrapped in # marks eg "testtext= #" & "31/12/08" & "#"


i have not checked in detail, but I noticed you did not show the dates in this way, at least.

 

[cmarucco]

TEXT
but a string has to be wrapped in " marks eg testtext="whatever"
-however you cant just use " directly, to get a ", you have to use "", which makes formatting tricky - to get around this i use chr(34), so you end "testtext= " &chr(34) & "whatever" & chr(34)
quote]


I'm a little confused by this? I changed the code to what is copy/pasted below...but are you saying that for my date ranges (which are text variables), I can't just put " around them? Do I need to change my formatting somehow?

If so, can you show me an example?! Thanks!


INSERT INTO Temp_Table_Inpatient_Item_Level ( Medical_Record_Number, Patient_Account_Number, ID, Fee_Schedule_Amount, Fee_Schedule_Total )

SELECT Inpatient_Account_Level.Medical_Record_Number, Inpatient_Item_Level.Patient_Account_Number, Inpatient_Item_Level.ID, SWITCH([Date_of_Service] BETWEEN “01/01/2008” AND “03/31/2008”, NZ(AddendumB_2008_January.[Payment Rate],0) AS [Fee_Schedule_Amount], [Date_of_Service] BETWEEN “04/01/2008” AND “06/30/2008”, NZ(AddendumB_2008_April.[Payment Rate],0) AS [Fee_Schedule_Amount], [Date_of_Service] BETWEEN “07/01/2008” AND “09/30/2008”, NZ(AddendumB_2008_July.[Payment Rate],0) AS [Fee_Schedule_Amount], [Date_of_Service] BETWEEN “10/01/2008” AND “12/31/2008”, NZ(AddendumB_2008_October.[Payment Rate],0) AS [Fee_Schedule_Amount]), [Units]*[Fee_Schedule_Amount] AS Fee_Schedule_Total

FROM ((((Inpatient_Patient_Level INNER JOIN (Inpatient_Account_Level INNER JOIN Inpatient_Item_Level ON Inpatient_Account_Level.Patient_Account_Number = Inpatient_Item_Level.Patient_Account_Number) ON Inpatient_Patient_Level.Medical_Record_Number = Inpatient_Account_Level.Medical_Record_Number) INNER JOIN AddendumB_2008_October ON Inpatient_Item_Level.CPT = AddendumB_2008_October.[HCPCS Code]) INNER JOIN AddendumB_2008_April ON Inpatient_Item_Level.CPT = AddendumB_2008_April.[HCPCS Code]) INNER JOIN AddendumB_2008_January ON Inpatient_Item_Level.CPT = AddendumB_2008_January.[HCPCS Code]) INNER JOIN AddendumB_2008_July ON Inpatient_Item_Level.CPT = AddendumB_2008_July.[HCPCS Code];

 

[ByteMyzer]

Hello, cmarucco,

gemma's post is in reference to your earlier post. If the Date_of_Service field in your table is indeed a Text field, the SQL I prodived should work for you.

Hello, gemma-the-husky,

No disrespect intended, but while your post contains valid points, well worth following, in the context of this thread I think it only served to confuse cmarucco.

 

[cmarucco]

Hello, gemma-the-husky,

No disrespect intended, but while your post contains valid points, well worth following, in the context of this thread I think it only served to confuse cmarucco

Not hard to do! Thanks all for the help; and ByteMyzer, with a few tweaks to the date fields I was able to get the code you pasted to work PERFECTLY!

Thanks again,
cmarucco.

 

[ByteMyzer]

You're very welcome, I'm glad it worked for you.