Code error

[Geordie2008]

I am trying to filter a form on two fields (one being a wildcard)

For example where:

Last name = smith
First name begins with = m*

The code falls over at:

stLinkCriteria = "[Last_Name]=" & "'" & Me![StaffLastName] & "'" And "[First_Name] Like " & " '" & Me![StaffFirstName] & "'*"


Can anyone see my error?
Thanks,
Mand


Option Compare Database

Private Sub cmdOpenStaffDetails_Test_Click()
'On Error GoTo Err_cmdOpenStaffDetails_Test_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmStaffDetails"

stLinkCriteria = "[Last_Name]=" & "'" & Me![StaffLastName] & "'" And "[First_Name] Like " & " '" & Me![StaffFirstName] & "'*"


DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms(stDocName).Modal = True

Exit_cmdOpenStaffDetails_Test_Click:
Exit Sub

Err_cmdOpenStaffDetails_Test_Click:
MsgBox Err.Description
Resume Exit_cmdOpenStaffDetails_Test_Click

End Sub

 

[Alc]

stLinkCriteria = "[Last_Name]= '" & Me![StaffLastName] & "' And [First_Name] Like '" Me![StaffFirstName] & "*'"

Think that might do it.

 

[Geordie2008]

Sadly not.... I imagine I just have the commas etc in the wrong place, but I have tried so many different variations of this code, I cant see the wood for the trees anymore....!

I get the same error as before with your code above.....

 

[Alc]

Sorry, must have been half asleep when I wrote that.

What we have so far is saying 'where the last name = last name and the first name is like first name. What we want to do is to compare the entire last name and ONLY the first character of the first name.
How about

stLinkCriteria = "Last_Name = '" & Me!StaffLastName & "' And Left(First_Name,1) = '" & Left(Me!StaffFirstName,1) & "'"

 

[Geordie2008]

I want the last name to match exactly but I wanted the user to be able to select starts with "m" or starts with "ma"

if they entered "m" then the results returned would be

Smith, Michael
Smith, Mandy
Smith, Michelle

etc.... so that if the user enetered

"ma" in the first name field only the following would be returned:

Smith, Mandy
mith, Mark

is this possible?

Thanks,
Mandy

 

[Alc]

Then we're back to your original 'Like' and I think the main issue was that the wildcard character was originally outside the quotation marks. The extra spaces may have cuased a problem too, but I'm not sure about that.

stLinkCriteria = "Last_Name = '" & Me!StaffLastName & "' And First_Name Like '" & Me!StaffFirstName & "*'"

If this doesn't work, have you tried displaying the values of Me!StaffFirstName, Me!StaffLastName and stLinkCriteria, just to see exactly what's getting set?

 

[Geordie2008]

works a treat... thanks so much!
Regards
Mandy

 

[Alc]

You're welcome (and thanks for letting me know it finally worked).