468,135 Members | 1,467 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,135 developers. It's quick & easy.

Form shows incorrect results from Union Query

Hi Everyone, I'm back for some more expert help. Here's what I am
doing and what I tried.

My database has entries with Contract Names and Expiry Dates, among
other fields. I have a form designed to show the expiring contracts.
To do this I use a straight forward query in my form's ON LOAD code

strwhere = "[Expiry_Date] BETWEEN #" & Now() & "# AND #" &
DateAdd("m", 6, Now()) & "#"
Set MyQueryDef = MyDatabase.CreateQueryDef("qryMattersQuery", "SELECT
* FROM Matters " & (" WHERE " & strwhere & " ORDER BY
[Expiry_Date];"))

This looks 6 months into the future and puts the expiring contract
info into a query. I use this query as the record source for a for
with conditional formatting to color code by time interval.

When the form's Record Source is set to the query "qryMattersQuery" =,
everything works.

I then created another query to check and alter the expiry Date for
contract in the past (since the database was last accessed). I use
analogous coding as shown above and create a query called
"qryExpiringList". When I set that as the Record Source for my form,
it works fine.

What doesn't work right is:

I want to show all of the results at the same time on the form. So, I
created a Union query with the following line of code:

Set MyQueryDef = MyDatabase.CreateQueryDef("qryUnionQuery",
"SELECT * FROM qryExpiringList UNION SELECT * FROM qryMattersQuery
ORDER BY [Expiry_Date];")

The query results in tabular format are correct, but when I use the
query as the record source for my form, I don't always get the correct
results. I frequently see additional entries listed, seemingly
leftover from previous queries.

I have even added code to delete the previous queries before they are
created:

If ObjectExists("Queries", "qryUnionQuery") = True Then
MyDatabase.QueryDefs.Delete "qryUnionQuery"
MyDatabase.QueryDefs.Refresh
End If

but I still get the leftover record on the form, but NOT in the
tabular display of the UnionQuery results. The tabular display is
always what I expect to see. Where are the old results being stored?

I'm stumped. Thanks in advance for you help.

- Bernie

May 10 '07 #1
1 2345
Hi,
I don't have the answer but here are two things you might want to try
or concider

- you know that there are two types of union queries, right? 'Union'
and 'Union All'

- maybe its not the querydef that needs refreshing, it might be that
the form needs either refreshing or requerying after you set the query
to the new querydef something like; Me.Form.ReQuery or Me.Form.Refresh

bobh.

On May 10, 3:09 pm, bgreens...@gmail.com wrote:
Hi Everyone, I'm back for some more expert help. Here's what I am
doing and what I tried.

My database has entries with Contract Names and Expiry Dates, among
other fields. I have a form designed to show the expiring contracts.
To do this I use a straight forward query in my form's ON LOAD code

strwhere = "[Expiry_Date] BETWEEN #" & Now() & "# AND #" &
DateAdd("m", 6, Now()) & "#"
Set MyQueryDef = MyDatabase.CreateQueryDef("qryMattersQuery", "SELECT
* FROM Matters " & (" WHERE " & strwhere & " ORDER BY
[Expiry_Date];"))

This looks 6 months into the future and puts the expiring contract
info into a query. I use this query as the record source for a for
with conditional formatting to color code by time interval.

When the form's Record Source is set to the query "qryMattersQuery" =,
everything works.

I then created another query to check and alter the expiry Date for
contract in the past (since the database was last accessed). I use
analogous coding as shown above and create a query called
"qryExpiringList". When I set that as the Record Source for my form,
it works fine.

What doesn't work right is:

I want to show all of the results at the same time on the form. So, I
created a Union query with the following line of code:

Set MyQueryDef = MyDatabase.CreateQueryDef("qryUnionQuery",
"SELECT * FROM qryExpiringList UNION SELECT * FROM qryMattersQuery
ORDER BY [Expiry_Date];")

The query results in tabular format are correct, but when I use the
query as the record source for my form, I don't always get the correct
results. I frequently see additional entries listed, seemingly
leftover from previous queries.

I have even added code to delete the previous queries before they are
created:

If ObjectExists("Queries", "qryUnionQuery") = True Then
MyDatabase.QueryDefs.Delete "qryUnionQuery"
MyDatabase.QueryDefs.Refresh
End If

but I still get the leftover record on the form, but NOT in the
tabular display of the UnionQuery results. The tabular display is
always what I expect to see. Where are the old results being stored?

I'm stumped. Thanks in advance for you help.

- Bernie

May 10 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Matt O'Donnell | last post: by
2 posts views Thread by DS | last post: by
19 posts views Thread by Coward 9 | last post: by
7 posts views Thread by php_mysql_beginer911 | last post: by
27 posts views Thread by didacticone | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.