bejhan
asked on
SQL Syntax Error (Not a valid name)
I get an error with the following SQL statement: The SQL Statement is not a valid name. Make sure that it does not include invalid characters or punctuation that is too long.
SELECT DISTINCT CustomerID, CustomerLastActivity FROM ltblAllItems WHERE CollectorID = 'DEFAULT' AND (ISNULL(CollectionStatus) OR CollectionStatus = '');
Last Comment
ASKER
Same problem.
ASKER
Okay I put it in query creator in MS Access and it changed it to this which works...
SELECT DISTINCT ltblAllItems.CustomerID, ltblAllItems.CustomerLastActivity
FROM ltblAllItems
WHERE (((ltblAllItems.CollectorID)='DEFAULT') AND ((ltblAllItems.CollectionStatus) Is Null Or (ltblAllItems.CollectionStatus)=''));
ASKER
Hmm but when I put that back in my code it gives the same problem...
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
use " instead of '
SELECT DISTINCT CustomerID, CustomerLastActivity FROM ltblAllItems WHERE CollectorID = "DEFAULT" AND (ISNULL(CollectionStatus) OR CollectionStatus = "");
Is CollectorID numeric?
Are you using something other than MS Access?
If CollectorID is numeric then you would need to use a variable instead of a string.
If you are using something other than MS Access, it MIGHT not be able to handle single quotes, try double.
Are you using something other than MS Access?
If CollectorID is numeric then you would need to use a variable instead of a string.
If you are using something other than MS Access, it MIGHT not be able to handle single quotes, try double.
ASKER
CollectorID is a string and I am using MS Access.
ASKER
angelll, the double quotes gives me a syntax error in VBA.
Try this?
SELECT DISTINCT CustomerID, CustomerLastActivity
FROM ltblAllItems
WHERE CollectorID = 'DEFAULT' AND (Nz((CollectionStatus), '')='')
SELECT DISTINCT CustomerID, CustomerLastActivity
FROM ltblAllItems
WHERE CollectorID = 'DEFAULT' AND (Nz((CollectionStatus), '')='')
ASKER
Okay that works when I build a query of it, but when I try to create a querydef in VBA I get the same error, which is what happened to me before:
https://www.experts-exchange.com/questions/23586248/SQL-Syntax-Error-Not-a-valid-name.html?anchorAnswerId=22062843#a22062843
https://www.experts-exchange.com/questions/23586248/SQL-Syntax-Error-Not-a-valid-name.html?anchorAnswerId=22062843#a22062843
Set ARCustomersQR = CurrentDb.CreateQueryDef("SELECT DISTINCT CustomerID, CustomerLastActivity FROM ltblAllItems WHERE CollectorID = 'DEFAULT' AND (Nz((CollectionStatus), '')='')")
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Oh jeez I feel like an idiot, it has been a long day. Sorry for not including that earlier!
ASKER
I should have known that... but I'm glad because I didn't know you could use NZ in there.
Dude... it's cool... no worries...
SQL
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
SELECT DISTINCT CustomerID, CustomerLastActivity FROM ltblAllItems WHERE CollectorID = 'DEFAULT' AND (CollectionStatus IS NULL) OR CollectionStatus = '');