|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
February 10th, 2005, 10:19 AM
|
|||
|
|||
|
SQL LIKE, half way there I think
I have a text box that when I start typing automatically gets the results from a query, Here is the code so far:
Code:
Function BuildSQLString(strSQL As String) As Boolean
Dim strSELECT As String, strFROM As String, strWHERE As String, strLIKE As String
strSELECT = "*"
strFROM = "[shop numbers]"
strWHERE = " [shop numbers]![company name]"
strLIKE = "[Forms]![add-bespoke-residential]![company name]"
strSQL = "SELECT" & strSELECT
strSQL = strSQL & "FROM " & strFROM
strSQL = strSQL & "WHERE" & strWHERE & " LIKE '%" & strLIKE & "%'"
BuildSQLString = True
End Function
Private Sub company_name_Change()
Dim strSQL As String
If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If
CurrentDb.QueryDefs("bespoke-residential-add-form-address-query").SQL = strSQL
RefreshDatabaseWindow
Me.Form.RecordSource = "bespoke-residential-add-form-address-query"
Me.Refresh
End Sub
I think its the LIKE statement that isnt right, as Im sure the rest is as it is based on something a very reliable source has helped me with. also is there a way to stop the focus going to the results text boxes? As the on change makes it jusp to them everytime you enter a key.
|
|
#2
February 10th, 2005, 10:54 AM
|
|||
|
|||
|
Ive also tried using a normal query
Code:
SELECT * FROM [shop numbers] WHERE [shop numbers]![company name] LIKE "[Forms]![add-bespoke-residential]![company name] *"; but again this doesnt seem to work, please help!
|
|
#3
February 10th, 2005, 05:23 PM
|
|||
|
|||
|
Looks like just a syntax problem
SELECT * FROM [shop numbers] (((WHERE [shop numbers]![company name]) LIKE "[Forms]![add-bespoke-residential]![company name] & "*")); In your other post, you need to change it slightly as well in the strSQLclause. strSQL = strSQL & "WHERE " & strWHERE & LIKE & " '" & strLIKE & "'" & "*" lwells Last edited by lwells : February 10th, 2005 at 05:26 PM.
|
|
#4
February 11th, 2005, 03:28 AM
|
|||
|
|||
|
both of those come up with errors lwells, I cant see why either really, they seem pretty ok to me but access doesnt accept it.
I have tried swapping the end '* to *' but still no results turn up, and I also had to put " around the LIKE otherwise I got errors. I know the syntex is right because If I swap the strLIKE for a value like pe the rsults come up, so somewhere the text box value isnt getting passes intot eh sql stament correctly.
|
|
#5
February 11th, 2005, 01:56 PM
|
|||
|
|||
|
If you are triggering your code from the text box directly, then the value hasn't been saved yet and the control still has the focus, therefore your procedure will fail. Try telling access to look at what was entered if this is the case and not what the value is.
strLIKE = "[Forms]![add-bespoke-residential]![company name].Text" With the absense of the identifier, access assumes that it is the value of the object and the control no longer has the focus. You can step through your code to find and correct the errors but at this point it would be a good idea to start using some form of error handling to trap errors into a message box so you can see what the problem is. lwells
|
|
| Viewing: Dev Articles Community Forums > Databases > Microsoft Access Development > SQL LIKE, half way there I think |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
