Search for records in all fields.

[karthikcoep]

I have 6 fields.
Allotment Number
Date 1
Date 2
Date 3
Date 4
Date 5

I want to write a query which when run will give all the dates and their corresponding Allotment Number which are 1 week after today but not later than 2 weeks from today i.e.

Suppose today is 11.03.2010.

I would like the query to return all the alloment numbers between 18/03/2010 to 25/03/2010.

 

[vbaInet]

Try this first:

Between #18/03/2010# AND #25/03/2010#

Once you have that working, lookup at the DateAdd() and DateDiff() functions

 

[karthikcoep]

How exactly will I go about executing it,
Where will I write the function.
i wrote 18/3 and 25/3 as examples/

 

[ghudson]

Try this first:

Between #18/03/2010# AND #25/03/2010#

Once you have that working, lookup at the DateAdd() and DateDiff() functions

I will add that sometimes it is easier to play with date results in a message box to see your date result. Yours seems simple so something like this might do what you want.

MsgBox "Beginning Date = " & Date()+7
MsgBox "Ending Date = " & Date()+14

Between Date()+7 And Date()+14

 

[vbaInet]

I think you would need to learn the basics first by reading some books. Query is what you're looking for. Even doing a quick google search could lead to some tutorials. Here's what I could find:

http://office.tizag.com/accessTutorial/accessqueries.php
http://office.microsoft.com/en-us/access/HA102473131033.aspx

From the sound of things you also need to understand the concept of relational databases. Reading up on Access as a whole will help.

 

[karthikcoep]

Dear VBAINET
Thanks for your help.
I am new to Access and so far managed to create simple databases.

But I am stuck here.
I have a table which has many fields, of which "allotment number" and "date of sign" are 2 fields.

I have created a query which calls these two field and I have created expressions so that I get fields date 1, date 2 ...
Here
date 1 = Date of sign + 6 months.
date 2 = Date of sign + 12 months.
date 3 = Date of sign + 18 months.
date 4 = Date of sign + 24 months.
date 5 = Date of sign + 30months.

These are due dates for taking signature from some authority.

Now my problem is I want to check every week whether there are any due dates in the forthcoming week.

Hence I came up with the idea of creating a query, but i am puzzled at creating a query that will look-up my initial query and come with alloment numbers that are due,

Any help in solving this is welcome.

 

[vbaInet]

Have a look at ghudson's solution paying particular attention to the way the Date() is increased. You would do something like this in the Criteria row, under date field:

[Date of sign] + 7

7 being seven days from the field's date.

 

[karthikcoep]

Thanks to all,
I found the solution,
It seems more than access I need to be fimiliar with SQL symtax and jargon,

 

[vbaInet]

Excellent! Glad your problem is solved.

 

[karthikcoep]

I have used the "Between [Enter Start Date] And [Enter End Date]" for all the fields, i.e. Date 1, Date 2...and used OR in between so that I get all the allotment numbers between those dates.

Now, say a record in Date 2 fields falls between the entered dates, I get the result of the query in Date 2 fields, but i also get all other dates in their respective fields.

An example wil explain better.

Table
Allotment NumberDate 1Date 2Date 3A00115/10/200913/04/201015/10/2010A02020/11/200919/05/201020/11/2010A12125/09/200924/03/201025/09/2010A22120/03/201016/09/201020/03/2011A55228/03/201024/09/201028/03/2011

Query
Find Snapshot attached.

Now when I make the fields Date1, Date 2, Date 3 as visible.
I get the following query result.

Query ResultAllotment NumberDate1Date2Date3A02020/11/200919/05/201020/11/2010A22120/03/201016/09/201020/03/2011A55228/03/201024/11/201028/03/2011

Here Start Date was 20/09/2009 and end date is 20/10/2010/

mY QUESTION IS "Is it possible to add an extra fields which will give allotment number as well as the allotment date fetched, because in the current example is showing dates 28/03/2011 which is beyond the range I entered.

I would like the query to return

Allotment Number Date
A020 20/11/2009
A020 19/05/2010
A221 20/03/2010
A221 16/09/2010
A552 28/03/2010.

How to added this new field Date in the query?
What code needs to be written.

 

[vbaInet]

I don't understand what your trying to do. Please reformat the data you wrote and explain clearly.

 

[karthikcoep]

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.
mY QUESTION IS "Is it possible to add an extra fields which will give allotment number as well as the allotment date fetched

I would like the query to return

Allotment Number Date
A020 20/11/2009
A020 19/05/2010
A221 20/03/2010
A221 16/09/2010
A552 28/03/2010.

How to added this new field Date in the query?
What code needs to be written.

 

[vbaInet]

You build another query that is based on query2 and use the between again.

 

[karthikcoep]

How?
U mean to say like

between results of date1 and date2 and date3?

 

[Rich]

Why do you have so many Date fields?

 

[karthikcoep]

Because I have to take signature from an authority after certain interval of time.

The Date1 field, is the initial date,

Date2 is six months after date1
and
Date3 is 1 year after date1.

 

[Rich]

Because I have to take signature from an authority after certain interval of time.

The Date1 field, is the initial date,

Date2 is six months after date1
and
Date3 is 1 year after date1.

You don't actually need six separate fields to do this,from just one date field, which I would assume is the initial date of contract, a query and an archive boolean field would control the intervals

 

[karthikcoep]

Can u elaborate sir?

I will be glad to learn a new way of doing this.

 

[karthikcoep]

Here is what it finally burns down to,

I have queried few fields,
the result of these fields I need them in a separate field.

 

[karthikcoep]

Have I explained the problem correctly.