RE: Incorecctly passing Date Parameter

I finally found the problem, it was the fact that the format function was
used on the Day field. This was applied when I created the cross tab with
the wizard. After I removed the formatting the comparisons all worked
correctly. Once again the fact that I hate wizards bites me in the rear.

"Greg P" wrote:

Here is my query

PARAMETERS prmName Text ( 255 ), prmDayEnd DateTime;
TRANSFORM Sum(qryForm40A.Hours) AS CountOfHours
SELECT qryForm40A.Employee, qryForm40A.Project
FROM qryForm40A
WHERE (((Format([Day],"Short Date"))<=[prmDayEnd]) AND
((qryForm40A.Employee)=[prmName]))
GROUP BY qryForm40A.Employee, qryForm40A.Project
ORDER BY Format([Day],"Short Date")
PIVOT Format([Day],"Short Date");


"Greg P" wrote:

Here is all of my code. I have the first date parameter commented out here,
I'm trying to the the second date and the Name tow work. If I comment out
the second date on only use the name parameter it works fine. It has to be
something to do with the date parameter???

'set up the command object
Set objCmd = New ADODB.Command
objCmd.ActiveConnection = cnxnAccess
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "ctbForm40A"

'objCmd.Parameters.Append objCmd.CreateParameter("prmDayBegin", adDate,
adParamInput, 10, dteWeek - 7)

objCmd.Parameters.Append objCmd.CreateParameter("prmDayEnd", adDate, , ,
dteWeek)

objCmd.Parameters.Append objCmd.CreateParameter("prmName", adVarWChar, ,
200, strName)

'set up the recordset
Set rsDtgTime = New ADODB.Recordset
rsDtgTime.CursorLocation = adUseClient

rsDtgTime.Open objCmd, , adOpenDynamic, adLockOptimistic

Set dtgTime.DataSource = rsDtgTime

"Greg P" wrote:

Some may have seen this on an earlier post yet this is a second issue snad
should be addressed seperatly.

I am passing a parameter to access throught a command object. I assign a
date parameter using the following code.

objCmd.Parameters.Append objCmd.CreateParameter("prmDayEnd", adDate, , ,
dteWeek)

When I view the parameter in the watch windows everythign looks good but I
recieve an empty record set back. If I type in the same value that is listed
in the watch window in Access I do recieve the correct response?

Is there some trick with passing dates that I don't know of? The value is
lised in the watch window as "#4/14/06#" (with out the quotes). This looks
right to me as far as I understood for passing a date value to access. When
in access I type in just he date with outh the #'s and get the correct
response?

Any ideas here? I am going to set up a very simple test to make sure I'm not
missing something stupid but... I'm stuck! I do have a text value getting
passed with out any issues to the same query.

Thanks,
.