Can I force a query criteria to limit to Like "*ABC*" - forcing the cap's ?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • MLH
    #1

    Can I force a query criteria to limit to Like "*ABC*" - forcing the cap's ?

    If I drop Like "*ABC*" in a QBE grid criteria cell,
    the records returned include mixed case. Can I
    force the uppercase limitation in a QBE grid?
    Tags: None
    • Salad
      #2
      Re: Can I force a query criteria to limit to Like "*ABC*&quo t; - forcingthe cap's ?

      MLH wrote:
      If I drop Like "*ABC*" in a QBE grid criteria cell,
      the records returned include mixed case. Can I
      force the uppercase limitation in a QBE grid?
      You could create a calculated column. Ex:
      UcaseField:Ucas e(FieldName)
      then put the filter criteria under that column. You could also use
      StrConv() as well.

      Hanahana
      http://www.youtube.com/watch?v=VoI4-bkcYmc&feature=related


        Comment

        • MLH
          #3
          Re: Can I force a query criteria to limit to Like "*ABC*&quo t; - forcing the cap's ?

          On Tue, 27 May 2008 09:53:25 -0700, Salad <oil@vinegar.co mwrote:
          >MLH wrote:
          >
          >If I drop Like "*ABC*" in a QBE grid criteria cell,
          >the records returned include mixed case. Can I
          >force the uppercase limitation in a QBE grid?
          >
          >You could create a calculated column. Ex:
          > UcaseField:Ucas e(FieldName)
          >then put the filter criteria under that column. You could also use
          >StrConv() as well.
          >
          >Hanahana
          >http://www.youtube.com/watch?v=VoI4-...eature=related
          Not a bad idea. But there's one gotcha. (there's always
          one of them, isn't there?)

          I have rows that contain say "NC" ==the state abbreviation
          for North Carolina. And I have rows that contain XYZ Company, Inc
          (which has an "nc" in it). I ONLY want the rows containing the
          uppercase NC anywhere in the string.

            Comment

            • MLH
              #4
              Re: Can I force a query criteria to limit to Like &quot;*ABC*&quo t; - forcing the cap's ?

              I've tried crit's like Like "*" & Chr$(78) & "*"

              but they return strings with both N's and n's.
              Forcing the uppercase characteristic of a given
              letter of the alphabet may just not be possible in
              a query. But I'm not giving up hope.

                Comment

                • Rick Brandt
                  #5
                  Re: Can I force a query criteria to limit to Like &quot;*ABC*&quo t; - forcing the cap's ?

                  MLH wrote:
                  I've tried crit's like Like "*" & Chr$(78) & "*"
                  >
                  but they return strings with both N's and n's.
                  Forcing the uppercase characteristic of a given
                  letter of the alphabet may just not be possible in
                  a query. But I'm not giving up hope.
                  InStr() with the binary compare option is the only way to get case
                  sensitivity in Access.

                  --
                  Rick Brandt, Microsoft Access MVP
                  Email (as appropriate) to...
                  RBrandt at Hunter dot com


                    Comment

                    • MLH
                      #6
                      Re: Can I force a query criteria to limit to Like &quot;*ABC*&quo t; - forcing the cap's ?

                      On Tue, 27 May 2008 16:56:25 -0500, "Rick Brandt"
                      <rickbrandt2@ho tmail.comwrote:
                      >MLH wrote:
                      >I've tried crit's like Like "*" & Chr$(78) & "*"
                      >>
                      >but they return strings with both N's and n's.
                      >Forcing the uppercase characteristic of a given
                      >letter of the alphabet may just not be possible in
                      >a query. But I'm not giving up hope.
                      >
                      >InStr() with the binary compare option is the only way to get case
                      >sensitivity in Access.
                      Thanks Rick. OK, Option compare Binary in a procedure
                      until after I'm finished running the query, then set it back to
                      Option compare Text ==Is that the recommended way?

                        Comment

                        • MLH
                          #7
                          Re: Can I force a query criteria to limit to Like &quot;*ABC*&quo t; - forcing the cap's ?

                          Oops...
                          Looking at the InStr HELP, I see. You're recommending this syntax...
                          MyPos = Instr(1, SearchString, SearchChar, 0)
                          .... the key point being the 0 value for the 4th argument. Thanks.
                          That is the most direct approach.

                          xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxx


                          On Tue, 27 May 2008 16:56:25 -0500, "Rick Brandt"
                          <rickbrandt2@ho tmail.comwrote:
                          >MLH wrote:
                          >I've tried crit's like Like "*" & Chr$(78) & "*"
                          >>
                          >but they return strings with both N's and n's.
                          >Forcing the uppercase characteristic of a given
                          >letter of the alphabet may just not be possible in
                          >a query. But I'm not giving up hope.
                          >
                          >InStr() with the binary compare option is the only way to get case
                          >sensitivity in Access.

                            Comment

                            • lyle fairfield
                              #8
                              Re: Can I force a query criteria to limit to Like &quot;*ABC*&quo t; - forcingthe cap's ?

                              On May 28, 9:28 am, MLH <C...@NorthStat e.netwrote:
                              On Tue, 27 May 2008 16:56:25 -0500, "Rick Brandt"
                              >
                              <rickbran...@ho tmail.comwrote:
                              MLH wrote:
                              I've tried crit's like    Like "*" & Chr$(78) & "*"
                              >
                              but they return strings with both N's and n's.
                              Forcing the uppercase characteristic of a given
                              letter of the alphabet may just not be possible in
                              a query. But I'm not giving up hope.
                              >
                              InStr() with the binary compare option is the only way to get case
                              sensitivity in Access.
                              >
                              Thanks Rick. OK,   Option compare Binary in a procedure
                              until after I'm finished running the query, then set it back to
                              Option compare Text  ==Is that the recommended way?
                              SELECT *
                              FROM Customers
                              WHERE StrComp([First Name],[FirstName],0) = 0

                              successfully selects "ANTONIO" but not "Antonio" when the parameter
                              [FirstName] is passed as "ANTONIO".

                              Changing the Option Compare Default, in my opinion, is unnecessary and
                              I would be hesitant about doing so.



                                Comment

                                Previous template Next
                                Working...