Hello Access Experts ;) ,

I would like to make a query, based on the input of the user. I already have created a form, on which the user can select the possible fields to be displayed (including a optional filter).

The corresponding sql - code is generated with VBA (a SQL string starting with SELECT - FROM command expanded with the selected fields with the use of if-then-else. The filter is created with the use of the WHERE () command

Only I can't seem to make it work. Can anyone help me?

A second question is that I would like to display the selected data into a graph, but have no idea how to do this.

Thx in advance.

Marc

Marc,

I would be more able to help you if you could supply the code where you are generating the SQL. But for some pointers, a lot of the problems with generating SQL strings has to do with the correct data types...ie. making sure strings have ' s.

If you need more clarification, please include a snippet of your code so we can critique it.



-Shay Shepston

To display it in a graph, have the code which gets the criteria as part of the Load event of the report/form with the graph in it. Then you can simply change the source of the graph to be the SQL string you've made.
But like Shay said, give us the actual code, and we can help you more

Steven

There are 10 kinds of people in the world - those who understand binary - and those with friends

Thx for the response this far.

This is part of the code as far. It's connected on a form with checkboxes (to display or not) and combox (for a filter).

I would like to open a new form, with the selected fields displayed. A second form should display the information in a graph.

Marc

Private Sub knopOK_Click()
On Error GoTo Err_knopOK_Click

    Dim strSQL As String, strWHERE As String
    Dim response
    Dim myRCD As New ADODB.Recordset
    Dim cn As ADODB.Connection

    Set cn = CurrentProject.Connection
    strSQL = "SELECT "

    If Me.w_date Then
        strSQL = strSQL & " Date,"
    End If

    If Me.w_installation Then
        strSQL = strSQL & " Installation,"
    End If

    If Me.w_unit Then
        strSQL = strSQL & " Unit,"
    End If

    If Right(strSQL, 1) = "," Then
        strSQL = Left(strSQL, Len(strSQL) - 1)
    End If

    strSQL = strSQL & " FROM qryUnit "


    If Not (Me.cmbUnit = Empty) Then
        strWHERE = strWHERE & " AND ( Unit = '" & Me.cmbUnit & "')"
    End If

    If Not (Me.cmbType = Empty) Then
        strWHERE = strWHERE & " AND ( Unit = '" & Me.cmbType & "')"
    End If

    If Not (strWHERE = Null) Then
        strWHERE = " WHERE " & strWHERE
    End If

    strSQL = strSQL & strWHERE & ";"
    Me.txtHulp = strSQL

    DoCmd.RunSQL (strSQL)


Exit_knopOK_Click:
    Exit Sub

Err_knopOK_Click:
    MsgBox Err.Description
    Resume Exit_knopOK_Click

End Sub

After a quick look through your code, I found the problem, or at least part thereof.

DoCmd.RunSQL is used for action sql only - i.e. INSERT, UPDATE - that sort of thing.

To get around that (and also to solve the problem of how to put it in a graph/chart), change the DoCmd.RunSQL to (for example)

Form_Form1.Graph1.RowSource = strSQL

Steven



There are 10 kinds of people in the world - those who understand binary - and those with friends

Marc, this bit confuses me
It looks to me like its possible to include the Unit field twice in the where clause with different values each time, for example suppose Me.cmbUnit = "X" and Me.cmbType = "Y", then you would end up with SQL like this:
but this would never select anything since there can't be a record that has the value 'X' in Unit and also the value 'Y' in Unit.

?
Phil

Steven,

Thx, it works.. Is is also possible to display the query result as report?

Phil,

It was a typo. The first part uses (cmb)Unit and the second part uses (cmb)Type. The selection is based on different fields.

Thx

Marc

Marc, there is a problem with your WHERE clause. With the your current code, you'll get a syntax error because of a AND. Your current code will give you this result.
Change the build of your WHERE clause to this:


Stéphane Lajoie

Marc,

Basically change the bit in my last reply from Form_Form1.Graph1.RowSource to
Report_ReportName.RecordSource

What you might want to do, is put that code in the OnLoad event of your Report.

Of course, you'll have to change the relative references (eg: Me.cmbType) to absolute references (eg: Form_Form1.cmbType).

Then you can have a cmd button on the form with the criteria to open the report, and that's it, then the OnLoad function gets triggered.

This way, you won't have to open the report, change the recordsource, save the report, close it and re-open it.

Steven


There are 10 kinds of people in the world - those who understand binary - and those with friends