Union Query with OLE and Memo fields?

[WebDude]

I have over 20 fields,
in the [Volunteers] Table,
PLUS 10 more calculated fields
in the Queries i use to retrieve
records from the [Volunteers] Table.

So i put a "Select * From [Volunteers]"
instead of writing out the huge SELECT Statement
of all the fields i need.

The queries work fine alone,
but when i UNION them together
i get the following error


**********************
RunTime Error '3364'
Cannot use Memo, OLE, or Hyperlink Object field 'Photo' in
the select clause of a union query.
**********************


True, i have one OLE object field ('photo') and one MEMO object field
('notes') in the [Volunteers] Table...Is there a trick around this?

Cheers,
Jeff

 

[Sylvain Lafontaine]

The first solution is to remove the * and use only the fields that you need.

The second is to add the word ALL after the word UNION, if you don't have
duplicates in both selects or if you don't mind them; otherwise you will
have to transform the UNION into a subquery that will return only the
relevant ID and get the other fields after that in the outer query.

 

[WebDude]

... add the word ALL after the word UNION, if you don't have
duplicates in both selects or if you don't mind them;

Sylvain,

Each of the queries return a unique set of records so, union'ing them wont
return any duplicates. *phew* And following your suggestion, here is
what i got;


If Me.Form.CheckBoxAssigned = True Then
strSQL = strSQL & "UNION ALL SELECT * FROM Volunteers_Assigned "

End If

If Me.Form.CheckBoxNotAssigned = True Then
strSQL = strSQL & "UNION ALL SELECT * FROM Volunteers_NotAssigned "
End If


If Me.Form.CheckBoxRetired = True Then
strSQL = strSQL & "UNION ALL SELECT * FROM Volunteers_Retired "
End If

Me.Form.RecordSource = Mid(strSQL, 10)


...and it works like a charm! Thanks a million!!


Um, Though, i have never understood why i use the
"SELECT * FROM" statement....Cause, for example, "Volunteers_Assigned"
*is* the name of a saved query whose very first line in SQL is infact
"SELECT * FROM " So, why do we need both "SELECT * FROM"...

me.recordsource = "SELECT * FROM Volunteers_Assigned UNION ALL SELECT *
FROM Volunteers_Retired"

Why not remove one of the "SELECT * FROM";

me.recordsource = "Volunteers_Assigned UNION Volunteers_NotAssigned UNION
Volunteers_retired"

Anyways,
Cheers!
Jeff

thanks again!

 

[Sylvain Lafontaine]

Because you may not be interested in getting all the available fields for a
particular query using this subquery, even if you are for another query.

The fact that you had trouble with an OLE field should be a clear exemple of
that.

 

[WebDude]

Thanks Sylvain