SQL Syntax error in VBA/Access

[npatil]

Hello Guys,

Here is my code:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
rs.ActiveConnection = cnn
rs.LockType = adLockReadOnly
rs.CursorType = adOpenDynamic
Dim strSQL As String
'Define Query and Run it
'CASE 1: For TireWidth
'If (Len(Nz(txttirewidth, "")) > 0 And Len(Nz(txttirewidthop, "")) > 0 And Len(Nz(txttirewidthvalue, "")) > 0) Then
strSQL = "SELECT TOP 10

• .[Field3],
  • .[Field7] FROM List WHERE (
    • .[Field3] Like(" * " & Me!txttirewidthvalue & " * " ))"
      
      
      
      rs.Open strSQL
      
      'lstResult.RowSource = vbNullString
      
      rs.MoveFirst
      Do While Not rs.EOF
      lstResult.additem rs![Field3]
      rs.MoveNext
      Loop
      rs.Close
      'End If
      
      I want to use the value from textbox txttirewidth in the like operator.
      I am getting a data type error. I am not able to resolve it.
      
      Thanks in advance,
      
      Nikhil

 

[LPurvis]

Hi

strSQL = "SELECT TOP 10 [Field3], [Field7] FROM List WHERE [Field3] Like '%" & Replace(Me!txttirewidthvalue, "'", "''") & "%'"

Not everything there is compulsory but I'd recommend it.
If you can avoid the leading wildcard - you can better optimisation (very much so if Field3 is indexed).

The wildcard needs to change because of the library/provider you're using to query.
The Replace I'd recommend - especially given the context.

Cheers

 

[npatil]

Thanks Leigh. It worked perfect.

NOw, another doubt I have is, I need to build another query using the same data. However, I have no idea how to reference the records from the previous recordset.
I have put the code below, if you can help me in this, it would be great!

Dim rs_tw As ADODB.Recordset
Set rs_tw = New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
rs_tw.ActiveConnection = cnn
rs_tw.LockType = adLockReadOnly
rs_tw.CursorType = adOpenDynamic
Dim strSQL As String
'Define Query and Run it
'CASE 1: For TireWidth
If (Len(Nz(txttirewidth, "")) > 0 And Len(Nz(txttirewidthop, "")) > 0 And Len(Nz(txttirewidthvalue, "")) > 0) Then
strSQL = "SELECT DISTINCT

• .[Field3],
  • .[Field7] FROM List WHERE
    • .[Field3] Like '%" & Me!txttirewidthvalue & "%';"
      
      'strSQL = "SELECT TOP 10 [Field3], [Field7] FROM List WHERE [Field3] Like '%" & Replace(Me!txttirewidthvalue, "'", "''") & "%'"
      
      rs_tw.Open strSQL
      
      lstResult.RowSource = vbNullString
      
      rs_tw.MoveFirst
      'If (txtaspectratiovalue = Null) Then
      
      Do While Not rs_tw.EOF
      lstResult.additem rs_tw![Field3]
      rs_tw.MoveNext
      Loop
      rs_tw.Close
      'End If
      End If
      
      'CASE 2: For ASPECT RATIO
      
      Dim rs_ar As ADODB.Recordset
      Set rs_ar = New ADODB.Recordset
      Dim cnn_ar As ADODB.Connection
      Set cnn_ar = CurrentProject.Connection
      rs_ar.ActiveConnection = cnn_ar
      rs_ar.LockType = adLockReadOnly
      rs_ar.CursorType = adOpenDynamic
      
      If (Len(Nz(txtaspectratio, "")) > 0 And Len(Nz(txtaspectratioop, "")) > 0 And (Len(Nz(txtaspectratiovalue, "") > 0))) Then
      
      ' Now I want to select the same records: rs_tw![Field3] and use the foll. WHERE statement to filter it and retrive the records. I have used " ? " for the missing blocks.
      
      
      strSQL = " SELECT DISTINCT " ? " FROM ? WHERE ? Like '%" & "/" & Me.txtaspectratiovalue & "R" & "%')"
      
      rs_ar.Open strSQL
      rs_ar.MoveFirst
      Do While Not rs_ar.EOF
      lstResult.additem rs_ar! ?
      rs_ar.MoveNext
      Loop
      rs_ar.Close
      End If
      'End If
      
      Nikhil

 

[LPurvis]

Does the first recordset contain table and field names?
Or are you actually trying to open a second recordset based on the first as its source?

You can't do the latter (recordsets are in memory code objects - which aren't within the scope of the database engine - which is what you're always using once you perform a query.)
However you could open a clone of the former recordset and filter it down to the required rows only.

If the former option - then you just refer to recordset object field members.

strSQL = " SELECT DISTINCT " & rs_tw!FieldName & " FROM " & rs_tw!FieldTableName & " WHERE " & rs_tw!FieldWhereField & " Like '%/" & Me.txtaspectratiovalue & "R%'"

If the latter then you would have something like
Set rs_ar = rs_tw.Clone
rs_ar.Filter = "Somefield Like '%/" & Me.txtaspectratiovalue & "R%'"

Cheers.

 

[npatil]

Yes, the recordset rs_tw takes in Field3 and Field7 from Table

• .
  
  I tried the following but it gives an error:
  
  strSQL = " SELECT DISTINCT " & rs_tw!Field3& " FROM " rs_tw!Field3 & " WHERE " & rs!Field3 & Like '%" & "/" & Me!txtaspectratiovalue & "R" & "%'"
  
  Also, can I still use the clone method ?
  
  thanks,

 

[LPurvis]

An error? That matters not so much. What error - matters much.

strSQL = " SELECT DISTINCT " & rs_tw!Field3 & " FROM " & rs_tw!Field3 & " WHERE " & rs!Field3 & Like '%/" & Me!txtaspectratiovalue & "R%'"

This concatenation and the clone method are totally separate options. If one is appropriate for what you're doing then the other isn't.
It seems unlikely that Field3 from rs_tw can have both the field and table name in question. These are names that we're retrieving to build up a string.
To reiterate - you can not select from a recordset object itself.

 

[npatil]

Sorry..I am getting syntax error.

I am using this:

strSQL = " SELECT DISTINCT " & rs_tw!Field3 & " FROM " rs_tw!Field3 & " WHERE " & rs!Field3 & Like '%" & "/" & Me!txtaspectratiovalue & "R" & "%'"

thx,.

 

[LPurvis]

That's not what I just posted though. (I posted a correction of your code as it stood. You were missing an ampersand - and still are missing it.)

 

[npatil]

Apologies...yes I put the ampersand after "FROM" and still get the syntax error for:

strSQL = " SELECT DISTINCT " & rs_tw!Field3 & " FROM " & rs_tw!Field3 & " WHERE " & rs!Field3 & Like '%" & "/" & Me!txtaspectratiovalue & "R%'"

 

[LPurvis]

strSQL = " SELECT DISTINCT " & rs_tw!Field3 & " FROM " & rs_tw!Field3 & " WHERE " & rs!Field3 & " Like '%/" & Me!txtaspectratiovalue & "R%'"

 

[npatil]

OK, the syntax issue is solved, however , I am getting " Syntax error(missing operator) in query expression '155/65R1373T' (which would be the first record of rs_tw!Field3)

 

[LPurvis]

Well, you really need to know what it is you're executing.
Standard debugging practice has you output the SQL statement to the immediate window.

After your line of code.
strSQL = " SELECT DISTINCT " & rs_tw!Field3 & " FROM " & rs_tw!Field3 & " WHERE " & rs!Field3 & " Like '%/" & Me!txtaspectratiovalue & "R%'"
insert
Debug.Print strSQL

After you get the error raised, hit Ctrl G to view the immedite window.
The SQL statement will be displayed there so see what's wrong with it.

 

[npatil]

Dim strSQL_ar As String

If (Len(Nz(txtaspectratio, "")) > 0 And Len(Nz(txtaspectratioop, "")) > 0 And (Len(Nz(txtaspectratiovalue, "") > 0))) Then
strSQL_ar = " SELECT DISTINCT " & rs_tw!Field3 & " FROM " & rs_tw!Field3 & " WHERE " & rs_tw!Field3 & " Like '%/" & Me!txtaspectratiovalue & "R%'"

Debug.Print strSQL_ar

rs_ar.Open strSQL_ar

----------
Immediate Window:

SELECT DISTINCT 125/80R1365M FROM 125/80R1365M WHERE 125/80R1365M Like '%/45R%'

Now, I want to extract only those fields, which have125/" ", which is why I have the like operator.

Sorry for the constant replying, but I am new to SQL lang.

 

[LPurvis]

We need to go back and be way clear here.
What are you actually trying to do.
It looks clear that there's been a misconception.

The first of the two options I suggested looks like it isn't what you really want.
I will reiterate again...
You build a SQL statement. It must be a string which is valid SQL syntax which references data objects (fields, tables etc.)
Clearly
SELECT DISTINCT 125/80R1365M FROM 125/80R1365M WHERE 125/80R1365M Like '%/45R%'
is not that.
The values from your field (125/80R1365M) is a value.
What relevance does that value have to what you're trying to do.

Focus on concept - not code.

Cheers.

 

[npatil]

Ahh...I got mixed up, sorry. I tried to use values from recordset as tables/fields, and that does not make sense. I realized I do not need it now. thanks for the suggestions though.

As a side question, in the "like" section of SQL, can I use operators >,< along with Me!txtname ? How would it look ?

For example: In '%/45R%' where would the ">" have to go, if I am looking for values greater than 45?

 

[LPurvis]

You're into a performance minefield there.
It's the problem once data which lends itself to atomic (individual) inspecetion is stored concatenated like this.
Yes, it may represent something like a manufacturer's serial or model number, but if you need to consider it as individual values then it's worth considering storing it as such.

As it stands you'd need to extract the numeric portion of the field value and then compare against that. It's a resource intensive process - very much so if you have a lot of rows in your table.
(If you can limit the rows that you're examining by some other criteria first then do that, let the DB engine do the heavy lifting first before the expression service needs to start resolving values.)

Functions to strip out the numeric portion are faily common.
For example in this thread over the way.

Then you can check with
WHERE Val(fGetNumOnly(FieldName)) > 45

Not pretty though.

Cheers.