Error 2447 - Invalid use of dot, ! operator, or invalid parens

[mdweezer]

I have a form with a bunch of fields, you can select various drop downs and such and hit submit and it'll print a form using what you selected as the criteria. Well there is one field that keeps "exploding" and it gives the error described in the topic. It's a drop down using the following for its row source

SELECT TBL_Org.OrgNumber, TBL_Org.OrgName
FROM TBL_Org;
UNION select 0, "All Orgs"
from TBL_Org
ORDER BY 2;

I didn't write the code and i'm not familiar with Union queries, all I know that it selects all options from that drop down or something, but is there incorrect syntax or something? Thanks!!!!

 

[mdweezer]

Also, when I copy the value from that field i'm using:

OrgValue = Nz(cboOrg.Value)

and the error occurs on the right side of that line of code.

I have about 3 other drop downs doing the same exact thing and they work fine, all except for this one. However if I select a value from the dropdown it works fine and I don't get any errors.

 

[PHV]

Get rid of the ; at the end of the first FROM clause:
SELECT TBL_Org.OrgNumber, TBL_Org.OrgName
FROM TBL_Org[highlight] [/highlight]
UNION select 0, "All Orgs"
from TBL_Org
ORDER BY 2;

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

 

[mdweezer]

Ok, now i'm using

SELECT TBL_Org.OrgNumber, TBL_Org.OrgName
FROM TBL_Org
UNION select 0, "All Orgs"
from TBL_Org
ORDER BY 2;

However it still blows up on me.... Although it does populate the drop-down with the correct values that I need. Just when I leave it blank, it gives the error, but like I said, there are 3 other fields doing some the same union query so this just isn't making sense. I must be missing something somewhere

 

[willir]

What is "0" in select 0, "All Orgs" ??

Are you using numbers as field names???

 

[KenReay]

Hi

Try

SELECT TBL_Org.OrgNumber, TBL_Org.OrgName
FROM TBL_Org
UNION select 0 As OrgNumber, "All Orgs" As OrgName
from TBL_Org
ORDER BY 2;

or even

SELECT TBL_Org.OrgNumber, TBL_Org.OrgName
FROM TBL_Org
UNION select 0 As OrgNumber, "All Orgs" As OrgName
from TBL_Org
ORDER BY OrgName;

And

OrgValue = Nz(cboOrg.Value,0)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK