asked on FormatDateTime with CDate wrapper not returning Date data type in make table query
I have this line of code in my Access Make-Table query and it returns the right value, however, when i open the table it creates and look at the properties, all my date fields are formatted as Text. Anyone know why this might be?
LA: IIf(Item001_AllFilesPresen
LA: IIf(Item001_AllFilesPresen
Microsoft AccessSQL
Last Comment
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Just a wild design idea here...
Instead of using a make-table query, where Access often assumes (get it, ass-u-me) the data types for the various columns, have the table pre-defined with the column types you want, and then just run a delete query followed with an append query.
Instead of using a make-table query, where Access often assumes (get it, ass-u-me) the data types for the various columns, have the table pre-defined with the column types you want, and then just run a delete query followed with an append query.
ASKER
Thanks, that worked. Just so i understand, it was the "" that was screwing it up because access considers that text?
yes, "" is text so the date value is also convert to text.
But with CVDate you'll skip all the IIf and Null stuff.
/gustav
/gustav
ASKER
with cvdate, how do i make it return on the the date portion of the field? I tried this
Expr2:cvdate(([Item001_All
and this
Expr1: CVDate(FormatDateTime([Ite
HOWEVER, Expr1 only works if the value isn't null because of the FormatDateTime function.
But if CVDATE can format the date/time automatically to only display the date, then it will work.
Expr2:cvdate(([Item001_All
and this
Expr1: CVDate(FormatDateTime([Ite
HOWEVER, Expr1 only works if the value isn't null because of the FormatDateTime function.
But if CVDATE can format the date/time automatically to only display the date, then it will work.
CVDate only converts from something to DateTime data type, much like CDate, but it does not convert the value.
CDate does not accept Null which CVDate does.
FormatDateTime can be dangerous as it returns localised expressions.
A better choice would be to use Format(value, "m\/d\/yyyy") but it will return an empty string for Null.
To cut the time part, Fix can be used and it accepts and returns Null as well.
Thus, to return either Null or a DateTime value without the time part:
LA: CVDate(Fix([Item001_AllFil
/gustav
CDate does not accept Null which CVDate does.
FormatDateTime can be dangerous as it returns localised expressions.
A better choice would be to use Format(value, "m\/d\/yyyy") but it will return an empty string for Null.
To cut the time part, Fix can be used and it accepts and returns Null as well.
Thus, to return either Null or a DateTime value without the time part:
LA: CVDate(Fix([Item001_AllFil
/gustav
ASKER
what do you mean by "localised expressions"?
I tried the Fix function and it worked!
I tried the Fix function and it worked!
Great!
Localised means in local language as to the settings of the machine. If that language is not English in one of its varieties, it may cause troubles in code.
The main purpose of FormatDateTime is for display for human reading.
/gustav
Localised means in local language as to the settings of the machine. If that language is not English in one of its varieties, it may cause troubles in code.
The main purpose of FormatDateTime is for display for human reading.
/gustav
Isn't CVDate deprecated now ?
From Help:
"A CVDate function is also provided for compatibility with previous versions of Visual Basic. The syntax of the CVDate function is identical to the CDate function, however, CVDate returns a Variant whose subtype is Date instead of an actual Date type. Since there is now an intrinsic Date type, there is no further need for CVDate. The same effect can be achieved by converting an expression to a Date, and then assigning it to a Variant. This technique is consistent with the conversion of all other intrinsic types to their equivalent Variant subtypes."
mx
From Help:
"A CVDate function is also provided for compatibility with previous versions of Visual Basic. The syntax of the CVDate function is identical to the CDate function, however, CVDate returns a Variant whose subtype is Date instead of an actual Date type. Since there is now an intrinsic Date type, there is no further need for CVDate. The same effect can be achieved by converting an expression to a Date, and then assigning it to a Variant. This technique is consistent with the conversion of all other intrinsic types to their equivalent Variant subtypes."
mx
Not quite. It is still here in Access 2007 and it is documented.
And it is extremely useful in some cases. We've just seen one example.
/gustav
And it is extremely useful in some cases. We've just seen one example.
/gustav
ASKER
That makes sense. I was wondering why my formatting was getting screwed up depending on which machine i was running the database. Thanks. This post has been very informative!!
ASKER
Cactus data has it right in this case. When i run the function with CDate i get an error when the values are null, when i run it with CVDate, no errors.
Well, I wasn't questioning whether he was right, only what it says in the Help File regarding CVDate() ... and that "The same effect can be achieved by ......".
mx
mx
Yes, the on-line help should be more clear.
/gustav
/gustav
For M$ ... that might be in a parallel Universe !
mx
mx
LA: CVDate([Item001_AllFilesPr
/gustav