vba - Need to Allow Null or "" values for Dates - Access Query Data type mismatch -
basically i'm trying use module or public functions pull datediff business days. works far code concerned reason particular date field (one i've added after database has been in production time) code not working correctly , i'm getting "data type mismatch in expression". i'm 99% sure data problem. if compare 2 different dates runs, i've created test table 10 records , runs.
the field set date/time. guess question is, there anyway rid of ""'s or make code accept these blanks nulls? or convert them?
this call function in query:
exp1: businessdays([intcalldate],[aintcall1])
and here code in module...
thanks - appreciated!!!
public function businessdays(dtestartdate date, dteenddate date) long on error goto err_workingdays dim lngyear long dim lngeyear long dim dtestart date, dteend date dim dtecurr date dim lngday long dim lngdiff long dim lngacount long dim dteloop variant dim blnhol boolean dim dteholiday() date dim lngcount long, lngtotal long dim lngthanks long if isdate(dtestartdate) , isdate(dteenddate) 'added here begin dtestart = dtestartdate dteend = dteenddate lngyear = datepart("yyyy", dtestart) lngeyear = datepart("yyyy", dteend) if lngyear <> lngeyear lngdiff = (((lngeyear - lngyear) + 1) * 7) - 1 redim dteholiday(lngdiff) else redim dteholiday(6) end if lngacount = -1 lngcount = lngyear lngeyear lngacount = lngacount + 1 'july fourth dteholiday(lngacount) = dateserial(lngcount, 7, 4) lngacount = lngacount + 1 'christmas dteholiday(lngacount) = dateserial(lngcount, 12, 25) lngacount = lngacount + 1 'new years dteholiday(lngacount) = dateserial(lngcount, 1, 1) lngacount = lngacount + 1 'thanksgiving - 4th thursday of november lngday = 1 lngthanks = 0 if weekday(dateserial(lngcount, 11, lngday)) = 5 lngthanks = lngthanks + 1 end if lngday = lngday + 1 loop until lngthanks = 4 dteholiday(lngacount) = dateserial(lngcount, 11, lngday) lngacount = lngacount + 1 'memorial day - last monday of may lngday = 31 if weekday(dateserial(lngcount, 5, lngday)) = 2 dteholiday(lngacount) = dateserial(lngcount, 5, lngday) else lngday = lngday - 1 end if loop until dteholiday(lngacount) >= dateserial(lngcount, 5, 1) lngacount = lngacount + 1 'labor day - first monday of septemeber lngday = 1 if weekday(dateserial(lngcount, 9, lngday)) = 2 dteholiday(lngacount) = dateserial(lngcount, 9, lngday) else lngday = lngday + 1 end if loop until dteholiday(lngacount) >= dateserial(lngcount, 9, 1) 'msgbox dteholiday(5) lngacount = lngacount + 1 'easter lngday = (((255 - 11 * (lngcount mod 19)) - 21) mod 30) + 21 dteholiday(lngacount) = dateserial(lngcount, 3, 1) + lngday + _ (lngday > 48) + 6 - ((lngcount + lngcount \ 4 + _ lngday + (lngday > 48) + 1) mod 7) next lngcount = 1 datediff("d", dtestart, dteend) dtecurr = (dtestart + lngcount) if (weekday(dtecurr) <> 1) , (weekday(dtecurr) <> 7) blnhol = false dteloop = 0 ubound(dteholiday) 'msgbox dteholiday(dteloop) & " " & dteloop if (dteholiday(dteloop) = dtecurr) blnhol = true end if next dteloop if blnhol = false lngtotal = lngtotal + 1 'msgbox dtecurr end if end if next lngcount businessdays = lngtotal else 'add businessdays = -1 ' add end if 'add err_workingdays: msgbox "error no: " & err.number & vbcr & _ "description: " & err.description resume exit_workingdays end function
the code fails when year(dtestartdate) > year(dteenddate)
Comments
Post a Comment