Prev: Days in part of the month
Next: parameter query issue with single date
From: Avid Fan on 21 Mar 2010 06:00
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.

From: John Spencer on 21 Mar 2010 08:47
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 wrote:
> 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.
>
From: Avid Fan on 21 Mar 2010 09:37
John Spencer wrote:
> 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.
 | 
Pages: 1
Prev: Days in part of the month
Next: parameter query issue with single date