Convert String to a Date 1

[JohnAcc]

Hi All,

I have a text box where the user enters a date. This is stored as a string within vba and I need to convert it to a date.

Any ideas ?

Rgds, John

 

[dhulbert]

What format do they enter the string in,
22/02/2005, 22022005 20050222 etc etc

 

[RoyVidar]

If it is recognized by VBA as a date, the cdate or cvdate functions should work. If the string does not look like a recognizable date, for instance 122804, you may have to use some of the string manipulation functions to retrieve parts of the string:

[tt]mydate=dateserial(mid(strDt,5,2), mid(strDt,3,2), left(strDt,2))[/tt]

Roy-Vidar

 

[Loomah]

Have a look at the DateValue function
eg
myDate = DateValue(myString)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[JohnAcc]

It is entered as 01-Jan-2005

Rgds, John

 

[dhulbert]

just use the cdate function then.

 

[xlbo]

I think you'll find that if you use

Textbox1.VALUE
rather than
Textbox1.TEXT

you will not need to do ANY conversions

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[JohnAcc]

Hi all,

Thanks for all your help but I still can't seem to get this to work: I'll give a fuller explanation of what I'm trying to do:

I am running the following stored procedure from vba. I get an error when I try to open the query and I have concluded that it is the dates that are causing the error.

There is a userform with drop downs and text boxes to select the input for the stored procedure. The from date and to date are both free entry fields.

Here is the stored procedure with their data types.

exec dbo.MI_GetDealsEnteringStatus
@status int,
@from datetime,
@to datetime,
@organisation varchar(50) = NULL

Any help is much appreciated as always.

Rgds, John

 

[xlbo]

Very simple test in VBA before doing any processing

if isdate(textboxname.VALUE) then
'it is a date that is being passed through
else
'not a date
end if

If it runs through the TRUE part of the if then, it is not that which is causing the issue

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[JohnAcc]

Hi all,

I have removed the userform and tried to call the stored procedure but it still doesn't work. Here is my code if anyone can shed any light !

Sub TestDateRange()
On Error GoTo ErrHandler_
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=Darkstar;INITIAL CATALOG=MG_Midget;"
strConn = strConn & " User ID=coxj_mi;Password=zora05"
cnPubs.Open strConn
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
strQueryString = "exec dbo.MI_GetDealsEnteringStatus"
strQueryString = strQueryString & " @status = 10"
strQueryString = strQueryString & ", @from = Feb 1 2005"
strQueryString = strQueryString & ", @to = Feb 10 2005"
strQueryString = strQueryString & ", @organisation = Promfin"
MsgBox strQueryString
With rsPubs
.ActiveConnection = cnPubs
.Open strQueryString
ActiveWorkbook.Sheets("Sheet1").Range("A1").CopyFromRecordset rsPubs
.Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
Exit Sub
ErrHandler_:
MsgBox "Error Occured"
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
Unload fm_PullAgedStatus
Application.StatusBar = False
End Sub

Rgds, John

 

[PHV]

Have you tried this ?
strQueryString = strQueryString & ", @from = '2005-02-01'"
strQueryString = strQueryString & ", @to = '2005-02-10'"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JohnAcc]

Thanks PHV worked a treat