Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2016
    Posts
    4

    Unanswered: Writing a Union SQL in VBA and exporting to Excel

    Hello, I've been working on an old database and my boss wanted a new report based off of the data. I can't get the charts in Access to work properly so I decided the best way to get the results I wanted was to export the query to excel and work with it there. I have got the program to work with other SQL queries but when I use the query I designed this for it fails to run. I am new to Access and VBA and I am learning as I go but can't seem to figure out why this SQL query won't run. Here is the portion of the code with the error (the record set is empty)...

    SQL = "SELECT tblBHistoricalSamples.[SystemNo], tblPSystems.[SystemName], tblPSource.[SourceName], tblBHistoricalSamples.[SOURCENO], tblBHistoricalSamples.[Month] + '/' + tblBHistoricalSamples.[Year] AS [SampleDate], tblBHistoricalSamples.[TOTAL], tblBHistoricalSamples.[FECAL], tblBHistoricalSamples.[TOTALMPN], tblBHistoricalSamples.[FECALMPN], tblBHistoricalSamples.[Comments]" & _
    "FROM (tblPSystems INNER JOIN tblBHistoricalSamples ON tblPSystems.[SystemNo] = tblBHistoricalSamples.[SYSTEMNO]) INNER JOIN tblPSource ON (tblPSystems.[SystemName] = tblPSource.[SystemName]) AND (tblBHistoricalSamples.[SOURCENO] = tblPSource.[SourceNumber])" & _
    "WHERE tblBHistoricalSamples.[SYSTEMNO] = [Enter System Number] And tblBHistoricalSamples.[SOURCENO] = [Which Source?] And tblBHistoricalSamples.[SAMPLETYPE] = '5' Or tblBHistoricalSamples.[SAMPLETYPE]) = '6' " & _
    "UNION SELECT tblBSlip.[SystemNo], tblPSystems.[SystemName], tblPSource.[SourceName], tblBSlip.[SOURCENO], tblBSlip.[MONTH] + '/' + tblBSlip.[Year] AS [SampleDate], tblBSlip.[TOTAL], tblBSlip.[FECAL], tblBSlip.[TOTALMPN], tblBSlip.[FECALMPN], tblBSlip.[Comments]" & _
    "FROM (tblPSystems INNER JOIN tblBSlip ON tblPSystems.[SystemNo] = tblBSlip.[SYSTEMNO]) INNER JOIN tblPSource ON (tblPSystems.[SystemName] = tblPSource.[SystemName]) AND (tblBSlip.[SOURCENO] = tblPSource.[SourceNumber])" & _
    "WHERE tblBSlip.[SystemNo] = [Enter System Number] AND tblBSlip.[SOURCENO] = [Which Source?] AND tblBSlip.[SAMPLETYPE] = '5' Or (tblBSlip.[SAMPLETYPE] = '6';"

    Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)

    If rs1.RecordCount = 0 Then
    MsgBox "No data selected for export", vbInformation + vbOKOnly, "No data exported"
    GoTo subexit
    End If

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What seems strange to me is:
    Code:
    tblBHistoricalSamples.[SAMPLETYPE] = '5' Or tblBHistoricalSamples.[SAMPLETYPE]) = '6' "
    Is tblBHistoricalSamples.[SAMPLETYPE] defined as Text?
    Have a nice day!

  3. #3
    Join Date
    Mar 2016
    Posts
    4
    Yes it is. I tried to set it as a number but I will lose some data, is there any way I can see what it will be deleting so I can just re-enter it?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not sure to ubnderstand what you mean;
    Quote Originally Posted by Thomas.Moulton View Post
    Yes it is. I tried to set it as a number but I will lose some data
    If the column is defined as numeric, I don't see why you would "lose" any data.
    Quote Originally Posted by Thomas.Moulton View Post
    is there any way I can see what it will be deleting so I can just re-enter it?
    The SQL expression you posted is you first message yields a SELECT query. It's impossible to actually delete rows in a table with such a query.
    Have a nice day!

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    does the SQL after the union work
    U have run and You see what you want NOTE the Colounm Order


    now run the First bit before the Union Select does that work NOTE the Colounm Order is it the same and the other one
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Mar 2016
    Posts
    4
    Quote Originally Posted by myle View Post
    does the SQL after the union work
    U have run and You see what you want NOTE the Colounm Order


    now run the First bit before the Union Select does that work NOTE the Colounm Order is it the same and the other one
    I'm not sure entirely sure what you mean but this SQL works if I use it straight in Access but it won't work in VBA - even if I run it without the union. I think the issue lies within the query parameters. If I do a query without any user-entered criteria it works fine.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If it works fine without the parameters then check all the column types are what you think they are

    Rather than see the vba tbat creates the query can we see the actual value of sql
    It may be a typo but you are missing a closing bracket on the final where clause
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What I would do is:
    1. print the contents of the SQL string into the immediate window:
    Code:
    SQL = "SELECT tblBHistoricalSamples.[SystemNo], tblPSystems.[SystemName], tblPSource.[SourceName], tblBHistoricalSamples.[SOURCENO], tblBHistoricalSamples.[Month] + '/' + tblBHistoricalSamples.[Year] AS [SampleDate], tblBHistoricalSamples.[TOTAL], tblBHistoricalSamples.[FECAL], tblBHistoricalSamples.[TOTALMPN], tblBHistoricalSamples.[FECALMPN], tblBHistoricalSamples.[Comments]" & _
    "FROM (tblPSystems INNER JOIN tblBHistoricalSamples ON tblPSystems.[SystemNo] = tblBHistoricalSamples.[SYSTEMNO]) INNER JOIN tblPSource ON (tblPSystems.[SystemName] = tblPSource.[SystemName]) AND (tblBHistoricalSamples.[SOURCENO] = tblPSource.[SourceNumber])" & _
    "WHERE tblBHistoricalSamples.[SYSTEMNO] = [Enter System Number] And tblBHistoricalSamples.[SOURCENO] = [Which Source?] And tblBHistoricalSamples.[SAMPLETYPE] = '5' Or tblBHistoricalSamples.[SAMPLETYPE]) = '6' " & _
    "UNION SELECT tblBSlip.[SystemNo], tblPSystems.[SystemName], tblPSource.[SourceName], tblBSlip.[SOURCENO], tblBSlip.[MONTH] + '/' + tblBSlip.[Year] AS [SampleDate], tblBSlip.[TOTAL], tblBSlip.[FECAL], tblBSlip.[TOTALMPN], tblBSlip.[FECALMPN], tblBSlip.[Comments]" & _
    "FROM (tblPSystems INNER JOIN tblBSlip ON tblPSystems.[SystemNo] = tblBSlip.[SYSTEMNO]) INNER JOIN tblPSource ON (tblPSystems.[SystemName] = tblPSource.[SystemName]) AND (tblBSlip.[SOURCENO] = tblPSource.[SourceNumber])" & _
    "WHERE tblBSlip.[SystemNo] = [Enter System Number] AND tblBSlip.[SOURCENO] = [Which Source?] AND tblBSlip.[SAMPLETYPE] = '5' Or (tblBSlip.[SAMPLETYPE] = '6';"
    Debug.Print SQL
    Stop
    Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
    2. Open the immediate window (Ctrl+G) and select/copy the SQL string printed there.

    3. Create a new query, switch it to SQL view and paste the SQL string copied at step 2.

    4. Switch the query to Datasheet view and see what happens (nothing, OK, error message...).
    Have a nice day!

  9. #9
    Join Date
    Mar 2016
    Posts
    4
    Quote Originally Posted by healdem View Post
    If it works fine without the parameters then check all the column types are what you think they are

    Rather than see the vba tbat creates the query can we see the actual value of sql
    It may be a typo but you are missing a closing bracket on the final where clause
    It definitely lays within the VBA syntax for the Where statements. I ran the whole Click Event without them and everything worked, except the entire query was exported. This is what the query looks like when I use the SQL in Access. (I can't get the columns to line up so I alternated the columns in Bold)

    SystemNo SystemName SourceName SOURCENO SampleDate TOTAL FECAL TOTALMPN FECALMPN Comments
    1234567 WATER SERVICE COMPANY WELL 31-01 031 01/2015 1 0 3 1/26 W31: 3.1 mpn
    1234567 WATER SERVICE COMPANY WELL 31-01 031 01/2015 1 0 3 1/26 W31: 3.1 mpn
    1234567 WATER SERVICE COMPANY WELL 31-01 031 01/2015 1 0 3 1/26 W31: 3.1 mpn
    1234567 WATER SERVICE COMPANY WELL 31-01 031 01/2015 1 0 3 1/26 W31: 3.1 mpn

    Here is the SQL:

    SELECT tblBHistoricalSamples.SystemNo, tblPSystems.SystemName, tblPSource.SourceName, tblBHistoricalSamples.SOURCENO, [Month]+"/"+[Year] AS SampleDate, tblBHistoricalSamples.TOTAL, tblBHistoricalSamples.FECAL, tblBHistoricalSamples.TOTALMPN, tblBHistoricalSamples.FECALMPN, tblBHistoricalSamples.Comments
    FROM (tblPSystems INNER JOIN tblBHistoricalSamples ON tblPSystems.SystemNo = tblBHistoricalSamples.SYSTEMNO) INNER JOIN tblPSource ON (tblPSystems.SystemName = tblPSource.SystemName) AND (tblBHistoricalSamples.SOURCENO = tblPSource.SourceNumber)
    WHERE ((((tblBHistoricalSamples.SystemNo)=[Enter System Number]) AND ((tblBHistoricalSamples.SOURCENO)=[Which Source?])) AND ((tblBHistoricalSamples.SAMPLETYPE)="5" Or (tblBHistoricalSamples.SAMPLETYPE)="6"))
    UNION SELECT tblBSlip.SystemNo, tblPSystems.SystemName, tblPSource.SourceName, tblBSlip.SOURCENO,[MONTH]+"/"+[Year] AS SampleDate, tblBSlip.TOTAL, tblBSlip.FECAL, tblBSlip.TOTALMPN, tblBSlip.FECALMPN, tblBSlip.Comments
    FROM (tblPSystems INNER JOIN tblBSlip ON tblPSystems.SystemNo = tblBSlip.SYSTEMNO) INNER JOIN tblPSource ON (tblPSystems.SystemName = tblPSource.SystemName) AND (tblBSlip.SOURCENO = tblPSource.SourceNumber)
    WHERE ((((tblBSlip.SystemNo)=[Enter System Number]) AND ((tblBSlip.SOURCENO)=[Which Source?])) AND ((tblBSlip.SAMPLETYPE)="5" Or (tblBSlip.SAMPLETYPE)="6"));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •