Date field - set Default to Previous Month

[ccma]

Hi~
I have created a form that will be used on the first week every month, the data entered is based on the previous month. I have currently set the date field to default to
=Format(Now(),"mm/""01""/yy") what I would like the field to do is to give me the previous month as "04/01/04". Hope this makes sense. Thanks in advance for your help. CC

 

[zemp]

You can try something like this,

   If Month(Date) = 1 Then  'January
      Debug.Print Format("12/01/" & Year(Date) - 1, "mm/dd/yyyy")
   Else 'any other month
      Debug.Print Format(Month(Date) - 1 & "/01/" & Year(Date), "mm/dd/yyyy")
   End If

The condition is for when you are going back from Jan to Dec of the previous year.


zemp

 

[BobJacksonNCI]

Or:

=Format(DateSerial(Year(Date()),Month(Date())-1,1),"mm/dd/yy")

HTH,
Bob

 

[ccma]

Thank you both for the reply... I went the easy route and used BobJacksonNCI's code- it worked like a charm! I'm fairly new at code and was not sure at what event I should assign zemp's code to... but I tried. Thank you both. CC

 

[ESquared]

I wish SQL Server had the equivalent of the DateSerial function (Date() in Excel).

 

[zemp]

ESquared, have you tried the GETDATE() function in SQL Server?

zemp

 

[ESquared]

Hi Zemp,

The Date() function in Excel does not give the current date.

Instead, it receives values for a month, day, and year, and returns a valid date serial number.

[tt]=Date(2004,7,4)[/tt]

Evaluates to 38172, which is the number of days since December 31, 1899, which is the date July 4, 2004.

 

[MDXer]

in MS SQL if you cast the date to a float it will give you the days since 1/1/1900 as the value to the left of the decimal. This is doing the same as ESquared's requested =DATE() function

Select 
Cast(0 as datetime) as defaultdate, 
DateDiff(dd,'1/1/1900',GetDate()) as CurrentDaysFromDefault,
Cast(Cast(GetDate() as float) as int) as DATECast

results:
defaultdate            CurrentDaysFromDefault  DATECast           
---------------------- ---------------------- ----------- 
1900-01-01 00:00:00.000 38186                  38186

(1 row(s) affected)

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ESquared]

MDXer,

That is nothing like the Date() function. Date() accepts THREE parameters, not one. The equivalent in VB is DateSerial(y,m,d).

In any case, I have experimented with casting to a float in the past and found it to be inaccurate in some situations... something to do with 23:59:59.997 and precision, giving the wrong date. Unfortunately, I haven't been able to reproduce it in a few short minutes, here. I'll let you know if I find an example.