|
 |
access thread: Upper Case
Message #1 by "Dave Loukola" <dave.loukola@H...> on Tue, 29 Oct 2002 16:30:24
|
|
Here's an easy one.
In a standard query I want to filter in the criteria all records that only
have "*ST*" where ST is in caps. I don't want it to return "street"
or "Street", but would want it to return "STreet" or "STREET".
Thanks in advance
Dave
Message #2 by "Wesley Kendrick" <wez.k@n...> on Tue, 29 Oct 2002 16:27:36 -0000
|
|
Hi Dave
Look up the UCase() function in VB Help
Regards, Wesley Kendrick
----- Original Message -----
From: "Dave Loukola" <dave.loukola@H...>
To: "Access" <access@p...>
Sent: Tuesday, October 29, 2002 4:30 PM
Subject: [access] Upper Case
> Here's an easy one.
>
> In a standard query I want to filter in the criteria all records that only
> have "*ST*" where ST is in caps. I don't want it to return "street"
> or "Street", but would want it to return "STreet" or "STREET".
>
> Thanks in advance
>
>
> Dave
>
Message #3 by "Dave Loukola" <dave.loukola@H...> on Tue, 29 Oct 2002 17:14:26
|
|
Thanks, but I'm not trying to convert anything to Upper Case, I'm trying
to query on upper case letters. Access 97 seems to default to searching
for "*S*" Or "*s*" when it sees "*S*". I think that there could be a
control character that prevents this, but I haven't found it yet.
Maybe someone else knows the answer?
Thanks in advance
Dave
> Hi Dave
Look up the UCase() function in VB Help
Regards, Wesley Kendrick
----- Original Message -----
From: "Dave Loukola" <dave.loukola@H...>
To: "Access" <access@p...>
Sent: Tuesday, October 29, 2002 4:30 PM
Subject: [access] Upper Case
> Here's an easy one.
>
> In a standard query I want to filter in the criteria all records that
only
> have "*ST*" where ST is in caps. I don't want it to return "street"
> or "Street", but would want it to return "STreet" or "STREET".
>
> Thanks in advance
>
>
> Dave
>
Message #4 by Lonnie Johnson <prodevmg@y...> on Tue, 29 Oct 2002 09:20:02 -0800 (PST)
|
|
How is your VBA? Sounds like you need to write a function that will iterate thru the character string using the ASC() function for
charaters with a value between 65 and 90 (all capital letters).
Dave Loukola <dave.loukola@H...> wrote:Thanks, but I'm not trying to convert anything to Upper Case, I'm trying
to query on upper case letters. Access 97 seems to default to searching
for "*S*" Or "*s*" when it sees "*S*". I think that there could be a
control character that prevents this, but I haven't found it yet.
Maybe someone else knows the answer?
Thanks in advance
Dave
> Hi Dave
Look up the UCase() function in VB Help
Regards, Wesley Kendrick
----- Original Message -----
From: "Dave Loukola"
To: "Access"
Sent: Tuesday, October 29, 2002 4:30 PM
Subject: [access] Upper Case
> Here's an easy one.
>
> In a standard query I want to filter in the criteria all records that
only
> have "*ST*" where ST is in caps. I don't want it to return "street"
> or "Street", but would want it to return "STreet" or "STREET".
>
> Thanks in advance
>
>
> Dave
>
Lonnie Johnson
ProDev, Professional Development of MS Access Databases
Let me build your next MS Access database application.
---------------------------------
Do you Yahoo!?
HotJobs - Search new jobs daily now
Message #5 by "Gerald, Rand" <RGerald@u...> on Tue, 29 Oct 2002 11:29:54 -0600
|
|
Hi Dave,
You will need use the StrComp function instead of Ucase. You must also
make
certain that the vbUseCompareOption is vbBinaryCompare instead of the
vbDatabaseCompare. This will force the comparison to be case
sensitive.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Lonnie Johnson [mailto:prodevmg@y...]
Sent: Tuesday, October 29, 2002 11:20
To: Access
Subject: [access] Re: Upper Case
How is your VBA? Sounds like you need to write a function that will
iterate
thru the character string using the ASC() function for charaters with a
value between 65 and 90 (all capital letters).
Dave Loukola <dave.loukola@H...> wrote:Thanks, but I'm not
trying
to convert anything to Upper Case, I'm trying
to query on upper case letters. Access 97 seems to default to searching
for "*S*" Or "*s*" when it sees "*S*". I think that there could be a
control character that prevents this, but I haven't found it yet.
Maybe someone else knows the answer?
Thanks in advance
Dave
> Hi Dave
Look up the UCase() function in VB Help
Regards, Wesley Kendrick
----- Original Message -----
From: "Dave Loukola"
To: "Access"
Sent: Tuesday, October 29, 2002 4:30 PM
Subject: [access] Upper Case
> Here's an easy one.
>
> In a standard query I want to filter in the criteria all records that
only
> have "*ST*" where ST is in caps. I don't want it to return "street"
> or "Street", but would want it to return "STreet" or "STREET".
>
> Thanks in advance
>
>
> Dave
>
Lonnie Johnson
ProDev, Professional Development of MS Access Databases
Let me build your next MS Access database application.
---------------------------------
Do you Yahoo!?
HotJobs - Search new jobs daily now
Message #6 by "Gerald, Rand" <RGerald@u...> on Tue, 29 Oct 2002 11:55:33 -0600
|
|
Dave,
Here is some additional information from the Microsoft Knowledge Base.
http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;Q98227
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: Tuesday, October 29, 2002 11:30
To: Access
Subject: [access] Re: Upper Case
Importance: High
Hi Dave,
You will need use the StrComp function instead of Ucase. You must also
make
certain that the vbUseCompareOption is vbBinaryCompare instead of the
vbDatabaseCompare. This will force the comparison to be case
sensitive.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Lonnie Johnson [mailto:prodevmg@y...]
Sent: Tuesday, October 29, 2002 11:20
To: Access
Subject: [access] Re: Upper Case
How is your VBA? Sounds like you need to write a function that will
iterate
thru the character string using the ASC() function for charaters with a
value between 65 and 90 (all capital letters).
Dave Loukola <dave.loukola@H...> wrote:Thanks, but I'm not
trying
to convert anything to Upper Case, I'm trying
to query on upper case letters. Access 97 seems to default to searching
for "*S*" Or "*s*" when it sees "*S*". I think that there could be a
control character that prevents this, but I haven't found it yet.
Maybe someone else knows the answer?
Thanks in advance
Dave
> Hi Dave
Look up the UCase() function in VB Help
Regards, Wesley Kendrick
----- Original Message -----
From: "Dave Loukola"
To: "Access"
Sent: Tuesday, October 29, 2002 4:30 PM
Subject: [access] Upper Case
> Here's an easy one.
>
> In a standard query I want to filter in the criteria all records that
only
> have "*ST*" where ST is in caps. I don't want it to return "street"
> or "Street", but would want it to return "STreet" or "STREET".
>
> Thanks in advance
>
>
> Dave
>
Lonnie Johnson
ProDev, Professional Development of MS Access Databases
Let me build your next MS Access database application.
---------------------------------
Do you Yahoo!?
HotJobs - Search new jobs daily now
Message #7 by "Gerald, Rand" <RGerald@u...> on Tue, 29 Oct 2002 12:16:43 -0600
|
|
One more reference on case-insensitive queries:
Access Cookbook
Getz, Litwin and Baron
O'Reilly and Associates (2002)
Section 1.14 Page 47
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: Tuesday, October 29, 2002 11:56
To: Access
Subject: [access] Re: Upper Case
Dave,
Here is some additional information from the Microsoft Knowledge Base.
http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;Q98227
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: Tuesday, October 29, 2002 11:30
To: Access
Subject: [access] Re: Upper Case
Importance: High
Hi Dave,
You will need use the StrComp function instead of Ucase. You must also
make
certain that the vbUseCompareOption is vbBinaryCompare instead of the
vbDatabaseCompare. This will force the comparison to be case
sensitive.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Lonnie Johnson [mailto:prodevmg@y...]
Sent: Tuesday, October 29, 2002 11:20
To: Access
Subject: [access] Re: Upper Case
How is your VBA? Sounds like you need to write a function that will
iterate
thru the character string using the ASC() function for charaters with a
value between 65 and 90 (all capital letters).
Dave Loukola <dave.loukola@H...> wrote:Thanks, but I'm not
trying
to convert anything to Upper Case, I'm trying
to query on upper case letters. Access 97 seems to default to searching
for "*S*" Or "*s*" when it sees "*S*". I think that there could be a
control character that prevents this, but I haven't found it yet.
Maybe someone else knows the answer?
Thanks in advance
Dave
> Hi Dave
Look up the UCase() function in VB Help
Regards, Wesley Kendrick
----- Original Message -----
From: "Dave Loukola"
To: "Access"
Sent: Tuesday, October 29, 2002 4:30 PM
Subject: [access] Upper Case
> Here's an easy one.
>
> In a standard query I want to filter in the criteria all records that
only
> have "*ST*" where ST is in caps. I don't want it to return "street"
> or "Street", but would want it to return "STreet" or "STREET".
>
> Thanks in advance
>
>
> Dave
>
Lonnie Johnson
ProDev, Professional Development of MS Access Databases
Let me build your next MS Access database application.
---------------------------------
Do you Yahoo!?
HotJobs - Search new jobs daily now
Message #8 by "Dave Loukola" <dave.loukola@H...> on Tue, 29 Oct 2002 18:35:24
|
|
Thanks a lot. I figured there would be a simpler solution (not that the
solution is at all complicated) but it will do just fine.
Dave
Message #9 by "Bob Bedell" <bobbedell15@m...> on Tue, 29 Oct 2002 18:30:41 +0000
|
|
I'm thinking the InStr function, somethng like:
If InStr(1, SearchString, "ST") > 0 Then
frmAddress.txtField(intFields).SelStart = _
InStr(1, frmAddress.txtField(intFields), _
strFind, vbTextCompare) - 1
frmAddress.txtField(intFields).SelLength = Len(strFind)
frmAddress.txtField(intFields).SetFocus
Exit Sub
End If
>From: "Dave Loukola" <dave.loukola@H...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Upper Case
>Date: Tue, 29 Oct 2002 17:14:26
>
>Thanks, but I'm not trying to convert anything to Upper Case, I'm trying
>to query on upper case letters. Access 97 seems to default to searching
>for "*S*" Or "*s*" when it sees "*S*". I think that there could be a
>control character that prevents this, but I haven't found it yet.
>
>Maybe someone else knows the answer?
>
>Thanks in advance
>
>Dave
>
> > Hi Dave
>
>Look up the UCase() function in VB Help
>
>Regards, Wesley Kendrick
>
>----- Original Message -----
>From: "Dave Loukola" <dave.loukola@H...>
>To: "Access" <access@p...>
>Sent: Tuesday, October 29, 2002 4:30 PM
>Subject: [access] Upper Case
>
>
> > Here's an easy one.
> >
> > In a standard query I want to filter in the criteria all records that
>only
> > have "*ST*" where ST is in caps. I don't want it to return "street"
> > or "Street", but would want it to return "STreet" or "STREET".
> >
> > Thanks in advance
> >
> >
> > Dave
> >
>
_________________________________________________________________
Unlimited Internet access for only $21.95/month. Try MSN!
http://resourcecenter.msn.com/access/plans/2monthsfree.asp
Message #10 by "Gerald, Rand" <RGerald@u...> on Tue, 29 Oct 2002 12:32:24 -0600
|
|
Dave,
You may call it "EASY" but it's not. The Jet Database Engine does not
like
to do case-sensitive queries.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Dave Loukola [mailto:dave.loukola@H...]
Sent: Tuesday, October 29, 2002 10:30
To: Access
Subject: [access] Upper Case
Here's an easy one.
In a standard query I want to filter in the criteria all records that
only
have "*ST*" where ST is in caps. I don't want it to return "street"
or "Street", but would want it to return "STreet" or "STREET".
Thanks in advance
Dave
Message #11 by "Bob Bedell" <bobbedell15@m...> on Tue, 29 Oct 2002 19:00:49 +0000
|
|
Sorry,
Didn't mean to send that. I'm a little intrigued by this and am
trying to work it out using ADO.
If InStr(1, SearchString, "ST") > 0 Then ...
seems to be the way to go. InStr deafults to a binary comparison. If
InStr returns 0, the value was not found.
>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Upper Case
>Date: Tue, 29 Oct 2002 18:30:41 +0000
>
>I'm thinking the InStr function, somethng like:
>
>If InStr(1, SearchString, "ST") > 0 Then
>
> frmAddress.txtField(intFields).SelStart = _
> InStr(1, frmAddress.txtField(intFields), _
> strFind, vbTextCompare) - 1
> frmAddress.txtField(intFields).SelLength = Len(strFind)
> frmAddress.txtField(intFields).SetFocus
> Exit Sub
> End If
>
>
>
>
>
>
>>From: "Dave Loukola" <dave.loukola@H...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] Re: Upper Case
>>Date: Tue, 29 Oct 2002 17:14:26
>>
>>Thanks, but I'm not trying to convert anything to Upper Case, I'm trying
>>to query on upper case letters. Access 97 seems to default to searching
>>for "*S*" Or "*s*" when it sees "*S*". I think that there could be a
>>control character that prevents this, but I haven't found it yet.
>>
>>Maybe someone else knows the answer?
>>
>>Thanks in advance
>>
>>Dave
>>
>> > Hi Dave
>>
>>Look up the UCase() function in VB Help
>>
>>Regards, Wesley Kendrick
>>
>>----- Original Message -----
>>From: "Dave Loukola" <dave.loukola@H...>
>>To: "Access" <access@p...>
>>Sent: Tuesday, October 29, 2002 4:30 PM
>>Subject: [access] Upper Case
>>
>>
>> > Here's an easy one.
>> >
>> > In a standard query I want to filter in the criteria all records that
>>only
>> > have "*ST*" where ST is in caps. I don't want it to return "street"
>> > or "Street", but would want it to return "STreet" or "STREET".
>> >
>> > Thanks in advance
>> >
>> >
>> > Dave
>> >
>>
>
>
>_________________________________________________________________
>Unlimited Internet access for only $21.95/month. Try MSN!
>http://resourcecenter.msn.com/access/plans/2monthsfree.asp
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to
_________________________________________________________________
Choose an Internet access plan right for you -- try MSN!
http://resourcecenter.msn.com/access/plans/default.asp
Message #12 by "Hamilton. Tom" <hamiltont@s...> on Tue, 29 Oct 2002 11:50:17 -0800
|
|
Here is a relatively simple function .
1) Copy the following code into a new module:
2) Compile and Save
3) The key to this uses a particular Declaration of 'Option Compare
Binary' it is important that you create a new module, not just append
this to an existing one which typically has the line 'Option Compare
Database'. VBA will warn you if a countermanding Declaration exists, is
so, delete the line with the bad declaration and use the 'Option Compare
Binary'.
To execute, put the function call in an expression in your query, form
or report
'~~~~~~~~~ CODE START
Option Explicit
Option Compare Binary ' This makes the code CASE SENSITIVE
Function bInstr(sInput As String, sSrch As String, Optional iStart As
Integer) As Integer
' Binary search compare routine
' Input string to search
' string to locate
' number of position to begin search in string
' Returns Positive number of when found, Negative for not found
' ? binstr("booSTer","ST") returns 4
' ? binstr("booster","ST",3) returns -3
If iStart =3D 0 Then iStart =3D 1
bInstr =3D InStr(iStart, sInput, sSrch) - (iStart - 1)
End Function
'~~~~~~~~~~CODE ENDS
Ofcourse you may prefer to define the result to be an integer as
Tom Hamilton
T_Systems, Inc
(xxx) xxx-xxxx
-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Tuesday, October 29, 2002 11:01 AM
To: Access
Subject: [access] Re: Upper Case
Sorry,
Didn't mean to send that. I'm a little intrigued by this and am
trying to work it out using ADO.
If InStr(1, SearchString, "ST") > 0 Then ...
seems to be the way to go. InStr deafults to a binary comparison. If
InStr returns 0, the value was not found.
>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Upper Case
>Date: Tue, 29 Oct 2002 18:30:41 +0000
>
>I'm thinking the InStr function, somethng like:
>
>If InStr(1, SearchString, "ST") > 0 Then
>
> frmAddress.txtField(intFields).SelStart =3D _
> InStr(1, frmAddress.txtField(intFields), _
> strFind, vbTextCompare) - 1
> frmAddress.txtField(intFields).SelLength =3D
Len(strFind)
> frmAddress.txtField(intFields).SetFocus
> Exit Sub
> End If
>
>
>
>
>
>
>>From: "Dave Loukola" <dave.loukola@H...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] Re: Upper Case
>>Date: Tue, 29 Oct 2002 17:14:26
>>
>>Thanks, but I'm not trying to convert anything to Upper Case, I'm
trying
>>to query on upper case letters. Access 97 seems to default to
searching
>>for "*S*" Or "*s*" when it sees "*S*". I think that there could be a
>>control character that prevents this, but I haven't found it yet.
>>
>>Maybe someone else knows the answer?
>>
>>Thanks in advance
>>
>>Dave
>>
>> > Hi Dave
>>
>>Look up the UCase() function in VB Help
>>
>>Regards, Wesley Kendrick
>>
>>----- Original Message -----
>>From: "Dave Loukola" <dave.loukola@H...>
>>To: "Access" <access@p...>
>>Sent: Tuesday, October 29, 2002 4:30 PM
>>Subject: [access] Upper Case
>>
>>
>> > Here's an easy one.
>> >
>> > In a standard query I want to filter in the criteria all records
that
>>only
>> > have "*ST*" where ST is in caps. I don't want it to return
"street"
>> > or "Street", but would want it to return "STreet" or "STREET".
>> >
>> > Thanks in advance
>> >
>> >
>> > Dave
>> >
>>
>
>
>_________________________________________________________________
>Unlimited Internet access for only $21.95/month. Try MSN!
>http://resourcecenter.msn.com/access/plans/2monthsfree.asp
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to
_________________________________________________________________
Choose an Internet access plan right for you -- try MSN!
http://resourcecenter.msn.com/access/plans/default.asp
|
|
|
View Cart
