Apply a filter when opening a query

[moishier]

A bit new to VBA.

I have a union select query that users a hardcoded date in both sections of the union query (the date filter is on the same table for both parts of the union). I would like to implement that when the query is opened a popup first displays asking for the start and end dates, and then filters results based on that.

How can that be done?

Thanks!

 

[plog]

Remove the criteria from the UNION. Then build another query using the UNION as the data source:

SELECT * FROM UnionQueryName WHERE [YourFieldName]=[PopUp Message Here]

 

[moishier]

The issue is that the query is a groupby query and that field is not returned.

What are my options?

 

[jdraw]

Suggest you post the SQL of the query you are using.
Also, you should investigate Parameter Queries.

Here is an example:
BDate and EDate are parameters of type Date/Time.
BDate is Beginning Date and EDate is Ending Date

PARAMETERS [Bdate] DateTime, [Edate] DateTime;
SELECT Orders.OrderID, Orders.OrderDate, Orders.ShipVia, Orders.ShipName
FROM Orders
WHERE (((Orders.OrderDate) Between [Bdate] And [EDate]));

 

[plog]

Don't make the UNION a GROUP BY, do that in the subsequent query I discussed. Use the UNION just for tying the two datasources together.

Also, now that I think of it, a UNION is often a hack used to fix improperly structured tables. Why are you using a UNION? Can you post the SQL of it?

 

[moishier]

jdraw, your solution was perfect and just what I needed.

Thanks plog for your input too.

 

[jdraw]

Happy to help.
Good luck with your project