Type mismatch in open recordset 3

[jadams0173]

I'm try to use this expression for my recordset. I get a type mismatch error. I've tried using % instead of * to no avail. Can someone help please. What I would like it to do is if [StencilNumber] is ABC, for it to find *ABC*. Many thanks in advance

Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT Customer, StorageLoc, StencilPartNumber, " _
     & "StencilRev, SLRPartNumber, Date, Comments, " _
     & "NoLongerNeeded" _
     & "FROM tblStencils" _
     & "WHERE (((tblStencils.StencilPartNumber) " _
     & "Like " * " &[forms]![frmfindstencil]![StencilNumber] & " * "));", dbopendynaset)

 

[PHV]

Dim rs1 As DAO.Recordset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AnnabelleMarie]

Use single quotes around a string in a SQL statement.

"Like ' * '" &[forms]![frmfindstencil]![StencilNumber] & " ' * '));", dbopendynaset)

Hope this helps.

 

[denosaur]

Check your References...you may need to move the reference to Microsoft DAO 3.x Object Library to a higher priority that the reference to Microsoft ActiveX Data Objects 2.x Library.

If this is not the issue, then you may also try changing the...

 & "Like " * " &[forms]![frmfindstencil]![StencilNumber] & " * "));",

line to...

& "Like '*" & [forms]![frmfindstencil]![StencilNumber] & " *'"));",

I hope that helps.

 

[jadams0173]

Thanks for the quick responses.
PHV, my apologies. The Dim rs1 as DAO.Recordset was there I missed coping it by one line.

AnnabelleMarie you got me closer yet but I still get an error. I will see if I can figure it out but you and PHV please feel free to suggest someting else. The error is this:

syntax error (missing operator) in query expression 'NoLongerNeededFROMtblStencilsWHERE(((tblstencils.StencilPartNumber)Like'*'32L'*'))'

32L is what I entered to search for. Thanks so much to the both of you for getting me this far.

 

[PHV]

It's simply a space and quote issue:
Set rs1 = db.OpenRecordset("SELECT Customer, StorageLoc, StencilPartNumber, " _
& "StencilRev, SLRPartNumber, Date, Comments, " _
& "NoLongerNeeded" _
& "[highlight] [/highlight]FROM tblStencils" _
& "[highlight] [/highlight]WHERE StencilPartNumber " _
& "Like [highlight][tt]'*"[/tt] & [/highlight][forms]![frmfindstencil]![StencilNumber] & "[highlight][tt]*'"[/tt][/highlight], dbopendynaset)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jadams0173]

Hey denosaur thanks for the response. I get an error Expected end of: Statement error when I change to your suggestions.

& "Like '*" & [forms]![frmfindstencil]![StencilNumber] & " *'[blue]"[/blue])[red])[/red];",

The debugger goes to the ) in red. If I remove the quotes in blue I'm back to the error I described above.

 

[jadams0173]

PHV THANK YOU VERY MUCH!!!!! The spaces and quotes were the problems. Stars around to all who offered assistance and again PHV thank you very much.