Help with dates

[CanaryWoolf]

Hi



I'm looking for some help with a query which produces 3 sets of expiry dates (1 month apart) which are all printed on a voucher.

Data is entered into a forum, for example on Monday 17th September, and the query does this:

Adds 21 days to the date entered and returns the last day of the month (in the above case it would return 31 October 2012) "Month1"
Adds 1 month + 21 days (30 November 2012) "Month2"
Adds 2 months + 21 days (31 December 2012) "Month3"

The query below works just fine with the exception of days where the report is generated around + 21 days mark. Let's say we generated the report on 10 September 2012, month 1 would be October, Month 2 would also be October because of the 31 days in September, 30 in October.

Ideally I would like the query to work on Month 1 as it is (date inputed + 21 days, showing end of that month) and then Month 2 to be month after the end of the resulting month end, Month 3 to be the month after that.

- hopefully the script will make this clearer. Sorry if any of this is a little 'clumsy', I have no formal training in access, I just try and work it out...

Thanks for looking

SELECT Data.*


UCase(Format(DateSerial(Year([Record Added Date]),Month([Record Added Date]),Day([Record Added Date])+21),"mmmyy")) AS Month1
UCase(Format(DateSerial(Year([Record Added Date]),Month([Record Added Date])+1,Day([Record Added Date])+21),"mmmyy")) AS Month2
UCase(Format(DateSerial(Year([Record Added Date]),Month([Record Added Date])+2,Day([Record Added Date])+21),"mmmyy")) AS Month3


DateSerial(Year([Record Added Date]),Month([Record Added Date]),Day([Record Added Date])+21) AS Month1Hidden
Format(DateSerial(Year([Month1Hidden]),Month([Month1Hidden])+1,0),"dd mmmm yyyy") AS FullMonth1


DateSerial(Year([Record Added Date]),Month([Record Added Date])+1,Day([Record Added Date])+21) AS Month2Hidden
Format(DateSerial(Year([Month2Hidden]),Month([Month2Hidden])+1,0),"dd mmmm yyyy") AS FullMonth2


DateSerial(Year([Record Added Date]),Month([Record Added Date])+2,Day([Record Added Date])+21) AS Month3Hidden
Format(DateSerial(Year([Month3Hidden]),Month([Month3Hidden])+1,0),"dd mmmm yyyy") AS FullMonth3


FROM Data
WHERE (((Data.[Request Type])="New Trader") AND ((Data.[Record Added Date])>Date()-[The last how many days?]+1));

[June7]

If you always want the end of the month as date, why are you adding 21 days? How does that return last day of the month?

If the given date is 12/15/2012:

DateSerial(Year([MyDate],Month([MyDate])+2,0)

Will return: 1/31/2013

[CanaryWoolf]

If you always want the end of the month as date, why are you adding 21 days? How does that return last day of the month?

If the given date is 12/15/2012:

DateSerial(Year([MyDate],Month([MyDate])+2,0)

Will return: 1/31/2013

Sorry for not making that clear - the +21 days is to ensure that everyone receives at least 21 days in their first month's voucher. i.e. if you placed the order today your first voucher would expire on 11/30/2012, 2nd voucher expires on 12/31/2012, third voucher expires on 1/13/2013 (Vouchers are printed three on on a sheet)

But an entry placed on 09/10/2012 (10th September) would return the following: 10/31/2012, 10/31/2012, and 12/31/2012 (i.e. the 2nd month is kicked out of place because the +21 day in the 2nd month = 31st October

Hope you can understand that!

[June7]

So the 21 day guarantee is only for the first voucher? First voucher might have 'bonus' days? If the order is 9/9 it would expire 9/30 (21 days)? Wouldn't it be easier and fairer to give vouchers in 30 day increments from date of purchase?

Maybe calculate vouchers 2 and 3 from the calculated end date of voucher 1?

These calculations might be easier performed in textboxes on report instead of in query.

Regardless, have voucher 2 and 3 refer to result of voucher 1, not the fields in table. Try:

UCase(Format(DateSerial(Year([Month1]),Month([Month1])+1,0),"mmmyy")) AS Month2
UCase(Format(DateSerial(Year([Month1]),Month([Month1])+2,0),"mmmyy")) AS Month3

[CanaryWoolf]

Try:

UCase(Format(DateSerial(Year([Month1]),Month([Month1])+1,0),"mmmyy")) AS Month2
UCase(Format(DateSerial(Year([Month1]),Month([Month1])+2,0),"mmmyy")) AS Month3

With your help I've resolved the issue with the following simplified (from my original) format:

SELECT Data.*, 

DateSerial(Year([Record Added Date]),Month([Record Added Date]),Day([Record Added Date])+21) AS Month1Hidden, 
Format(DateSerial(Year([Month1Hidden]),Month([Month1Hidden])+1,0),"dd mmmm yyyy") AS FullMonth1, 
Format(DateSerial(Year([Month1Hidden]),Month([Month1Hidden])+2,0),"dd mmmm yyyy") AS FullMonth2, 
Format(DateSerial(Year([Month1Hidden]),Month([Month1Hidden])+3,0),"dd mmmm yyyy") AS FullMonth3, 
UCase(Format(DateSerial(Year([FullMonth1]),Month([FullMonth1])+1,0),"mmmyy")) AS Month1, 
UCase(Format(DateSerial(Year([FullMonth1]),Month([FullMonth1])+2,0),"mmmyy")) AS Month2, 
UCase(Format(DateSerial(Year([FullMonth1]),Month([FullMonth1])+3,0),"mmmyy")) AS Month3


FROM Data
WHERE (((Data.[Request Type])="New Trader") AND ((Data.[Record Added Date])>Date()-[The last how many days?]+1));

Thanks for your help?