SQL statement in VB not matching date variable in 'where' clause 1

[CanNeverThinkOfaName]

I was wondering if anyone could help me with the following VB code attached to an Access form text box.

The point of the code is to include weekends and bank holidays when you're counting 8 working days from a certain date(the date in the text box-txtD0301Sent)

My problem is I can't get the SQL statement in the code below to compare to the variable calc_date-
all dates used are in the short date format.

The idea is if the SQL statement returns any results, then the date doesn't increment but it just doesn't do the compare right- it gives no errors but the recordset never returns as anything other than 0 so it always increments the date by bank holidays!!

can anyone please help me!!


Dim D0301Sent As Date
Dim Calc_Date As Date
Dim dbs As Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Dim ssql As String

D0301Sent = CDate(Me.txtD0301Sent) 'whats in the text box on the form

i = 0
Calc_Date = Me.txtD0301Sent
Do While i <> 9
Calc_Date = DateAdd(&quot;d&quot;, 1, Calc_Date)
ssql = &quot;SELECT BankHolsCalender.ShortDate&quot; _
& &quot; FROM BankHolsCalender&quot; _
& &quot; WHERE BankHolsCalender.ShortDate = &quot; & Calc_Date
Set rst = dbs.OpenRecordset(ssql, dbOpenDynaset)

If rst.RecordCount = 0 Then
If DatePart(&quot;w&quot;, Calc_Date, vbSunday) <> 1 And DatePart(&quot;w&quot;, Calc_Date, vbSunday) <> 7 Then
i = i + 1
End If
End If
rst.Close
Loop

 

[BiggerBrother]

use format(clacdate,MM\/dd\/yyyy) to change you date to the correct format.

BB

 

[zemp]

Also with Access surround the date value with the '#' character.

& " WHERE BankHolsCalender.ShortDate = #" & Calc_Date & "#"

Take Care,

zemp

&quot;Show me someone with both feet on the ground and I will show you someone who can't put their pants on.&quot;