query code for contains (Like?) term typed in query pop up window

[weeblebiker]

I have a query set up below which when launched, a pop up field appears to type in the serial number and returns all the repair entries containing that serial number.
I want it to be able to return any serial numbers that contain a partial string in the pop up field
ei: type "13" in the pop up field and return
1113
1113c
1130
1300

the serial numbers are alpha numeric.
the query is also used in a secondary summarizing query and form, that's why the field after the = is kooky looking

I've played with Like in place of the = in the WHERE statement, but can't get it to go :banghead:

any ideas?

SELECT [XRS-3 repair].[Serial #], [XRS-3 repair].[Customer #], [XRS-3 repair].Customer, [XRS-3 repair].[Order#], [XRS-3 repair].[Date in], [XRS-3 repair].[Date out], [XRS-3 repair].[Reason for Service], [XRS-3 repair].[Sent in for inspection], [XRS-3 repair].[Pulse Code], [XRS-3 repair].[Unit Fires], [XRS-3 repair].[Unit Counts], [XRS-3 repair].Output, [XRS-3 repair].[Output #], [XRS-3 repair].Penetration, [XRS-3 repair].[Head tested with Ref Boards], [XRS-3 repair].[Feedback Voltage], [XRS-3 repair].[Unit Fires with boards], [XRS-3 repair].[Unit Counts with boards], [XRS-3 repair].[Output with boards], [XRS-3 repair].[Output # with boards], [XRS-3 repair].[Pulsing / Noises], [XRS-3 repair].[Oil leak], [XRS-3 repair].[Inspection Other], [XRS-3 repair].[Disassembly Inspection], [XRS-3 repair].[Warrenty Repair], [XRS-3 repair].[XRS-3B Head], [XRS-3 repair].Tube, [XRS-3 repair].[Tube Description], [XRS-3 repair].Canister, [XRS-3 repair].[Canister Description], [XRS-3 repair].Bellows, [XRS-3 repair].[Bellows Description], [XRS-3 repair].Capacitor, [XRS-3 repair].[Capacitor Description], [XRS-3 repair].[Spark gap], [XRS-3 repair].[spark gap Description], [XRS-3 repair].[Tri electrode], [XRS-3 repair].[Russian gap], [XRS-3 repair].[gap updated in repair], [XRS-3 repair].Transformer, [XRS-3 repair].[Transformer Description], [XRS-3 repair].[Peaking Discharger], [XRS-3 repair].[Ceramic Failure], [XRS-3 repair].[Peaking Discharger Description], [XRS-3 repair].Choke, [XRS-3 repair].[Choke Description], [XRS-3 repair].Coil, [XRS-3 repair].[Coil Description], [XRS-3 repair].[Counter Board], [XRS-3 repair].[counter circuit failure], [XRS-3 repair].[Counter board Description], [XRS-3 repair].[Oscillator Board], [XRS-3 repair].[Fuse Failure], [XRS-3 repair].[Oscillator circuit failure], [XRS-3 repair].[Oscillator Board Description], [XRS-3 repair].[Handle Broken], [XRS-3 repair].[Housing Broken], [XRS-3 repair].Other, [XRS-3 repair].[other description], [XRS-3 repair].Notes, [XRS-3 repair].[Repaired by], [XRS-3 repair].[Page Number], [XRS-3 repair].[5 Pin], [XRS-3 repair].[Estimate Date], [XRS-3 repair].[Approval Date], [XRS-3 repair].Email, [XRS-3 repair].[Contact Info], [XRS-3 repair].[Head #], [XRS-3 repair].[18v Conversion]
FROM [XRS-3 repair]
WHERE (([XRS-3 repair].[Serial #])=[Forms]![XRS-3 Repair Summary by Serial #]![Serial #]);

 

[James Deckert]

have you tried something like
WHERE (([XRS-3 repair].[Serial #]) Like "*" & [Forms]![XRS-3 Repair Summary by Serial #]![Serial #] & "*"

 

[weeblebiker]

that's the ticket!
I was trying derivatives of
WHERE (([XRS-3 repair].[Serial #])Like "*([Forms]![XRS-3 Repair Summary by Serial #]![Serial #])*");

Thanks!

 

[James Deckert]

Since you're referencing a form within the sql, you need the value typed into the form to become the string value in the sql. Leaving the form reference within the quotes won't do this conversion but the form reference itself is what is passed to the query engine. Therefore you're asking the query to spit out any value which contains "([Forms]![XRS-3 Repair Summary by Serial #]![Serial #])" within it instead of what is typed into the form. Subtle difference.

 

[weeblebiker]

thanks for the clarification of the logic