Drop down box for query using LIKE (1 Viewer)

PWG

Registered User.
Local time
Today, 02:13
Joined
Jun 13, 2019
Messages
56
I have made a form and are able to open the query with a drop down box selection. It only works if I put in all the correct information but I want to use the * wild card to bring in more information as below.
Field contains values FS1A12 FS1A11 FS2A13 FS2A15 BA1A5 and so on.
I have created a table that contains FS* and FS1A12 FS1A11.
If I select FS* my query returns no records but if I select FS1A12 i get that record.
I want to select FS1* and get all the FS1 records
I have used [Forms]![Search]![Combo0] as Criteria in my Query and think that I need to add a LIKE but can not work out where to put it.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:13
Joined
Feb 19, 2013
Messages
16,553
I have used [Forms]![Search]![Combo0] as Criteria in my Query
always helps to show the code you have used but would guess your criteria needs to be something like

myField Like "'" & [Forms]![Search]![Combo0] & "'"
 

PWG

Registered User.
Local time
Today, 02:13
Joined
Jun 13, 2019
Messages
56
This is the code on the FORM drop down box
Row Source SELECT Locations.Loc, Locations.Field1 FROM Locations;
Event Procedure
Private Sub Combo0_Change()
DoCmd.OpenQuery "Copy of GET Stock Take P"
Me.Combo0 = ""
End Sub
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:13
Joined
Feb 19, 2013
Messages
16,553
it's the sql to your query I was referring to, not the rowsource or code on the combo.

Also I don't recommend you use the change event to open a query - it will happen every time you enter or delete a character in the combo - the change event can only reference the text property whilst the query references the value property and that is not updated with the change event.

Use the combo after update event instead or have a separate button to open the query.
 

PWG

Registered User.
Local time
Today, 02:13
Joined
Jun 13, 2019
Messages
56
Thanks for your reply I will change it to the on update.
Im sorry but I donot know where to find the SQL for the query to show you what it is i got how to do it of databasedev.uk and all it said to do was to put
[Forms]![Search]![Combo0] in the criteria in the query.
This works but only if you have the exact writing in the query and I can not us * to return more results. I will try your earlier solotution and see how I get on.
Thanks for your help
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:13
Joined
Feb 19, 2013
Messages
16,553
I donot know where to find the SQL for the query
Plenty of options - open the query in design view, then either click on the SQL button bottom right of the access window, or select SQL from the View dropdown on the ribbon.

If you are using overlapping windows you can right click on top the query window and select SQL view from there. I don't use tabbed documents but if you do I believe right clicking on the tab provides the same choice
 

PWG

Registered User.
Local time
Today, 02:13
Joined
Jun 13, 2019
Messages
56
I found it here it is Thanks
SELECT Inventory.ID, Inventory.Location, Inventory.Description, Inventory.Item, [Inventory Stock Levels].[Current Stock], Inventory.Comments
FROM Inventory INNER JOIN [Inventory Stock Levels] ON Inventory.ID = [Inventory Stock Levels].ID
WHERE (((Inventory.Location)=[Forms]![Search]![Combo0]));
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:13
Joined
Feb 19, 2013
Messages
16,553
so as I suggested

WHERE (((Inventory.Location) LIKE [Forms]![Search]![Combo0] & '*'))
 

PWG

Registered User.
Local time
Today, 02:13
Joined
Jun 13, 2019
Messages
56
That worked fantastic thanks so much for that
 

Users who are viewing this thread

Top Bottom