I have a function created in ADP using ADODB to talk to our SQL Server. The
function is now required to accept two parameters, BeginDate and EndDate,
both are in date format of 'mm/dd/yyyy'. I have trouble trying to pass these
date parameters and hope you could help.
Thank you!

The line below, I changed the adInteger to adDBDate and changed the 4 to 8,
and still didn't work.

..Parameters.Append .CreateParameter("@DateValue", adInteger, _
adParamInput, 4, BeginMonth)


'The following is the full function module for your reference.
'-------------------------------------------------------------------
Function DateRangeTaken(ByVal BeginDate, EndDate As Date, ByVal PlanCode As
Integer, ByVal EmplID As string) As Currency

Dim GLOBAL_Timekeep_Total As Currency

GLOBAL_Timekeep_Total = 0

Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
If EntireMonth = 1 Then
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "SQLProcedureA"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@ID", adChar, _
adParamInput, 20, EmplID)
.Parameters.Append .CreateParameter("@DateValue", adInteger, _
adParamInput, 4, BeginDate)
.Parameters.Append .CreateParameter("@DateValue2r", adInteger, _
adParamInput, 4, EndDate)
.Parameters.Append .CreateParameter("@Catergory", adInteger, _
adParamInput, 4, PlanCode)
Set rst = .Execute

End With


Do While Not rst.EOF

GLOBAL_Timekeep_Total = GLOBAL_Timekeep_Total + rst.Fields("hrs_col") +
(rst.Fields("mins_col") / 60)
rst.MoveNext
Loop

Hi,
try to pass dates as varchar in format yyyymmdd - this should work


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


"Martin" <(E-Mail Removed)> wrote in message
news:8DCF5FEA-C931-4438-96CA-(E-Mail Removed)...
>I have a function created in ADP using ADODB to talk to our SQL Server.
>The
> function is now required to accept two parameters, BeginDate and EndDate,
> both are in date format of 'mm/dd/yyyy'. I have trouble trying to pass
> these
> date parameters and hope you could help.
> Thank you!
>
> The line below, I changed the adInteger to adDBDate and changed the 4 to
> 8,
> and still didn't work.
>
> .Parameters.Append .CreateParameter("@DateValue", adInteger, _
> adParamInput, 4, BeginMonth)
>
>
> 'The following is the full function module for your reference.
> '-------------------------------------------------------------------
> Function DateRangeTaken(ByVal BeginDate, EndDate As Date, ByVal PlanCode
> As
> Integer, ByVal EmplID As string) As Currency
>
> Dim GLOBAL_Timekeep_Total As Currency
>
> GLOBAL_Timekeep_Total = 0
>
> Set cmd = New ADODB.Command
> Set rst = New ADODB.Recordset
> If EntireMonth = 1 Then
> With cmd
> .ActiveConnection = CurrentProject.Connection
> .CommandText = "SQLProcedureA"
> .CommandType = adCmdStoredProc
> .Parameters.Append .CreateParameter("@ID", adChar, _
> adParamInput, 20, EmplID)
> .Parameters.Append .CreateParameter("@DateValue", adInteger, _
> adParamInput, 4, BeginDate)
> .Parameters.Append .CreateParameter("@DateValue2r", adInteger, _
> adParamInput, 4, EndDate)
> .Parameters.Append .CreateParameter("@Catergory", adInteger, _
> adParamInput, 4, PlanCode)
> Set rst = .Execute
>
> End With
>
>
> Do While Not rst.EOF
>
> GLOBAL_Timekeep_Total = GLOBAL_Timekeep_Total + rst.Fields("hrs_col") +
> (rst.Fields("mins_col") / 60)
> rst.MoveNext
> Loop
>

You should be using adDate, not adInteger.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Martin" <(E-Mail Removed)> wrote in message
news:8DCF5FEA-C931-4438-96CA-(E-Mail Removed)...
> I have a function created in ADP using ADODB to talk to our SQL Server.
The
> function is now required to accept two parameters, BeginDate and EndDate,
> both are in date format of 'mm/dd/yyyy'. I have trouble trying to pass
these
> date parameters and hope you could help.
> Thank you!
>
> The line below, I changed the adInteger to adDBDate and changed the 4 to
8,
> and still didn't work.
>
> .Parameters.Append .CreateParameter("@DateValue", adInteger, _
> adParamInput, 4, BeginMonth)
>
>
> 'The following is the full function module for your reference.
> '-------------------------------------------------------------------
> Function DateRangeTaken(ByVal BeginDate, EndDate As Date, ByVal PlanCode
As
> Integer, ByVal EmplID As string) As Currency
>
> Dim GLOBAL_Timekeep_Total As Currency
>
> GLOBAL_Timekeep_Total = 0
>
> Set cmd = New ADODB.Command
> Set rst = New ADODB.Recordset
> If EntireMonth = 1 Then
> With cmd
> .ActiveConnection = CurrentProject.Connection
> .CommandText = "SQLProcedureA"
> .CommandType = adCmdStoredProc
> .Parameters.Append .CreateParameter("@ID", adChar, _
> adParamInput, 20, EmplID)
> .Parameters.Append .CreateParameter("@DateValue", adInteger, _
> adParamInput, 4, BeginDate)
> .Parameters.Append .CreateParameter("@DateValue2r", adInteger, _
> adParamInput, 4, EndDate)
> .Parameters.Append .CreateParameter("@Catergory", adInteger, _
> adParamInput, 4, PlanCode)
> Set rst = .Execute
>
> End With
>
>
> Do While Not rst.EOF
>
> GLOBAL_Timekeep_Total = GLOBAL_Timekeep_Total + rst.Fields("hrs_col") +
> (rst.Fields("mins_col") / 60)
> rst.MoveNext
> Loop
>

Shouldn't that be yyyy-mm-dd, Alex? (In other words, aren't date separators
necessary?)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Alex Dybenko" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
> try to pass dates as varchar in format yyyymmdd - this should work
>
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://alexdyb.blogspot.com
> http://www.PointLtd.com
>
>
> "Martin" <(E-Mail Removed)> wrote in message
> news:8DCF5FEA-C931-4438-96CA-(E-Mail Removed)...
> >I have a function created in ADP using ADODB to talk to our SQL Server.
> >The
> > function is now required to accept two parameters, BeginDate and
EndDate,
> > both are in date format of 'mm/dd/yyyy'. I have trouble trying to pass
> > these
> > date parameters and hope you could help.
> > Thank you!
> >
> > The line below, I changed the adInteger to adDBDate and changed the 4 to
> > 8,
> > and still didn't work.
> >
> > .Parameters.Append .CreateParameter("@DateValue", adInteger, _
> > adParamInput, 4, BeginMonth)
> >
> >
> > 'The following is the full function module for your reference.
> > '-------------------------------------------------------------------
> > Function DateRangeTaken(ByVal BeginDate, EndDate As Date, ByVal PlanCode
> > As
> > Integer, ByVal EmplID As string) As Currency
> >
> > Dim GLOBAL_Timekeep_Total As Currency
> >
> > GLOBAL_Timekeep_Total = 0
> >
> > Set cmd = New ADODB.Command
> > Set rst = New ADODB.Recordset
> > If EntireMonth = 1 Then
> > With cmd
> > .ActiveConnection = CurrentProject.Connection
> > .CommandText = "SQLProcedureA"
> > .CommandType = adCmdStoredProc
> > .Parameters.Append .CreateParameter("@ID", adChar, _
> > adParamInput, 20, EmplID)
> > .Parameters.Append .CreateParameter("@DateValue", adInteger, _
> > adParamInput, 4, BeginDate)
> > .Parameters.Append .CreateParameter("@DateValue2r", adInteger, _
> > adParamInput, 4, EndDate)
> > .Parameters.Append .CreateParameter("@Catergory", adInteger, _
> > adParamInput, 4, PlanCode)
> > Set rst = .Execute
> >
> > End With
> >
> >
> > Do While Not rst.EOF
> >
> > GLOBAL_Timekeep_Total = GLOBAL_Timekeep_Total + rst.Fields("hrs_col") +
> > (rst.Fields("mins_col") / 60)
> > rst.MoveNext
> > Loop
> >
>

Douglas J Steele wrote in message
<(E-Mail Removed)> :
> Shouldn't that be yyyy-mm-dd, Alex? (In other words, aren't date separators
> necessary?)
>

I think SQL-server will accept that string as a date. Bol (2005) states
this, and the yyyy-mm-dd format to be valid ISO 8601 date formats,
which
also seems consistant with http://en.wikipedia.org/wiki/ISO_8601 (basic
vs extended "for human readability" formats)

But this would mean converting the date to string before passing it as
a parameter, and I think, have SQL server perform implicit conversion
to date again, within the SP (which shouldn't be a problem, though), vs
your recommandation of using adDate, which is also what I would use.

--
Roy-Vidar

Dear Alex, Douglas and Royvidar:
Thank you for your help! It finally worked!!!
Although I wasn't able to use the adDate apporach with two date formats
suggested, when I apply Royvidar's approach, I've used the adChar in the
ADODB statement, and keep the parameter of SQL Stored Procedure in
smalldatetime data type, and let the 'implicit' conversion run the magic,
and waalaaa!! It ran beautifully and gave me a precise dataset.
Thanks again for working together on this issue.
Martin

Hi Doug,
according to SQL BOL (and my experience) - yyyymmdd - is a format which
always correctly understandable by SQL Server. At me it always work correct

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:(E-Mail Removed)...
> Shouldn't that be yyyy-mm-dd, Alex? (In other words, aren't date
> separators
> necessary?)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Alex Dybenko" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi,
>> try to pass dates as varchar in format yyyymmdd - this should work
>>
>>
>> --
>> Best regards,
>> ___________
>> Alex Dybenko (MVP)
>> http://alexdyb.blogspot.com
>> http://www.PointLtd.com
>>
>>
>> "Martin" <(E-Mail Removed)> wrote in message
>> news:8DCF5FEA-C931-4438-96CA-(E-Mail Removed)...
>> >I have a function created in ADP using ADODB to talk to our SQL Server.
>> >The
>> > function is now required to accept two parameters, BeginDate and
> EndDate,
>> > both are in date format of 'mm/dd/yyyy'. I have trouble trying to pass
>> > these
>> > date parameters and hope you could help.
>> > Thank you!
>> >
>> > The line below, I changed the adInteger to adDBDate and changed the 4
>> > to
>> > 8,
>> > and still didn't work.
>> >
>> > .Parameters.Append .CreateParameter("@DateValue", adInteger, _
>> > adParamInput, 4, BeginMonth)
>> >
>> >
>> > 'The following is the full function module for your reference.
>> > '-------------------------------------------------------------------
>> > Function DateRangeTaken(ByVal BeginDate, EndDate As Date, ByVal
>> > PlanCode
>> > As
>> > Integer, ByVal EmplID As string) As Currency
>> >
>> > Dim GLOBAL_Timekeep_Total As Currency
>> >
>> > GLOBAL_Timekeep_Total = 0
>> >
>> > Set cmd = New ADODB.Command
>> > Set rst = New ADODB.Recordset
>> > If EntireMonth = 1 Then
>> > With cmd
>> > .ActiveConnection = CurrentProject.Connection
>> > .CommandText = "SQLProcedureA"
>> > .CommandType = adCmdStoredProc
>> > .Parameters.Append .CreateParameter("@ID", adChar, _
>> > adParamInput, 20, EmplID)
>> > .Parameters.Append .CreateParameter("@DateValue", adInteger, _
>> > adParamInput, 4, BeginDate)
>> > .Parameters.Append .CreateParameter("@DateValue2r", adInteger, _
>> > adParamInput, 4, EndDate)
>> > .Parameters.Append .CreateParameter("@Catergory", adInteger, _
>> > adParamInput, 4, PlanCode)
>> > Set rst = .Execute
>> >
>> > End With
>> >
>> >
>> > Do While Not rst.EOF
>> >
>> > GLOBAL_Timekeep_Total = GLOBAL_Timekeep_Total + rst.Fields("hrs_col") +
>> > (rst.Fields("mins_col") / 60)
>> > rst.MoveNext
>> > Loop
>> >
>>
>
>