I am getting an error message "near mileage". Any ideas? This looks like the last big problem with this procedure. WHERE qryFieldVoucherPersonnel.PersNum=[FDMI] AND tblCurrentReportStatus.PRODStatus='Report received' AND tblFDMIVouchers.PaidStatus=0 Or tblFDMIVouchers.PaidStatus Is Null AND tblAutopsy.ExamType Not Like 'field*' OR qryFieldVoucherPersonnel.PersNum=[FDMI] AND tblCurrentReportStatus.PRODStatus='Report received' AND tblFDMIVouchers.PaidStatus=0 Or tblFDMIVouchers.PaidStatus Is Null AND tblAutopsy.ExamType Like 'field*' AND tblCurrentReportStatus.FieldExternalStatus='Report received' [mileage]*[qryMileageFee.Fee] AS [Mileage Paid], Gee

Hi Great ! You need to fix your query; Your [Mileage Paid] calculation should be in you SELECT list like show below; SELECT [mileage]*[qryMileageFee.Fee] AS [Mileage Paid] Your WHERE clause should be something like below; WHERE qryFieldVoucherPersonnel.PersNum=[FDMI] AND tblCurrentReportStatus.PRODStatus='Report received' AND tblFDMIVouchers.PaidStatus=0 Or tblFDMIVouchers.PaidStatus Is Null AND tblAutopsy.ExamType Not Like 'field*' OR qryFieldVoucherPersonnel.PersNum=[FDMI] AND tblCurrentReportStatus.PRODStatus='Report received' AND tblFDMIVouchers.PaidStatus=0 Or tblFDMIVouchers.PaidStatus Is Null AND tblAutopsy.ExamType Like 'field*' AND tblCurrentReportStatus.FieldExternalStatus='Report received' Please let me know if this helps. Hopefully i have answered you correctly. Thanks, Hasham Niaz

I appreciate your response. Now I am getting this message: Msg 207, Level 16, State 1, Procedure prcFieldVouchersRD, Line 88 Invalid column name 'mileage'. Msg 207, Level 16, State 1, Procedure prcFieldVouchersRD, Line 88 Invalid column name 'qryMileageFee.Fee'. Gee

Hi Great ! Can you post the whole query which you are trying to execute. Thanks, Hasham Niaz

Here it is. I really appreciate this help! SET NOCOUNT ON SELECT qryFieldVoucherPersonnel.PersNameLast, qryFieldVoucherPersonnel.PersNum, tblCurrentReportStatus.CaseNum, tblCurrentReportStatus.PRODStatus, tblFDMIVouchers.PaidStatus, tblCase.FieldInvestigator, tblCase.PrnByDate, tblFDMIVouchers.Mileage, tblCase.CaseType, tblAutopsy.ExamType, qryFieldVouchers.mileage, CASE WHEN ExamType like 'No%' THEN NewJTFee WHEN ExamType Is Null THEN NewAutoFee ELSE ExamType End AS [Case Paid], tblCase.FDMI, tblCase.NameLast, tblCase.NameFirst, tblCase.NameMiddle, qryFieldVoucherPersonnel.PersAddr, [PersCity] & ", " & [PersState] & " " & [PersZip] AS CSZ, tblFDMIVouchers.PaidStatusDate, tblCurrentReportStatus.PRODStatusDate, [PersNameLast]+[PersNum] AS GroupFDMI, CASE WHEN [PrnByDate]<'10/1/2006' THEN 54.3 ELSE 65.16 END AS [NewJTFee], CASE WHEN [PrnByDate]<'10/1/2006' THEN 76.02 WHEN [PrnByDate] Between '10/1/2006' And '8/1/2007' THEN 86.88 ELSE 103.17 END AS [NewFldFee], CASE WHEN [PrnByDate]<'10/1/2006' THEN 76.02 ELSE 86.88 END AS [NewAutoFee], tblCurrentReportStatus.FieldExternalStatus FROM qryMileageFee, qryFieldVouchers, qryJTFee, qryFieldFee, qryFieldVoucherPersonnel, tblCurrentReportStatus INNER JOIN tblFDMIVouchers ON tblCurrentReportStatus.CaseNum Like tblFDMIVouchers.CaseNum INNER JOIN tblCase ON tblCurrentReportStatus.CaseNum Like tblCase.CaseNum INNER JOIN tblAutopsy ON tblCurrentReportStatus.CaseNum Like tblAutopsy.CaseNum WHERE qryFieldVoucherPersonnel.PersNum Like [FDMI] AND tblCurrentReportStatus.PRODStatus Like'Report received' AND tblFDMIVouchers.PaidStatus Like '0' Or tblFDMIVouchers.PaidStatus Is Null AND tblAutopsy.ExamType Not Like 'field*' OR qryFieldVoucherPersonnel.PersNum Like [FDMI] AND tblCurrentReportStatus.PRODStatus Like 'Report received' AND tblFDMIVouchers.PaidStatus Like '0' Or tblFDMIVouchers.PaidStatus Is Null AND tblAutopsy.ExamType Like 'field*' AND tblCurrentReportStatus.FieldExternalStatus='Report received' SELECT [mileage]*[Fee] AS [Mileage Paid] Gee

Hi Great ! You may get the desired output using below query; SELECT qryfieldvoucherpersonnel.persnamelast, qryfieldvoucherpersonnel.persnum, tblcurrentreportstatus.casenum, tblcurrentreportstatus.prodstatus, tblfdmivouchers.paidstatus, tblcase.fieldinvestigator, tblcase.prnbydate, tblfdmivouchers.mileage, tblcase.casetype, tblautopsy.examtype, qryfieldvouchers.mileage, CASE WHEN examtype LIKE 'No%' THEN newjtfee WHEN examtype IS NULL THEN newautofee ELSE examtype END AS [Case Paid], tblcase.fdmi, tblcase.namelast, tblcase.namefirst, tblcase.namemiddle, qryfieldvoucherpersonnel.persaddr, [perscity] & ",, " & [persstate] & " " & [perszip] AS CSZ, tblfdmivouchers.paidstatusdate, tblcurrentreportstatus.prodstatusdate, [persnamelast] + [persnum] AS GroupFDMI, CASE WHEN [prnbydate] < '10/1/2006' THEN 54.3 ELSE 65.16 END AS [NewJTFee], CASE WHEN [prnbydate] < '10/1/2006' THEN 76.02 WHEN [prnbydate] BETWEEN '10/1/2006' AND '8/1/2007' THEN 86.88 ELSE 103.17 END AS [NewFldFee], CASE WHEN [prnbydate] < '10/1/2006' THEN 76.02 ELSE 86.88 END AS [NewAutoFee], tblcurrentreportstatus.fieldexternalstatus, qryfieldvouchers.mileage * CASE WHEN examtype LIKE 'No%' THEN newjtfee WHEN examtype IS NULL THEN newautofee ELSE examtype END AS [Mileage Paid] FROM qrymileagefee, qryfieldvouchers, qryjtfee, qryfieldfee, qryfieldvoucherpersonnel, tblcurrentreportstatus INNER JOIN tblfdmivouchers ON tblcurrentreportstatus.casenum LIKE tblfdmivouchers.casenum INNER JOIN tblcase ON tblcurrentreportstatus.casenum LIKE tblcase.casenum INNER JOIN tblautopsy ON tblcurrentreportstatus.casenum LIKE tblautopsy.casenum WHERE qryfieldvoucherpersonnel.persnum LIKE [fdmi] AND tblcurrentreportstatus.prodstatus LIKE'Report received' AND tblfdmivouchers.paidstatus LIKE '0' OR tblfdmivouchers.paidstatus IS NULL AND tblautopsy.examtype NOT LIKE 'field*' OR qryfieldvoucherpersonnel.persnum LIKE [fdmi] AND tblcurrentreportstatus.prodstatus LIKE 'Report received' AND tblfdmivouchers.paidstatus LIKE '0' OR tblfdmivouchers.paidstatus IS NULL AND tblautopsy.examtype LIKE 'field*' AND tblcurrentreportstatus.fieldexternalstatus = 'Report received' Note : Assuming that qryfieldvouchers.mileage is 'Mileage' and [Case Paid] as 'Fee' Please let me know if this helps. Hopefully i have answered you correctly. Thanks, Hasham Niaz

Yes! It says completed successfully...now to put it into a report and see what happens. Thank you SO much!Gee

Hello, I put the code into a report and it will not give me fields. It says the fields are invalid. It runs as no errors in SQL Management Studio, but when I call the procedure in a report it does not work. Any help on this would be greatly appreciated. Thank you,Gee

Hi Great ! The error is due to you have duplicate columns Alias in your query. SSMS allows it but SSRS would failed to parse it. So you have to fix the query like below; SELECT qryfieldvoucherpersonnel.persnamelast, qryfieldvoucherpersonnel.persnum, tblcurrentreportstatus.casenum, tblcurrentreportstatus.prodstatus, tblfdmivouchers.paidstatus, tblcase.fieldinvestigator, tblcase.prnbydate, tblfdmivouchers.mileage AS fdmvMileage, tblcase.casetype, tblautopsy.examtype, qryfieldvouchers.mileage AS qfvMileage, CASE WHEN examtype LIKE 'No%' THEN newjtfee WHEN examtype IS NULL THEN newautofee ELSE examtype END AS [CasePaid], tblcase.fdmi, tblcase.namelast, tblcase.namefirst, tblcase.namemiddle, qryfieldvoucherpersonnel.persaddr, [perscity] & ",, " & [persstate] & " " & [perszip] AS CSZ, tblfdmivouchers.paidstatusdate, tblcurrentreportstatus.prodstatusdate, [persnamelast] + [persnum] AS GroupFDMI, CASE WHEN [prnbydate] < '10/1/2006' THEN 54.3 ELSE 65.16 END AS [NewJTFee], CASE WHEN [prnbydate] < '10/1/2006' THEN 76.02 WHEN [prnbydate] BETWEEN '10/1/2006' AND '8/1/2007' THEN 86.88 ELSE 103.17 END AS [NewFldFee], CASE WHEN [prnbydate] < '10/1/2006' THEN 76.02 ELSE 86.88 END AS [NewAutoFee], tblcurrentreportstatus.fieldexternalstatus, qryfieldvouchers.mileage * CASE WHEN examtype LIKE 'No%' THEN newjtfee WHEN examtype IS NULL THEN newautofee ELSE examtype END AS [MileagePaid] FROM qrymileagefee, qryfieldvouchers, qryjtfee, qryfieldfee, qryfieldvoucherpersonnel, tblcurrentreportstatus INNER JOIN tblfdmivouchers ON tblcurrentreportstatus.casenum LIKE tblfdmivouchers.casenum INNER JOIN tblcase ON tblcurrentreportstatus.casenum LIKE tblcase.casenum INNER JOIN tblautopsy ON tblcurrentreportstatus.casenum LIKE tblautopsy.casenum WHERE qryfieldvoucherpersonnel.persnum LIKE [fdmi] AND tblcurrentreportstatus.prodstatus LIKE'Report received' AND tblfdmivouchers.paidstatus LIKE '0' OR tblfdmivouchers.paidstatus IS NULL AND tblautopsy.examtype NOT LIKE 'field*' OR qryfieldvoucherpersonnel.persnum LIKE [fdmi] AND tblcurrentreportstatus.prodstatus LIKE 'Report received' AND tblfdmivouchers.paidstatus LIKE '0' OR tblfdmivouchers.paidstatus IS NULL AND tblautopsy.examtype LIKE 'field*' AND tblcurrentreportstatus.fieldexternalstatus = 'Report received' Please let me know if this helps. Hopefully i have answered you correctly. Thanks, Hasham Niaz

Thank you for keeping with this! It is still not working...this is the message I'm getting now: TITLE: Microsoft Report Designer ------------------------------ An error occurred while executing the query. Invalid object name 'qrymileagefee'. ------------------------------ ADDITIONAL INFORMATION: Invalid object name 'qrymileagefee'. (Microsoft SQL Server, Error: 208)Gee

Hi Gee, As the error says, SQL Server is not able to find the table "qrymileagefee" in the database you are running the query against. Can you please check if this table exists in your database?Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

Thank you for keeping with this! It is still not working...this is the message I'm getting now: TITLE: Microsoft Report Designer ------------------------------ An error occurred while executing the query. Invalid object name 'qrymileagefee'. ------------------------------ ADDITIONAL INFORMATION: Invalid object name 'qrymileagefee'. (Microsoft SQL Server, Error: 208) Gee Hi Gee, Thanks for your posting. Based on the error message, please make sure the table name is absolutely correct in case that the table name is case-sensitive. Additionally, the Reporting Services Query Designer also supports stored procedure query type. Since the command lines run correctly in the SQL Server Management Studio, we can also create a stored procedure that includes the statements and then generate a dataset by using the stored procedure. Reference: Graphical Query Designer User Interface Hope this helps. Regards, Mike YinMike Yin TechNet Community Support

I tried re-writing with caps incase it was case sensitive and that did not solve the problem. This is already a stored procedure...I'm sorry, I thought you understood that. When I run it, to check for errors in the stored procedure, it runs and says "no errors". When I call the procedure in the report builder, it gives me the error message.Gee

Then most likely the data source in Report Builder is pointing to a different database. Can you check that?Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

Checked it...it's pointing to the correct db. Ah, this is frustrating! Gee

I've been making some progress on this, but now this part doesn't work...can anyone help me? Case_Paid is not a field in a table, it is a text box where the amount to charge is held. CASE WHEN tblAutopsy.ExamType = 'Autopsy' THEN '86.88' WHEN tblAutopsy.ExamType = 'Field*' THEN '103.17' ELSE tblAutopsy.ExamType = '65.16' END AS 'CASE_PAID' Gee