Search for records in all fields.

[vbaInet]

Ok. I made a mistake typing all that.
Here is the attached access file.

I have a table (Table 1) containing fields Allotment Number and Date1
I query the table and the query has 4 fields Allotment Number and Date1, Date2 and Date3. (Query 1)
Where Date2=Date1+180
and
Date3=Date1+365
I query this query for find dates between any [start date] and [End Date]
(QUery 2)

I do this by writing Between [Enter Start Date] And [Enter End Date]
as the criteria in fields Date1, Date2 and Date3, one below the other so that I do the OR operation.

Now when I run the query, and say for example I type Start Date as 20/09/2009 and end date as 20/10/2010. I get the following result

Allotment Number Date1 Date2 Date3
A001 15/10/2009 13/04/2010 15/10/2010
A020 20/11/2009 19/05/2010 20/11/2010
A121 25/09/2009 24/03/2010 25/09/2010
A221 20/03/2010 16/09/2010 20/03/2011
A552 28/03/2010 24/10/2010 28/03/2011


Now this result contains dates that were not part of the interval entered.
for eg 28/03/2011.

Didn't you add 365 days to Date1? So how come you say it's not giving the right results?

What you want to achieve is what is eluding me.

 

[karthikcoep]

My apologies for making this too complex.
Let me ask my question afresh,

I make certificates (reports) that have a unique allotment number and these certificates are signed by an examiner.
In my table I record the allotment number and the date of signing the certificates.
I need to get these certificates signed again every six months,

I need a method, to find which certificate are due within a particular interval of time for signature.

Hence the final result I need to see is

Field 1 Field 2
Allotment Number Due Date

The allotment number will tell me which certificate I need to get signature and the due date will tell me when.

 

[Rich]

Since one allotment can have many records, you should have a separate table for those records, do some research on normalis(z)ation.
There are some posts on here which go into greater detail and there's also a plethora of info about it on the Web