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
Thanks in advance
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