sql in vb

  • Thread starter Thread starter trev b
  • Start date Start date
T

trev b

can somebody please tell me what is wrong with the
following code ?
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT " & _
"T04_Software.name " & _
"FROM T04_SoftwareNames inner JOIN T01_Software
ON " & _
"T04_SoftwareNames.SoftwareNameID =
T01_Software.SoftwareNameID " & _
" WHERE (T04_SoftwareNames.Software_Name like "*"
& [filterstring] & "*" & ";"))
 
-----Original Message-----
can somebody please tell me what is wrong with the
following code ?
Click to expand...

You are using special characters within your field and
table names. To get around this you have to enclose your
field and table names within square brackets.

i.e.

[My_Table].[My_Field]

As an aside, Name is a reserved word within Access due to
its usage as a property for many objects - you should not
use it as a field name.
 
Public Sub Example()

On Error GoTo Err_ErrorHandler

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [T04_Software].[Name] " & _
"FROM [T04_SoftwareNames] INNER JOIN
[T01_Software] ON " & _
"[T04_SoftwareNames].[SoftwareNameID] =
[T01_SoftwareNameID] " & _
"WHERE ([T04_SoftwareNames].[Software_Name]
Like ""*" & Me.[FilterString] & "*"");"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)



Exit_ErrorHandler:
strSQL = vbNullString
Set rs = Nothing
Set db = Nothing
Exit Sub
Err_ErrorHandler:
MsgBox Err.Description, vbExclamation, "Error #" &
Err.Number
Resume Exit_ErrorHandler

End Sub


A few comments:

Firstly, your field and table names contain a special
character (the underscore) which causes problems when
querying. To remedy this I've added square brackets
around these;

I moved the query to a string. When testing you can use
the MsgBox yourString method to test that the SQL has
been built properly;

I've added error handling to an example sub to
demonstrate the proper usage and as a way of reclaiming
memory once you are finished with the DAO objects;

Name is a reserved word in Access and should not be the
name of a field.

You did not have the correct delimiter for a text field
to apply your search string.
 
You've got a mistake in your WHERE clause.

" WHERE (T04_SoftwareNames.Software_Name like ""*" & [filterstring] &
"*"""))

If [filterstring] is trev, for instance, this will result in:

WHERE (T04_SoftwareNames.Software_Name like "*trev*"))

You need those quotes like that (and the semicolon isn't actually required)

An alternative would be

" WHERE (T04_SoftwareNames.Software_Name like " & Chr(34) & "*" &
[filterstring] & "*" & Chr(34)))

Chr(34) is the same as "
 
Many thanks for your help, finally got it working with the

Dim db As Database, rst As Recordset, strSQL As String


If Not IsNull(filterstring) Then
Set db = CurrentDb
If (SelectFilter = 1) Then
' Find out if any records match the filterstring
Set rst = db.OpenRecordset("SELECT " & _
"T01_Software.sw_id " & _
"FROM T04_SoftwareNames INNER JOIN T01_Software ON " & _
"T04_SoftwareNames.SoftwareNameID =
T01_Software.SoftwareNameID " & _
"WHERE (T04_SoftwareNames.Software_Name like '*" &
[filterstring] & "*')")

Once again many thanks
Regards
TrevB
 
Back
Top