update query string date into date field vba access

Hi, I'm trying to get an update query to work!  I have 2 tables with the destination table that has a date field (datetime)   (on an MSSQL server) and  the sourse is an Excel file I linked to in Access.

In my Query:

Field: Start_date
Table:  Monthly_rep
Update To: [Excel_rep].[start_date2]

the update To:  I tried to put in CAST , CONVERT  etc keep getting error!  

CAST([Excel_rep].[start_date2] as DATE)
CAST([Excel_rep].[start_date2] as DATETIME)
Cdate([Excel_rep].[start_date2])

ETC.

Thanks

Pete
Peter GrovesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Peter GrovesAuthor Commented:
Forgot to mention that the source file has a mix of string and numeric dates!

Pete
Gustav BrockCIOCommented:
You should clean that up first.

TrueDate: DateValue([LinkedDateField])

Open in new window

Then use ODBC and link the table from SQL Server, and you can create a normal update query using Access SQL.
No CAST, no CONVERT.
Peter GrovesAuthor Commented:
The problem I have is that the source field contains numeric and text date info . One field may have  2017-08-19 the next has 40226 .
The SQL table I need to update is of the datetime type. I think I'll have to do it manually by openrecordset  if field_S is numeric then field_D
= CDATE(Field_S) movenext and so on!  or something of that nature!

Pete
Gustav BrockCIOCommented:
No, just create a query using the linked table as source. Convert your date field like:

TrueDate: CDate([Field_S])

Open in new window

and it will return your dates:

2017-08-19
2010-02-17

When the query runs as expected, create a new query that uses the first query as source and appends to your link table in SQL Server.
Peter GrovesAuthor Commented:
Sorry for the delay as I bypassed the problem TEMPORARILY,  but I'll finish working on the permanent solution later this week!

Pete
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.