• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

create Union Query problems

Dear Experts,

I have a piece of code which used to work, but not longer does.  What the code does is merge (Union All) 2 queries together.  

Basically there is a table with the SQL string stored in it with the following fields:

SearchID (Autonumber)
SearchName (Text)
SearchTime (Date/Time)
SearchString (Memo)
Merge (Yes/No)

The search string field basically contains the sql for the query (without the; at the end) – e.g.

SELECT [Tbl-Contacts].* FROM [Tbl-Contacts] WHERE ((([Tbl-Contacts].SumBoard) Is Not Null) AND (([Tbl-Contacts].SumFinance) Is Not Null) AND (([Tbl-Contacts].SumHR) Is Not Null) AND (([Tbl-Contacts].SumRD) Is Not Null)) AND (([Tbl-Contacts].SumReg) Is Not Null) AND (([Tbl-Contacts].SumSales) Is Not Null) AND (([Tbl-Contacts].State)='Delaware')

The attached code should go through the table picking up the searchstring where Merge = -1 and join the statements up with a union all and then add that as a new record in the table, the result would look something like this …

SELECT [Tbl-Contacts].* FROM [Tbl-Contacts] WHERE ((([Tbl-Contacts].SumBoard) Is Not Null) AND (([Tbl-Contacts].SumFinance) Is Not Null) AND (([Tbl-Contacts].SumHR) Is Not Null) AND (([Tbl-Contacts].SumRD) Is Not Null)) AND (([Tbl-Contacts].SumReg) Is Not Null) AND (([Tbl-Contacts].SumSales) Is Not Null) AND (([Tbl-Contacts].State)='Delaware')
Union All
SELECT [Tbl-Contacts].* FROM [Tbl-Contacts] WHERE ((([Tbl-Contacts].SumBoard)=0) AND (([Tbl-Contacts].SumFinance) Is Not Null) AND (([Tbl-Contacts].SumHR) Is Not Null) AND (([Tbl-Contacts].SumRD) Is Not Null)) AND (([Tbl-Contacts].SumReg) Is Not Null) AND (([Tbl-Contacts].SumSales) Is Not Null) AND ((([Tbl-Contacts].Firm)  Like '*GSK*'))

However result that is produced doesn’t include the first record (however many records are selected for merging

Can anybody help?

Private Sub Merge_Click()
  
    
    ' ********* Check Bit  *********
If Len(Me!TextName & "") = 0 Then
       MsgBox "You must add a Name", vbExclamation
        Cancel = True
        Exit Sub
    End If
    
DoCmd.Save
Dim rs As DAO.Recordset, uSQL As String
Set rs = CurrentDb.OpenRecordset("select * from [Tbl-SavedSearches] where merge=-1")
uSQL = ""
Do Until rs.EOF
uSQL = uSQL & " Union All " & rs("SearchString")


rs.MoveNext
Loop

rs.Close

uSQL = Mid(uSQL, 11)
Debug.Print uSQL
   
    
' ********* Save Bit  *********
Dim rs1 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset("tbl-SavedSearches")
rs1.AddNew
rs1!SearchName = Me.[TextName]
rs1!SearchTime = Now()
rs1!SearchString = uSQL
rs1.Update
rs1.Close

  
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Frm-SavedSearches"
    DoCmd.Close acForm, "Frm-SavedSearches"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "Frm-Merge-Searches"

End Sub

Open in new window

0
correlate
Asked:
correlate
  • 5
  • 3
  • 2
  • +2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try

rs.movefirst    'ADD this line before doing the loop

Do Until rs.EOF
0
 
Paul_Harris_FusionCommented:
If I understand you correctly, it is the first SQL statement that is missing not the first record returned from the entire statement.

Try replacing the inner loop with this:

Dim rs As DAO.Recordset, uSQL As String
Set rs = CurrentDb.OpenRecordset("select * from [Tbl-SavedSearches] where merge=-1")
uSQL = ""

If not rs.BOF then
   rs.MoveFirst

   Do While not rs.EOF
        uSQL = uSQL & " Union All " & rs("SearchString")
       rs.MoveNext
   Loop

End if


0
 
Helen FeddemaCommented:
Also, you might want to replace the IsNull function with Nz, and check for Nz([FieldName]) <> 0 or <> "" (depending on whether the field is text or numeric), since often one wants to exclude blanks or zeroes as well as Nulls.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
correlateAuthor Commented:
Hi Capricorn 1 & Paul Harris,

I tried both, but alas I'm getting the same result, its still only only picking up on the 1,

here I'm attaching what I am using as Paul's code, I tried, I'll attach Capricorn's version in the next comment
Private Sub Merge_Click()
  
    
    ' ********* Check Bit  *********
If Len(Me!TextName & "") = 0 Then
       MsgBox "You must add a Name", vbExclamation
        Cancel = True
        Exit Sub
    End If
    
DoCmd.Save

Dim rs As DAO.Recordset, uSQL As String
Set rs = CurrentDb.OpenRecordset("select * from [Tbl-SavedSearches] where merge=-1")
uSQL = ""

If Not rs.BOF Then
   rs.MoveFirst

   Do While Not rs.EOF
        uSQL = uSQL & " Union All " & rs("SearchString")
       rs.MoveNext
   Loop

End If

'uSQL = Mid(uSQL, 11)
'Debug.Print uSQL
   
    
' ********* Save Bit  *********
Dim rs1 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset("tbl-SavedSearches")
rs1.AddNew
rs1!SearchName = Me.[TextName]
rs1!SearchTime = Now()
rs1!SearchString = uSQL
rs1.Update
rs1.Close

  
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Frm-SavedSearches"
    DoCmd.Close acForm, "Frm-SavedSearches"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "Frm-Merge-Searches"

End Sub

Open in new window

0
 
correlateAuthor Commented:
Capricorn's code...
Private Sub Merge_Click()
  
    
    ' ********* Check Bit  *********
If Len(Me!TextName & "") = 0 Then
       MsgBox "You must add a Name", vbExclamation
        Cancel = True
        Exit Sub
    End If
    
DoCmd.Save
Dim rs As DAO.Recordset, uSQL As String
Set rs = CurrentDb.OpenRecordset("select * from [Tbl-SavedSearches] where merge=-1")
uSQL = ""
rs.MoveFirst
Do Until rs.EOF
uSQL = uSQL & " Union All " & rs("SearchString")


rs.MoveNext
Loop

rs.Close

uSQL = Mid(uSQL, 11)
Debug.Print uSQL
   
    
' ********* Save Bit  *********
Dim rs1 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset("tbl-SavedSearches")
rs1.AddNew
rs1!SearchName = Me.[TextName]
rs1!SearchTime = Now()
rs1!SearchString = uSQL
rs1.Update
rs1.Close

  
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Frm-SavedSearches"
    DoCmd.Close acForm, "Frm-SavedSearches"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "Frm-Merge-Searches"

End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
try this


post the message box content
Private Sub Merge_Click()
  
    
    ' ********* Check Bit  *********
If Len(Me!TextName & "") = 0 Then
       MsgBox "You must add a Name", vbExclamation
        Cancel = True
        Exit Sub
    End If
    
DoCmd.Save
Dim rs As DAO.Recordset, uSQL As String
Set rs = CurrentDb.OpenRecordset("select * from [Tbl-SavedSearches] where merge=-1")
uSQL = ""

rs.movelast
msgbox rs.recordcount & " records found"

rs.MoveFirst
Do Until rs.EOF
uSQL = uSQL & " Union All " & rs("SearchString")


rs.MoveNext
Loop

rs.Close

uSQL = Mid(uSQL, 11)
Debug.Print uSQL
   
    
' ********* Save Bit  *********
Dim rs1 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset("tbl-SavedSearches")
rs1.AddNew
rs1!SearchName = Me.[TextName]
rs1!SearchTime = Now()
rs1!SearchString = uSQL
rs1.Update
rs1.Close

  
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Frm-SavedSearches"
    DoCmd.Close acForm, "Frm-SavedSearches"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "Frm-Merge-Searches"

End Sub

Open in new window

0
 
Dale FyeCommented:
What are you attempting to achieve with the line:

uSQL = Mid(uSQL, 11)

0
 
correlateAuthor Commented:
Hi Capricorn1

Message = 1 records found
0
 
Rey Obrero (Capricorn1)Commented:
that is the reason, why you are only getting one record
0
 
correlateAuthor Commented:
Hi fyed

This bit I believe cuts off the first 11 characters in the string otherwise the string would start " Union All SELECT..." rather than SELECT
0
 
Dale FyeCommented:
Duh!  brain cramp.
0
 
correlateAuthor Commented:
Hi Capricorn1 - thanks for that - works a treat now - your comment made me check other codes in there and what I found was there was another RS! which ran on Open form that cleared out the ticks,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now