Automated creation of queries

[exaccess]

Hello Experts,
I am trying to write an application where there is a query form and a results form. The query form accepts two text fields and values and one date field with two values with GE and LE operators. The search values can contain the null values. It is a pretty complicated job. Does anybody have any links to propose that can help please.

 

[Ranman256]

you dont have to build a query, just filter the data...
Test all controls for a possible filter then build the where clause.

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

    'remove 1st And
if len(sWhere)>0 then sWhere= mid(sWhere,5)

  'just use the filter
iLen = Len(sWhere) - 5
If iLen <= 0 Then
    me.filterOn = false
Else
    me.filter = sWhere
    me.filterOn = true
End If

   'OR if you want     'apply the sql to the query itself

sSql = "SELECT * FROM tblCompany WHERE " & sWhere

     'save the sql as a qry or open the sql
set qdf = currentdb.querydefs("qsResults")
qdf.sql = ssql
qdf.close
docmd.openquery qdf.name

 

[arnelgp]

Im hoping you have a command button there to invoke your Result form:

Private Sub CommandButton_Click()

  dim strWhere As String

  ' build condition here
  ' process 1st textbox
  strWhere = strWhere & IIF( Me.yourTextBox1 & "" <> "", "[yourField1] = '" & Me.yourTextBox1 "' And ", "True=True And ")
  ' process 2nd textbox
  strWhere = strWhere & IIF( Me.yourTextBox2 & "" <> "", "[yourField2] = '" & Me.yourTextBox1 "' And ", "True=True And ")
  ' process date control
  strWhere = strwhere & IIF( Me.yourDateControl & "" <> "", "[yourDateField] " & _
      iif(Me.yourComboLEGE & "" <> "", Me.yourCoboLEGE, "=") & _
      "#" & Format(Me.yourDateControl, "mm\/dd\/yyyy") & "#", _
      "True=True")

  If IsFormLoaded("yourResultForm") Then DoCmd.Close acForm,"yourResultForm",acSaveNo  

  DoCmd.OpenForm "yourResultForm, acNormal,, strWhere
   

End Sub

Public Function IsFormLoaded(ByVal strFormName As String) As Boolean
    Dim oAccessObject As AccessObject
    Set oAccessObject = CurrentProject.AllForms(strFormName)
    IsFormLoaded = False
    If oAccessObject.IsLoaded Then
        If oAccessObject.CurrentView <> acCurViewDesign Then IsFormLoaded = True
    End If
    Set oAccessObject = Nothing
End Function

 

[exaccess]

you dont have to build a query, just filter the data...
Test all controls for a possible filter then build the where clause.

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

    'remove 1st And
if len(sWhere)>0 then sWhere= mid(sWhere,5)

  'just use the filter
iLen = Len(sWhere) - 5
If iLen <= 0 Then
    me.filterOn = false
Else
    me.filter = sWhere
    me.filterOn = true
End If

   'OR if you want     'apply the sql to the query itself

sSql = "SELECT * FROM tblCompany WHERE " & sWhere

     'save the sql as a qry or open the sql
set qdf = currentdb.querydefs("qsResults")
qdf.sql = ssql
qdf.close
docmd.openquery qdf.name

Your code is easy to implement and it works. The problem is that in my case I am getting the names of the control fields from combo boxes that have fields lists of tables and/or queries as rowsource. So I need to display and use the name of the control field that changes in every query. How do I achieve that? Thanks.