Solved

ColdFusion How can I adjust my search criteria to include/not include dashes?

Posted on 2013-06-28
18
452 Views
Last Modified: 2013-06-29
How can my search criteria find a number with or with out dashes included?

Example:
Numbers are in one field in an Access database formated: 1234-56-789-1011

I would like the search to find this record (1234-56-789-1011) when the user types in:

1234-56-789-1011
1234567891011 (no dashes inputted by user)
1011
 
I.E. The search will find the record if dashes are inputted or not.
0
Comment
Question by:DJPr0
  • 6
  • 6
  • 4
  • +2
18 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 39285131
Use the like operator ... i think the access version of it uses *


<cfquery...>
select * from tbl where field like "*#Form.searchstring#*"
</cfquery

it should find any instance of the characters
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 39285133
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 39285154
I would suggest stripping out dashing and comparing them without dashes

Not sure the command in Access, something like replace(string,'-','')
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 39285170
gd - that won't help if they search by a partial ie: 1011
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39285180
Strip out all but alphanumeric characters before doing the search.  I use this function:

Public Function StripNonAlphaNumericChars(strText As String) As String
'Strips a variety of non-alphanumeric characters from a text string
'Created by Helen Feddema 10-15-97
'Modified by Ruud H.G. van Tol 6-18-99
'Modified by Brad Beacham 6-Feb-2005
'Last modified by Helen Feddema 7-Feb-2005

On Error GoTo ErrorHandler

   Dim strTestString As String
   Dim strBadChar As String
   Dim i As Integer
   Dim strStripChars As String

   strStripChars = " `~!@#$%^&*()-_=+[{]};:',<.>/?" & Chr$(34) & Chr$(13) & Chr$(10)
   strTestString = strText

   For i = 1 To Len(strStripChars)
      strBadChar = Mid(strStripChars, i, 1)
      strTestString = Replace(strTestString, strBadChar, vbNullString)
   Next

   StripNonAlphaNumericChars = strTestString
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in StripNonAlphaNumericChars procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window


It is very useful for processing phone numbers that may be entered in a variety of formats -- I convert them to all numbers, then apply a standard formatting before saving them.
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 39285329
That's a nice function but I think that the UI is web based (hence the coldfusion TA) so the solution has to be either straight sql or coldfusion based...

(i may be wrong...)
0
 

Author Comment

by:DJPr0
ID: 39285502
SidFishes you are correct - this is a web based UI via CF.

The * didn't work, I tried:
 AND GSN LIKE '*#GSN#*'        No results, even if I input the exact number
 AND GSN LIKE "*#GSN#*"       Produces an error
 AND GSN LIKE '*%#GSN#%*'  Nothing
 AND GSN LIKE '%*#GSN#*%'  Nothing

The statement below works for partials I.E. 1011, but not for dashes 1234567891011 (no dashes inputted by user)

Here is my actual search statement:
<cfif GSN Is NOT "">
			   <cfif GSN Is NOT "ALL">
                           <cfif Exact Is NOT "No">
				       AND GSN = '#GSN#' 
					   <cfelse>
				        AND GSN LIKE '%#GSN#%' 
					  </cfif>
				  </cfif>	
                </cfif>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 39285547
You need to strip out the dashes from the database field and the input search string

<cfset Form.searchstring = replace(Form.searchstring,"-","")>

select *
 from tbl
where replace(field,'-','') like "*#Form.searchstring#*"
0
 

Author Comment

by:DJPr0
ID: 39285835
Not sure what I'm doing wrong:

My actual statements:
<cfset #Form.GSN# = replace(Form.GSN,"-","")>


<cfif GSN Is NOT "">
		 <cfif GSN Is NOT "ALL">
                      <cfif Exact Is NOT "No">
				       AND GSN = '#GSN#' 
					   <cfelse>

                   AND replace(#GSN#,'-','') like '%#Form.GSN#%'

				    <!---    AND GSN LIKE '%#Form.GSN#%' --->
					  </cfif>
				  </cfif>	
                </cfif>

Open in new window


Error:
 Syntax error (missing operator) in query expression '1=1 AND (received is null OR received < project) AND GSN replace([2-],[-],[]) like '*2*''.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 39285880
is GSN the column name?

 AND replace(#GSN#,'-','') like '%#Form.GSN#%'


If it is, it should not be enclosed in #'s

 AND replace(GSN,'-','') like '%#Form.GSN#%'

as I said before, I am not sure if replace() is the correct function name for access, you may have to look that up.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39286174
DJPr0,

Just my 2c...

As you can see this is not easy.
...nor is it the standard way most searches are conducted (hence the difficulty)

Why not just keep it simple and just let them search the numbers only?
I don't really see any benefit to adding the dashes.

If the number is "formatted" with dashes, the the dashes are not stored anyway...
A true query only inspects the actual stored value, ...not the formatted value.

Finally, if they will be typing in the "entire" value (as you show in your example), then why not just give them a combobox?


JeffCoachman
0
 

Author Comment

by:DJPr0
ID: 39286907
Jeff - The project manager wants to see the hyphens on the output results and not interested in a combobox for some reason.

Everyone - Could I store the numbers in the database without the dashes and automatically add them on the output via CF?

AND replace(GSN,'-','') like '%#Form.GSN#%' Can not get this part to work - error: Syntax error (missing operator) in query expression

Read conflicting reports if Replace works with an Access database.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 39286945
> Could I store the numbers in the database without the dashes and automatically add them on the output via CF?

Of course, you have a couple of options here.
1) You can strip out all dashes and store the number without dashes in the database, then when displaying them, put the dashes back in.   This will work if the dashes are always in the same spot and it never will change.
2) Store two values of the number (in two different fields).  One formatted and one without dashes.  Search on the unformatted column and display the formatted colum
3) store the number formatted and simply strip out the dashes when doing a search.  The search may be a tad slower, but if you don't have tens of thousand of records, it should not be a big deal


To remove dashes in your query...

http://webcheatsheet.com/SQL/access_functions/replace.php

the example shows double quotes, I don't know if that's necessary in access or not

Replace("Green Apple", "Green", "Yellow")      returns "Yellow Apple"
Replace("Green Apple", "e", "a", 7)                returns "Green Appla"
Replace("Green Apple", "e", "a", 1, 2)            returns "Graan Apple"

Access uses * not %

Seems that this should work:

AND Replace(GSN,"-","") like "*#Form.GSN#*"
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 39286946
sorry, just found out that MS Access can use either * or %
either are Ok
0
 

Author Comment

by:DJPr0
ID: 39286968
AND Replace(GSN,"-","") like "*#Form.GSN#*"  

This statement produces an error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3.
 

AND Replace(GSN,"-","","all") like "*#Form.GSN#*"

This statement produces an error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 4.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 39287052
That's weird..

http://www.techonthenet.com/access/functions/string/replace.php

Replace ( string1, find, replacement, [start, [count, [compare]]] )


AND Replace(GSN,"-","",1)
0
 

Author Comment

by:DJPr0
ID: 39287095
gdemaria - Finally got it to work with your previous suggestion:

 AND Replace(GSN,'-','') Like '%#Form.GSN#%'

Not sure what happen!
0
 

Author Closing Comment

by:DJPr0
ID: 39287379
Thanks gdemaria!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now