CDATE issues

msmeth
01-14-2004, 05:48 AM
Can anyone tell me why this isn't working?


Dim TheDate As Date
TheDate = CDate(Format("2004.08.01", "yyyy.dd.mm"))


I'm getting a Type Mismatch error....many thanks.

Iceplug
01-14-2004, 05:50 AM
CDate is not going to convert a string with periods as the delimiter. Try using / forward slashes instead. Preferably in the second parameter where you declare the format. :)

msmeth
01-14-2004, 05:53 AM
CDate is not going to convert a string with periods as the delimiter. Try using / forward slashes instead. Preferably in the second parameter where you declare the format. :)

god * * * * that was it *LOL*

Well, I actually had to change it to forward slashes in both parts, but what to do now.....I need it with periods in my database.

arluijen
01-14-2004, 05:54 AM
Even the format will not work with that "2004.08.01" value...
I'm not familiar with that date type, so I would start replacing the "."'s with "-"'s.

A quick solution would look like this:


TheDate = CDate(Format(Replace("2004.08.01", ".", "-"), "yyyy-dd-mm"))

arluijen
01-14-2004, 05:55 AM
...and if you want it with periods in your database you're going to have to work with it as a string....

msmeth
01-14-2004, 05:56 AM
Those ideas both work, but it's not formatting correctly. Even though I specify the format as "yyyy/mm/dd" or "yyyy-mm-dd" its giving me it as "mm/dd/yyyy"

msmeth
01-14-2004, 05:58 AM
...and if you want it with periods in your database you're going to have to work with it as a string....

But its setup with periods in my database...... :confused:

00100b
01-14-2004, 06:00 AM
The DateTime field within a database will store the date values within one format (actually a double-precision floating point number). How values are displayed will need to be handled by you if you want anything other than the default format that the DB Engine is using.

arluijen
01-14-2004, 06:03 AM
It'll return the date in the format as the date format is specified on your local machine in the regional settings. If you stop using a Date variable, format will simply return it the way you want so...:


Dim TheDate As String

TheDate = Format(Replace("2004.08.01", ".", "-"), "yyyy-dd-mm")


...will give you the yyyy-dd-mm format.

What kind of database are you working on ? and what is the datatype definition of your date field ?

Cyber_Zeus
01-14-2004, 06:05 AM
...and if you want it with periods in your database you're going to have to work with it as a string....

But its setup with periods in my database...... :confused:

Ok, when you have the date in dd/mm/yyyy format or mm/dd/yyyy format you can convert it to a string with CStr and then use the Replace function to change each '/' in '.'

Yours
Cyber_Zeus

///////////////////////////////////////////////////////////
///// SORRY, DIDN'T SEE THE REPLY OF arluijen /////
//////////////////////////////////////////////////////////

msmeth
01-14-2004, 06:09 AM
It'll return the date in the format as the date format is specified on your local machine in the regional settings. If you stop using a Date variable, format will simply return it the way you want so...:


Dim TheDate As String

TheDate = Format(Replace("2004.08.01", ".", "-"), "yyyy-dd-mm")


...will give you the yyyy-dd-mm format.

What kind of database are you working on ? and what is the datatype definition of your date field ?

Its a dbaseIV database and the datatype definition of the date field is Date and they look like this "mm.dd.yy"

msmeth
01-14-2004, 06:16 AM
...and if you want it with periods in your database you're going to have to work with it as a string....

But its setup with periods in my database...... :confused:

Ok, when you have the date in dd/mm/yyyy format or mm/dd/yyyy format you can convert it to a string with CStr and then use the Replace function to change each '/' in '.'

Yours
Cyber_Zeus

///////////////////////////////////////////////////////////
///// SORRY, DIDN'T SEE THE REPLY OF arluijen /////
//////////////////////////////////////////////////////////

So if I do this and compare the new string with a date in my database, will it work properly?

Cyber_Zeus
01-14-2004, 06:22 AM
...and if you want it with periods in your database you're going to have to work with it as a string....

But its setup with periods in my database...... :confused:

Ok, when you have the date in dd/mm/yyyy format or mm/dd/yyyy format you can convert it to a string with CStr and then use the Replace function to change each '/' in '.'

Yours
Cyber_Zeus

///////////////////////////////////////////////////////////
///// SORRY, DIDN'T SEE THE REPLY OF arluijen /////
//////////////////////////////////////////////////////////

So if I do this and compare the new string with a date in my database, will it work properly?

Comparing a string with a date is non-sense.
They have to be of the same type.
Convert the date to a string and then compare them.

arluijen
01-14-2004, 06:22 AM
I've never worked with a dbaseIV database, but by the sound of it I would use strings to handle your dates.
The thing is that if you want to use specific date functions you're going to have to convert it to a date.
I gues you can't insert a date into you database in the conventional format (mm/dd/yyyy) ?

Merri
01-14-2004, 07:12 AM
Dim TheDate As Date
'store the date as a value (this has nothing to do what the date looks like)
TheDate = DateSerial(2004, 8, 1)
'you need to format the date when you're displaying it
Debug.Print Format$(TheDate, "yyyy.dd.mm"))

msmeth
01-14-2004, 07:20 AM
Well it turns out the database is actually storing the data in a Character field!!!! It should work just as string now. Thanks for all the responses everybody. I learned a little more.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum