"Like" query

[Paul Fenton]

The lines below are used to check if there's a matching record to the
9 digit ID the user has entered on the form frmNewSubjects. Til now,
there could only be one instance of that ID in the Subjects table.

With qdf
.Parameters("forms!frmNewSubjects!txtsubjectid") = _
Forms!frmNewSubjects.txtSubjectID
Set rsSubject = .OpenRecordset()
End With

We now have a situation where there could be multiple instances of
that ID, but differentiated by an appended letter. For example:

111111111
111111111A
111111111B
etc.

I need to modify those lines above so that I can find every record
where the 1st 9 characters match the number entered by the user.

If I was doing it in a query form, I'd use

'Like forms!frmNewSubjects.txtSubjectID & *'

but I can't figure out how to get that form into the .Parameters line.


Paul Fenton
(e-mail address removed)

 

[Guest]

.... Like Forms!frmNewSubjects.txtSubjectID & "*"

HTH
Van T. Dinh
MVP (Access)

 

[Paul Fenton]

Van, I tried your suggestion with this..

With qdf
.Parameters("forms!frmNewSubjects!txtsubjectid") = _
Like Forms!frmNewSubjects.txtSubjectID & "*"
Set rsSubject = .OpenRecordset()
End With

It doesn't work.


Paul Fenton

 

[Guest]

No, you cannot include "Like" as part of the Parameter value.

What I meant is the criteria (*in SQL View*) should be:

WHERE [Field1] Like Forms!frmNewSubjects.txtSubjectID & "*"

and "Forms!frmNewSubjects.txtSubjectID" will be treated as a Parameter which
you can resolve as usual.

HTH
Van T. Dinh
MVP (Access)