Avatar of Bob Collison
Bob Collison
Flag 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.
Microsoft Access

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
Gozreh

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
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.
Gozreh

Did you try  CDate("1900-01-01")
Or  #1900-01-01#

on which line of code are you getting the mismatch error ?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Bob Collison

ASKER
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
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Bob Collison

ASKER
Hi Gustav,
Both of these solutions work great!
Thanks.
Bob C.
Gustav Brock

You are welcome!

/gustav
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.