Ommitting blank values

[Blitznb]

Struggling with omitting entire rows when no orders exist for customers.

I have a crosstab that pulls & groups suppliers orders per customer based on months. I then pull that info to a report which provides a comparision of sales in specified year verses prior year and next year when available.

This report groups suppliers and then lists customers below it. I am struggling with how to omit companies that do not have orders in a given periord and in turn exclude suppliers if there are no customers with orders through them in the given time.

I have tired to address the querry with Is Not Null or Is Null or >0 etc and much the same in vb and on report. Im having no luck. Any suggestion on best way to handle this?

 

[the_net_2.0]

whats the sql?

 

[Blitznb]

Form Code

Dim txtYear As String
Dim txtBeginMonth As String
Dim txtEndMonth As String
Dim PrinName As String
If Nz(Me.cboPrincipal, "") <> "" Then
        PrinName = "([PrinName] = """ & Me.cboPrincipal & """)"
End If
If Nz(Me.txtBeginMonth, "") <> "" Then
        txtBeginMonth = "([txtBeginMonth] = ""#" & Me.txtBeginMonth & "#"")"
End If
If Nz(Me.txtEndMonth, "") <> "" Then
        txtEndMonth = "([txtEndMonth] = ""#" & Me.txtEndMonth & "#"")"
End If
If Nz(Me.txtYear, "") <> "" Then
        txtYear = "([txtYear] = ""#" & Me.txtYear & "#"")"
        
End If
   
MySQL = "SELECT DISTINCTROW Principals.[PrincipalName], Principals.[Principal ID],Orders.[Employee ID],Orders.[Order Amount],Orders.[Comm Earned], Customers.[Company], Customers.[ID], Orders.[Order Date] FROM Principals INNER JOIN (Customers INNER JOIN Orders ON Customers.[ID] = Orders.[Customer ID]) ON Principals.[Principal ID] = Orders.[Principal ID] WHERE((Principals.[PrincipalName] = PrinName) AND (Orders.[Orders Date] Between " & Me.txtBeginMonth & " And " & Me.txtEndMonth & ")) ORDER BY Principals.[PrincipalName], Customers.[Company];"

DoCmd.OpenReport "rptComparable", A_PREVIEW

 

[Blitznb]

Report Code

'-----------------------------Create SQL String---------------
Dim FindLast As String, Findthis As String
Findthis = ",'" & Forms![FmComparable]![txtBeginMonth] & "_" & Forms![FmComparable]![txtYear] & " ' "
For counter = 2 To Forms![FmComparable]![txtEndMonth]
Findthis = Findthis & ",'" & Trim$(Str(counter)) & "_" & Forms![FmComparable]![txtYear] & "'"
Next
FindLast = "In('" & Forms![FmComparable]![txtBeginMonth] & "_" & LastYear & "'"
For counter = 2 To 12
FindLast = FindLast & ",'" & Trim$(Str(counter)) & "_" & LastYear & "'"
Next
Finish = ");"
SQLString = "TRANSFORM Sum(Orders.[Order Amount]) AS SumOfOrderAmount SELECT Principals.[PrincipalName] FROM Principals INNER JOIN (Customers INNER JOIN Orders ON Customers.[ID] = Orders.[Customer ID]) ON Principals.[Principal ID] = Orders.[Principal ID]"
SQLString = SQLString & "WHERE (Principals.[PrincipalName])= [Forms]![FmComparable]![cboPrincipal] AND [Orders].[Order Amount] Is >0"
SQLString = SQLString & " GROUP BY Principals.[PrincipalName]  PIVOT Format([Order Date],'m_yyyy')"
SQLString = SQLString & FindLast & Findthis & Finish
'Forms!FmComparable!Text60 = Findthis
Reports![rptComparable]![TotalNextYear].ControlSource = txtLastTotal
Reports![rptComparable]![TotalLastYear].ControlSource = txtLast

End Sub

Thx 4 helps!