Private Sub Select_Sector() Dim rs As DAO.Recordset Dim RegEx As Object Set RegEx = CreateObject("vbscript.regexp") Dim qdef As QueryDef Set qdef = getCurrentDb.QueryDefs("qry_Select_Sector") qdef.Connect = CurrentDb.TableDefs("BOCClientIndex").Connect RegEx.Pattern = "IIf\(\[ServiceStatus\]=3,30,20\)\)=([0-9]+)" qdef.SQL = RegEx.Replace(qdef.SQL, "IIf([ServiceStatus]=3,30,20))=" & [Forms]![MainMenu_Services]![SelectedStatusIndicator]) RegEx.Pattern = "\(View_qryServiceProviderOrganisationalStructure\.SectorCode\)=([0-9]+)" qdef.SQL = RegEx.Replace(qdef.SQL, "(View_qryServiceProviderOrganisationalStructure.SectorCode)=" & [Forms]![MainMenu_Services]![SectorCode]) 'For Testing purposes only - Do not use in production code Set rs = qdef.OpenRecordset Dim i As Long For i = 0 To rs.Fields.Count - 1 Debug.Print rs.Fields(i).Name, Next rs.MoveFirst Do Until rs.EOF Debug.Print For i = 0 To rs.Fields.Count - 1 Debug.Print rs.Fields(i).value, Next rs.MoveNext Loop End Sub
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter