If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query with 2 parameters asks for 3
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. |
#2
|
|||
|
|||
Query with 2 parameters asks for 3
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. |
#3
|
|||
|
|||
Query with 2 parameters asks for 3
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. |
Thread Tools | |
Display Modes | |
|
|