Error sending date variable to Stored Procedure

  • Thread starter Thread starter conjured.illusions
  • Start date Start date
C

conjured.illusions

This is the first adp I've worked with so I'm pretty new at this.
I'm using Access 2003 with SQL Server 2000.

I keep getting an error: Line 1: Incorrect syntax near '/'. When I
try to execute a stored procedure from a button. Basically what I'm
doing is running a stored procedure, and dumping the output to a temp
table that I've built and calling the report afterwards to run off
that data.

Here's the code for the button. I've tried to use single ' ' around
the variables that have the dates, but that doesn't seem to work
either.

Any help would be appreciated !

Private Sub packing_champ_scorecard_Click()
On Error GoTo Err_packing_champ_scorecard_Click

Dim stDocName As String

Dim startVAR As Date
Dim endVAR As Date
Dim pmuVAR As String
Dim umonthVAR As String

DoCmd.OpenForm "rep_vars2", , , , , acDialog

startVAR = Format(Forms!rep_vars2!START_DATE_FRM, "mm/dd/yyyy")
endVAR = Format(Forms!rep_vars2!END_DATE_FRM, "mm/dd/yyyy")
pmuVAR = UCase(Forms!rep_vars2!PM_USER_ID_FRM)
umonthVAR = Format(Forms!rep_vars2!START_DATE_FRM, "mm/yyyy")

CurrentProject.Connection.Execute "outbdChampionshipScorecard" &
startVAR & "," & endVAR & "," & pmuVAR & "," & umonthVAR & ""

stDocName = "Outbd_Championship_Score_Card"
DoCmd.OpenReport stDocName, acPreview

Exit_packing_champ_scorecard_Click:
Exit Sub

Err_packing_champ_scorecard_Click:
MsgBox Err.Description
Resume Exit_packing_champ_scorecard_Click

End Sub
 
and remember.. if yu create the tempTable usnig a sproc; the tempTable will
go away when the sproc is gone

I like temp tables-- don't get me wrong

i just don't think that they're necessary for most situations

I prefer dropping the PK into a persistent table and having that table
either filtered by username or @@SPID
 
Because you've declare the variables as Dates, using the Format function on
them does nothing. And assuming that startVAR and endVAR are declared as
Dates in the SP, you'll need to delimit them with quotes (as well as the
Text parameters)

Dim startVAR As String
Dim endVAR As String
Dim pmuVAR As String
Dim umonthVAR As String

DoCmd.OpenForm "rep_vars2", , , , , acDialog

startVAR = Format(Forms!rep_vars2!START_DATE_FRM, "yyyy\-mm\-dd")
endVAR = Format(Forms!rep_vars2!END_DATE_FRM, "yyyy\-mm/\-dd")
pmuVAR = UCase(Forms!rep_vars2!PM_USER_ID_FRM)
umonthVAR = Format(Forms!rep_vars2!START_DATE_FRM, "mm/yyyy")

Assuming that startVAR and endVAR

CurrentProject.Connection.Execute "outbdChampionshipScorecard '" & _
startVAR & "','" & endVAR & "','" & pmuVAR & "','" & umonthVAR & "'"

Exagerated for clarity, that's

CurrentProject.Connection.Execute "outbdChampionshipScorecard ' " & _
startVAR & " ',' " & endVAR & " ',' " & pmuVAR & " ',' " & umonthVAR & "
' "

Note that there must be a space between the SP name and the first quote.
 
Because you've declare the variables as Dates, using the Format function on
them does nothing. And assuming that startVAR and endVAR are declared as
Dates in the SP, you'll need to delimit them with quotes (as well as the
Text parameters)

Dim startVAR As String
Dim endVAR As String
Dim pmuVAR As String
Dim umonthVAR As String

DoCmd.OpenForm "rep_vars2", , , , , acDialog

startVAR = Format(Forms!rep_vars2!START_DATE_FRM, "yyyy\-mm\-dd")
endVAR = Format(Forms!rep_vars2!END_DATE_FRM, "yyyy\-mm/\-dd")
pmuVAR = UCase(Forms!rep_vars2!PM_USER_ID_FRM)
umonthVAR = Format(Forms!rep_vars2!START_DATE_FRM, "mm/yyyy")

Assuming that startVAR and endVAR

CurrentProject.Connection.Execute "outbdChampionshipScorecard '" & _
startVAR & "','" & endVAR & "','" & pmuVAR & "','" & umonthVAR & "'"

Exagerated for clarity, that's

CurrentProject.Connection.Execute "outbdChampionshipScorecard ' " & _
startVAR & " ',' " & endVAR & " ',' " & pmuVAR & " ',' " & umonthVAR & "
' "

Note that there must be a space between the SP name and the first quote.
Click to expand...

Doug Steele,

Thanks, it looks as though I had the variables wrong after the SP,
once I popped in yours it ran without a hitch.

Thanks again!

5th Amendment,

I do use a persistent table (for my clarity, I do an insert to a table
that doesn't start in #, and blow out the data once the report
closes) , since I'm I newb I guess I just referred to it wrong.
 
Back
Top