Calculate Data

[gatarossi]

Dear all,

My system of date in ms access is 200701 (year+month), then in my
table I have this data:

Date
200709
200710
200711
200712
200801
200802

I'm trying to do a consult, and I need that the consult put the next
period from the actual... then I put it: Expr1: ([period])+1, but in
december of 2007 access (of course) don't understand that it's
necessary to put 200801, and put 200713...

Is there a form to solve this problem?

Thanks a lot!!!

André.

 

[Guest]

The solution to your problem is to change from the number sustem you are
using to a DateTime field. Then if you only want to display the year and
month you just need to format the date to fit your desires.
Also to add a month you would use this ---
DateAdd("m", 1, [YourDateTimeField])

 

[Michael Gramelspacher]

Dear all,

My system of date in ms access is 200701 (year+month), then in my
table I have this data:

Date
200709
200710
200711
200712
200801
200802

I'm trying to do a consult, and I need that the consult put the next
period from the actual... then I put it: Expr1: ([period])+1, but in
december of 2007 access (of course) don't understand that it's
necessary to put 200801, and put 200713...

Is there a form to solve this problem?

Thanks a lot!!!

André.

You have a column for a date string "200712".

for example: (using the Immediate Window)

s="200712"
?Format(DateAdd("m",1,Cdate(left(s,4) & "-" & Right(s,2))),"yyyymm")
200801

What this does is takes your string date, inserts a hyphen between the
date and the year, converts the string to a date using the Cdate
function, adds one month using the DateAdd function, and finally
formats the date as you requested.

so, this might work:

Expr1:Format(DateAdd("m",1,Cdate(left([period],4) & "-" &
Right([period],2))),"yyyymm")

 

[Larry Daugherty]

Your migh try putting the following into the Default value of
txtYearMon
= format(Now(),'yyyymm')

HTH
--
-Larry-
--

Dear all,

My system of date in ms access is 200701 (year+month), then in my
table I have this data:

Date
200709
200710
200711
200712
200801
200802

I'm trying to do a consult, and I need that the consult put the next
period from the actual... then I put it: Expr1: ([period])+1, but in
december of 2007 access (of course) don't understand that it's
necessary to put 200801, and put 200713...

Is there a form to solve this problem?

Thanks a lot!!!

André.

 

[gatarossi]

Dear Michael,

It works!!!!

Dear Karl,

How in my work I'm doing a Forecast I'm afraid I couldn't use DateTime
field, because in my Forecast I only to use the year and month!

Thanks in advance for all!!!

André.

 

[Pieter Wijnen]

You can still use datetime, just leave the day part as the first of the
month
ie
2007-01-01
...
2007-12-01

and use
Format([DateField],'yyyymm') in your queries

Pieter

Dear Michael,

It works!!!!

Dear Karl,

How in my work I'm doing a Forecast I'm afraid I couldn't use DateTime
field, because in my Forecast I only to use the year and month!

Thanks in advance for all!!!

André.