Union all queries if value is null

[mickie52]

What I am trying to do is merge data from several tables into one query, based on user defined options through multiple listboxes. I've figured out the VBA for the union all for the different selections, my problem is I can't determine how to make it dynamic and skip over a select statement if the list value is null. My code looks something like this so far. Sorry for the sloppy.

 Dim db As DAO.Database
    Set db = CurrentDb
    
Dim qdf As DAO.QueryDef
    Set qdf = db.QueryDefs("Flex2")
    
Dim SDay As String
 Dim sFilter As String, sFilter1 As String, sFilter2 As String, sFilter3 As String, sFilter4 As String, sFilter5 As String
Dim sFilter6 As String, sFilter7 As String, sFilter8 As String, sForm As String

 sFilter = Forms![Packets]!
[List6]
sFilter1 = Forms![Packets]!
[List10]
sFilter2 = Forms![Packets]!
[List12]
sFilter3 = Forms![Packets]!
[List27]
 sForm = "[CALENDAR MONTH]"
  
 "SELECT [4-5-4 WEEK 1-4].jobs, [4-5-4 WEEK 1-4].[sla/ola], [4-5-4 WEEK 1-4].[special instructions], [4-5-4 WEEK 1-4].times" & _
    " FROM  [4-5-4 WEEK 1-4]" & _
    " WHERE ((([4-5-4 WEEK 1-4].ADOM)= " & sFilter & "))" & _
    " UNION ALL" & _
    " SELECT [4-5-4-QUARTER].jobs, [4-5-4-QUARTER].[sla/ola], [4-5-4-QUARTER].[special instructions], [4-5-4-QUARTER].TIMES" & _
    " FROM [4-5-4-QUARTER] " & _
    " WHERE  ((([4-5-4-QUARTER].[DAY of Week]) = " & Chr(34) & sFilter1 & Chr(34) & ") And (([4-5-4-QUARTER].[MONTH OF QUARTER]) = " & sFilter2 & "))" & _
    " UNION ALL" & _
    " SELECT " & sForm & ".jobs, " & sForm & ".[sla/ola], " & sForm & ".[special instructions], " & sForm & ".TIMES" & _
    " FROM  " & sForm & _
    " WHERE (" & sForm & ".[Day of Month])= " & Chr(34) & sFilter3 & Chr(34) & ";"
  
 qdf.SQL = sState
 'DoCmd.RunSQL sState
      
DoCmd.OpenQuery ("Flex2")
'DoCmd.OpenReport ("Flex3"), acViewPreview, , , acHidden
    
Set qdf = Nothing
    
Set db = Nothing
 End Sub

 

[MarkK]

My bet is that you have serious table design problem. This . . .

[4-5-4 WEEK 1-4]

. . . is not a table name, that is data. This is the same mistake as if you had table names like Ed, Phil, and Steve to store peoples' names. Time is a variable quantity with Date as a discrete measure of that quantity, just like FirstName is a variable quantity with Dave as a discrete measure of that quantity, but Dave is not a good name for a table. "Dave" is data, just as any measure of time is data, and should be in a field in a table, not in a table name.

I know this doesn't answer your question directly, but if you want to save yourself hours and hours and hours, and then more hours, re-design your tables such that writing dynamic Union queries is not required.

Hope this helps,

 

[mickie52]

Thanks but the table names aren't an issue, the search criteria for each table is completely different. and if I choose a value the Union All works just fine, it's when there isn't a value that I want it to skip over and move onto the next. To be honest If then, and or elseif whatever statements are the bane of my existence..

 

[MarkK]

Do you have multiple tables with exactly the same structure, but different names? Is that why you need to Union them together like that?

 

[mickie52]

Yes I have 6 rtables with similar structure, each have 4 of the same fields, but the rest differ. I need the 4 fields to join depending on user defined listbox search criteria. I've tried subreports, but the issue is they error out when one of the listboxes is empty or do not update every subreport. So I am back at a union query.

 

[MSAccessRookie]

I believe that what MarkK has been trying to tell you is that while you may not feel that the Table Names are at issue, they are, at least to a degree. More importantly, the Table Structures are certainly at issue. We would really like to assist you in this, but more information will be needed to determine what can be done to get you going in the right direction.

Are you able to provide us with the structures of the Tables that "have 4 of the same fields, but the rest differ"? If you can, then please include all of the fields in each Table, not just the ones that are the same. In addition, some samples of the user searchbox criteria choices and the applicable rules would be very useful.

-- Rookie