Wildcard Question

[Guest]

Hi,

I have a command button that opens another form and filters the information
based on a job number and "FCN" Number. Due to various billing amounts by
the same contractor on the same job, I have added a suffix to the job number.
ie. 25168, 25168C,25168SF.

I have the following code:

Private Sub cmdViewTickets_Click()
On Error GoTo Err_cmdViewTickets_Click

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "EXTRA WORK TICKETS EDITfrm"

stLinkCriteria = "[FIELDWORKFCNNUM]=" & Me![FCNNUM] & "AND" &
"[FIELDWORKJOBNUM]=" & "'" & Me![FCNJOBNUM] & "*" & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdViewTickets_Click:
Exit Sub

Err_cmdViewTickets_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdViewTickets_Click

End Sub

The watch window shows that the value for stLinkCriteria is:

"[FIELDWORKFCNNUM]=1AND[FIELDWORKJOBNUM]='25168*'"

(25168 is the root job number)

The form opens blank. Why doesn't '25168*' return 25168SF or 25168C or 25168?
What can I do to get any record matching with these job numbers to be
diplayed in the form?

TIA

 

[Douglas J. Steele]

Looks as though you're missing a space between the word AND and
[FIELDWORKJOBNUM].

 

[Guest]

Try the following modifications:

1.) Add spaces as indicated here: & " AND " &
2.) Use the LIKE keyword

stLinkCriteria = "[FIELDWORKFCNNUM]=" & Me![FCNNUM] & " AND " &
"[FIELDWORKJOBNUM] LIKE " & "'" & Me![FCNJOBNUM] & "*" & "'"


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Douglas J Steele]

D'oh. How'd I miss point 2!

Good eyes, Tom.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try the following modifications:

1.) Add spaces as indicated here: & " AND " &
2.) Use the LIKE keyword

stLinkCriteria = "[FIELDWORKFCNNUM]=" & Me![FCNNUM] & " AND " &
"[FIELDWORKJOBNUM] LIKE " & "'" & Me![FCNJOBNUM] & "*" & "'"


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi,

I have a command button that opens another form and filters the information
based on a job number and "FCN" Number. Due to various billing amounts by
the same contractor on the same job, I have added a suffix to the job number.
ie. 25168, 25168C,25168SF.

I have the following code:

Private Sub cmdViewTickets_Click()
On Error GoTo Err_cmdViewTickets_Click

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "EXTRA WORK TICKETS EDITfrm"

stLinkCriteria = "[FIELDWORKFCNNUM]=" & Me![FCNNUM] & "AND" &
"[FIELDWORKJOBNUM]=" & "'" & Me![FCNJOBNUM] & "*" & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdViewTickets_Click:
Exit Sub

Err_cmdViewTickets_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdViewTickets_Click

End Sub

The watch window shows that the value for stLinkCriteria is:

"[FIELDWORKFCNNUM]=1AND[FIELDWORKJOBNUM]='25168*'"

(25168 is the root job number)

The form opens blank. Why doesn't '25168*' return 25168SF or 25168C or 25168?
What can I do to get any record matching with these job numbers to be
diplayed in the form?

TIA

 

[Guest]

Hi Doug,

Just so that you will know, I wasn't trying to correct you. We posted
independently, within minutes of each other. I had not seen your answer when
I posted my answer.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

D'oh. How'd I miss point 2!

Good eyes, Tom.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try the following modifications:

1.) Add spaces as indicated here: & " AND " &
2.) Use the LIKE keyword

stLinkCriteria = "[FIELDWORKFCNNUM]=" & Me![FCNNUM] & " AND " &
"[FIELDWORKJOBNUM] LIKE " & "'" & Me![FCNJOBNUM] & "*" & "'"


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi,

I have a command button that opens another form and filters the information
based on a job number and "FCN" Number. Due to various billing amounts by
the same contractor on the same job, I have added a suffix to the job number.
ie. 25168, 25168C,25168SF.

I have the following code:

Private Sub cmdViewTickets_Click()
On Error GoTo Err_cmdViewTickets_Click

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "EXTRA WORK TICKETS EDITfrm"

stLinkCriteria = "[FIELDWORKFCNNUM]=" & Me![FCNNUM] & "AND" &
"[FIELDWORKJOBNUM]=" & "'" & Me![FCNJOBNUM] & "*" & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdViewTickets_Click:
Exit Sub

Err_cmdViewTickets_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdViewTickets_Click

End Sub

The watch window shows that the value for stLinkCriteria is:

"[FIELDWORKFCNNUM]=1AND[FIELDWORKJOBNUM]='25168*'"

(25168 is the root job number)

The form opens blank. Why doesn't '25168*' return 25168SF or 25168C or 25168?
What can I do to get any record matching with these job numbers to be
diplayed in the form?

TIA