what's wrong with this code

[lala]

Dim TblName As Long
Dim FMonth As Long

Dim FYear As Long
Dim FSort As Long
Dim TMonth As Long
Dim TYear As Long
Dim TSort As Long





'delete after the form is made with a date picker
FYear = 2010
FMonth = 3
TYear = 2010
TMonth = 9
TblName = 2
'delete after the form is made with a date picker

Dim TrendSQLSelect As String
Dim TrendSQLJoins As String
Dim TrendSQL As String


TrendSQLSelect = ""
TrendSQLJoins = ""
TrendSQL = ""


If TblName = 2 Then 'market




  Dim QryToLoop As String
  Dim LMonth As Long
  Dim LYear As Long
  Dim db As Database
  Dim Rst As Recordset
        
  QryToLoop = "SELECT CmbMonthsYears.Dates FROM CmbMonthsYears WHERE CmbMonthsYears.Dates>#" & FMonth & "/1/" & FYear & _
              "# AND CmbMonthsYears.Dates<#" & TMonth & "/1/" & TYear & "#"
        
        
  Set db = CurrentDb
  Set Rst = db.OpenRecordset(QryToLoop)
        
        
        
                  Do Until Rst.EOF
                        
                  LMonth = Left(Rst("Dates"), InStr(Rst("Dates"), "/1/") - 1)
                  LYear = Right(Rst("Dates"), 4)
                        
                        
                  'MsgBox LMonth & " - " & LYear
                                
                
                
                            Dim dbs As Database
                            Dim qryDef As QueryDef
                
                            Dim QryMonth As String
                            Dim QryName As String
                            
                            
                            On Error Resume Next
                            
                            Set dbs = CurrentDb()
                            QryName = LMonth & LYear
                            
                            
                            If QueryExists(LMonth & LYear) Then
                            dbs.QueryDefs.Delete QryName
                            End If
                            
                            
                            QryMonth = "SELECT MONTHLY.State, MONTHLY.Mkt, MONTHLY.Acute, MONTHLY.Ancil, MONTHLY.PCP, MONTHLY.Spec, MONTHLY.TotalPrac, MONTHLY.DAcuteI, " & _
                                       "MONTHLY.DAcuteO, MONTHLY.DAncil, MONTHLY.DPrac FROM MONTHLY WHERE MONTHLY.CMonth=" & LMonth & " AND MONTHLY.CYear=" & LYear & " " & _
                                       "AND MONTHLY.TableName=" & TblName & " ORDER BY MONTHLY.SortID, MONTHLY.State, MONTHLY.Mkt"
                            
                            Set qryDef = dbs.CreateQueryDef(QryName, QryMonth)
                
                
                
                    If IsNull(TrendSQL) Or TrendSQL = "" Then
                    TrendSQLSelect = ", [" & QryName & "].Acute"
                    TrendSQLJoins = "LEFT JOIN " & QryName & " ON (AllMkts.Mkt = [" & QryName & "].Mkt) AND (AllMkts.State = [" & QryName & "].State) "
                    Else
                    TrendSQLSelect = TrendSQLSelect & ", [" & QryName & "].Acute"
                    TrendSQLJoins = TrendSQLJoins & " LEFT JOIN " & QryName & " ON (AllMkts.Mkt = [" & QryName & "].Mkt) AND (AllMkts.State = [" & QryName & "].State) "
                    End If
                
                
                    TrendSQL = "SELECT AllMkts.State, AllMkts.Mkt" & TrendSQLSelect & " FROM AllMkts " & TrendSQLJoins & "ORDER BY AllMkts.ID, AllMkts.State, AllMkts.Mkt;"
                
                                
                  Rst.MoveNext
                  Loop


                Dim db11 As Database
                Dim qryDef11 As QueryDef
                                                    
                Set db11 = CurrentDb
                                            
                            If QueryExists("QTrends") Then
                            db11.QueryDefs.Delete "QTrends"
                            End If
                                            
                                
                                
[B][SIZE="5"]Set qryDef11 = db11.CreateQueryDef("QTrends", TrendSQL)[/SIZE][/B]

[SIZE="5"]msgbox trendsql[/SIZE]


ElseIf TblName = 4 Then 'MSA
'will write later



ElseIf TblName = 8 Then 'State
'will write later



End If

everything works fine except the large line
it's just not creating the query. everything runs perfect, i tested it with the MSGBOX to see the sql and it looks fine to me but the query doesn't get created

if i put the query-create-block inside the loop - it creates it, but only with the first go round of values


what am i doing wrong?

thank you

 

[boblarson]

Do a

debug.print TrendSQL

and paste what comes out in the Immediate Window.

 

[lala]

wow, i spend half hour trying to figure out how to get this on a cliboard

thank you for another lesson

SELECT AllMkts.State, AllMkts.Mkt, [42010].Acute, [52010].Acute, [62010].Acute, [72010].Acute, [82010].Acute FROM AllMkts LEFT JOIN 42010 ON (AllMkts.Mkt = [42010].Mkt) AND (AllMkts.State = [42010].State)  LEFT JOIN 52010 ON (AllMkts.Mkt = [52010].Mkt) AND (AllMkts.State = [52010].State)  LEFT JOIN 62010 ON (AllMkts.Mkt = [62010].Mkt) AND (AllMkts.State = [62010].State)  LEFT JOIN 72010 ON (AllMkts.Mkt = [72010].Mkt) AND (AllMkts.State = [72010].State)  LEFT JOIN 82010 ON (AllMkts.Mkt = [82010].Mkt) AND (AllMkts.State = [82010].State) ORDER BY AllMkts.ID, AllMkts.State, AllMkts.Mkt;

SELECT AllMkts.State, AllMkts.Mkt, [42010].Acute, [52010].Acute, [62010].Acute, [72010].Acute, [82010].Acute
FROM AllMkts
LEFT JOIN 42010 ON (AllMkts.Mkt = [42010].Mkt) AND (AllMkts.State = [42010].State)
LEFT JOIN 52010 ON (AllMkts.Mkt = [52010].Mkt) AND (AllMkts.State = [52010].State)
LEFT JOIN 62010 ON (AllMkts.Mkt = [62010].Mkt) AND (AllMkts.State = [62010].State)
LEFT JOIN 72010 ON (AllMkts.Mkt = [72010].Mkt) AND (AllMkts.State = [72010].State)
LEFT JOIN 82010 ON (AllMkts.Mkt = [82010].Mkt) AND (AllMkts.State = [82010].State)
ORDER BY AllMkts.ID, AllMkts.State, AllMkts.Mkt;


edit making it easier to read

 

[lala]

i think i know, there's a problem in a query, that's why it wasn't creating

just can't find it yet

Bob, you're amazing

 

[lala]

found it, it's the (, right? missing a few, i have to take each set of joins and wrap them, right?
i'm not smarter than you, i created a query by hand and compared

will try coding now and see what happens

thank you for the idea!!!

 

[lala]

i still need your help. if it is the ('s im missing, i'm not sure how to code them. the first set has to be wrapped starting from

((AllMkts LEFT JOIN 42010 ON (AllMkts.State = [42010].State) AND (AllMkts.Mkt = [42010].Mkt))

and the second and all the rest from

(AllMkts.State = [52010].State) AND (AllMkts.Mkt = [52010].Mkt))

off the top of my head nothing comes to mind
if you have a minute then can you help? if not - it's ok, this is something i will eventually get myself

 

[lala]

got it!!!!!!!!
thank you, thank you, thankyou!!!!!!!!!!!

 

[boblarson]

got it!!!!!!!!
thank you, thank you, thankyou!!!!!!!!!!!

You did all of the work. I just made a suggestion to view it from the Immediate window. Glad you got it to work.

 

[lala]

You did all of the work. I just made a suggestion to view it from the Immediate window. Glad you got it to work.

that was the part i needed to move on)))))))))
i was looking on the net for a way to copy a variable on the clipboard and, believe it or not, couldn't find anything that'd work in access (at least for me)

never knew about debug.print, so cool, so easy