By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,190 Members | 1,063 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,190 IT Pros & Developers. It's quick & easy.

Cannot send ADO Date/Time parameter to a parameterized query

P: n/a
I have a table with 3 fields: UnitTime, Unit, and UnitCount
in an MDB database.

I have a query, qryWriteUnitOccupancy, that appends a record
to that table using 2 parameters: TimePoint, and PointUnit

I am using the following code to write a record:
For dUnitHour = FromDate To ToDate Step 1 / 24
With cmdUnit
.ActiveConnection = cnnUnit
.CommandType = adCmdText
.CommandText = "execute qryWriteUnitOccupancy"
Set prmUnit = .CreateParameter("TimePoint",
adDBTimeStamp, adParamInput, , Format(dUnitHour, "mm/dd/yyyy
hh:nn"))
.Parameters.Append prmUnit
Set prmUnit = .CreateParameter("PointUnit", adChar,
adParamInput, 5, sUnit)
.Parameters.Append prmUnit
.Execute
End With
Next dUnitHour

I run this, and no matter what the time is in dUnitHour, it
gets written to the table as a truncated date (no time). I
have stepped through this and watched the value of dUnitHour
step through hours, and I have run the query directly,
passing date/time and unit, and it writes just fine.

Any ideas?

Thanks,

Kevin Nechodom
University of Utah

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Try adDate, rather than adDBTimeStamp. They're not the same thing, and
AFAIK, Access doesn't support adDBTimeStamp.

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

"Kevin Nechodom" <Ke************@nospamhsc.utah.edu> wrote in message
news:d4**********@vegh.ks.cc.utah.edu...
I have a table with 3 fields: UnitTime, Unit, and UnitCount
in an MDB database.

I have a query, qryWriteUnitOccupancy, that appends a record
to that table using 2 parameters: TimePoint, and PointUnit

I am using the following code to write a record:
For dUnitHour = FromDate To ToDate Step 1 / 24
With cmdUnit
.ActiveConnection = cnnUnit
.CommandType = adCmdText
.CommandText = "execute qryWriteUnitOccupancy"
Set prmUnit = .CreateParameter("TimePoint",
adDBTimeStamp, adParamInput, , Format(dUnitHour, "mm/dd/yyyy
hh:nn"))
.Parameters.Append prmUnit
Set prmUnit = .CreateParameter("PointUnit", adChar,
adParamInput, 5, sUnit)
.Parameters.Append prmUnit
.Execute
End With
Next dUnitHour

I run this, and no matter what the time is in dUnitHour, it
gets written to the table as a truncated date (no time). I
have stepped through this and watched the value of dUnitHour
step through hours, and I have run the query directly,
passing date/time and unit, and it writes just fine.

Any ideas?

Thanks,

Kevin Nechodom
University of Utah

Nov 13 '05 #2

P: n/a
I wasn't aware of the limitations of adDBTimeStamp usage on
Access queries. Learn something new! Anyway, the problem
was the fact that I was trying to reuse an ADODB.Command
object in a loop, and it just spit out the same thing every
time!

Thanks!

Kevin
Douglas J. Steele<NOSPAM_djsteele@NOSPAM_canada.com>
4/25/2005 6:53:25 PM >>>Try adDate, rather than adDBTimeStamp. They're not the samething, and
AFAIK, Access doesn't support adDBTimeStamp.

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

"Kevin Nechodom" <Ke************@nospamhsc.utah.edu> wrote
in message
news:d4**********@vegh.ks.cc.utah.edu...
I have a table with 3 fields: UnitTime, Unit, andUnitCount
in an MDB database.

I have a query, qryWriteUnitOccupancy, that appends a

record
to that table using 2 parameters: TimePoint, and

PointUnit

I am using the following code to write a record:
For dUnitHour = FromDate To ToDate Step 1 / 24
With cmdUnit
.ActiveConnection = cnnUnit
.CommandType = adCmdText
.CommandText = "execute qryWriteUnitOccupancy"
Set prmUnit = .CreateParameter("TimePoint",
adDBTimeStamp, adParamInput, , Format(dUnitHour,

"mm/dd/yyyy
hh:nn"))
.Parameters.Append prmUnit
Set prmUnit = .CreateParameter("PointUnit",

adChar,
adParamInput, 5, sUnit)
.Parameters.Append prmUnit
.Execute
End With
Next dUnitHour

I run this, and no matter what the time is in dUnitHour,

it
gets written to the table as a truncated date (no time).

I
have stepped through this and watched the value of

dUnitHour
step through hours, and I have run the query directly,
passing date/time and unit, and it writes just fine.

Any ideas?

Thanks,

Kevin Nechodom
University of Utah

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.