Topics Search

Running Parameter Requires Table ID# Not Field Name

Running Parameter Requires Table ID# Not Field Name
Views: 3
When I try to set a parameter for my inquiry, Access is looking for the table ID# instead of the text in the field I'm looking for. e.g:

Table - Inspection Results
ID Inspection Results
1 Pass
2 Fail
3 Non-rated

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?
Sponsored Links:

Similar posts...

 

Passing Parameters to a Query Without Parameter Prompts

Passing Parameters to a Query Without Parameter Prompts Icon
I have a query that requires a parameter to work. I need to pass a form field (entered by the user) to the query as the parameter, but I don't want the parameter box to pop-up asking the user for input when the button is clicked to run the parameter.

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?
 

How to compare the field name as per parameter value

How to compare the field name as per parameter value Icon
I want to update one table field from other table through the passing of parameter. If I pass the parameter 30/11/2011, the numeric field of the table [Nov_A] should be updated as per the other table's numeric field [Nov-11]. same like for December 2011, January 2012 and so on. I have written some code is showing the following error.
Run-time error '3129'
 

Passing query parameters (in Tables) where the parameter varies

Passing query parameters (in Tables) where the parameter varies Icon
I know how to do this with Forms. I'm also aware that it is bad practice to use lookup fields in Tables. and by experimenting to find out how stuff works. Don't jump to the conclusion I am trying to use this technique for a finished job, I'm not.

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.
 

Referrence a field rather than a parameter

Referrence a field rather than a parameter Icon
Having a problem completing a calculation in a query: When I type in a field name (enclosed in square brackets) in a calculation, things look fine. But, when I run the query, it treats the field name as a parameter and asks for the parameter's input. How do I get access to use the quantity in the referenced field for the calculation rather than ask for parameter iput
 

Can I use a query in VBA if it has a parameter?

Can I use a query in VBA if it has a parameter? Icon
me? I'm using Access 2007. I have a query which uses a number of sub queries in order to build up its data. A couple of the sub queries use a control on a form as to get their parameter (i.e. the year).

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
 

Excel passing parameters to Access for queries

Excel passing parameters to Access for queries Icon
I've got a piece of code in Excel which links to an Access database and runs queries in it. Some of the queries need parameters which are being passed to it using this piece of code:-

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.
 

Designing a query with a parameter and a lookup field?

Designing a query with a parameter and a lookup field? Icon
I'm designing a query. I want to include the field "Location". "Location" in the table I'm quering (is that a word?) is a look-up field, so that my people can just click on their choice from a list of locations.

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?
 

Using Combo Boxes as a Source for Parameter Queries

Using Combo Boxes as a Source for Parameter Queries Icon
I often encounter a situation where I want to run a select (parameter) query on a particular table, but instead of having the user type in their own parameter, I'd like for them to be able to choose, from a combo box that auto-completes, the actual item in the underlying table, then use this string as the basis for the parameter query. I want to limit the parameters to already-extant items in the underlying table and so avoid close-but-not-exact matching problems by free-typing the parameter.
 

Drop-down list n parameter query

Drop-down list n parameter query Icon
My users find it hard to use my parameter queries because they have to enter exact spelling. If the field that has the parameter value is driven by a list or table, is there a way to providea drop-down with the values for the user to choose from?
 

conditional formatting in parameter query

conditional formatting in parameter query Icon
I am using a parameter query. Criterion field has
< [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