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:

More topics

 

Running Parameter Requires Table ID# Not Field Name

Preview
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.

eg:

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 (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?
 

Passing Parameters to a Query Without Parameter Prompts

Preview
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

Preview
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'
 

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

Preview
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

Preview
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.
 

Parameter prompts twice but exists once

Preview
Running on Access 2000. A query has one parameter in it in the supplier criteria row It is the only criteria applied to the whole query.

Parameter was defined as.

Like [Enter Supplier Name or Part of Name or Blank for ALL] & "*"

The parameter is only in the query in one location.
It is NOT part of a calculation
It is NOT defined in the Parameters list (maybe that is part of the cause)

The issue occurred when running the query on its own. The parameter question was requested twice although only referenced once.

If the query was edited and saved the problem went away. But later for no reason it then appeared to return.

The only thing I can think of is that the database had not had a compact and repair for some while. I have now made them do this and will see if the issue still arises.
 

Referrence a field rather than a parameter

Preview
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
 

Passing query parameters (in Tables) where the parameter varies

Preview
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.
 

conditional formatting in parameter query

Preview
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
 

Parameter Query - if Null show all

Preview
Im running a parameter query and I need it to show all records if left blank by the user.
I can achieve this but I cannot achieve it for more than one parameter.
How do I go about this?

Basically, if the user enters a value for the first parameter and enters nothing on the second one, i'd like to see all values for the first parameter