Query with 2 parameters asks for 3

A

Avid Fan

I want to search on first and last name at the same time, so I have made
a combined field called BigName. When I run this query I get a request
for three parameters not two. BigName comes up as a parameter.

Query1

PARAMETERS mBigName Text ( 15 ), mSuburb1 Text ( 15 );
SELECT Customer.ID, Customer.Title, Customer.First_Name,
Customer.Last_Name, Customer.Address1, Customer.Suburb1,
Customer.State1, Customer.Postcode1, Customer.Phone, Customer.Target,
Trim(Customer.First_Name)+' '+Trim(Customer.Last_Name) AS BigName
FROM Customer
WHERE (((Customer.Suburb1) LIKE LTrim(Trim([mSuburb1]))+'*') AND
(([BigName]) LIKE '*'+LTrim(Trim([mBigName]))+'*'))
ORDER BY Customer.Last_Name, Customer.Suburb1, Customer.Target;

Any help appreciated.
 
J

John Spencer

Your problem is that you cannot refer to the calculated field by name in the
where clause. At the time the where clause is executed the calculated field
does not yet exist. You have to reiterate the calculation in the where clause.

PARAMETERS mBigName Text ( 15 ), mSuburb1 Text ( 15 );
SELECT Customer.ID, Customer.Title, Customer.First_Name, Customer.Last_Name,
Customer.Address1, Customer.Suburb1, Customer.State1, Customer.Postcode1,
Customer.Phone, Customer.Target
, Trim(Customer.First_Name)+' '+Trim(Customer.Last_Name) AS BigName
FROM Customer
WHERE (((Customer.Suburb1) LIKE LTrim(Trim([mSuburb1]))+'*')
AND ((Trim(Customer.First_Name)+' '+Trim(Customer.Last_Name)) LIKE
'*'+LTrim(Trim([mBigName]))+'*'))
ORDER BY Customer.Last_Name, Customer.Suburb1, Customer.Target;

I'm not sure why you are using all the TRIM commands. Especially since
LTRIM(TRIM(X)) will always give the same result as TRIM(X).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
A

Avid Fan

John said:
Your problem is that you cannot refer to the calculated field by name in
the where clause. At the time the where clause is executed the
calculated field does not yet exist. You have to reiterate the
calculation in the where clause.

PARAMETERS mBigName Text ( 15 ), mSuburb1 Text ( 15 );
SELECT Customer.ID, Customer.Title, Customer.First_Name,
Customer.Last_Name, Customer.Address1, Customer.Suburb1,
Customer.State1, Customer.Postcode1, Customer.Phone, Customer.Target
, Trim(Customer.First_Name)+' '+Trim(Customer.Last_Name) AS BigName
FROM Customer
WHERE (((Customer.Suburb1) LIKE LTrim(Trim([mSuburb1]))+'*')
AND ((Trim(Customer.First_Name)+' '+Trim(Customer.Last_Name)) LIKE
'*'+LTrim(Trim([mBigName]))+'*'))
ORDER BY Customer.Last_Name, Customer.Suburb1, Customer.Target;

Thank you very much!!!!!!
I'm not sure why you are using all the TRIM commands. Especially since
LTRIM(TRIM(X)) will always give the same result as TRIM(X).

In XBase and Visual Fox Pro Trim() only removes blank spaces to the
right of the text, LTrim to the left.

If you what to do both you use allTrim(). VB is different.
 

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

Top