Combo Box

[Jessica240]

Hello everyone! I am trying to write a query which uses the selection from a combo box on my form to be part of a calculated field. I have success with this in one of my queries, but for some reason when I try to make the exact same query but using a different table as the data source, it doesn't work. So I'm guessing the problem has to do with the tables I'm using.



Here is a copy of the SQL that works with the combo box expression in bold:

SELECT [999 - CPSI Patient Detail].[AR0-DOCNM1], [999 - CPSI Patient Detail].IVSRVDTE, [999 - CPSI Patient Detail].IVFDEPT, [999 - CPSI Patient Detail].IVNUM, [999 - CPSI Patient Detail].IVDESC, [999 - CPSI Patient Detail].IVCPTCD, [999 - CPSI Patient Detail].RVU, [MCARE RATE1]*[FORMS]![FORM1]![PERCENTAGECOMBO] AS RATE, [999 - CPSI Patient Detail].IVCHGAMT, [999 - CPSI Patient Detail].IVCHGQTY, [999 - CPSI Patient Detail].IVCHGDTE, [999 - CPSI Patient Detail].IVPHYNM
FROM [999 - CPSI Patient Detail];

Here is a copy of the SQL that does NOT work:

SELECT [2000 - SUMMARY].[AR0-DOCNM1], [2000 - SUMMARY].Rate, [2000 - SUMMARY].IVCPTCD, [2000 - SUMMARY].IVDESC, [2000 - SUMMARY].[Total Of IVCHGQTY] AS QTY, [RATE]*[FORMS]![FORM1]![PERCENTAGECOMBO] AS RATE1
FROM [2000 - SUMMARY];

So as you can see, when I try to query from my 999-CPSI Patient Detail query, I am golden. But for some reason when I try to query from my 2000 - SUMMARY query I run into trouble.....

Any help is much appreciated!!

[ranman256]

This should work, as long as
FORM1 is the name of the form
PERCENTAGECOMBO is the name of the combo
RATE is a field in the table

Since it works in the 1st query, I'm puzzled on the 2nd one.

It will give null if PERCENTAGECOMBO or RATE is null.

?????

[ssanfu]

Here is a copy of the SQL that does NOT work:

Doesn't work doesn't provide a lot of info. What is the error description?

SELECT [2000 - SUMMARY].[AR0-DOCNM1], [2000 - SUMMARY].Rate, ..........

One thing you might consider is that "Rate" is a reserved word in Access; reserved words shouldn't be used as object names. Access gets confused.
You could try using brackets:
[2000 - SUMMARY].[Rate],

Here is a list of reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html

Also, "Rate" is not very descriptive. "Rate" of what? Tax rate, Interest Rate, Diffusion Rate??

Objects names should be letters and numbers. No punctuation (the underscore is the exception).