Running Parameter Requires Table ID# Not Field Name
Table - Inspection Results
ID Inspection Results
When I run a parameter inquiry requirement to look for inspection results, and I type the word "Pass" in, I receive an error. But, when I type "1" in, I get the information I'm looking for (all records with inspection results that were a "Pass"). Why is Access looking for the table ID number instead of the text value?
Could it be because the ID# is set as the Primary Key?
Thank you for taking the time to report an issue.
What's wrong... Please write below.
I tried using DoCmd.RunSQL, but, if I understand correctly, this can't be used for a select statement. I used it with updating and inserting with no problems.
I'm trying to use qdf.Parameters() now, but I still get the popup. Is there a way to pass parameter without the parameter prompts?
Run-time error '3129'
Basically, PLEASE no 'don't use lookup fields in tables' lecture. I have readthat on SO many posts,
[Totally by-the-way, but why do Microsoft offer lookups as fields in Tables,]
Anyway, if you do write a Select query in a table field with a WHERE clause, and within the WHERE criteria you reference a parameter, and you keep it numerical, it works.
But if you ask the query to take this parameter from another field in the same table (because this parameter should vary depending on the record), Access won't have it. It gives a parameter entry prompt.
It is as though when you reference another field for the parameter value, Access doesn't understand that the parameter varies, doesn't understand that the user wants it to look at the PARTICULAR RECORD for a PARTICULAR VALUE for the parameter.
It seems to want either a general value for the parameter, Would there be a third way? and if so I'd like to know what.
Everything works fine, except that now as well as displaying the data on the screen, I would also like to write it to a table in order to keep a historic record.
It seems logical to use the same queries which already give me the data I require. However, every way I try to use the queries in VBA I get an error which says "Too few parameters expected 1". I have tried running the query by using dbs.execute, and I have tried opening a recordset with the query as it's source. Same error each time. The form is always open, and the control is available, so surely it should be able to get the parameters it requires?
It seems that I cannot use a query in VBA if it has a parameter. Is this correct? Can anybody suggest a solution
If param1 "" Then cmd.Parameters(0) = param1
The query I'm running requires 1 parameter which is contained in param1 (and I've stepped through the code to make sure it's there).
When I try to pass the parameter, Item cannot be found in the collection corresponding to the requested name or ordinal
If I miss out the the line of code above (leaving the parameter empty) and try to execute the query, I get the following error message:-
Too few parameters. Expected 1
It looks like I need to pass the parameter, but when I do it isn't accepted.
I want to have a parameter in the query so that they can specify which location is to be used in the query. But when I drag "Location" from the field list into the query design and put the following in the criteria section it doesn't work: Like [Enter location]&"*". When I run the query and type in a location at the parameter prompt I get no records.
I'm wondering if, since "location" is a lookup field in my table, the parameter will not recognize a "typed in" location. Do I have to enter a different phrase in the criteria to perhaps access the same lookup list that appears in my lookup field in the table?
< [enter your value]
so after running the query, I want all values less than input value to be marked in red color/background. please suggest how to do that