Solved

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

Posted on 2013-01-18
10
320 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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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
Comment Utility
Try renaming that field to GT180Days, or something else without the > sign.
0
 

Author Comment

by:Seamus2626
Comment Utility
Cant upload the Mdb, its too large

Below is a screenshot of the query and the field expression

Thanks
TEST.docx
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 39

Expert Comment

by:als315
Comment Utility
I do not see field .[> 180 days] in your source table/query:
fields
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
legend!

Just what i needed!

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

Thanks
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
What is "5 tomorrow" - :text field?
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now