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
Code:
Is Null Or >[Forms]![frm_InventoryQuery]![DateBOM]
For StructureOnDate I use
Code:
Is Null Or <[Forms]![frm_InventoryQuery]![DateBOM]
Code:
[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:
Code:
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
Code:
Is Null Or >[Forms]![frm_InventoryQuery]![DateBOM]
Is Null Or >[Forms]![frm_InventoryQuery]![DateBOM]
Is Not Null
StructureOnDate
Code:
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.