Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

CDate Problem

Posted on 2004-10-26
9
Medium Priority
?
3,385 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 53

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses

618 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