[RESOLVED] Compile Error when converting to date

[connap1]

Hi, to preface my question, I'm not very experienced in writing code so I wont understand complex answers.

The problem I am having is I am trying to convert a string to a date.

I am using the following code

Dim Current_Date As String
Dim D As Integer
Dim M As Integer
Dim Y As Integer


D = Current_Date.Substring(0, 2)
M = Current_Date.Substring(2, 2)
Y = Current_Date.Substring(4, 4)
MsgBox (CDate(D & M & Y))

End Sub



The problem is occuring when it gets to the D variable, the error says Compile error; Invalid qualifier.

Can anyone help?

[LaVolpe]

Welcome to the forums.

This is the VB6 & earlier forum. It appears you are using .Net? If so, a moderator will move this for you to the correct forum. In the mean time, you may want to search the .Net forum for a quick solution.

[techgnome]

what is it you are trying to do? Umm.... are you using VB6 or VB.NET? there's a big difference. Specifically, the code you posted is .NET code, not VB6.

-tg

[connap1]

Hi,

I am using VB6.5, I didn't realise the code is not interchangable. I have changed the code into what I think is VB6 code but its still not working, the new code is

Sub test3()
'
' test3 Macro


Dim Current_Date As String

Dim MyValue As Date



Current_Date = Application.InputBox("Please enter end date of period (DDMMYYYY)", Type:=2)


File_path = "K:\Investment\Appraisals\Rebalance\"
File_name = Current_Date & ".xls"
Application.Workbooks.Open Filename:=File_path & "Cashflows " & File_name
MyValue = CDate(Current_Date)
Workbooks("Asset Allocations FY2010 (27 November_ Current)1.xls").Activate
Sheets("Cap Stable").Activate
Rows("1:1").Select
Selection.Find(What:=MyValue, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate


The code stops at the CDate function with the error message saying 'Run time error 13 Type mismatch'

Any Thoughts??

[Ellis Dee]

This can be handled with a one-liner:

MyValue = DateSerial(Right(Current_Date,4),Mid(Current_Date,3,2),Left(Current_Date,2))

For clarity you can declare three additional variables if you like:

Dim lngYear As Long
Dim lngMonth As Long
Dim lngDay As Long
 
lngYear = Right(Current_Date, 4)
lngMonth = Mid(Current_Date, 3, 2)
lngDay = Left(Current_Date, 2)
 
MyValue = DateSerial(lngYear, lngMonth, lngDay)

[connap1]

Thanks all, problem solved and I even learnt a few things.

[si_the_geek]

Just to be even more awkward, VB 6.5 is not the same as VB 6 either, even tho they can be very similar (such as the kind of thing Ellis Dee showed).

VB 6.x is actually VBA rather than VB (I'm not sure why MS has never made that clear in Help/About etc!), so there are significant differences depending on which host program you are using (typically one of the programs in MS Office).


I don't know which host program you are using, but I suspect that you should post future questions in our 'Office Development' forum rather than the 'VB6' forum.