sql error

[tim johnson]

Please, why am I getting an error in the procedure below.

"No value given for one or more of the parameters"


strSQL, cnn and rst are public variables in a module so I
do not need to declare then in procedure

Private Sub Form_Load()
strSQL = "SELECT Sum(LettersSent) AS SumOfLettersSent,
Sum(Gross) AS SumOfGross " & _
"FROM tblCallsMade " & _
"WHERE BDCRepID = " & Forms!frmReport!BDCRepID & _
" AND DateCalled Between Forms!frmReport!StartDate
AND Forms!frmReport!EndDate"

Set rst = New ADODB.Recordset
Set cnn = CurrentProject.Connection
rst.Open strSQL, cnn, adOpenKeyset, , adCmdText
MsgBox rst!SumOfLettersSent


End Sub

 

[Douglas J. Steele]

Many possibilities.

1) frmReport isn't open when the code runs
2) The controls on form frmReport are actually named something other than
BDCRepID, StartDate or EndDate
3) One or more of the controls doesn't have a value

Check that strSQL contains what you think it should before trying to open
the recordset.

 

[dan artuso]

Hi,
You have it correctly for the BDCRepID parameter but for some reason
you didn't put the dates outside of your quotes so that they could be evaluated.

"WHERE BDCRepID = " & Forms!frmReport!BDCRepID & _
" AND DateCalled Between #" & Forms!frmReport!StartDate & _
"# AND #" & Forms!frmReport!EndDate & "#"

 

[Douglas J. Steele]

Good catch, Dan. I missed that.

It's also worth mentioning that the date must be in mm/dd/yyyy format,
regardless of the Regional Settings:

"WHERE BDCRepID = " & Forms!frmReport!BDCRepID & _
" AND DateCalled Between " & _
Format(Forms!frmReport!StartDate, "\#mm\/dd\/yyyy\#") & _
" AND " & Format(Forms!frmReport!EndDate, "\#mm\/dd\/yyyy\#")

 

[dan artuso]

Yes, I always forget about that. Even in my own apps