Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Something slightly harder...
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! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
harder than i thought | Excel Discussion (Misc queries) | |||
Autofilter so different, harder to use | Excel Discussion (Misc queries) | |||
Slightly OT (Maybe) printing problem | Excel Worksheet Functions | |||
This is A LOT harder than I thought it would be | Excel Discussion (Misc queries) | |||
Things are getting a little harder! | New Users to Excel |