I am receiving the following error: "The Microsoft Access database does not recognize '[ASX Code]' as a valid field name or expression."

Here is the code that throws the error:

QUICK_RATIO_CHART_SQL = "SELECT [Quick Ratio] FROM [RATIO ANALYSIS] WHERE [ASX Code] like '" & ASXCODE & "';"
Me.QUICK_RATIO_CHART.RowSource = QUICK_RATIO_CHART_SQL

'RATIO ANALYSIS' is a table that contains a field 'ASX Code'.

Can anyone point me in the right direction please?

Thanks Lee

CodePudding user response：

Not the answer, but a method to help you getting it right:

• add debug.print QUICK_RATIO_CHART_SQL after the line QUICK_RATIO_CHART_SQL=...
• run the proc until the debug.print is executed
• paste the printed statement in a new view (in SQL view)
• switch to design view and execute
• you should then have a clear view of the error

That how I'd proceed when my eyes pop out my head, like it may happen sometimes.

You can also try to qualify everything, using an Alias:

QUICK_RATIO_CHART_SQL = "SELECT r.[Quick Ratio] FROM [RATIO ANALYSIS] r WHERE r.[ASX Code] like '" & ASXCODE & "';"

As a last option, use a saved with a criteria pointing to the form control (formName!controlName). This way you can test the query without VBA, as long as the form is open.

CodePudding user response：

I found the solution through trial and error. When I had originally inserted the graph, under Chart Settings \ Legend I had ticked [ASX Code]. Changing this to None resolved the error message.