?
Solved

CDate Problem

Posted on 2004-10-26
9
Medium Priority
?
3,387 Views
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
Fabian
0
Comment
Question by:fistaag
9 Comments
 
LVL 16

Accepted Solution

by:
jimbobmcgee earned 1400 total points
ID: 12409641
Try

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

HTH

J.
0
 
LVL 8

Expert Comment

by:Ashutosh Vyas
ID: 12409660
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.
0
 

Author Comment

by:fistaag
ID: 12409677
ashutosh9910 can you post a example?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 26

Expert Comment

by:EDDYKT
ID: 12409680
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
0
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 12409757
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.

J.
0
 
LVL 8

Expert Comment

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

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

Expert Comment

by:Ryan Chong
ID: 12409861
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.
0
 
LVL 18

Expert Comment

by:JR2003
ID: 12410106
Why is 12/20/2004 converted to 12.20.2004 correct but 02/10/2004 converted to 02.10.2004 incorrect?

JR
0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 12410753
ashutosh9910
>>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.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

601 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