Make Table Query fails with invalid expression

[BBrayton]

Hello All. First post to this forum.



Last year I created an application to managed health care recipients for Medical, Dental, Life Ins., LTD, Disability options for their employer. The user manages the population and monthly runs a query that ultimately creates an Excel spreadsheet that we then import into QuickBooks.

All worked flawlessly until I wanted to add 2 new fields to the "Make Query" that gets exported to Excel.

The error I get when running the Make Query is:
"A calculated column cannot be saved without a valid express in the Expression property".

The fields I'm adding come from an existing query as all the other data in the Make Query do, and they are valid.

Anyone run across this before and have a solution or suggestion.

Thanks
Bruce

[JoeM]

If your final goal is to export the results to Excel, why are you using a Make Table Query?
Why not just a Select Query?
You can export the results from a Select Query just as easily as you can from a Table.

[BBrayton]

Hi Joe.

I do further work with the table by doing an append query and some other calculations down the road. I have to interlace some "Subtotal" commands into the file for QB import.

Bruce

[JoeM]

Confirm that the following two things work:
1. If you temporarily change the Make Table Query to a Select Query and run it, does it run without errors?
2. If you leave it as a Make Table Query, and remove the two calculated fields, and try running it, does it run without errors?

If both those are true, that confirms that it is choking on your calculations fields. Can you post the calculations here?

Or even better yet, can you post the SQL code of the entire Make Table Query here?

[BBrayton]

1. Yes runs as Select Query
2. Yes removing 2 fields Make Table Query runs fine.
SQL code:
SELECT [Invoice Query].InvNum, [Invoice Query].Classification, [Invoice Query].[Last Name], [Invoice Query].[First Name], "MEDDEN" AS XType, [Invoice Query].Desc, [Invoice Query].Customer, [Invoice Query].Address, [Invoice Query].InvDt, [Invoice Query].InvMsg, [Invoice Query].FullDenMed, [Invoice Query].MedDenCov, [Invoice Query].Other2, [Invoice Query].FullMed, [Invoice Query].FullDen, [Invoice Query].Name INTO [MedDen Invoice Data]
FROM [Invoice Query];

New fields are FullMed & FullDen

[JoeM]

OK, it looks like we will need to see the SQL code of you Invoice Query to see those two calculations.

Note, if you view this query, do you see any errors blanks, or null values for any records being returned in those two fields?
If so, that may cause problems when trying to write them to a new table.

[BBrayton]

Ok here's where it gets tough:
Make Table derived from Invoice Query:

PARAMETERS [Invoice Date] DateTime;
SELECT Account.Acct AS InvNum, [Insured Query].Classification, [Insured Query].[Last Name], [Insured Query].[First Name], Account.Acct, [Insured Query].[Account Split], Account.Desc, Account.Customer, Account.Address, Account.Contact, Account.Phone, Account.Fax, [Insured Query].Location, [Insured Query].SSN, [Insured Query].[Date of Birth], [Insured Query].[Zip Code], [Insured Query].Sex, [invoice date] AS InvDt, [Enter Invoice Footer Message] AS InvMsg, [Insured Query].[Start Date], [Insured Query].[Termination Date], IIf(IsNull([Termination Date]),True,IIf([Termination Date]>[Invoice Date],True,False)) AS TermCheck, IIf(IsNull([Start Date]),True,IIf([Start Date]<=[Invoice Date],True,False)) AS StartCheck, [Insured Query].[Plan Chosen], [Insured Query].Area, [Insured Query].Notes, [Insured Query].[Dental Coverage], [Insured Query].DenCovQ.ID, [Insured Query].DenCovQ.Carrier, [Insured Query].DenCovQ.Type, [Insured Query].DenCovQ.Plan, [Insured Query].DenCovQ.Coverage, [Insured Query].DenCovQ.EERate, [Insured Query].DenCovQ.ERRate, [Insured Query].DenCovQ.FullRate, [Insured Query].DenCovQ.Split, [Insured Query].[Medical Coverage], [Insured Query].MedCovQ.ID, [Insured Query].MedCovQ.Carrier, [Insured Query].MedCovQ.Type, [Insured Query].MedCovQ.Plan, [Insured Query].MedCovQ.Coverage, [Insured Query].MedCovQ.EERate, [Insured Query].MedCovQ.ERRate, [Insured Query].MedCovQ.FullRate, [Insured Query].MedCovQ.Split, [Insured Query].EEDenMed, [Insured Query].ERDenMed, [Insured Query].FullDenMed, 'Den: '+[DenCovQ]![Coverage]+' / Med: '+[MedCovQ]![Coverage] AS MedDenCov, Format([ERDenMed],"Standard")+" / "+Format([EEDenMed],"Standard") AS Other2, [Last Name]+", "+[First Name]+IIf([Account Split]=1,""," (Split "+Str(Round([Account Split]*100,2))+"%)") AS Name, IIf([MedCovQ]![Coverage]="Waived" And [DenCovQ]![Coverage]="Waived",0,1) AS DualWaived, IIf(IsNull([MedCovQ]![Coverage]) And IsNull([DenCovQ]![Coverage]),0,1) AS DualNull, [Insured Query].FullMed, [Insured Query].FullDen, [Insured Query].FullMed, [Insured Query].FullDen
FROM Account INNER JOIN [Insured Query] ON Account.ID = [Insured Query].Account
WHERE (((IIf(IsNull([Termination Date]),True,IIf([Termination Date]>[Invoice Date],True,False)))<>0) AND ((IIf(IsNull([Start Date]),True,IIf([Start Date]<=[Invoice Date],True,False)))<>0) AND ((IIf([MedCovQ]![Coverage]="Waived" And [DenCovQ]![Coverage]="Waived",0,1))>0) AND ((IIf(IsNull([MedCovQ]![Coverage]) And IsNull([DenCovQ]![Coverage]),0,1))>0))
ORDER BY Account.Acct, [Insured Query].Classification, [Insured Query].[Last Name], [Insured Query].[First Name];


The Invoice Query comes from Insured Query:

SELECT Account.Acct, Insured.[Account Split], Account.Customer, Insured.ID, Insured.Acct, Insured.Account, Insured.Classification, Insured.Location, Insured.[Last Name], Insured.[First Name], Insured.MedId, Insured.[Medical Coverage], Insured.DenId, Insured.[Dental Coverage], Insured.SSN, Insured.[Date of Birth], Insured.[Zip Code], Insured.Sex, Insured.[Start Date], Insured.[Termination Date], Insured.[Change Date], Insured.[Plan Chosen], Insured.Area, Insured.Notes, Insured.[Dental Coverage], DenCovQ.ID, DenCovQ.Carrier, DenCovQ.Type, DenCovQ.Plan, DenCovQ.Coverage, DenCovQ.EERate, DenCovQ.ERRate, DenCovQ.FullRate, DenCovQ.Split, Insured.[Medical Coverage], MedCovQ.ID, MedCovQ.Carrier, MedCovQ.Type, MedCovQ.Plan, MedCovQ.Coverage, MedCovQ.EERate, MedCovQ.ERRate, MedCovQ.FullRate, MedCovQ.Split, ([MedCovQ].[EERate]+[DenCovQ].[EERate])*[Account Split] AS EEDenMed, ([MedCovQ].[ERRate]+[DenCovQ].[ERRate])*[Account Split] AS ERDenMed, (([DenCovQ]![FullRate]+[MedCovQ]![FullRate]))*[Account Split] AS FullDenMed, Insured.[Life LTD Participant], Insured.[Annual Earnings], Insured.[Life Class], Insured.[LTD Class], Insured.[Life LTD Start Date], Insured.[Life LTD Termination Date], LTDQuery.Carrier, LTDQuery.Coverage, LTDQuery.Class, LTDQuery.Factor, LTDQuery.Additional, LTDQuery.[To Maximum], LTDQuery.[Age Reduction], LTDQuery.[Reduction Factor], [LifeAdd Query].Carrier, [LifeAdd Query].Coverage, [LifeAdd Query].Class, [LifeAdd Query].Factor, [LifeAdd Query].Additional, [LifeAdd Query].[To Maximum], [LifeAdd Query].[Age Reduction], [LifeAdd Query].[Reduction Factor], Int(((([Annual Earnings])*[LifeAdd Query]![Factor])+[LifeAdd Query]![Additional]+999)/1000)*1000 AS LifeADDVol1, IIf([LifeADDVol1]>[LifeAdd Query]![To Maximum],[LifeAdd Query]![To Maximum],[LifeADDVol1]) AS LifeADDVol2, CDate("01-01-"+Right$(Date$(),4)) AS jan1date, DateDiff("yyyy",[Date of Birth],[jan1date]) AS Age, IIf([LifeAdd Query]![Age Reduction]>0,IIf([age]>=70,[LifeADDVol2]*[LifeAdd Query]![Reduction Factor],[LifeADDVol2]),[lifeaddvol2]) AS LifeADDVolume, IIf([Life LTD Participant],IIf([LTD Class]>0,IIf([Annual Earnings]/12>8333,8333,[Annual Earnings]/12),0),0) AS LTDVolume, "RATELIFE" AS RateLife, "RATEADD" AS RateADD, "RATELTD" AS RateLTD, Insured.[Changes Verified], MedCovQ.FullMed, DenCovQ.FullDen
FROM ((((Insured LEFT JOIN MedCovQ ON Insured.MedId = MedCovQ.ID) LEFT JOIN DenCovQ ON Insured.DenId = DenCovQ.ID) LEFT JOIN Account ON Insured.Account = Account.ID) LEFT JOIN [LifeAddLTD Query] AS LTDQuery ON Insured.[LTD Class] = LTDQuery.ID) LEFT JOIN [LifeAdd Query] ON Insured.[Life Class] = [LifeAdd Query].ID
ORDER BY Account.Customer, Insured.[Last Name], Insured.[First Name];

The confusion I think, but can't confirm is the 2 new fields are in the MedCovQ & DenCovQ queries and they show as calc'd MedCovQ!FullRate is the ! causing a problem or is that a normal operator. may be nothing but that's all I can see.

Bruce

[JoeM]

Wow, that is a bit more involved that I expected. And without seeing your data, it might be kind of tricky to determine. Note that you can upload databases to this site for analysis, but be sure to remove any sensitive data, if you decide to try that.

Here is something you can review yourself. You seem to have identified the two "problem" fields. In your SQL query that does these calculations, scan the results of these two fields. What kind of data is being returned (numeric? text?)? Then looking for any values in these fields which look different or out of place (like errors, nulls, blanks, etc). These could be your problem. Access is not going to like it if you have an error in a calculation, and are trying to write that error to a new table.

[BBrayton]

Thanks Joe.
The data in all records for those 2 fields show correctly and they are numeric.

I am not able to upload as they contain HIPAA type data without extensive modification.

I'll plod along with some other testing and changes.

Bruce

[JoeM]

The data in all records for those 2 fields show correctly and they are numeric.

Just keep in mind that one non-numeric entry could blow the whole process up.

[ssanfu]

Just some observations:

You have used several reserved words: "Type", "Name", "Plan", "Split" as field names. (see http://allenbrowne.com/AppIssueBadWord.html) Plus, they are not very descriptive.
Along with that, using "ID" as a PK is also not descriptive. If you use "ID" in 5 tables, it gets confusing which "ID" is being refered to.


You have spaces in object names. Should never use spaces in names .... ( [Insured Query].Location, [Insured Query].[Dental Coverage],)
A query named "Insured_Query" or "InsuredQuery" is just as easy to read.... spaces cause headaches. Same with field names: "DentalCoverage" or "Dental_Coverage" are better names.

If you keep recreating a table (using a make table query), it would be better to create the table once and delete the records, then use an append query to add records. Constantly creating tables means you have a greater chance for corruption.


When I see something like "[Insured Query].DenCovQ.Coverage," and "[Insured Query].MedCovQ.Coverage,", it makes me think that there are Multi-value fields in a table, since you only had problems after adding the fields "DenCovQ" and "MedCovQ".
MVFs don't act like normal fields - they need "special handling" (AFAIK).


I never use dynamic parameters (as in: [Enter Invoice Footer Message] AS InvMsg, ). I have a control on a form that I enter the data into.

When combining text, use the ampersand (&) instead of the 'plus' (+). 'Den: '+[DenCovQ]![Coverage]+' / Med: '+[MedCovQ]![Coverage] AS MedDenCov,

[BBrayton]

Thanks for all the input. I'm learning as we go here.
I was able to engineer a workaround by storing the id of the record that has the data i needed. I then modified the next final query before my export to bring in that table and access that field.

I'm way too far down the road to go back and fix the items called out here. I'll take it as good input going forward.

Enjoy and thanks
Bruce