I am having trouble debugging an error, any help would be appreciated. Thank you.
Scenario
I am writing a On_Click VBA that executes an SQL string. One of the criteria (WHERE) uses the BETWEEN / AND condition. I have a combo box that passes the date to a txtbox, and the strSQL reads the txtbox from the WHERE strSQL
ERROR
date type mismatch.
Steps I took
I looked at both the txtStartDate and txtEndDate and made sure I set the formate to short date.
I also looked at cboStartDate and cboEndDate, and made those short date format - both to no avail.
Finally, I had the strSQL point directly to the cboStart and EndDate, hoping the strSQL would recognize the cbo as date...I am beginning to think
my solution lies in formatting the line:
BETWEEN '&#&Me.cboStartDate&#&' AND '&#&me.cboEndDate&#&' " or converting cboStartDate and cboEndDate to DATE???? and that I
am also having trouble with....
Code
Private Sub lstAccount_Click()
On Error GoTo Error_Handler
strSQL = "SELECT DISTINCTROW tbl_FinRpt.Entity, tbl_FinRpt.Months, tbl_FinRpt.Account, tbl_FinRpt.Amount " & _
"FROM tbl_CoA INNER JOIN tbl_FinRpt " & _
"ON tbl_CoA.Account = tbl_FinRpt.Account " & _
"WHERE tbl_FinRpt.Entity = '" & Me.txtPhysician & "' " & _
"AND tbl_FinRpt.Account = '" & Me.txtAccount & "' " & _
"AND tbl_FinRpt.Months BETWEEN '&#&Me.cboStartDate&#&' AND '&#&me.cboEndDate&#&' "
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub