pass parameter docmd.openquery

[souris]

I would like to use docmd.openquery to open a query with parameter to pass.
Is it possible to do this?
If yes, Are there any suggestions?

Any information is great appreciated,

Souris

 

[fredg]

I would like to use docmd.openquery to open a query with parameter to pass.
Is it possible to do this?
If yes, Are there any suggestions?

Any information is great appreciated,

Souris

If you are using a form event to open the query, simply add an unbound
text control to your form.

Code the query criteria field:
forms!FormName!ControlName

Enter your criteria in the text box on the form.
Click the button to open the query.
It will use the data in the text control as it's criteria.

 

[souris]

Thanks for the message.
My Query just updates data. There is no form involve.
I have the code in OnClick Event when user click the command button.
I have my criteria form!FormName!Control, but I got type mismatch.

Souris,

 

[fredg]

Thanks for the message.
My Query just updates data. There is no form involve.
I have the code in OnClick Event when user click the command button.
I have my criteria form!FormName!Control, but I got type mismatch.

Souris,

But if you are using a Click event (of a command button) you do have a
form involved.

Did you change FormName!ControlName to the actual name of the form and
the control?

If you are getting a type mismatch error, then you are trying to
enter, for example, a text parameter into a field that requires a
number or a date.

Perhaps you should post the entire query SQL, as well as an example of
the parameter you will enter.

 

[souris]

SELECT TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number
FROM TSC_CMPLD_CUSTOMER
WHERE (((TSC_CMPLD_CUSTOMER.Account_Number)=[forms]![design]![text1]))
GROUP BY TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number;

This is my SQL.
I get empty result if I do not enter data.
It makes sense, but I still got type mismatch if I enter anything.
The field of type is 'text 10'

Souris,

 

[fredg]

SELECT TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number
FROM TSC_CMPLD_CUSTOMER
WHERE (((TSC_CMPLD_CUSTOMER.Account_Number)=[forms]![design]![text1]))
GROUP BY TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number;

This is my SQL.
I get empty result if I do not enter data.
It makes sense, but I still got type mismatch if I enter anything.
The field of type is 'text 10'

Souris,

But if you are using a Click event (of a command button) you do have a
form involved.

Did you change FormName!ControlName to the actual name of the form and
the control?

If you are getting a type mismatch error, then you are trying to
enter, for example, a text parameter into a field that requires a
number or a date.

Perhaps you should post the entire query SQL, as well as an example of
the parameter you will enter.

This is the exact SQL of a sample query I just created.
Except for my using a shorter table name, and a different form and
form control, it looks to be exactly the same as yours.

SELECT tblCustomers.Name, tblCustomers.Account_Number
FROM tblCustomers
WHERE
(((tblCustomers.Account_Number)=[forms]![frmBasicData]![txtCriteria]))
GROUP BY tblCustomers.Name, tblCustomers.Account_Number;

If I enter 126 as the account number in the form's txtCriteria box and
press the command button to run the query, I get one record returned
(because it is a Group query) for the customer whose Account_number is
126. Which is the correct result.
Account_Number is Text datatype.
Are you sure you are entering a valid Account_Number?
Are you sure your [text1] control is an unbound control, and it has no
formatting?

By the way, you should not be using the word "Name" as a field name.
Name is a reserved Access/VBA/Jet word.

See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[souris]

I do not know why I can not make it work.
I use docmd.runSqL instead.
It works for me.

I appreciated your help

Souirs,

SELECT TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number
FROM TSC_CMPLD_CUSTOMER
WHERE (((TSC_CMPLD_CUSTOMER.Account_Number)=[forms]![design]![text1]))
GROUP BY TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number;

This is my SQL.
I get empty result if I do not enter data.
It makes sense, but I still got type mismatch if I enter anything.
The field of type is 'text 10'

Souris,

On Sat, 19 Mar 2005 18:17:28 -0500, souris wrote:

Thanks for the message.
My Query just updates data. There is no form involve.
I have the code in OnClick Event when user click the command button.
I have my criteria form!FormName!Control, but I got type mismatch.

Souris,

On Sat, 19 Mar 2005 12:47:33 -0500, souris wrote:

I would like to use docmd.openquery to open a query with parameter to
pass.
Is it possible to do this?
If yes, Are there any suggestions?

Any information is great appreciated,

Souris

If you are using a form event to open the query, simply add an unbound
text control to your form.

Code the query criteria field:
forms!FormName!ControlName

Enter your criteria in the text box on the form.
Click the button to open the query.
It will use the data in the text control as it's criteria.



--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

But if you are using a Click event (of a command button) you do have a
form involved.

Did you change FormName!ControlName to the actual name of the form and
the control?

If you are getting a type mismatch error, then you are trying to
enter, for example, a text parameter into a field that requires a
number or a date.

Perhaps you should post the entire query SQL, as well as an example of
the parameter you will enter.

This is the exact SQL of a sample query I just created.
Except for my using a shorter table name, and a different form and
form control, it looks to be exactly the same as yours.

SELECT tblCustomers.Name, tblCustomers.Account_Number
FROM tblCustomers
WHERE
(((tblCustomers.Account_Number)=[forms]![frmBasicData]![txtCriteria]))
GROUP BY tblCustomers.Name, tblCustomers.Account_Number;

If I enter 126 as the account number in the form's txtCriteria box and
press the command button to run the query, I get one record returned
(because it is a Group query) for the customer whose Account_number is
126. Which is the correct result.
Account_Number is Text datatype.
Are you sure you are entering a valid Account_Number?
Are you sure your [text1] control is an unbound control, and it has no
formatting?

By the way, you should not be using the word "Name" as a field name.
Name is a reserved Access/VBA/Jet word.

See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[souris]

Thanks for the information,
It works,
Thanks millions,


Souris,

SELECT TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number
FROM TSC_CMPLD_CUSTOMER
WHERE (((TSC_CMPLD_CUSTOMER.Account_Number)=[forms]![design]![text1]))
GROUP BY TSC_CMPLD_CUSTOMER.Name, TSC_CMPLD_CUSTOMER.Account_Number;

This is my SQL.
I get empty result if I do not enter data.
It makes sense, but I still got type mismatch if I enter anything.
The field of type is 'text 10'

Souris,

On Sat, 19 Mar 2005 18:17:28 -0500, souris wrote:

Thanks for the message.
My Query just updates data. There is no form involve.
I have the code in OnClick Event when user click the command button.
I have my criteria form!FormName!Control, but I got type mismatch.

Souris,

On Sat, 19 Mar 2005 12:47:33 -0500, souris wrote:

I would like to use docmd.openquery to open a query with parameter to
pass.
Is it possible to do this?
If yes, Are there any suggestions?

Any information is great appreciated,

Souris

If you are using a form event to open the query, simply add an unbound
text control to your form.

Code the query criteria field:
forms!FormName!ControlName

Enter your criteria in the text box on the form.
Click the button to open the query.
It will use the data in the text control as it's criteria.



--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

But if you are using a Click event (of a command button) you do have a
form involved.

Did you change FormName!ControlName to the actual name of the form and
the control?

If you are getting a type mismatch error, then you are trying to
enter, for example, a text parameter into a field that requires a
number or a date.

Perhaps you should post the entire query SQL, as well as an example of
the parameter you will enter.

This is the exact SQL of a sample query I just created.
Except for my using a shorter table name, and a different form and
form control, it looks to be exactly the same as yours.

SELECT tblCustomers.Name, tblCustomers.Account_Number
FROM tblCustomers
WHERE
(((tblCustomers.Account_Number)=[forms]![frmBasicData]![txtCriteria]))
GROUP BY tblCustomers.Name, tblCustomers.Account_Number;

If I enter 126 as the account number in the form's txtCriteria box and
press the command button to run the query, I get one record returned
(because it is a Group query) for the customer whose Account_number is
126. Which is the correct result.
Account_Number is Text datatype.
Are you sure you are entering a valid Account_Number?
Are you sure your [text1] control is an unbound control, and it has no
formatting?

By the way, you should not be using the word "Name" as a field name.
Name is a reserved Access/VBA/Jet word.

See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'