Query Date Criteria, using multiple column data within one criteria

[PTE]

Hi,

Using Access 2010 I have made a program that everyone in our company uses along side our ERP system. Our ERP system is Syspro.
We use Access mostly for queries, since using up an expensive license in Syspro just for querying just doesn't make sense, plus it is much quicker in Access.
The issue that I am having is displaying our BOM structures correctly.
Within the Structures and Routings program in Syspro you can supply "On Dates" and "Off Dates" for the items that you put into that BOM structure, although by default the date field is blank, a simple "Is Null", allows inclusion of items that have no dates. We use these dates to track changes in the structures that have come from engineering changes, and we don't really want to get rid of them just to fix this issue.

Items that have one "Off date" filter out correctly, and items that have one "On Date" filter out correctly. The issue occurs when the stock code in the structure had an "On Date" in the past, and also has an "Off Date" sometime after that due to another change.

The code that works to correctly filter items that either have one "On Date" and one "Off Date" is as follows:
For the criteria in StructureOffDate I use

Is Null Or >[Forms]![frm_InventoryQuery]![DateBOM]

For StructureOnDate I use

Is Null Or <[Forms]![frm_InventoryQuery]![DateBOM]

 
[Forms]![frm_InventoryQuery]![DateBOM]

is a date picker field in the Inventory Query Program to view structures at any date in the past based on the changes made with On and Off dates, default is today.

To fix the issue of items that have both dates I have tried the following:


StructureOffDate - Stays the same as above because things shouldn't be turned back on after they are off.

StructureOnDate:

Is Null Or <[Forms]![frm_InventoryQuery]![DateBOM] And [StructureOffDate] >[Forms]![frm_InventoryQuery]![DateBOM]

I have also tried many variations of the criteria above but none have worked, tried brackets in places.

When I save it and reopen the query it looks like this, and it isn't a typo

StructureOffDate

Is Null Or >[Forms]![frm_InventoryQuery]![DateBOM]
Is Null Or >[Forms]![frm_InventoryQuery]![DateBOM]
Is Not Null

StructureOnDate

Is Null
<[Forms]![frm_InventoryQuery]![DateBOM]

It always modifies my code and moves criteria from StructureOnDate into StructureOffDate.
But realistically the codes is always going to fight eachother, when an item has two dates, and satisfies either criteria it will show up, even if it is technically turned off with the date, it still satisfies the "On Date"
any Suggestions?
Am I going about this wrong?
Should I have multiple Queries?

thanks for any help.

[ranman256]

You can have multiple queries,...called UNION query.
You put several queries to gether to produce 1 result. Esp if the logic gets messy for 1 query.

ex:
select * from tbl where [state] = "NY"
union
select * from tbl where [age] > 65

NOTE: EACH QRY MUST HAVE THE EXACT SAME FIELDS IN SAME ORDER.
(and they cant be viewed in the qry designer...only as SQL)

[PTE]

ranman,

Thanks for the quick reply.
I use UNION queries often, but I'm not sure how that will help me.
If I gather up all of the items that satisfy the "On Date" and gather up the items that satisfy the "OffDate", and UNION them, then this will just give me exactly what I already have working, this will not exclude the records that have both dates in them.
Unless I'm missing a method of the UNION query that you are thinking of?

regards