Calculate Data

  • Thread starter Thread starter gatarossi
  • Start date Start date
G

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é.
 
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])
 
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é.
Click to expand...

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")
 
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é.
 
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é.
 
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é.
 
Back
Top