Date Picker Default Value 1

[PWD]

Good afternoon. I am trying to replicate a form field that was in a database in my previous job where there was a Date Picker but the default value was the start of the previous month.

Reading a previous post on here I have formatted the text box as Short Date.

I have attempted to start by adding something to the 'Default Value' - =Day(Date()) but it still just displayes today's full Short Date.

Any help will be gratefully received.

Many thanks,
D€$

 

[Andrzejek]

[tt]Day(Date)[/tt] (today) will give you 22, which is not a Date.
"the start of the previous month" how about:
[tt]
MsgBox Month(Date) - 1 & "/01/" & Year(Date)
[/tt]
You get: [tt]3/01/2015[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

Ooops, you will have to play wit the [tt]Year(Date)[/tt] part of it, because in January you get the December but of the same Year :-(

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PWD]

Hi Andy, that was just to see how to start getting, well anything, to display. I've just been doing a bit more punting around and I've now created a Bound object from the "Admission Date" field in the table and am amending the Text Box's 'Control Source'.

So I've now got

=Day(Date()) & "/" & Month(Date())-1 & "/" & Year(Date())

which displays "22/3/2015". Again, just to test but I suppose I'll just need

"01/" & Month(Date())-1 & "/" & Year(Date())

Now, at some stage I will need to use the result of this box to filter records that have the "Admission date" between this date and a corresponding "EndOfPeriod" textbox so I guess I'll have to format it as well. How am I doing, so far?

Many thanks,
D€$

 

[PWD]

Dang, that's no good as it's no longer a Date Picker

Many thanks,
D€$

 

[PWD]

Stupid Boy!"

Unbound Text Box; Format Short Date; Default Value - ="01/" & Month(Date())-1 & "/" & Year(Date()). I was expecting the default value to kick in every time I switched from Design to Form View instead of every time the form was opened; Doh!!

Yes, I'll have to do something to get around the "December" issue.

Many thanks,
D€$

 

[strongm]

= DateAdd("m", -1, Now) - Day(Now) + 1

 

[PWD]

This is what I ended up with:

=Format("01/" & Month(Date())-1 & "/" & IIf(Month(Date())-1=12,Year(Date())-1,Year(Date())),"Short Date")

Many thanks,
D€$

 

[strongm]

Hmm

 

[PWD]

Well, I must admit I don't understand no, just got it. Wow! Simples!

Many thanks,
D€$

 

[PWD]

Just a quicky, how do I make it Date only and get rid of the time?

01/03/2015 16:16:25

Many thanks,
D€$

 

[Andrzejek]

[tt]
= DateAdd("m", -1, [blue]Date[/blue]) - Day([blue]Date[/blue]) + 1
[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

... or not. :-(

I was testing this code and it works for most of the dates, but

Dim dtMyDate As Date
dtMyDate = CDate("[blue]3/30/2015[/blue]")
Debug.Print DateAdd("m", -1, dtMyDate) - Day(dtMyDate) + 1

Gives me: 1/30/2015 not 2/1/2015 :-(

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

you may also use the dateserial which converts a numeric year, month, and day into a date.
dateserial(year,month,day)

=DateSerial(Year(now), Month(now) - 1, 1)

The date serial function has some nice built in functionality making it real flexible. The month and day properties wrap around so if you pass a month of 13 it will return January of the next year. A zero returns the last day of the previous month or the last month of the previous year. So a date serial on (2015,0,1) would return 12/1/2014. Same thing for the day dateSerial(2015,4,0) would give you the last day of march. Even if you put a month in of 25 it will add two years. These features make it super usefulf for finding all types of dates and holidays.

 

[strongm]

I'd go with MajP's solution

 

[strongm]

(Nevertheless

>works for most of the dates, but

the correction to my solution would be

[tt]= DateAdd("m", -1, Now - Day(Now) + 1)[/tt]

)

 

[fdcusa]

Late to the thread, but the easiest/most accurate way is: DateSerial(Year([date]),Month([date])-1,1). Instead of trying to put it as a control default value, I would put it in the OnCurrent Event - If the control is null then set the control to the value.

 

[strongm]

Surely that's MajP's solution?

But I question how it is more 'accurate' than, say, my solution. Could you perhaps share your reasoning?

 

[fdcusa]

DateSerial() automatically adjusts when the final date based on the three values which may or may not fall into the range of the parts - like Month argument could be 0 or 34, Days 0 or 388.

 

[strongm]

Sure, but I'm not sure how that counts as more accurate, though. Nor how relevant that is to OP's scenario. I seek enlightenment.