Re: Case insensitive WHERE statement in query
Adrian was right. I needed to use LOWER(LastName) in my query. I was trying LCase(LastName) which does not work with Access. So, my statement now reads (and works) as follows: WHERE Lower(LastName) LIKE '#LCase(SearchString)#%' ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230750 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Case insensitive WHERE statement in query
I think he was asking for case insensitive. Tim? Ade -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: 30 January 2006 16:42 To: CF-Talk Subject: Re: Case insensitive WHERE statement in query hmm...i wouldn't have thought that would be case-sensitive. you sure? either way, both Access and CF have ucase() functions, so try: WHERE ucase(lastName) LIKE '#ucase(stringToLookFor)#%' (lcase should work too) On 1/30/06, Claremont, Timothy <[EMAIL PROTECTED]> wrote: > Using Access as my database, how would I write a query statement with a > LIKE operator that is completely case insensitive? If the end user > searches for DeChantel; Dechantel deChantel, I want it to match the real > name, which is deChantel. > > Using the following statement, the case must match in order to return a > match. > > WHERE LastName LIKE '#StringToLookFor#%' > > ** > This email and any files transmitted with it are confidential and > intended solely for the use of the individual or entity to whom they > are addressed. If you have received this email in error please delete it from your system. > > This footnote also confirms that this email message has been swept for > the presence of computer viruses. > > Thank You, > Viahealth > ** > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230749 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Case insensitive WHERE statement in query
hmm...i wouldn't have thought that would be case-sensitive. you sure? either way, both Access and CF have ucase() functions, so try: WHERE ucase(lastName) LIKE '#ucase(stringToLookFor)#%' (lcase should work too) On 1/30/06, Claremont, Timothy <[EMAIL PROTECTED]> wrote: > Using Access as my database, how would I write a query statement with a > LIKE operator that is completely case insensitive? If the end user > searches for DeChantel; Dechantel deChantel, I want it to match the real > name, which is deChantel. > > Using the following statement, the case must match in order to return a > match. > > WHERE LastName LIKE '#StringToLookFor#%' > > ** > This email and any files transmitted with it are confidential and > intended solely for the use of the individual or entity to whom they > are addressed. If you have received this email in error please delete it from > your system. > > This footnote also confirms that this email message has been swept for > the presence of computer viruses. > > Thank You, > Viahealth > ** > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230747 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Case insensitive WHERE statement in query
Force both to either upper or lower case.
WHERE LOWER(LastName) LIKE LOWER('#stringToLookFor#')
Check it's LOWER in Access, cos I'm not sure.
Adrian
-Original Message-
From: Claremont, Timothy [mailto:[EMAIL PROTECTED]
Sent: 30 January 2006 16:36
To: CF-Talk
Subject: Case insensitive WHERE statement in query
Using Access as my database, how would I write a query statement with a
LIKE operator that is completely case insensitive? If the end user
searches for DeChantel; Dechantel deChantel, I want it to match the real
name, which is deChantel.
Using the following statement, the case must match in order to return a
match.
WHERE LastName LIKE '#StringToLookFor#%'
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please delete it
from your system.
This footnote also confirms that this email message has been swept for
the presence of computer viruses.
Thank You,
Viahealth
**
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230746
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Case insensitive WHERE statement in query
Using Access as my database, how would I write a query statement with a LIKE operator that is completely case insensitive? If the end user searches for DeChantel; Dechantel deChantel, I want it to match the real name, which is deChantel. Using the following statement, the case must match in order to return a match. WHERE LastName LIKE '#StringToLookFor#%' ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please delete it from your system. This footnote also confirms that this email message has been swept for the presence of computer viruses. Thank You, Viahealth ** ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230744 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
