What's wrong with this SQL for Date?

[MNNovice]

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:

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

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

Private Sub cmdtoday_Click()
'Sets the Date From and Date To text boxes
'to Today's Date

    Me!txtdatefrom = Date
    Me!txtDateTo = Date

End Sub

THIS WEEK

Private Sub cmdweek_Click()
'Sets the Date From and Date To text boxes
'to show complete working week (Mon - Fri)
    
    Dim today

    today = Weekday(Date)
    
    Me!txtdatefrom = DateAdd("d", (today * -1) + 2, Date)
    Me!txtDateTo = DateAdd("d", 6 - today, Date)

End Sub

I need your experties to help me with my code for MONTH. Thanks.

[MNNovice]

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

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 = DateAdd("m", (today * -1), Date)
    Me!txtDateTo = DateAdd("d", -1, DateAdd("m", 1, Me!txtdatefrom))

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.

[Denburt]

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/answers/869435-whats-wrong-sql-date

   Me!txtdatefrom = Dateserial(Year(Date),month(Date),1)
     Me!txtDateTo =Dateserial(Year(Date),month(Date),1-1)

[IT Couple]

Hi

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

Regards
Emil

[Denburt]

Hi

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

Regards
Emil

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:

CDate(format(date, "dd/mm/yyyy"))

Just an FYI

[Chinde]

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:

CDate(format(date, "dd/mm/yyyy"))

Just an FYI

Good tip thank you!!

[MNNovice]

Denburt:

The following code:

   Me!txtdatefrom = Dateserial(Year(Date),month(Date),1) 
     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

   Me!txtdatefrom = Dateserial(Year(Date),month(Date),1) 
     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.

[Denburt]

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.

   Me!txtdatefrom = Dateserial(Year(Date),month(Date),1) 
     Me!txtDateTo =Dateserial(Year(Date),month(Date)+1,1-1)

[Denburt]

Good tip thank you!!

Your quite welcome, glad you found it useful.

[MNNovice]

Denburt: Many thanks. It worked just fine.

How do I close this thread now? Just delete from "my subscription?" Thanks.

[Denburt]

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).

[NeoPa]

For this I always use something like :

Me.txtDateFrom = Format(Date(), "1 mmmm yyyy")
Me.txtDateTo = Format(DateAdd("m", 1, Date()) - 1, "d mmmm yyyy")