CDate Problem

Posted on 2004-10-26
Last Modified: 2008-03-17
Hi all

I have a serious problem with a sql-query using the CDate command on a Windows-System (german win 2000 server) using german Date Format. The Date locally cannot be changed to english short format, because of other programs.

The Dates in the Database are in US-Format: e.g. 01/30/2004

The Users enters a Form where he enters his date selection: e.g. from 02/10/2004 untill 04/30/2004. This dates are used to query the database with the following query:

SQL = "SELECT * FROM tblKundenstamm, tblVertrag " _
        & "WHERE tblVertrag.strKSID = tblKundenstamm.IDRef AND tblKundenstamm.strFirma = '" & Request.Form("Kunde")  
        & "' AND CDate(strDate) BETWEEN #" & firstDate & "# AND #" & lastDate & "# " _

If the Date ist 12/20/2004 CDate converts it to 12.20.2004 which is correct. But if the Date looks like 02/10/2004 it is converted to 02.10.2004 which is not correct.

How can i correct this problem?

Thanks for your help
Question by:fistaag
    LVL 16

    Accepted Solution


            CDate(Format(strDate, "mm.dd.yyyy")


    LVL 8

    Expert Comment

    by:Ashutosh Vyas
    may be i did not get your point is it like
    12.20.2004 - means - December 20 2004 and
    02.10.2004 - means - October 02 2004

    if so then you can use Format function along with the CDate to convert it to your own desired format.

    Author Comment

    ashutosh9910 can you post a example?
    LVL 26

    Expert Comment

    Dim d As Date
    Dim str As String
    str = "02/10/2004"
    d = DateSerial(Mid(str, 7, 4), Mid(str, 1, 2), Mid(str, 4, 2))
    Debug.Print d
    LVL 16

    Expert Comment

    CDate(Format(strDate, "mm/dd/yyyy")) will make the CDate function read the first two digits as the month, the second two as the day and the last four as the year and convert it into the system format.

    LVL 8

    Expert Comment

    by:Ashutosh Vyas
    Format(CDate(strdate), "MM/dd/yyyy")

    Be particular about using MM and not mm. This would cause you an error.
    LVL 48

    Expert Comment

    by:Ryan Chong
    What's the data type of strDate ? Just always make sure the value of strDate is in mm/dd/yyyy format, try to avoid to use CDate function if possible.
    LVL 18

    Expert Comment

    Why is 12/20/2004 converted to 12.20.2004 correct but 02/10/2004 converted to 02.10.2004 incorrect?

    LVL 10

    Expert Comment

    >>Format(CDate(strdate), "MM/dd/yyyy")

    >> particular about using MM and not mm. This would cause you an error.

    Not in Format function in VB. m, mm = Months (except when following h or hh - then minutes) ; n, nn = minutes all the time.

    MyDateVarString = Format(MyDate, "mm/dd/yyyy")  ' is correct.
    MyDateString = Format$(MyDate, "mm/dd/yyyy")  ' is slightly more efficient.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now