Query with 2 parameters asks for 3

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

 

[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

 

[Avid Fan]

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.