UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> SQL Like "*", Access 2013    
 
   
fogline
post Sep 2 2019, 06:59 PM
Post#1



Posts: 73
Joined: 5-August 15
From: Ringgold, GA. USA



In my table I have a field text3 and Driver1FullName
on my form I have a combo box Combo49

I am having trouble with the AND Driver1FullName= LIKE " * " & " & Me.Combo49 & "*"
I want to run the SQL with the WHERE text3 = '" & 2 & "' which works good
but I would also like if I pick a name from the Me.Combo49 it will short by it also
If it is left blank the just short by text3 = '" & 2 & "'
I am just doing something wrong with the wild card AND Driver1FullName= LIKE " * " & " & Me.Combo49 & "*"

Thanks for any help



Dim strSQL As String
Dim strSQLSF As String


strSQL = "SELECT DISTINCT qryLiveLoads.text3, Driver1FullName FROM qryLiveLoads "
strSQL = strSQL & " WHERE text3 = '" & 2 & "' AND Driver1FullName= LIKE " * " & " & Me.Combo49 & "*"


strSQLSF = "SELECT * FROM qryLiveLoads "
strSQLSF = strSQLSF & " WHERE text3 = '" & 2 & "' AND Driver1FullName= LIKE " * " & " & Me.Combo49 & "*"


Me.RecordSource = strSQLSF
Me.Requery
Go to the top of the page
 
dale.fye
post Sep 2 2019, 07:07 PM
Post#2



Posts: 161
Joined: 28-March 18
From: Virginia


You cannot have the = and Like in the same criteria, and have to wrap the text that you are searching for in single quotes, so that the actual string looks like

SELECT DISTINCT text3, Driver1FullName FROM qryLiveLoads WHERE text3 = '2' AND Driver1FullName Like '*SomeValue*'

strSQL = "SELECT DISTINCT qryLiveLoads.text3, Driver1FullName FROM qryLiveLoads "
strSQL = strSQL & " WHERE text3 = '" & 2 & "' AND Driver1FullName LIKE '*" & Me.Combo49 & "*'"

BTW, "text3" is a terrible name for a field in your table, and Combo49 is a terrible name for a control on your form. You should rename those so that they are meaningful names.

HTH
Dale


--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
fogline
post Sep 2 2019, 07:26 PM
Post#3



Posts: 73
Joined: 5-August 15
From: Ringgold, GA. USA


Thanks Dale

Yes I know text3 and Me.combo49 are bad I'm just testing a SQL
Here is what I have and it's not working
where am I wrong?


strSQL = "SELECT DISTINCT text3, Driver1FullName FROM qryLiveLoads "
strSQL = "WHERE text3 = '2' AND Driver1FullName Like '*' & Me.Combo49 & '*'"

strSQL = "SELECT DISTINCT qryLiveLoads.text3, Driver1FullName FROM qryLiveLoads "
strSQL = strSQL & " WHERE text3 = '2' AND Driver1FullName LIKE '*' & Me.Combo49 & '*'"


Me.RecordSource = strSQLSF
Me.Requery

Go to the top of the page
 
fogline
post Sep 2 2019, 07:34 PM
Post#4



Posts: 73
Joined: 5-August 15
From: Ringgold, GA. USA


Not Last Post Sorry.
This one.


Dim strSQL As String
Dim strSQLSF As String


strSQL = "SELECT DISTINCT text3, Driver1FullName FROM qryLiveLoads "
strSQL = strSQL & "WHERE text3 = '2' AND Driver1FullName Like '*' & Me.Combo49 & '*'"

strSQLSF = "SELECT * FROM qryLiveLoads"
strSQLSF = strSQL & " WHERE text3 = '2' AND Driver1FullName LIKE '*' & Me.Combo49 & '*'"


Me.RecordSource = strSQLSF
Me.Requery


Go to the top of the page
 
DanielPineault
post Sep 2 2019, 07:38 PM
Post#5


UtterAccess VIP
Posts: 6,900
Joined: 30-June 11



You may like to review http://www.devhut.net/2016/06/10/ms-access...-is-left-empty/

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
June7
post Sep 2 2019, 07:39 PM
Post#6



Posts: 873
Joined: 25-January 16



What does 'not working' mean - error message, wrong result, nothing happens?

If field is a number type, don't use apostrophe delimiters.

Need quote marks:

" WHERE text3 = '2' AND Driver1FullName LIKE '*" & Me.Combo49 & "*'"
This post has been edited by June7: Sep 2 2019, 07:41 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
fogline
post Sep 2 2019, 07:49 PM
Post#7



Posts: 73
Joined: 5-August 15
From: Ringgold, GA. USA


Thank you Daniel for that link, That work GREAT
Thank You. woohoo.gif
This works..

=Forms!YourFormName.YourComboBoxName OR Forms!YourFormName.YourComboBoxName IS NULL


strSQL = "SELECT DISTINCT text3, Driver1FullName FROM qryLiveLoads "
strSQL = strSQL & "WHERE text3 = '2' AND Driver1FullName = Forms!frmLoadStatus.ComboDriver OR Forms!frmLoadStatus.ComboDriver IS NULL"



strSQLSF = "SELECT * FROM qryLiveLoads "
strSQLSF = strSQLSF & "WHERE text3 = '2' AND Driver1FullName = Forms!frmLoadStatus.ComboDriver OR Forms!frmLoadStatus.ComboDriver IS NULL"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th October 2019 - 01:13 AM