Solved

create Union Query problems

Posted on 2010-09-17
12
535 Views
Last Modified: 2012-05-10
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
Comment
Question by:correlate
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33701564
try

rs.movefirst    'ADD this line before doing the loop

Do Until rs.EOF
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 33701626
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 33701666
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:correlate
ID: 33701923
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
 

Author Comment

by:correlate
ID: 33701948
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 33701986
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
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 33702026
What are you attempting to achieve with the line:

uSQL = Mid(uSQL, 11)

0
 

Author Comment

by:correlate
ID: 33702133
Hi Capricorn1

Message = 1 records found
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33702149
that is the reason, why you are only getting one record
0
 

Author Comment

by:correlate
ID: 33702154
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
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 33702164
Duh!  brain cramp.
0
 

Author Closing Comment

by:correlate
ID: 33702384
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question