Seamus2626
asked on
MDB does not recognize xyz as a valid field name or expression
On running a query today, there is an age bucket [>180 days] that is blank, the below query is now returning the above error and when my excel vba trys to refresh the table, it errors out
How can i get this query to lock in >180 days and return 0 if there are no >180 day values
SELECT [S7b - Show Shared Services by Age Bucket Count].Group, [S7b - Show Shared Services by Age Bucket Count].[0 - 7 days], [S7b - Show Shared Services by Age Bucket Count].[8 - 30 days], [S7b - Show Shared Services by Age Bucket Count].[31 - 180 days], [S7b - Show Shared Services by Age Bucket Count].[> 180 days] AS Expr1
FROM [S7b - Show Shared Services by Age Bucket Count];
Thanks
How can i get this query to lock in >180 days and return 0 if there are no >180 day values
SELECT [S7b - Show Shared Services by Age Bucket Count].Group, [S7b - Show Shared Services by Age Bucket Count].[0 - 7 days], [S7b - Show Shared Services by Age Bucket Count].[8 - 30 days], [S7b - Show Shared Services by Age Bucket Count].[31 - 180 days], [S7b - Show Shared Services by Age Bucket Count].[> 180 days] AS Expr1
FROM [S7b - Show Shared Services by Age Bucket Count];
Thanks
Try renaming that field to GT180Days, or something else without the > sign.
ASKER
Cant upload the Mdb, its too large
Below is a screenshot of the query and the field expression
Thanks
TEST.docx
Below is a screenshot of the query and the field expression
Thanks
TEST.docx
Your field and table names all could use a little help.
General recommendations are to avoid spaces and special characters in your naming conventions, but I think that the > operator specifically might be making this choke.
As an aside, we also had a user here yesterday whose query was erring because field alias names were too long. I'm not sure what the exact cutoff is for field, table or alias names... just something to keep in mind, though.
Anyhow, go back to that table or spreadsheet and try renaming that > 180 days column to something that adheres to standard/recommended naming conventions (GT180Days, GreaterThan180Days, etc.)
General recommendations are to avoid spaces and special characters in your naming conventions, but I think that the > operator specifically might be making this choke.
As an aside, we also had a user here yesterday whose query was erring because field alias names were too long. I'm not sure what the exact cutoff is for field, table or alias names... just something to keep in mind, though.
Anyhow, go back to that table or spreadsheet and try renaming that > 180 days column to something that adheres to standard/recommended naming conventions (GT180Days, GreaterThan180Days, etc.)
ASKER
The problem is that the >180 days field is not in the table S7b - if you look there is no field >180 days, thats what is causing it to fail.
I dont want to remove the field >180 days in case there is data there tomorrow, is it possible to have an IF statement saying if >180 days not in S7b return 0, other wise return value?
Thanks
I dont want to remove the field >180 days in case there is data there tomorrow, is it possible to have an IF statement saying if >180 days not in S7b return 0, other wise return value?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi als,
Yep, this is the problem i need to get around!
Tomorrow they may be a field >180 days, i need the query to return 0 if there is no field and the value if there is a field.
An iff statement?!
Yep, this is the problem i need to get around!
Tomorrow they may be a field >180 days, i need the query to return 0 if there is no field and the value if there is a field.
An iff statement?!
ASKER
legend!
Just what i needed!
Just to confirm if there is an entry as 5 tomorrow, it will return 5?
Thanks
Just what i needed!
Just to confirm if there is an entry as 5 tomorrow, it will return 5?
Thanks
What is "5 tomorrow" - :text field?
Explain more, and upload file if shortens the talking.