| Author |
Message |
Kerkuk
Newbie
Joined: 08 Dec 2011
Online Status: Offline
Posts: 22
|
  
Topic: Parameter with wild card
Posted: 08 Dec 2011 at 4:24am |
I have to create parameter with wild card % , The parameter should be Full name(first and last Name) without Middle initial , and I have table called People with two Fields called First name and last name and some of first names with middle initial?Please anyone can help me ? Example John I. Doe I just want the user enter (John Doe)
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Dec 2011 at 4:57am |
possibly you can create one string parameter (called ?Name in the example here)
and in your select statement use
({table.firstname) + " " + {table.lastname}) LIKE replace((replace({?name},".",""))," ","*")
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 08 Dec 2011 at 7:22am |
just building on DBlank's solution I would think that you would replace the % with * as CR uses the * for the wildcard (more Access than SQL Server)...(again, thanks to DBlank for that tip as well) if the user is just entering john doe, without the wildcard you could something like: local numbervar space := instr({?Name, " "}; {table.firstname) + " " + {table.lastname}) LIKE LEFT{?Name, space - 1} + "*" + MID({?Name}, space + 1) just a thought
|
| IP Logged |
|
Kerkuk
Newbie
Joined: 08 Dec 2011
Online Status: Offline
Posts: 22
|
Posted: 08 Dec 2011 at 7:43am |
Thank you DBlank , I have one more problem The last name field contains company name and crew member's names for same person.I suppose get two or three set of names , but I am getting just first and last name of person.Please any idea to solve this problem?Thanks in advance
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Dec 2011 at 8:04am |
can't quite 'see' the set up you are describing...
can you please show sample (fake) data from the last name rows and how the user inputs this 'name' into the param? Edited by DBlank - 08 Dec 2011 at 8:07am
|
| IP Logged |
|
Kerkuk
Newbie
Joined: 08 Dec 2011
Online Status: Offline
Posts: 22
|
Posted: 08 Dec 2011 at 8:44am |
LastName (Field) ------------- James I. Brown Jim Pusak Lone Star Company G/S Advanture Inc. ----------------------------- Name:Brown James I. DOB: 05/17/1985 SSN: 123456789 Name:Lone Star Company DOB: Blank SSN: UNKNOWN Name:Pusak Jim DOB:9/15/1974 SSN:654893524 Thanks again
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Dec 2011 at 8:50am |
so the user types in "Lone Star Company",...it returns only one record and some records a remissing, correct?
it currently returns what?
and you need it to return what?
|
| IP Logged |
|
Kerkuk
Newbie
Joined: 08 Dec 2011
Online Status: Offline
Posts: 22
|
Posted: 08 Dec 2011 at 8:55am |
When the user types in James I. Brown it returns only one record just James I.Brown and Lone Star Company missing same as Jim Pusak
|
| IP Logged |
|
Kerkuk
Newbie
Joined: 08 Dec 2011
Online Status: Offline
Posts: 22
|
Posted: 08 Dec 2011 at 8:56am |
|
I need to return anything brlong to James I.Brown like his company name and his crew names
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 08 Dec 2011 at 9:10am |
this would seem to point to my favorite solution... a stored proc, unless there is a table of crew people. Is it linked to the company or to James? my standard response of stored procedure is that they are much more flexible than CR (or any reporting system) when it joins to the tables. Let's see what DBlank comes up with...I'm sure that he guessed my solution.
|
| IP Logged |
|
|
|
