Link to home
Start Free TrialLog in
Avatar of Bob Collison
Bob CollisonFlag for Canada

asked on

MS Access Convert 'CCYY-MM-DD' To DateTime Type

I have a DateTime field in a MS Access Application / Table that I need to load with the String  "CCYY-MM-DD" in an Event Procedure when the extracted data for loading doesn't have a value.

PRC_END_DATE is Data Type DateTime in both tables.

Here is the code that produces a Type Mismatch error.

If Not IsNull(RS_10_MEMBER_MSTR_120!PRC_END_DATE) And RS_10_MEMBER_MSTR_120!PRC_END_DATE <> "" Then
   RS_10_MEMBER_MSTR_110!PRC_END_DATE = RS_10_MEMBER_MSTR_120!PRC_END_DATE
Else
   RS_10_MEMBER_MSTR_110!PRC_END_DATE = "1900-01-01"
End If

How should this be done?
Thanks.
Bob C.
Avatar of Gozreh
Gozreh
Flag of United States of America image

RS_10_MEMBER_MSTR_110!PRC_END_DATE = CDate("1900-01-01")



Do you have this field on a form ?
Or do you want to edit them straight into the tables? if yes you will need to set a recordset
Avatar of Bob Collison

ASKER

The filed is not on a form.

I'm editing straight into the table.  I have all of the code for the Record Sets in place and everything works for all data types (Integer / Text) other than Date/Time.
Did you try  CDate("1900-01-01")
Or  #1900-01-01#

on which line of code are you getting the mismatch error ?
Hi Gozreh,
This is the line where the error occurs.
 RS_10_MEMBER_MSTR_110!PRC_END_DATE = "1900-01-01"

Sorry  I previously missed your line of code below.
RS_10_MEMBER_MSTR_110!PRC_END_DATE = CDate("1900-01-01")

I will try it but probably not until tomorrow.

Thanks.
Bob C.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Gustav,
Both of these solutions work great!
Thanks.
Bob C.
You are welcome!

/gustav