Solved List View Fill SQL With Criteria ( Convert Access SQL To SQL) (1 Viewer)

[ahmedjamalaboelez]

Hello Access Worled ,
I'm Using Following SQL To Fill My List View , it Works Fine but I need to add some criteria as bellow Access SQL

    Set rs = db.OpenRecordset( _
        "SELECT * " & _
        "FROM db_devices_search " & _
        "ORDER BY db_devices_search.device_lineid", dbOpenSnapshot, dbReadOnly Or dbForwardOnly)
        
        
        ' Replace Up Sql With Following Access Sql ,
        
        "SELECT db_devices.device_lineid, db_devices.device_name, db_devices.device_user, db_devices.device_notes, IIf([device_server]=True,"Server","Device") AS device_icon, db_devices.device_status, [device_lineid] & [device_name] & [device_user] & [device_notes] AS SearchCtria FROM db_devices WHERE (((db_devices.device_status) Like "*" & [forms]![frm_devices]![txt_status] & "*") AND (([device_lineid] & [device_name] & [device_user] & [device_notes]) Like "*" & [forms]![frm_devices]![txt_search] & "*"))"

Question :
1 - Help To Write Second Sql As First One
2 - Is There Any Converter from Normal Access Query To SQL?

 

[cheekybuddha]

1 - Help To Write Second Sql As First One
Use a variable to construct your SQL string. Then you can output it to the Immediate Window (Ctrl+G) first to make sure you have it correct:

  ' Replace Up Sql With Following Access Sql ,
    Dim strSQL As String
    strSQL =  "SELECT device_lineid, device_name, device_user, device_notes, " & _
                     "IIf([device_server]=True ,'Server', 'Device') AS device_icon, device_status, " & _
                     "device_lineid & device_name & device_user & device_notes AS SearchCtria " & _
              "FROM db_devices " & _
              "WHERE device_status Like '*" & [forms]![frm_devices]![txt_status] & "*' " & _
                "AND device_lineid & device_name & device_user & device_notes Like '*" & [forms]![frm_devices]![txt_search] & "*'"
    Debug.Print strSQL
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly Or dbForwardOnly)

2 - Is There Any Converter from Normal Access Query To SQL?
I think both Allen Browne and Colin @isladogs (here) both have tools to do this

 

[arnelgp]

2 - Is There Any Converter from Normal Access Query To SQL?

use Querydefs("QueryName").SQL to get the SQL string:

Dim strSQL As String
strSQL = Currentdb.QueryDefs("Query1").SQL

 

[ahmedjamalaboelez]

1 - Help To Write Second Sql As First One
Use a variable to construct your SQL string. Then you can output it to the Immediate Window (Ctrl+G) first to make sure you have it correct:

  ' Replace Up Sql With Following Access Sql ,
    Dim strSQL As String
    strSQL =  "SELECT device_lineid, device_name, device_user, device_notes, " & _
                     "IIf([device_server]=True ,'Server', 'Device') AS device_icon, device_status, " & _
                     "device_lineid & device_name & device_user & device_notes AS SearchCtria " & _
              "FROM db_devices " & _
              "WHERE device_status Like '*" & [forms]![frm_devices]![txt_status] & "*' " & _
                "AND device_lineid & device_name & device_user & device_notes Like '*" & [forms]![frm_devices]![txt_search] & "*'"
    Debug.Print strSQL
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly Or dbForwardOnly)

2 - Is There Any Converter from Normal Access Query To SQL?
I think both Allen Browne and Colin @isladogs (here) both have tools to do this

Mr. cheekybuddha Thank you So Much It's Work Like Magic I just modified little in code just replaced * With %

 Dim rs     As ADODB.Recordset
   Set rs = New ADODB.Recordset
    ' Replace Up Sql With Following Access Sql ,
    Dim strSQL As String
    strSQL = "SELECT device_lineid, device_name, device_user, device_notes, " & _
             "IIf([device_server]=True ,'Server', 'Device') AS device_icon, device_status, " & _
             "device_lineid & device_name & device_user & device_notes AS SearchCtria " & _
             "FROM db_devices " & _
             "WHERE device_status Like '%" & [Forms]![frm_devices]![txt_status] & "%' " & _
             "AND device_lineid & device_name & device_user & device_notes Like '%" & [Forms]![frm_devices]![txt_search] & "%'"
    Debug.Print strSQL
    rs.Open strSQL, CurrentProject.Connection

Thank you So Much
A.J

 

[theDBguy]

2 - Is There Any Converter from Normal Access Query To SQL?

Sounds like you may be looking for this.

Introducing Access to TSQL Converter + VBA Tool - AccessUserGroups.org

I’m proud to announce a new groundbreaking tool at https://accessusergroups.org/sql-converter/ to help Access developers worldwide on making the transition over to SQL Server much easier. The tool will convert your Access query SQL to SQL Server’s TSQL language for easy pasting into your code or...

accessusergroups.org

 

[ahmedjamalaboelez]

use Querydefs("QueryName").SQL to get the SQL string:

Dim strSQL As String
strSQL = Currentdb.QueryDefs("Query1").SQL

Mr. arnelgp
I Always Waiting Your Help , And I Appreciate the way you use simplify issues for any one , i tried it work very fine , but in this case I'm trying not to create 100 Query Object in my database.
Mr. @cheekybuddha Already Convert the SQL And Work directly assigned to my list view.
Thank You So Much ,
Always Remember "A true act of kindness always sparks another"
A.J

 

[cheekybuddha]

You're welcome!

Be aware, it is better to load ControlSources of comboboxes/listboxes using a linked table rather than by using a pass-through query.

As far as I understand, the Access/ODBC engine is smart enough to pull only the records that need to be displayed in the dropdown / visible list, whereas a pass-through needs to retrieve all the records in the recordset, which can appear as a performance hit if there are lots of records returned.

 

[cheekybuddha]

Ah, I see you are using a ListView, not a Listbox/Combobox - ignore my previous comment!

 

[ahmedjamalaboelez]

You're welcome!

Be aware, it is better to load ControlSources of comboboxes/listboxes using a linked table rather than by using a pass-through query.

As far as I understand, the Access/ODBC engine is smart enough to pull only the records that need to be displayed in the dropdown / visible list, whereas a pass-through needs to retrieve all the records in the recordset, which can appear as a performance hit if there are lots of records returned.

Yes I'm Aware sir , I just use it to fill Active x Controls Like Tree view and List View .
Thanks
A.J
Always Remember "A true act of kindness always sparks another"

 

[cheekybuddha]

I just use it to fill Active x Controls Like Tree view and List View

I realised just before your reply!

 

[ahmedjamalaboelez]

I Saw your Reply Before I post my reply But I Pressed Post Button
Thanks