Textbox display month and year with buttons

[vbaInet]

I am in the UK, pretty much the same time

 

[Wilse]

I have messed about with the control source on P1list.

MainMenu is the form with command button 8 opening for P1list

 

[vbaInet]

First of all take out the Format bit in your button code.

 

[Wilse]

in what bit

 

[vbaInet]

The button you initially asked about. The one that changes the date.

 

[Wilse]

ok done what next

 

[vbaInet]

Let's see what you have now?

Also, what are you filtering by when you open the form from Docmd? The exact date?

 

[Wilse]

no i just want the month and year, the database is like a monthly reporting tool

 

[vbaInet]

Is there anytime in your database you will save a full date? Or is it just month and year ever?

 

[Wilse]

on the P1 table it is full date, i need this info. it is in p1 table

 

[Wilse]

mate i have to go in a bit. the wifes birthday and i have to get up with the kids in the morning. Can you help?

 

[vbaInet]

Every question I ask is relevant to reach a solution. I've not tested it but try this:

DoCmd.OpenForm "P1list", acNormal, , "[DateOfWork]>=#" & DateSerial(Year(Me.monthtext), Month(Me.monthtext), 1) & "# AND " & _
                                    "[DateOfWork]<=#" & DateSerial(Year(Me.monthtext), Month(Me.monthtext) + 1, 0) & "#"

Format your text box on your main form as you did with the others. Don't format it using the button. By format I mean use the Format property of the text box and set it to

mmmm yy

 

[Wilse]

this is not working how it should be, it still doesnt filter alright

 

[Wilse]

got it to work I used

DoCmd.OpenForm "P1list", acNormal, , "[DateOfWork]>=#" & DateSerial(Year(Me.monthtext), Month(Me.monthtext), -1) & "# AND " & _
                                    "[DateOfWork]<=#" & DateSerial(Year(Me.monthtext), Month(Me.monthtext) + 1, 0) & "#"

 

[gemma-the-husky]

well date will only EVER return todays date.

to do what you want, you need a way of storing the active date, which needs you to manage an additional varaible, or a form control.
Here is a way with a form control

=================

Create a control called MYDATE. make it visible=false, and format as shortdate (or make it visible = true, so you can see what is happening)

in your form openevent have

mydate = date() (this sets the iniital value)


now, set the visible textbox where you wnat to display the date with the control source

=[mydate] 'so that it gets the value from the hidden textbox

and set the fornat to mmmm yy

============
now to add one month, you need a button that adds 1 month to the invisible box

so in the click event for this put

mydate = DateAdd("m", 1, myDate)

now this changes the value of the hidden field - which will be immediately reflected in the visible field


=========
just tested, and this works. If you store the date in a variable (rather than use a textbox), you need a slightly different technique to get the date into the visible field - so its a matter of taste which you prefer.


===========
I didnt realise this thread was so long - i added this at the end of page 1, which I thought was the end of the thread. Without reading everything, I imagine the original issue was fixed, and you went on to something else ....