?
Solved

MDB does not recognize xyz as a valid field name or expression

Posted on 2013-01-18
10
Medium Priority
?
327 Views
Last Modified: 2013-01-18
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
0
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 38792443
What do you have, test data, and expected result?
Explain more, and upload file if shortens the talking.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38792446
Try renaming that field to GT180Days, or something else without the > sign.
0
 

Author Comment

by:Seamus2626
ID: 38792460
Cant upload the Mdb, its too large

Below is a screenshot of the query and the field expression

Thanks
TEST.docx
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 61

Expert Comment

by:mbizup
ID: 38792461
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.)
0
 

Author Comment

by:Seamus2626
ID: 38792475
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
0
 
LVL 40

Expert Comment

by:als315
ID: 38792484
I do not see field .[> 180 days] in your source table/query:
fields
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 38792493
Sorry, window was not refreshed.
You can't add missing fields to query. You can add empty field with name > 180:0
0
 

Author Comment

by:Seamus2626
ID: 38792494
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?!
0
 

Author Closing Comment

by:Seamus2626
ID: 38792512
legend!

Just what i needed!

Just to confirm if there is an entry as 5 tomorrow, it will return 5?

Thanks
0
 
LVL 40

Expert Comment

by:als315
ID: 38792525
What is "5 tomorrow" - :text field?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question