ado parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I've been using parameters with an ado command and it works great with until
I add multiple criteria for one value. Any ideas on how to make this work. If
I make the strPeriodPub just ="Oct" it works great but it does not like the
"or" statement. I've experimented with lots of syntax so I don't think that
is it. It may be something with paramenters and commands that I do not know
about.

Thanks

Code
--------------------------------------------------------------------------------

Set cmd = New ADODB.Commandstr
FiscalYearPub = 2003
strPeriodPub = "Oct or Sep"
With cmd
..ActiveConnection = CurrentProject.Connection
..Properties("Jet OLEDB:Stored Query") = True
..CommandText = "qryDetailPL"
..Parameters("[GetPeriod]") = strPeriodPub
..Parameters("[GetFiscalYear]") = Val(strFiscalYearPub)
..Parameters("[Getsource]") = strSource
..Parameters("[GetPartType]") = strPartType
..Parameters("[GetProduct]") = strProduct
Set rst = .Execute
End With
 
You can't Or together values like that.

Your WHERE clause needs to be something like

WHERE Field1 = "Oct" Or Field1 = "Sep"

whereas you'll be generating something like

WHERE Field1 = "Oct Or Sep"


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


DataDay said:
Hello,

I've been using parameters with an ado command and it works great with until
I add multiple criteria for one value. Any ideas on how to make this work. If
I make the strPeriodPub just ="Oct" it works great but it does not like the
"or" statement. I've experimented with lots of syntax so I don't think that
is it. It may be something with paramenters and commands that I do not know
about.

Thanks

Code:
--------------------------------------------------------------------------
------

Set cmd = New ADODB.Commandstr
FiscalYearPub = 2003
strPeriodPub = "Oct or Sep"
With cmd
.ActiveConnection = CurrentProject.Connection
.Properties("Jet OLEDB:Stored Query") = True
.CommandText = "qryDetailPL"
.Parameters("[GetPeriod]") = strPeriodPub
.Parameters("[GetFiscalYear]") = Val(strFiscalYearPub)
.Parameters("[Getsource]") = strSource
.Parameters("[GetPartType]") = strPartType
.Parameters("[GetProduct]") = strProduct
Set rst = .Execute
End With
 
I'm not sure that you can using ADO (and, I hate to admit, I don't remember
how to do it using DAO!)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



DataDay said:
Thanks Doug,

So how do I apply that to

.Parameters("[GetPeriod]") = strPeriodPub

in order to or together the criteria?



Douglas J. Steele said:
You can't Or together values like that.

Your WHERE clause needs to be something like

WHERE Field1 = "Oct" Or Field1 = "Sep"

whereas you'll be generating something like

WHERE Field1 = "Oct Or Sep"


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


DataDay said:
Hello,

I've been using parameters with an ado command and it works great with until
I add multiple criteria for one value. Any ideas on how to make this
work.
If
I make the strPeriodPub just ="Oct" it works great but it does not
like
the
"or" statement. I've experimented with lots of syntax so I don't
think
that
is it. It may be something with paramenters and commands that I do
not
know
about.

Thanks

Code:
--------------------------------------------------------------------------
------

Set cmd = New ADODB.Commandstr
FiscalYearPub = 2003
strPeriodPub = "Oct or Sep"
With cmd
.ActiveConnection = CurrentProject.Connection
.Properties("Jet OLEDB:Stored Query") = True
.CommandText = "qryDetailPL"
.Parameters("[GetPeriod]") = strPeriodPub
.Parameters("[GetFiscalYear]") = Val(strFiscalYearPub)
.Parameters("[Getsource]") = strSource
.Parameters("[GetPartType]") = strPartType
.Parameters("[GetProduct]") = strProduct
Set rst = .Execute
End With
 
No big e.

I was just informed of a some major scope creep/spec change so I've got to
rethink a lot of stuff, so I may be able to rethink ado parameters right out
of the project.

Thanks for your help.

Douglas J. Steele said:
I'm not sure that you can using ADO (and, I hate to admit, I don't remember
how to do it using DAO!)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



DataDay said:
Thanks Doug,

So how do I apply that to

.Parameters("[GetPeriod]") = strPeriodPub

in order to or together the criteria?



Douglas J. Steele said:
You can't Or together values like that.

Your WHERE clause needs to be something like

WHERE Field1 = "Oct" Or Field1 = "Sep"

whereas you'll be generating something like

WHERE Field1 = "Oct Or Sep"


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Hello,

I've been using parameters with an ado command and it works great with
until
I add multiple criteria for one value. Any ideas on how to make this work.
If
I make the strPeriodPub just ="Oct" it works great but it does not like
the
"or" statement. I've experimented with lots of syntax so I don't think
that
is it. It may be something with paramenters and commands that I do not
know
about.

Thanks

Code:
--------------------------------------------------------------------------
------

Set cmd = New ADODB.Commandstr
FiscalYearPub = 2003
strPeriodPub = "Oct or Sep"
With cmd
.ActiveConnection = CurrentProject.Connection
.Properties("Jet OLEDB:Stored Query") = True
.CommandText = "qryDetailPL"
.Parameters("[GetPeriod]") = strPeriodPub
.Parameters("[GetFiscalYear]") = Val(strFiscalYearPub)
.Parameters("[Getsource]") = strSource
.Parameters("[GetPartType]") = strPartType
.Parameters("[GetProduct]") = strProduct
Set rst = .Execute
End With
 
Back
Top