Search criteria

[Matt Brown]

Hi all,

I am trying to get the following expression to pull a record into a form if only part of the data is entered
i.e. if 123 was entered and there are records called 123456 then this would be displayed.
i cant seem to get the mask right.
here is the code which works with exact criteria entered:

stLinkCriteria = "[Top Level Assy No]=" & "'" & Me![Text21] & "'"

I know i can use the:
Like'*'&

at the start and the

&'*'

at the end but seem to be struggling with this.
Any help appreciated.
Matt

 

[simongallop]

presuming that 123456 is Top Level Assy No then:

stLinkCriteria = "[Top Level Assy No]= Like *" & Me![Text21] & "*"

Ought to do it! If it doesn't then try * ' " & Me![Text21] & " ' *"

HTH

 

[Pat Hartman]

I think that this will work better:

stLinkCriteria = "[Top Level Assy No] Like " & Me![Text21] & "*"

 

[Matt Brown]

Thanks Pat and Harry,

Will try these out.
Been bugging me for a while now so hopefully your answers will be okay.

Matt

 

[Matt Brown]

Hi,
Just tried your suggestions and get an error saying "missing operator in query expression"

Have entered the following:

stLinkCriteria = "[Top Level Assy No]Like " & Me![Text31] & "*"
DoCmd.OpenForm stDocName, , , stLinkCriteria

(The text box has a different name due to placing another one onto my form as a test)

Any ideas on what the missing operator is?

thanks
Matt

 

[simongallop]

Space between the field name and LIKE

 

[Matt Brown]

Tried the space, still get the syntax error.
Tried quite a few permentations now and the closest i got was for it to open the form but no fields or objects were shown apart from a counter in the forms header.

There must be a way to do this!!!!

Talk about frustrating

Matt

 

[Alexandre]

Missing single quotes around text values:

stLinkCriteria = "[Top Level Assy No] Like '" & Me![Text31] & "*'"

 

[Matt Brown]

Alexandre,
thats it, it works!!!

Thanks for everyones inputs with this.
Just one small thing, is it possible to place the wildcard * at the start as well so that any permatation of numbers will be found?

Thanks again everyone.
Matt

 

[Alexandre]

Yes.
However, the easiest way to know would be to just give it a try, don't you think?