UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access VBA Command Button Month Filter, Access 2016    
 
   
Oblio
post Feb 23 2018, 09:06 AM
Post#1



Posts: 205
Joined: 5-February 15



Hi,

I have a command button named "cmdmonth" which is supposed to filter a report based on the current month. It is, however, returning last months records plus 1 day into the month.
The code came from another website, and all of the other functions work correctly (Today, Week etc)

The month format is short date.

Here is the code:

CODE
Private Sub cmdmonth_Click()
'Sets the Date From and Date To text boxes
'to show complete month (from start to end of current month)

    Me!txtdatefrom = CDate("01/" & Month(Date) & "/" & Year(Date))
    Me!txtDateTo = DateAdd("d", -1, DateAdd("m", 1, Me!txtdatefrom))

End Sub


Any ideas why this is not working correctly ?

Thank you very much for your help !

Bill
This post has been edited by Oblio: Feb 23 2018, 09:08 AM
Go to the top of the page
 
theDBguy
post Feb 23 2018, 09:14 AM
Post#2


Access Wiki and Forums Moderator
Posts: 72,439
Joined: 19-June 07
From: SunnySandyEggo


Hi Bill,

Are txtDateFrom and txtDateTo hidden on the Form? If so, try making them visible so you can see what you’re actually getting back from your code.

Based on today’s date (February), you would be getting the dates January 2, 2018 and February 1, 2018. Next month (March), you would get January 3, 2018 and February 2, 2018.

To get more accurate results, try using the DateSerial() function.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
zaxbat
post Feb 23 2018, 09:21 AM
Post#3



Posts: 951
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


First: Are you using dd/mm/yyyy format for dates?

Second: The dateadd function for "m" months does not work as expected----I don't use it

Trying to put something together for you that should help....will be back with it in a bit....

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
Oblio
post Feb 23 2018, 09:23 AM
Post#4



Posts: 205
Joined: 5-February 15



Hi, and thanks so much for your help !

The form is an unbound parameter form. Both txtdateFrom and txtDateTo are visible.

Not only do I have the date range option, but the individual command buttons : Today, Week, Month and Year. The only one which is not working is the Month button.

The immediate window shows:

Me.txtDateFrom is 2018-01-02
Me.txtDateTo is 2018-02-01


Any ideas ? Should the Me be removed???

Thanks,

Bill
This post has been edited by Oblio: Feb 23 2018, 09:30 AM
Go to the top of the page
 
Oblio
post Feb 23 2018, 09:25 AM
Post#5



Posts: 205
Joined: 5-February 15



Hi and thank you so much for your help !!!

The date format is Short date... 2018/02/23

Thanks again for your help with this !

Bill
Go to the top of the page
 
zaxbat
post Feb 23 2018, 10:51 AM
Post#6



Posts: 951
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


This is what works well for me:

myfirstdayofmonth = DateSerial(Year(Date), Month(Date), 0) 'the zero for the day technically sets the date to the very last day:hour:minute:second of the previous month
myfirstdayofmonth = DateAdd("d", 1, myfirstdayofmonth) 'then when you add one day to that you will get the first day of the next month

intyear = Year(Date)
intmonth = Month(Date)
mylastdayofmonth = DateSerial(intyear, intmonth + 1, 0) 'here we add a month to current month but set day to 0 again...you guessed it setting to last day:hour:min:sec of prev month

Seems convoluted.....but it works
This post has been edited by zaxbat: Feb 23 2018, 10:57 AM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
Oblio
post Feb 23 2018, 01:10 PM
Post#7



Posts: 205
Joined: 5-February 15



Hi, thanks again for your help!

Based on your code, this worked for me for the current month:

CODE
Private Sub cmdmonth_Click()
'Sets the Date From and Date To text boxes
'to show complete month (from start to end of current month)

Me.txtdatefrom = DateSerial(Year(Date), Month(Date), 1) 'the zero for the day technically sets the date to the very last day:hour:minute:second of the previous month

intyear = Year(Date)
intmonth = Month(Date)
Me!txtDateTo = DateSerial(intyear, intmonth + 1, 0) 'here we add a month to current month but set day to 0 again...you guessed it setting to last day:hour:min:sec of prev month
End Sub


Thank you so very much for your help with this !!!!!

Hope you have a great day and an awesome weekend !!!!!

Thanks,

Bill
Go to the top of the page
 
theDBguy
post Feb 23 2018, 01:18 PM
Post#8


Access Wiki and Forums Moderator
Posts: 72,439
Joined: 19-June 07
From: SunnySandyEggo


Hi Bill,

Glad to hear you got it sorted out using the DateSerial() function. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
zaxbat
post Feb 24 2018, 02:48 PM
Post#9



Posts: 951
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


is dateserial robust enough to interpret month 13 to 1 and -1 to 12 when you pass the dec-jan boundary?????
If not, going to need more code....

pretty sure it does not...think you need code to change the year too
This post has been edited by zaxbat: Feb 24 2018, 02:50 PM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
theDBguy
post Feb 24 2018, 07:07 PM
Post#10


Access Wiki and Forums Moderator
Posts: 72,439
Joined: 19-June 07
From: SunnySandyEggo


Re: "pretty sure it does not..."

Should be easy enough to check using the Immediate Window...

Attached File  dateserial.png ( 2.9K )Number of downloads: 3


Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
zaxbat
post Feb 24 2018, 09:04 PM
Post#11



Posts: 951
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Ha, that's great. Thanks DB

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
Oblio
post Feb 26 2018, 11:20 AM
Post#12



Posts: 205
Joined: 5-February 15



Thanks DB Guy...UtterAccess Rules !!! woohoo.gif
Go to the top of the page
 
Oblio
post Feb 26 2018, 11:21 AM
Post#13



Posts: 205
Joined: 5-February 15



Thanks zaxbat for all your help ! cheers.gif
Go to the top of the page
 
theDBguy
post Feb 26 2018, 11:30 AM
Post#14


Access Wiki and Forums Moderator
Posts: 72,439
Joined: 19-June 07
From: SunnySandyEggo


uarulez2.gif

I agree! thumbup.gif

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
zaxbat
post Feb 26 2018, 12:38 PM
Post#15



Posts: 951
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


My pleasure......keep that code flowing..................

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd June 2018 - 09:20 AM