Choosing a date between a criteria based on a calculated field 2

[Powerhouse123]

Hi there,

I have the following table and query and what I want to be able to do is find out who has expired between a a set of dates I select.

Table structure...

FIELD DATA DATA DATA DATA

ID 1 2 3 4
Name Bob Dave John Sally
Issue Date 01/01/2004 23/04/1998 02/01/2004 05/11/2007
Expiry Date This is calculated using the following code:

Expiry Date: DateSerial(Year([Issue Date])+3,Month([Issue Date]),Day([Issue Date]))

This code should just add 3 years to the issue date which it does.

In the criteria section of the Expiry date column I have... "Between [Please enter start date 'dd/mm/yyyy'] And [Please enter an end date 'dd/mm/yyyy']"

When I select the following between dates: 01/01/2007 and 31/01/2007 it brings back everything rather than what it should!!...JUst Bob and John

I any ideas? It should be so simple!

 

[Powerhouse123]

Hi,

I have a second part to this query which might be easier...is there a way of selecting all those records which expire in say January? Not being so specific with exact days and dates.

This would be useful because then I could select the query which is for each month and see who has expired...Although I may need ot include the year for this to work...


Many thanks in advance and kind regards...

 

[PaulBricker]

I'm looking at the data and this is what my table would look like

Table
ID Name IssueDate
1 Bob 01/01/2004
2 Dave 23/04/1998
3 John 02/01/2004
4 Sally 05/11/2007

Then I would create a query using this table and I would have a calculated field to create the expiry date. Then filter that info based on your criteria - "Between xdate and ydate"

Paul

 

[GingerR]

Is your IssueDate field a Date/Time type? Or TEXT?
Also check out the DateAdd() function.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Powerhouse123]

Thanks to you both. You are right with the table structure. I was doing it from the design view.

I can see what you are saying but the issue I have is not in the Issue Date field but the Expiry Date field. This field is calculating the Expiry Date based on the issue date field. Code shown above. I then want to select only those records which fall between two dates but it seems to bring back everything.

How would I use the DateAdd() in this example?

As I also mentioned, is there a way of selecting just a month period...so all those records which fell in January for expiry.

Many thanks for your help and support...

 

[PaulBricker]

The Dateadd() function will do the same thing as the DateSerial() function it's just a little cleaner function.

The select statement for your query should look something like this.

[blue]SELECT Table1.ID, Table1.Name, Table1.IssueDate, DateAdd("yyyy",1,[IssueDate]) AS ExpiryDate
FROM Table1
WHERE (((DateAdd("yyyy",1,[IssueDate])) Between [enter start date] And [enter end date]));[/blue]

When you go to open the query, you will be prompted for a start date and and end date. The records you want should come up accordingly.

Paul

 

[lespaul]

powerhouse, no one has directly stated this, but it breaks the rules of database design to store a calculated value. You can create the expiry date anytime you want in a query and there's no extra value to storing data that you calculate...

Leslie

 

[Powerhouse123]

Lespaul,

Thank you for this information, I'not sure what you mean by this though...

Are you saying that it is a good idea to calculate the expiry date in another separate query and then create another query based on the previous query created which is now storing the calculated expiry date to allow me to select a chosen criteria which will return only those records which meet it?

or...

Have I missed the point? I shouldn't be doing what I described above?

Sorry to sound really thick!

Regards

 

[lespaul]

No, i'm saying that you shouldn't be trying to store a calculated field in the table (not real clear if that's what you are trying to do, but I thought i would be direct about that scenario).

So that I'm clear...you want the user to enter a start date and an end date and return all records where that person's expiry date falls between the user entered parameters...is that correct?

 

[Powerhouse123]

Yes thats right...ideally I would like to pick a start date and end date but also...

... run the same thing and have it automatically select all those that meet the criteria of expiring in January without typing the start and end date would be very useful too. Click on a button that says January and it brings back all those which expire in the month of January but I guess you would have to specify a year for that to work...but that would be good.

I don't think I was currently doing what you were clarifying. I wasn't storing any info but thought that might be the reason why it wasn't working.

Many thanks...