How do I get the Cdate or Format Date function to work properly in VBA? Also, there's a MSGbox pulling up randomly?

If you simply use this...

customdate = (dd.Text & "/" & mm.Text & "/" & yyyy.Text)

it should fix your problem...

Saurabh...

try change:

 ElseIf IsDate(Format(dd.Text & "/" & mm.Text & "/" & yyyy.Text,"mm dd yyyy")) = True Then
        customdate = CDate(dd.Text & "/" & mm.Text & "/" & yyyy.Text)

to:

  ElseIf IsDate(mm.Text & dd.Text & "/" & "/" & yyyy.Text) = True Then
        customdate = CDate(mm.Text & dd.Text & "/" & "/" & yyyy.Text)

Ryancys,
I originally had it listed as the first one (CDate(mm.Text & "/" & dd.Text & "/" & yyyy.Text)) which gave me the error. I used 5/27/09 and 27/5/09 as dates, and the CDATE result was 5/27/09 with both.

Saurabh726,
Same Issue as above but with a slightly different result. With m = 27, d = 5, y = 2009, here's the code and result:

ElseIf IsDate(dd.Text & "/" & mm.Text & "/" & yyyy.Text) = True Then   ' Validates the date as 5/27/09
        customdate = (dd.Text & "/" & mm.Text & "/" & yyyy.Text)             'Produces 27/5/09 which isn't valid, but says it is above.

Ryancys,
I tried the last change as well, but moved the format to work and got the same result as the first one you sent over:

 ElseIf IsDate(mm.Text & "/" & dd.Text & "/" & yyyy.Text) = True Then
        customdate = CDate(mm.Text & "/" & dd.Text & "/" & yyyy.Text)

Ahh use this to check whether its a date or not...

ElseIf IsDate(DATEVALUE(mm.Text & "/" & dd.Text & "/" & yyyy.Text)) = True Then   ' Validates the date as 5/27/09
       customdate = (dd.Text & "/" & mm.Text & "/" & yyyy.Text)    

This will fix your problem..

Saurabh...

I just tried it (switched the second line) using m = 5, d = 27 y = 2009 which produced 5/27/2009
                                                 and also tried m = 27, d = 5, y = 2009 which produced 5/27/2009

ElseIf IsDate(DateValue(mm.Text & "/" & dd.Text & "/" & yyyy.Text)) = True Then  ' This needs to reject (27/5/09) and move to the
        customdate = (mm.Text & "/" & dd.Text & "/" & yyyy.Text)                              'last clause of the If-Then stmt but it still accepts it : (
        DayOption.Hide
Else
        MsgBox ("The date you have chosen is not valid. Please try again")
        mm.Text = ""
        dd.Text = ""
        yyyy.Text = ""

Saurabh726,
Thanks for your help! I modified your code a little to work, but I'd completely overlooked the idea of using a function to catch the error before the date switch happened. I'll have to tweak it just a bit as the function takes the left two parts of the month, but if ony 1 is entered, it grabs the next digit and invalidates the date. Not a big deal.

Now if I could just figure out now why it's giving me a message box with the customdate result in it, and turn it off, I'll be set!!!

Here's my final code:

Public Sub CommandButton1_Click()
 
If mm.Text = "" And dd.Text = "" And yyyy.Text = "" Then
        customdate = 0
        DayOption.Hide
    ElseIf checkdate(mm.Text & "/" & dd.Text & "/" & yyyy.Text) = True Then
        customdate = CDate(mm.Text & "/" & dd.Text & "/" & yyyy.Text)
        DayOption.Hide
    Else
        MsgBox ("The date you have chosen is not valid. Please try again")
        mm.Text = ""
        dd.Text = ""
        yyyy.Text = ""
End If
End Sub
 
Function checkdate(ddate As String) As Boolean
    If Left(ddate, 2) >= 1 And Left(ddate, 2) <= 12 Then
          checkdate = True
        Else
          checkdate = False
          Exit Function
    End If
 
If IsDate(CDate(ddate)) = True Then
        checkdate = True
    Else
        checkdate = False
        Exit Function
End If
End Function
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:

Why not use dateserial?

customdate = Dateserial(val(yyyy.Text),val(mm.Text),val(dd.Text))
                                              

1:

Thats is not in the code as in the msgbox code, Its coming out because of different reason.

you can try use DateSerial function:

http://www.techonthenet.com/access/functions/date/dateserial.php
http://msdn.microsoft.com/en-us/library/1we4t8hy%28VS.85%29.aspx

sorry rorya as repeated your suggestion.

I found out why the customdate message box was appearing. It was because I had the following MSG (custdate) hidden in my module that came from the customdate variable from the userform. I got rid of it, and am set for now.

Thanks for all that helped!

Thanks for all the help

Not to worry, ryancys, nobody was listening anyway... 8^P

Rory

lol@ Rory i was, I replied him back :-)

Saurabh...