Using wild cards in VBA

[Guest]

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

 

[Douglas J. Steele]

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

 

[Van T. Dinh]

Try:

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

(users only need to enter S or St)

 

[Guest]

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

Try:

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

(users only need to enter S or St)

--
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
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

 

[Van T. Dinh]

Sorry, use Doug's ...

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

--
HTH
Van T. Dinh
MVP (Access)



news:[email protected]...

 

[Guest]

Perfect! Thank you.

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

--
Doug Steele, Microsoft Access MVP

(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
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

 

[Douglas J. Steele]

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>

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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

Try:

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

(users only need to enter S or St)

--
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
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

 

[Van T. Dinh]

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