Just looking at this forum for the first time and your query was posted
some time ago so I guess you've resolved it.

It caught my eye because I had a similiar thing in SQL which should work
in Excel-VB with suitable syntax change.

NB: Suitable for comparisons but NOT for calculating days between.

Public Function MyDateNum(MYDATE As Date) As Long 'converts SQL mm/dd/yy
or mm/dd/yyyy into a number yyyymmdd
On Error GoTo ERR_MyDateNum

Dim MYSTR As String
Dim MYPOS As Byte

MYSTR = Str(MYDATE)
'convert any two-digit year into a four-digit year
If Len(MYSTR) < 10 Then MYSTR = Left(MYSTR, Len(MYSTR) - 2) & "20" &
Right(MYSTR, 2)
MYPOS = InStr(1, MYSTR, "/")
MyDateNum = Val(Right(MYSTR, 4) & Mid(MYSTR, MYPOS + 1, 2) &
Mid(MYSTR, 2, MYPOS - 1))

EXIT_MyDateNum:
Exit Function
ERR_MyDateNum:
MsgBox Err.Description
MsgBox ("Please discontinue and call an Administrator")
MyDateNum = 20010101 'Error defaults to this date number
Resume EXIT_MyDateNum
End Function

"WHERE Val(Str(Year([tblCLAIMLINES].[clResDate])) +
Mid(Str([tblCLAIMLINES].[clResDate]),4,2) +
Mid(Str([tblCLAIMLINES].[clResDate]),1,2)) = " &
MyDateNum([Forms]![frmCLAIMANALYSIS]![txtDateFrom]) & " " & _
"AND Val(Str(Year([tblCLAIMLINES].[clResDate])) +
Mid(Str([tblCLAIMLINES].[clResDate]),4,2) +
Mid(Str([tblCLAIMLINES].[clResDate]),1,2)) <= " &
MyDateNum([Forms]![frmCLAIMANALYSIS]![txtDateTo]) & " " & _


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!