In Stock Only (1 Viewer)

[AN60]

I have a report used for chemical use which has the following fields in each record;

ChemicalName
ADateField
BatchNumber
ExpiryDate
Volume
Purchased (checkbox)
Used (checkbox)

Each time a drum of chemical is purchased the name, two dates, batch number, volume & a tick in the "Purchased" check box is recorded. When the chemical is used the same data is entered except on this occasion the "Used" checkbox is ticked. Thus each drum of chemical has two records 1. a purchase record and 2. a used record. This has been working ok however I now want to create a report showing only chemicals in stock i.e. Those with a purchased record but not yet having a Used record.

Using the folowing in the detail section will remove all the Used chemicals but not their Purchased counterparts. I hope I haven't confused anyone so far.

If Me!Used =True then
me!Used.visibility=false
else
me!used.visibility=true
end if

Any suggestions?

 

[RV]

Create a query that comes with a condition to exclude all rows having the check box "Used" ticked.
Base your report on that query.

PS I'd store Purchase info and Usage info in separate tables if I were you

RV

 

[AN60]

RV
Thanks for your reply.
If I exclude all the "used" chemicals that is still only half of what I need to exclude as each drum of chemical has one purchase record and one used record. I would be left with chemicals which have been purchased and some of these will have been used, my report would then show all Purchases. Maybe I'll have to edit my tables to end up where I want to be but as yet I'm unsure what I will need to do.

Once a chemical is used I need to exclude the record that has it's checkbox as True Plus the original Purchased entry which has it's Purchased checkbox True.

In my original post I let the Title do the "speaking" instead of clearly stating my objective in the message section. In my report I only want to view chemicals which are currently left in stock and not those which have been used.
Thanks again.