sql - From VB6 checking if date exist in holiday table in access -

sql - From VB6 checking if date exist in holiday table in access -

below function i'm using subtract 20 days invoice date , move date mon if falls on weekend. next step check if date in holiday table , if adjust date accordingly. issue i'm running check against 30k plus records , it's taking long. there faster method accomplish same task? in advance.

public function printdate(thedate date) date dim officeclosed integer dim checkdate date dim qsdate string dim qs string officeclosed = false thedate = dateadd("d", -20, thedate) select case weekday(thedate) case 1 checkdate = dateadd("d", 1, thedate) case 7 checkdate = dateadd("d", 2, thedate) case else checkdate = thedate end select qsdate = checkdate qs = "" qs = qs & "select holidaydate " qs = qs & "from tbl_holidays " set ado_objconn = new adodb.connection ado_objconn.connectiontimeout = 130 ado_objconn.commandtimeout = 130 ado_objconn.connectionstring = "provider=microsoft.jet.oledb.4.0;data source='" & _ gl_autofinance_data_loc & "';persist security info=false" ado_objconn.open set ado_objrs8 = new adodb.recordset ado_objrs8.open qs, ado_objconn, , , adcmdtext ado_objrs8 if not .eof .movefirst while not .eof if ado_objrs8.fields(0) = qsdate officeclosed = true end if .movenext wend end if end set ado_objrs8 = nil set ado_objconn = nil if officeclosed = true select case weekday(checkdate) case 2 printdate = dateadd("d", 1, checkdate) case 3 printdate = dateadd("d", 1, checkdate) case 4 printdate = dateadd("d", 1, checkdate) case 5 printdate = dateadd("d", 1, checkdate) case 6 printdate = dateadd("d", 3, checkdate) end select else printdate = checkdate end if end function

you should include status in clause:

qs = "" qs = qs & "select holidaydate " qs = qs & "from tbl_holidays " qs = qs & "where holidaydate = #" & qsdate & "#"

if recordset returns record, officeclosed = true. must delete status in loop, way not have go through recordset

sql arrays vba date ms-access