Access VBA using dates as criteria in a query (1 Viewer)

[armesca]

I am storing dates as strings as putting them in a case statement. I want to cycle through them all, but am getting an error in my SQL string statement. I think the way I am storing the dates is incorrect. Any thought??

Private Sub cmd_runholds_Click()
Dim queryName As String
Dim ObjExcel As Object
Dim ObjWB As Object
Dim objWS As Object
Dim myRst As Recordset
Dim mystring As String
Dim myhold As String
Dim myremoved As String
Dim mystring2 As String
Dim i As Integer
Set ObjExcel = CreateObject("Excel.Application")

Set ObjWB = ObjExcel.Workbooks.Open _
("C:\Documents and Settings\carmes\Desktop\AdHoc\Trends Analysis\Hold_template.xlsx")

i = 1

Do While i <= 8

Select Case i
Case 1
myhold = "<= 07/01/2011"
myremoved = ">= 07/01/2011"
Case 2
myhold = "<= 06/01/2011"
myremoved = ">= 06/01/2011"
Case 3
myhold = "<=05/01/2011"
myremoved = ">=05/01/2011"
Case 5
myhold = "<=04/01/2011"
myremoved = ">=04/01/2011"
Case 6
myhold = "<=03/01/2011"
myremoved = ">=03/01/2011"
Case 7
myhold = "<=02/01/2011"
myremoved = ">=02/01/2011"
Case 8
myhold = "<=01/01/2011"
myremoved = ">=01/01/2011"

End Select

Set objWS = ObjWB.Worksheets("Holds Totals")

mystring = "Select [GRANTNUMBER], [Grant Title], [ifmis_vendor_id], [accepteddate], [HoldSTATUS], [HOLDDATE], [HOLDAMOUNT], [HOLDREASON], [HOLDREMOVEDDATE], [HOLDREMOVEDREASON], [AVAILABLEAMOUNT] from [PSGP Holds] Where [HOLDDATE] = '" & myhold & "' And [HOLDREMOVEDDATE] = '" & myremoved & "'"

MsgBox mystring
Set myRst = Application.CurrentDb.OpenRecordset(mystring)

objWS.Range("A3").CopyFromRecordset myRst
myRst.Close

Loop

mystring2 = "C:\Documents and Settings\carmes\Desktop\AdHoc\Trends Analysis\Hold Data Final"

mystring2 = Replace(mystring2, "/", "")
ObjWB.Saveas (mystring2), FileFormat:=51
ObjWB.Close

Set objWS = Nothing
Set ObjWB = Nothing
Set ObjExcel = Nothing

End Sub