Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date Picker Default Value 1

Status
Not open for further replies.

PWD

Technical User
Jul 12, 2002
823
GB
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€$
 
[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.
 
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.
 
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€$
 
Dang, that's no good as it's no longer a Date Picker :(

Many thanks,
D€$
 
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€$
 
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€$
 
Well, I must admit I don't understand no, just got it. Wow! Simples!

Many thanks,
D€$
 
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€$
 
[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.
 
... or not. :-(

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

Code:
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.
 
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.
 
(Nevertheless

>works for most of the dates, but

the correction to my solution would be

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

)
 
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.
 
Surely that's MajP's solution?

But I question how it is more 'accurate' than, say, my solution. Could you perhaps share your reasoning?
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top