Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 30th, 2003, 07:50 AM
Registered User
 
Join Date: Jun 2003
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to MRvLuijpen Send a message via MSN to MRvLuijpen
Default Dynamically query

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
 
Old July 1st, 2003, 08:30 AM
Authorized User
 
Join Date: Jun 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Code:
strSQL="SELECT field FROM table WHERE searchID = '" & strCriteria & "'"
If you need more clarification, please include a snippet of your code so we can critique it.



-Shay Shepston
 
Old July 1st, 2003, 09:07 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old July 2nd, 2003, 04:44 AM
Registered User
 
Join Date: Jun 2003
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to MRvLuijpen Send a message via MSN to MRvLuijpen
Default

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
 
Old July 9th, 2003, 06:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old July 10th, 2003, 03:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Marc, this bit confuses me
Code:
    
    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
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:
Code:
SELECT ... FROM qryUnit WHERE (Unit='X') AND (Unit='Y')
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
 
Old July 10th, 2003, 04:20 AM
Registered User
 
Join Date: Jun 2003
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to MRvLuijpen Send a message via MSN to MRvLuijpen
Default

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

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

    If Not (Me.cmbType = Empty) Then
        strWHERE = strWHERE & " AND ( Type = '" & Me.cmbType & "')"
    End If
 
Old July 10th, 2003, 09:50 AM
Authorized User
 
Join Date: Jun 2003
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Code:
    If Not (Me.cmbUnit = Empty) Then
        strWHERE = strWHERE & " AND ( Unit = '" & Me.cmbUnit & "')"
    End If

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

SELECT ... FROM qryUnit WHERE AND (Unit='X') AND (Type='Y')
Change the build of your WHERE clause to this:
Code:
    'It's the first time that you affect the variable, there is no need to
    ' concatenate it.
    If Not (Me.cmbUnit = Empty) Then
        strWHERE = " ( Unit = '" & Me.cmbUnit & "')"
    End If

    If Not (Me.cmbType = Empty) Then
        If Len(strWHERE) > 0 Then
            strWHERE = strWHERE & " AND "
        End If

        strWHERE = strWHERE & " ( Type = '" & Me.cmbType & "')"
    End If


Stéphane Lajoie
 
Old July 12th, 2003, 09:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
dynamically constructed sql action query dotcom25 Access VBA 1 August 21st, 2007 12:35 PM
dynamically query MRvLuijpen Access VBA 3 September 28th, 2006 07:46 AM
Syntax error in query. Incomplete query clause. dispickle ADO.NET 3 April 16th, 2004 01:04 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.