Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help converting number to date 2

Status
Not open for further replies.
Appollo14

Appollo14

Technical User
Sep 4, 2003
182
0
0
GB
Hi all,

I've started another project so you'll probably be sick of me in no time all. :)

The latest project i've embarked on requires me to use data from an sql database via an odbc connection. That bit i'm ok with. The problem i'm having is that one of the date fields that i need to use is stored as a string not a date.
The field (dwDate) stores the date, for example 31st March 2004, as '20040331'. What i need to be able to do is to convert this string to a standard short date format so that i can do a Date diff calculation on it.
What is the best way of doing this?

Regards,
Noel.
 
Sort by date Sort by votes
Hi, there's a CDate function that will retaurn a date type for any valid date expression (i.e. number or text)...

Hope that helps, Jamie
 
Upvote 0 Downvote
Hi guys,
Thanks for the quick response.
I've added the expression (in SQL view) "CDate([dwDate]) as MyDate" but all i get is the #ERROR returned.
In normal query design view the expression is MyDate:CDate([dwDate]). This also returns #ERROR. Where am i going wrong?

Regards,
Noel.
 
Upvote 0 Downvote
Hi

Is this a pass thru query? ie where the (SQL) syntax must be the syntax of the backend db? if yes you cannot have VBA functions in the SQL, in which case you will have to do the conversion after you retrieve the recordset

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Hi Ken,

In answer to your question, no it is not a pass through query. I've been tinkering around with it and looked on the web for more info and found out about the isdatde function so i included this in the query and the value of 0 was returned so i'm wondering if this is where the problem lies.Do I have to define how the string is laid out somewhere so that Cdate knows how to convert it? or am i barking up the wrong tree?

Regards,
Noel.
 
Upvote 0 Downvote
Hi

Yes sorry

? CDate("2004/04/05")
05/04/2004

so you would need CDate(Left([dwDate],4)&"/" & mid([dwDate],5,2) & "/" & right([dwDate],2))



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Brilliant,

Thanks Ken, that works a treat. I was actually part way through stripping out the string and rebuilding it but this way suits my needs much better.

Many thanks.

Regards,
Noel.
 
Upvote 0 Downvote
Hey Ken, so what if I am trying to do this in Access? I realize now I can't use the CDate function for pass through queries, but how do I convert a string to a date so I can do sorts on it, etc?

When I use the CDate function or DateValue function, it appears to create a Date field (if I turn it into a make table query it does make the field I am editing a date filed) however, if I try and do a report based on the query or re-query the query and sort on this new field, it gives me a Data Type Mismatch in Criteria Expression error every time.

Any ideas?

Thanks!
Scott
 
Upvote 0 Downvote
Hi

CDate() does as you say convert the string to a date, but the string must be a valid date, so as Scott says it is wise to check with IsDate(), so something like

IIF(IsDate((Left([dwDate],4)&"/" & mid([dwDate],5,2) & "/" & right([dwDate],2)),CDate(Left([dwDate],4)&"/" & mid([dwDate],5,2) & "/" & right([dwDate],2)),#01/01/01#)







Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Hmm, following your example, here is the code I wrote..

IIF(IsDate((Left([Comment Date],2)&"/"& (IF(val(mid([Comment Date],4,2))="0",mid([Comment Date],3,2),mid([Comment Date],4,2))& "/" & Right([Comment Date],2)),(Left([Comment Date],2)&"/"& (IIF(val(mid([Comment Date],4,2))="0",mid([Comment Date],3,2),mid([Comment Date],4,2))& "/" & Right([Comment Date],2),#01/01/01#)

However, it seems I have some invalid syntax in there somewhere, I am getting a "The expression you entered is mising a valid ) | or ]" error.
 
Upvote 0 Downvote
Nevermind, I figured that part out, new code...

IIF(IsDate((Val(Left([Comment Date],2)) & "/" & (IIf(Val(Mid([Comment Date],4,2))="0",Val(Mid([Comment Date],3,2)),Val(Mid([Comment Date],4,2))) & "/" & Val(Right([Comment Date],2))))),CDate(Val(Left([Comment Date],2)) & "/" & (IIf(Val(Mid([Comment Date],4,2))="0",Val(Mid([Comment Date],3,2)),Val(Mid([Comment Date],4,2))) & "/" & Val(Right([Comment Date],2)))),#01/01/01#)

However, when I put this into a report I ams till getting Data Type Mismatch errors on this field despite the fact that it pulls the data.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TomSalvato
  • Locked
  • Question
Getting the Monday date from a week number 1
Replies
2
Views
124
TomSalvato
TomSalvato
PrimoSteve
  • Locked
  • Question
Cannot add Image to Command Button?
Replies
9
Views
166
PrimoSteve
PrimoSteve
renigar
  • Locked
  • Question
Trying to add hyperlinks to pdfs through form entry
Replies
0
Views
78
renigar
renigar
caykamanj1966
  • Locked
  • Question
Help With Search Form Results 2
Replies
15
Views
114
caykamanj1966
caykamanj1966
MacroAlan
  • Locked
  • Question
Navigation Bar is too short to read
Replies
5
Views
92
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top