parameter Query won't work

G

Guest

I feel stupid for resorting to this but here goes... I can't figure out
what's wrong with my form based parameter query. It won't accept the
criteria entered into the form. I've got a form called frmPricingCriteria
and an unbound textbox called txtCompanyName. Also on the form is a command
button which runs the query. The criteria for the CompanyName field on the
query is [forms]![frmPricingCriteria]![txtCompanyName].

For giggles, here's the SQL: SELECT Contacts.CompanyName, [Order
Entry].[Part Number1], [Order Entry].[Start Temper], [Order Entry].[Finish
Temper], [Order Entry].Alloy, ProcessCodes.[Process Key]
FROM (Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID) INNER JOIN ProcessCodes ON [Order Entry].[ID Process Key] =
ProcessCodes.[ID Process Key]
WHERE (((Contacts.CompanyName) Like NZ("*" &
[forms]![frmPricingCriteria]![txtCompanyName] & "*")))
ORDER BY Contacts.CompanyName, [Order Entry].[Part Number1];

Whenever I run my query using the criteria from the form, it returns no
records. If I enter parameters directly without the form, it works. So, I
don't believe the problem is with my query. I believe the syntax for the
criteria is correct to read the values from my form. I've checked and double
checked the spelling of the names for both the form and the textbox.
Everything appears to be right. What am I missing? BTW, the data I'm using
as my sort criteria is valid as well. Please tell me its something I didn't
know and not something easy that I overlooked!
 
G

Guest

The use on Nz is Unnecessary in that case

Use

SELECT Contacts.CompanyName, [Order
Entry].[Part Number1], [Order Entry].[Start Temper], [Order Entry].[Finish
Temper], [Order Entry].Alloy, ProcessCodes.[Process Key]
FROM (Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID) INNER JOIN ProcessCodes ON [Order Entry].[ID Process Key] =
ProcessCodes.[ID Process Key]
WHERE Contacts.CompanyName Like "*" &
[forms]![frmPricingCriteria]![txtCompanyName] & "*"
ORDER BY Contacts.CompanyName, [Order Entry].[Part Number1]

I don't think that should make a different, but please try it

In any case while the form is open (run) open the Immidiate window, type
?[forms]![frmPricingCriteria]![txtCompanyName]

and press Enter, which value returned
 
G

Guest

Ofer's answer is correct, you don't need the Nz function. But FYI, in your
code it is used incorreclty. You should not wrap an expression with the Nz
function. It should be done on an item by item bases:

WHERE (((Contacts.CompanyName) Like "*" &
Nz([forms]![frmPricingCriteria]![txtCompanyName],"") & "*")))
 
G

Guest

Aha! I kind of suspected this, but didn't know how to figure it out...
Ofer's suggestion to look in the Immediate Window revealed that my value from
the form was actually the ID number rather than the text being sorted by the
query. I changed my bound column to the appropriate one and everything works
fine. Sheesh. I should have looked into that sooner. Thanks, everyone, for
helping out!
--
Why are you asking me? I dont know what Im doing!

Jaybird


David Cox said:
off the top of my head, leading or trailing blanks? try Trim

Jaybird said:
I feel stupid for resorting to this but here goes... I can't figure out
what's wrong with my form based parameter query. It won't accept the
criteria entered into the form. I've got a form called frmPricingCriteria
and an unbound textbox called txtCompanyName. Also on the form is a
command
button which runs the query. The criteria for the CompanyName field on
the
query is [forms]![frmPricingCriteria]![txtCompanyName].

For giggles, here's the SQL: SELECT Contacts.CompanyName, [Order
Entry].[Part Number1], [Order Entry].[Start Temper], [Order Entry].[Finish
Temper], [Order Entry].Alloy, ProcessCodes.[Process Key]
FROM (Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID) INNER JOIN ProcessCodes ON [Order Entry].[ID Process
Key] =
ProcessCodes.[ID Process Key]
WHERE (((Contacts.CompanyName) Like NZ("*" &
[forms]![frmPricingCriteria]![txtCompanyName] & "*")))
ORDER BY Contacts.CompanyName, [Order Entry].[Part Number1];

Whenever I run my query using the criteria from the form, it returns no
records. If I enter parameters directly without the form, it works. So,
I
don't believe the problem is with my query. I believe the syntax for the
criteria is correct to read the values from my form. I've checked and
double
checked the spelling of the names for both the form and the textbox.
Everything appears to be right. What am I missing? BTW, the data I'm
using
as my sort criteria is valid as well. Please tell me its something I
didn't
know and not something easy that I overlooked!
Click to expand...
Click to expand...
 
J

John Spencer

Let's do a bit of trouble shooting.

Modify your query to show the value that you are getting from the form

SELECT Contacts.CompanyName
, [Order Entry].[Part Number1]
, [Order Entry].[Start Temper]
, [Order Entry].[Finish Temper]
, [Order Entry].Alloy
, ProcessCodes.[Process Key]
, & "//" & [forms]![frmPricingCriteria]![txtCompanyName] & "//" as FindThis
FROM (Contacts INNER JOIN [Order Entry]
ON Contacts.ContactID = [Order Entry].ContactID)
INNER JOIN ProcessCodes
ON [Order Entry].[ID Process Key] = ProcessCodes.[ID Process Key]
ORDER BY Contacts.CompanyName, [Order Entry].[Part Number1];

What do you see? Do you see what you expect to be searching for between the
"//"?

Minor aside, there is no need to use the NZ in your where criteria. If
txtCompanyName is null or blank the criteria will be Like "**" which should
find everything other than records where CompanyName is blank.

WHERE (((Contacts.CompanyName) Like "*" &
[forms]![frmPricingCriteria]![txtCompanyName] & "*"))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jaybird said:
I feel stupid for resorting to this but here goes... I can't figure out
what's wrong with my form based parameter query. It won't accept the
criteria entered into the form. I've got a form called frmPricingCriteria
and an unbound textbox called txtCompanyName. Also on the form is a
command
button which runs the query. The criteria for the CompanyName field on
the
query is [forms]![frmPricingCriteria]![txtCompanyName].

For giggles, here's the SQL: SELECT Contacts.CompanyName, [Order
Entry].[Part Number1], [Order Entry].[Start Temper], [Order Entry].[Finish
Temper], [Order Entry].Alloy, ProcessCodes.[Process Key]
FROM (Contacts INNER JOIN [Order Entry] ON Contacts.ContactID = [Order
Entry].ContactID) INNER JOIN ProcessCodes ON [Order Entry].[ID Process
Key] =
ProcessCodes.[ID Process Key]
WHERE (((Contacts.CompanyName) Like NZ("*" &
[forms]![frmPricingCriteria]![txtCompanyName] & "*")))
ORDER BY Contacts.CompanyName, [Order Entry].[Part Number1];

Whenever I run my query using the criteria from the form, it returns no
records. If I enter parameters directly without the form, it works. So,
I
don't believe the problem is with my query. I believe the syntax for the
criteria is correct to read the values from my form. I've checked and
double
checked the spelling of the names for both the form and the textbox.
Everything appears to be right. What am I missing? BTW, the data I'm
using
as my sort criteria is valid as well. Please tell me its something I
didn't
know and not something easy that I overlooked!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

selecting union query results 7
Please Look at this query 6
RecordsetNot Updateable 22
What makes a query updateable? 2
Error 3162 2
Name field with variable? 1
Multiple Parameter Crosstab query filter by form 1
Unique records 2

Top