Using wild cards in VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex - 06 Nov 2006 20:28 GMT

How can I re-write the below code to allow users to use wildcards in the
SearchPart field. They'd like to be able to type S* and return all
SearchParts that start with S or type St* and return all SearchParts that
start with St. I'm not sure how to write it. Thanks.

stLinkCriteria = "[Part#]=" & "'" & Me![SearchPart] & "'"
If IsNull(Me.Model) = False Then
stLinkCriteria = stLinkCriteria & "AND [Model#]=" & "'" & Me![Model] & "'"
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria

If IsNull(Me.Model) Then
Forms!NewPartInputfrm!CountRecords = DCount("[Part#]", "AllNewParts",
"[PART#] = [SearchPart]")

Else

Reply to this Message

Douglas J. Steele - 06 Nov 2006 20:39 GMT

stLinkCriteria = "[Part#] Like " & "'" & Me![SearchPart] & "*'"

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> How can I re-write the below code to allow users to use wildcards in the
> SearchPart field. They'd like to be able to type S* and return all
[quoted text clipped - 13 lines]
>
> Else

Reply to this Message

Alex - 06 Nov 2006 21:45 GMT

Perfect! Thank you.

> stLinkCriteria = "[Part#] Like " & "'" & Me![SearchPart] & "*'"
>
[quoted text clipped - 15 lines]
> >
> > Else

Reply to this Message

Van T. Dinh - 06 Nov 2006 20:40 GMT

Try:

Forms!NewPartInputfrm!CountRecords = DCount("[Part#]", "AllNewParts", _
"[PART#] Like '" & [SearchPart] "*'")

(users only need to enter S or St)

Signature

HTH
Van T. Dinh
MVP (Access)

> How can I re-write the below code to allow users to use wildcards in the
> SearchPart field. They'd like to be able to type S* and return all
[quoted text clipped - 13 lines]
>
> Else

Reply to this Message

Klatuu - 06 Nov 2006 20:58 GMT

Why the DCount, Van?
The OP wants the records, not the count.

> Try:
>
[quoted text clipped - 20 lines]
> >
> > Else

Reply to this Message

Douglas J. Steele - 06 Nov 2006 22:33 GMT

The OP had the DCount in his question.

You & I concentrated on the first part of the VBA, Van apparently
concentrated on the second part. <g>

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Why the DCount, Van?
> The OP wants the records, not the count.
[quoted text clipped - 28 lines]
>> >
>> > Else

Reply to this Message

Van T. Dinh - 07 Nov 2006 00:34 GMT

I claimed I have just got up and the logical part of the brain is not quite
there yet when I posted the reply ...

Signature

Cheers
Van

> The OP had the DCount in his question.
>
> You & I concentrated on the first part of the VBA, Van apparently
> concentrated on the second part. <g>

Reply to this Message

Van T. Dinh - 06 Nov 2006 21:01 GMT

Sorry, use Doug's ...

I left out (or the keyboard was sticky?) the ampersand (&) in my expression
...

Signature

HTH
Van T. Dinh
MVP (Access)

Reply to this Message