By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,488 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,488 IT Pros & Developers. It's quick & easy.

What's wrong with this SQL for Date?

100+
P: 418
My form has two text boxes, txtDateFrom and txtDateTo. A macro button with the caption Month is to automatically fill in txtDateFrom and txtDateTo with current month date. My code reads as:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdmonth_Click()
  2. 'Sets the Date From and Date To text boxes
  3. 'to show complete month (from start to end of current month)
  4.  
  5.     Me!txtdatefrom = CDate("01/" & Month(Date) & "/" & Year(Date))
  6.     Me!txtDateTo = DateAdd("d", -1, DateAdd("m", 1, Me!txtdatefrom))
  7.  
  8. End Sub
But it's giving me wrong date: 1/5/09 and 2/4/09. I expect it to show 5/1/09 and 5/31/09

By the way, macros for Today, Week and Year are working just fine. These codes are as follow:

TODAY
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdtoday_Click()
  2. 'Sets the Date From and Date To text boxes
  3. 'to Today's Date
  4.  
  5.     Me!txtdatefrom = Date
  6.     Me!txtDateTo = Date
  7.  
  8. End Sub
THIS WEEK
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdweek_Click()
  2. 'Sets the Date From and Date To text boxes
  3. 'to show complete working week (Mon - Fri)
  4.  
  5.     Dim today
  6.  
  7.     today = Weekday(Date)
  8.  
  9.     Me!txtdatefrom = DateAdd("d", (today * -1) + 2, Date)
  10.     Me!txtDateTo = DateAdd("d", 6 - today, Date)
  11.  
  12. End Sub
I need your experties to help me with my code for MONTH. Thanks.
May 15 '09 #1
Share this Question
Share on Google+
11 Replies


100+
P: 418
To get current month (05/01/2009 - 05/31/2009) I modified the code to read as:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdmonth_Click()
  2. 'Sets the Date From and Date To text boxes
  3. 'to show complete month (from start to end of current month)
  4.  
  5.     Me!txtdatefrom = DateAdd("m", (today * -1), Date)
  6.     Me!txtDateTo = DateAdd("d", -1, DateAdd("m", 1, Me!txtdatefrom))
  7.  
  8. End Sub
but the end result has been 5/15/2009 - 6/14/2009.

Is it possible to get This month instead of a month from today?

Thanks.
May 15 '09 #2

Denburt
Expert 100+
P: 1,356
Is this a double post? If it is do you mind if I remove the other one? If it isn't please let me know so I can adjust the title as needed.

http://bytes.com/topic/access/answer...wrong-sql-date

Expand|Select|Wrap|Line Numbers
  1.    Me!txtdatefrom = Dateserial(Year(Date),month(Date),1)
  2.      Me!txtDateTo =Dateserial(Year(Date),month(Date),1-1)
  3.  
May 15 '09 #3

P: 36
Hi

Try adding the format function
format(date, "dd/mm/yyyy") or format(date, "short date")

Regards
Emil
May 15 '09 #4

Denburt
Expert 100+
P: 1,356
@IT Couple
The format function would return a Variant (String) not an actual date if you would like to keep it as a date you would need to convert it like so:
Expand|Select|Wrap|Line Numbers
  1. CDate(format(date, "dd/mm/yyyy"))
Just an FYI
May 15 '09 #5

P: 52
The format function would return a Variant (String) not an actual date if you would like to keep it as a date you would need to convert it like so:
Expand|Select|Wrap|Line Numbers
  1. CDate(format(date, "dd/mm/yyyy"))
Just an FYI
Good tip thank you!!
May 18 '09 #6

100+
P: 418
Denburt:

The following code:
Expand|Select|Wrap|Line Numbers
  1.    Me!txtdatefrom = Dateserial(Year(Date),month(Date),1) 
  2.      Me!txtDateTo =Dateserial(Year(Date),month(Date),1-1) 
gave me this:
Start Date: 5/1/09
End Date: 4/30/09

So I changed it to read as
Expand|Select|Wrap|Line Numbers
  1.    Me!txtdatefrom = Dateserial(Year(Date),month(Date),1) 
  2.      Me!txtDateTo =Dateserial(Year(Date),month(Date),1+30) 
Now I got: Start date: 5/1/09 and End Date 5/31/09

BUT what happens when the month has 28 days or 30 days? Can I include an IIF statement somewhere to make it dynamic?

Thanks.
May 18 '09 #7

Denburt
Expert 100+
P: 1,356
I should have added a month then subtracted 1-1in the day field to get the last day of any month. Yes I missed a little something there. The following will suit your needs and will take care of the ending date.
Expand|Select|Wrap|Line Numbers
  1.    Me!txtdatefrom = Dateserial(Year(Date),month(Date),1) 
  2.      Me!txtDateTo =Dateserial(Year(Date),month(Date)+1,1-1) 
May 18 '09 #8

Denburt
Expert 100+
P: 1,356
@Chinde
Your quite welcome, glad you found it useful.
May 18 '09 #9

100+
P: 418
Denburt: Many thanks. It worked just fine.

How do I close this thread now? Just delete from "my subscription?" Thanks.
May 18 '09 #10

Denburt
Expert 100+
P: 1,356
i would think that should work I am still getting used to some of the new functionality. So i am hit or miss on a lot of that. Generally I tend to keep myself subscribed (not sure if this is good or not) but if someone else makes a post even a year or so down the road it could be useful information, so I would like a notice but that's me (yeah I am kind of a pack rat).
May 18 '09 #11

NeoPa
Expert Mod 15k+
P: 31,709
For this I always use something like :
Expand|Select|Wrap|Line Numbers
  1. Me.txtDateFrom = Format(Date(), "1 mmmm yyyy")
  2. Me.txtDateTo = Format(DateAdd("m", 1, Date()) - 1, "d mmmm yyyy")
May 26 '09 #12

Post your reply

Sign in to post your reply or Sign up for a free account.