Invalid procedure call when running SQL statement (Max function with parameter)

Question

So this is my third question in so many hours. Thanks again to everyone who has taken the time to help me through my SQL ordeal. I think this might be my last tango for the night, so here goes:

After taking some very good advice from @Vojtěch Dohnal, I converted one of my queries from a concatenated string to a parameterized SQL query here:

PARAMETERS NewPrefix TEXT; SELECT MAX([Suffix]) FROM [SalesTable] WHERE [Prefix] = [NewPrefix];

From what I can tell, this should be the right syntax for creating a parameterized query; the user will define what should go into the NewPrefix field and it will find the appropriate max function based on that. However, whenever I go to execute this query it hits me with the same 'Run-time error '5'; Invalid procedure call' error I've been wrestling with for about 9 hours now haha

I went ahead and tried to test the same query in the Access SQL query window, and I receive an error message there claiming: "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables".

I'm not sure how to get around this. I don't think the syntax is wrong, but I can't find anything to compare it to on the Internet. I've used the debugger to step through and it looks like all of the values and variables and fields are populated correctly, but when it gets to the execute command it crashes with the same singularly unhelpful error message.

Thanks again for anyone who can help.

whenever I go to execute this query ... please show how you are executing query. — Parfait
I've never usee PARAMETERS clause so just tested - query works same with or without. — June7

jahamj jahamj · Accepted Answer · 2017-08-27T04:14:04

So it looks like the main problem that I was running into was that the "Prefix" field was actually a calculated field in my underlying Access table. For whatever reason, Access doesn't want to work with calculated fields with SQL; when I took out the calculation and just made the Prefix column a regular field, everything seems to work perfectly. I'm not really happy with this but it seems to work and that's what matters. Thanks to everyone who took the time to try and help me with this stuff. Cheers!

Invalid procedure call when running SQL statement (Max function with parameter)

1 Answers