Query troubl in VBA code.

geralf

Registered User.
Local time
Today, 20:30
Joined
Nov 15, 2002
Messages
212
Hi all,

This one is really making me want to throw the PC out the window, ao I hope there is someone who please can help me here.

Here's what I'm trying to do:

I have a search form where the user enters criteria to search for. I create the search strings based on the users selections, and build the queries, using the CreateQueryDef method.

When I run the queries through code it will run fine the first time. If U try to re-run it, I can get different errors, Likw query is to complicated' or 'Missmatch of coluns in table or union query. The debugger higlights the DoCmd.OpenQuery "G120" line at the end of my code. The funny thing though is that if I run the query from the db window, it runs fine, and the output is what I want it to be.

I know my question here is difficult to answer, and my code below is long and not easy to read. This is the first time I'm using query defs, so if anyone sees any wrong coding or have any suggestions or tips, I'll be very happy to hear of it.

Here's the code





Code:
Private Sub cbtOverfør_Click()
    Dim Flag As Boolean
    Dim I As Integer
    Dim J As Integer
    Dim K As Integer
    Dim D1 As Variant
    Dim D2 As Variant
    Dim strS1
    Dim strS2
    Dim strS3 As String
    Dim dbs As Database
    Dim qry As QueryDef
    Dim qdf1 As QueryDef
    Dim qdf2 As QueryDef
    Flag = True
    J = 0
    K = 0
    D1 = Null
    D2 = Null
    strS1 = ""
    strS2 = ""
    strS3 = ""
    Set dbs = CurrentDb
'********Drosskriterier for query G11************
    For I = 0 To Me.lboDross.ListCount - 1 '0 based
        If Me.lboDross.Selected(I) Then
            If Flag = True Then
                Flag = False
                strS2 = strS2 & " DROSSID=" & Me.lboDross.Column(0, I)
            Else
                strS2 = strS2 & " OR DROSSID=" & Me.lboDross.Column(0, I)
            End If
            J = J + 1
        End If
    Next I
'***********CREATE QUERIES*************
    With dbs
        For Each qry In .QueryDefs
            If qry.Name = "G11" Or qry.Name = "G120" Then
                .QueryDefs.Delete qry.Name
            End If
            Me.Refresh
        Next qry
    If J <> 0 Then ' Drosstyper valgt
        Set qdf1 = .CreateQueryDef("G11", _
                "SELECT G10uqDrossOmsmelt.CID, G10uqDrossOmsmelt.DROSSID, " _
                & "Sum(G10uqDrossOmsmelt.SumDross) AS SumAvSumDross " _
                & "FROM G10uqDrossOmsmelt " _
                & "GROUP BY G10uqDrossOmsmelt.CID, G10uqDrossOmsmelt.DROSSID " _
                & "HAVING " & strS2 & ";")
            Me.Refresh
    Select Case Me.fraDrossValg
        Case 1 'Minst én av de valgte typer er med
            strS3 = ""
 
        Case 2 'alle de valgte og andre typer
            strS3 = "HAVING Count(G11.DROSSID)=" & J & " AND G115.AntDT>=Count([DROSSID])"
        
        Case 3 'BARE de valgte typer
            strS3 = "HAVING Count(G11.DROSSID)=" & J & " AND G115.AntDT=" & J
    End Select
        Set qdf2 = .CreateQueryDef("G120", _
                "SELECT G11.CID, Count(G11.DROSSID) AS AntallAvDROSSID, G115.AntDT " _
                & "FROM G11 INNER JOIN G115 ON G11.CID = G115.CID " _
                & "GROUP BY G11.CID, G115.AntDT " _
                & strS3 & ";")
            Me.Refresh
    Else ' Ingen drosstyper valgt
        Set qdf1 = .CreateQueryDef("G11", _
                "SELECT G10uqDrossOmsmelt.CID, G10uqDrossOmsmelt.DROSSID, " _
                & "Sum(G10uqDrossOmsmelt.SumDross) AS SumAvSumDross " _
                & "FROM G10uqDrossOmsmelt " _
                & "GROUP BY G10uqDrossOmsmelt.CID, G10uqDrossOmsmelt.DROSSID;")
            Me.Refresh

        Set qdf2 = .CreateQueryDef("G120", _
                "SELECT G11.CID, Count(G11.DROSSID) AS AntallAvDROSSID, G115.AntDT " _
                & "FROM G11 INNER JOIN G115 ON G11.CID = G115.CID " _
                & "GROUP BY G11.CID, G115.AntDT ;")
            Me.Refresh
    End If
'**************Query G140***********************
    J = IsDate(Me.ctlSluttdato) * (-2) + IsDate(Me.ctlStartdato) * (-1)
    For Each qdf1 In .QueryDefs
        If qdf1.Name = "G140" Then
            .QueryDefs.Delete ("G140")
            Me.Refresh
            Exit For
        End If
    Next qdf1
    
    Select Case J
        Case 0 'Ingen datoer valgt
            Set qdf1 = .CreateQueryDef("G140", _
                "SELECT G116.PKM, G116.CID, CDate(Format([PDATO],'d/m/yyyy')) AS PD, " _
                & "G19NSower.SumAvANTALLFORMER, [SVEKT]+[IVEKT] AS M, " _
                & "Utbytte([G120.CID]) AS Yield " _
                & "FROM (G120 INNER JOIN G116 ON G120.CID = " _
                & "G116.CID) INNER JOIN G19NSower ON G120.CID = G19NSower.CID;")
            Me.Refresh
        Case 1 'Fra og med startdato
            D1 = DatePart("m", Me.ctlStartdato) & "/" & DatePart("d", Me.ctlStartdato) & "/" & DatePart("yyyy", Me.ctlStartdato)
            Set qdf1 = .CreateQueryDef("G140", _
                 "SELECT G116.PKM, G116.CID, CDate(Format([PDATO],'d/m/yyyy')) AS PD, " _
                & "G19NSower.SumAvANTALLFORMER, [SVEKT]+[IVEKT] AS M, " _
                & "Utbytte([G120.CID]) AS Yield " _
                & "FROM (G120 INNER JOIN G116 ON G120.CID = " _
                & "G116.CID) INNER JOIN G19NSower ON G120.CID = G19NSower.CID " _
                & "WHERE CDate(Format([PDATO],'d/m/yyyy'))  >=#" & D1 & "#;")
            Me.Refresh
        Case 2 'Til og med Sluttdato
            D2 = DatePart("m", Me.ctlSluttdato) & "/" & DatePart("d", Me.ctlSluttdato) & "/" & DatePart("yyyy", Me.ctlSluttdato)
            Set qdf1 = .CreateQueryDef("G140", _
                 "SELECT G116.PKM, G116.CID, CDate(Format([PDATO],'d/m/yyyy')) AS PD, " _
                & "G19NSower.SumAvANTALLFORMER, [SVEKT]+[IVEKT] AS M, " _
                & "Utbytte([G120.CID]) AS Yield " _
                & "FROM (G120 INNER JOIN G116 ON G120.CID = " _
                & "G116.CID) INNER JOIN G19NSower ON G120.CID = G19NSower.CID " _
                & "WHERE CDate(Format([PDATO],'d/m/yyyy')) <=#" & D2 & "#;")
            Me.Refresh
        Case 3
            D1 = DatePart("m", Me.ctlStartdato) & "/" & DatePart("d", Me.ctlStartdato) & "/" & DatePart("yyyy", Me.ctlStartdato)
            D2 = DatePart("m", Me.ctlSluttdato) & "/" & DatePart("d", Me.ctlSluttdato) & "/" & DatePart("yyyy", Me.ctlSluttdato)
            If D1 = D2 Then
                Set qdf1 = .CreateQueryDef("G140", _
                     "SELECT G116.PKM, G116.CID, CDate(Format([PDATO],'d/m/yyyy')) AS PD, " _
                    & "G19NSower.SumAvANTALLFORMER, [SVEKT]+[IVEKT] AS M, " _
                    & "Utbytte([G120.CID]) AS Yield " _
                    & "FROM (G120 INNER JOIN G116 ON G120.CID = " _
                    & "G116.CID) INNER JOIN G19NSower ON G120.CID = G19NSower.CID " _
                    & "WHERE CDate(Format([PDATO],'d/m/yyyy')) =#" & D1 & "#;")
                Me.Refresh
            Else
                Set qdf1 = .CreateQueryDef("G140", _
                     "SELECT G116.PKM, G116.CID, CDate(Format([PDATO],'d/m/yyyy')) AS PD, " _
                    & "G19NSower.SumAvANTALLFORMER, [SVEKT]+[IVEKT] AS M, " _
                    & "Utbytte([G120.CID]) AS Yield " _
                    & "FROM (G120 INNER JOIN G116 ON G120.CID = " _
                    & "G116.CID) INNER JOIN G19NSower ON G120.CID = G19NSower.CID " _
                    & "WHERE CDate(Format([PDATO],'d/m/yyyy')) BETWEEN #" _
                    & D1 & "# " And "#" & D2 & "#;")
                Me.Refresh
            End If
    End Select
    .Close
    Set dbs = Nothing
    Set qdf1 = Nothing
    Set qdf2 = Nothing
    Me.Application.RefreshDatabaseWindow
    DoCmd.OpenQuery "G11", acViewNormal
    DoCmd.OpenQuery "G120", acViewNormal
    DoCmd.OpenQuery "G140", acViewNormal
    'DoCmd.OpenReport "rptChargeDataMain", acViewNormal
    End With
End Sub

Thanks in advance
 
I recommend using Debug.Print YourQueryText statements to test the assembeld queries by copying the output to new queries.
 
Thanks for your reply Nouba.

I've dissected the queries, and made them more easy. I have a button on my search form, that when clicked, creates these queries based on the users selections, and then opens the report on the queries made.

This works fine most of the time. The part I can't figure out is if it opens ok the first and second time, but not the third. The error I then get is the same as earlier described ('to complicated query' or 'the number of columns don't match the query or table output' or something like that (translated from norwegian). If I then try to open the query which gives the error ftrom the db window, I might get the error again. Try running it once more and it runs!
This happens without changong anything in the queries?

I don't know if the queryDefs are created immediately or if they need to be 'updated' or something like that before they are used?

If anyone has other tips or help, please post them.

Thanks in advance.
 
Not sure if all this will help but you did ask for comments....

1) Indent properly.... (RE: With dbs .... end with)
2) Why delete the queries? And not replace the SQL?
dbs.querydefs("QueryName").SQL = "Select ... "
This will save both time and (a little) bloating.
If you feel you must delete, or feel more comfertable this way. Delete ONCE only. You have 2 seperate delete loops...
Also there might not be a need to loop like that. A simple straight forward command like below will do...
docmd.setwarnings false
dbs.querydefs("G11").delete
...
...
docmd.setwarnings true
3) Your redoing your SQL. That is... Take this SQL for instance...
Code:
Set qdf1 = .CreateQueryDef("G140", _
     "SELECT G116.PKM, G116.CID, CDate(Format([PDATO],'d/m/yyyy')) AS PD, " _
    & "G19NSower.SumAvANTALLFORMER, [SVEKT]+[IVEKT] AS M, " _
    & "Utbytte([G120.CID]) AS Yield " _
    & "FROM (G120 INNER JOIN G116 ON G120.CID = " _
    & "G116.CID) INNER JOIN G19NSower ON G120.CID = G19NSower.CID " _
    & "WHERE [COLOR=red]CDate(Format([PDATO],'d/m/yyyy')) BETWEEN #" _
    & D1 & "# " And "#" & D2 & "#;")[/COLOR]
While only your where clause actually changes.... If at any point your basic SQL changes you will have to amment the code multiple times. EG use/define the "Normal" bit - select.... from .... in this case - once and only attach the where clause.
4) "Properly" indent your SQL. By that i mean do it all 1 way... It would depend on your preference how to do it exactly. I would suggest:
Code:
Set qdf1 = .CreateQueryDef("G140", _
     "SELECT " & _
         "G116.PKM, " & _
         "G116.CID, " & _
         "CDate(Format([PDATO],'d/m/yyyy')) AS PD, " & _
         "G19NSower.SumAvANTALLFORMER, " & _
         "[SVEKT]+[IVEKT] AS M, " & _
         "Utbytte([G120.CID]) AS Yield " & _
     "FROM " & _
         "(G120                             INNER JOIN " & _
         "G116      ON G120.CID = G116.CID) INNER JOIN " & _
         "G19NSower ON G120.CID = G19NSower.CID " & _
     "WHERE " & _
         "CDate(Format([PDATO],'d/m/yyyy')) " & _
               "BETWEEN #" & D1 & "# " And "#" & D2 & "#;")
Which i find more readable. But again the actually layout is up to you. You can play with the & and the Spaces. But the general idea is kindoff like above....
5) Why the Cdate(Format()) construction?
6) Why
Code:
D1 = DatePart("m", Me.ctlStartdato) & "/" & DatePart("d", Me.ctlStartdato) & "/" & DatePart("yyyy", Me.ctlStartdato)
and not use the simple but effective "Format":
Code:
D1 = format(me.ctlStartdato,"mm/dd/yyyy")
7) why all the Me.Refresh commands?
8) Why all the seperate strings? You only need 1. And if you do need more properly dim.... (add as string to all)
Code:
     Dim strS1
    Dim strS2
    Dim strS3 As String
9)
Code:
    For I = 0 To Me.lboDross.ListCount - 1 '0 based
        If Me.lboDross.Selected(I) Then
            If Flag = True Then
                Flag = False
                strS2 = strS2 & " DROSSID=" & Me.lboDross.Column(0, I)
            Else
                strS2 = strS2 & " OR DROSSID=" & Me.lboDross.Column(0, I)
            End If
            J = J + 1
        End If
    Next I
Why use the J=J+1 as a counter when you allready have a counter in the for next loop (I)
Also this can be done neeter.... Try looking at this:
Code:
    For I = 0 To Me.lboDross.ListCount - 1 '0 based
        If Me.lboDross.Selected(I) Then
                strS2 = strS2 & " OR DROSSID=" & Me.lboDross.Column(0, I)
        End If
    Next I
    strS2 = mid(strS2,4)
10) Your missing: Set qry = nothing
Also "standardize" use qry or qdf

Sorry for the longish post and sorry if i am "overdoing" it

Regards

The Mailman
 
Sorry for the late reply here Mailman. I ve been so buzzy with my app at the company that's using it, and went there today to instal the new version where my 'search form' should be the new feature.
I appreciate your thourough reply extremely much. I considered this thread as a 'Zero-reply'- candidate.

I will make a lot of changes as you suggested, and see if it works better then. I'd like to comment your questions on my constructions of dates:
5) Why the Cdate(Format()) construction?
The field I used this on contained full date/time format, so I needed only the date, so it would group correct.
6) Why
Code:
D1 = DatePart("m", Me.ctlStartdato) & "/" & DatePart("d", Me.ctlStartdato) & "/" & DatePart("yyyy", Me.ctlStartdato)
and not use the simple but effective "Format":

In Norway it's common to use '.' as a separator in dates. This is the way the regional settings are set up as a default. So If I dim a variable as date in VBA and assign MyDate=#10/12/2003# it gets converted to 10.12.2003. If I try to use format, it also get converted.

These lines are copied from my immediate window of the VBE.
Code:
MyDate=#10/12/2003#
?MyDate
12.10.2003 

?Format(MyDate,"d/m/yyyy")
12.10.2003
The dots '.' don't do very well in a SQL string in VBA.

The app I tried today hasn't got the same code as my first post. But I may have discovered why I get the 'No match of number of columns....' on running the queries: If all fields are selected when the query is created in the querydef, the query shows up like 1 column with the '*', that means all fields are selected. The joins are lost somehow when using the query in a new query. This is what I saw when trying this at the company server.

I really appreciate your reply and all suggestions that I find interesting. I'll try making this over again with your methods and programming techniques.

If you have new comments, please post them. I'm most interested.

Thanks again Mailman.
 

Users who are viewing this thread

Back
Top Bottom