Docmd.RunSQL for Union

[ted.martin]

I have an SQL string based on a Select query and another SQL string based on an Append query. They both work fine.

Does anyone know the correct syntax to join these two SQL strings together as a UNION SQL statement.

Many thanks - keeping my fingers crossed.

 

[reclusivemonkey]

You can only create a UNION query with the exact same fields in each query. The syntax is;

SELECT [I]Field1[/I] FROM [I]Table1[/I]

UNION SELECT [I]Field2[/I] FROM [I]Table2[/I];

 

[Mile-O]

The DoCmd.RunSQL method is used only for action queries such as UPDATE, DELETE, CREATE TABLE, and INSERT; it is not used for SELECT queries, the category your UNION query falls into.

 

[ted.martin]

Thanks - that explains my problem. I was trying to do a select query in SQL.

I have now found another solution by turning the original Select query directly into an Update Query; viewing the QBE in SQL and then copying the SQL syntax, tweaking it with a few square brackets and then running it as a Docmd.RunSQL 19 times in a variable changing loop. Happy Now - got rid of 38 queries!

 

[Mile-O]

Since you seem to have come across a "workaround", how about you tell us the full problem to get a better solution?

 

[ted.martin]

Ok - I have a database with 19 date fields each of which through their field names need to be incorporated into a report only if they are between certain dates - selected via a clever calendar (found on this forum) and temporarily held as unbound fields on the form CARinterim along with the customer name and 19 field name labels (read from an Array as hard coded text in a module) - too much like hard work to caption each individual label, also messy to change.

There was added requirement of a from .. to dates filter.

This was straightforward to do with 19 Select queries designed in QBE with the results then Appended to a new table. Each Query then appends to the table - up to 19 times in all if the date criteria is passed.

The "Customer Audit" report is then produced in the normal way from this Table.

What I wanted to do was avoid having 19 select Queries and 19 Append Queries each only differing by the field name number e.g. SSI1date, SSI2date, SSI3date etc. By using the Docmd.RunSQL command in VB I hoped to be able to progressively Loop the code 19 times with the date field name being in the syntax [SSI "& iVar & date"]etc.

Here is the code that works:

For iVar = 1 to 19

mySQL = "INSERT INTO [Customer Audit report] ( ClientNumber, nbCustomer, ClientType, JobDate, JobID )" & _
"SELECT Contacts.number, Contacts.nbCustomer, Contacts.ClientType, Contacts.[SSI" & iVar & "date], Forms.[F-CARinterim].[text" & iVar & "] AS [SSItask]" & _
"FROM [Contacts]" & _
"WHERE (((Contacts.nbCustomer) = [Forms].[F-CARinterim].[txtnbCustomer]) And ((Contacts.ClientType) = 'S') And ((Contacts.[SSI" & iVar & "date]) >= [Forms].[F-CARinterim].[txtStartDate] And (Contacts.[SSI" & iVar & "date]) <= [Forms].[F-CARinterim].[txtEndDate]))" & _
"ORDER BY Contacts.number;"

DoCmd.RunSQL mySQL

Next iVar

As mentioned in my earlier post, I thought I needed to do a Union query but found a solution by not having the Append Query getting its source data from a Select query as two separate queries. By converting the Select directly into an Append, I could get the SQL syntax to then copy into mySQL VB statement.

Any comments would be welcome.

 

[Mile-O]

Okay, why on earth do you have 19 date fields? This, to me, suggests that yu have gotten ahead of yourself and not normalised your table structure. The 19 queries reinforces that.

Is it too late to salvage for the sake of normalisation?

 

[ted.martin]

There are 19 date fields on the form to match with 19 product fields. Each of which relates to a menu of installation tasks for a heating company. Of course we could pre-select from a list box and build up the overall Job but the client wanted to see all the tasks and just use a check box, which I haven't mentioned, to allocate the task. The date is to show when the task has been completed. This SQL relates to the requirement to produce a report showing which tasks have been completed (for each "house" record) between a period in time.