open recordset error

I get a 'data type mismatch in criteria expression' when trying to open a recordset from a query that uses a variable in the match. It works fine if I use the query without the varible. The error occurs at the rs1.open line, not the sqlstmt line.

ticket=1002
db_file = App.Path & "\goldpos.mdb"

Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
cn.Open

'sqlstmt = "SELECT * FROM orders where [invoice] = 1002" THIS WORKS
sqlstmt = "SELECT * FROM orders where [invoice] = '" & ticket & "'" 'THIS DOESN'T WORK

Set rs1 = New ADODB.Recordset

rs1.Open sqlstmt, cn, adOpenStatic, adLockOptimistic, adCmdText

Set DataGrid1.DataSource = rs1Originally posted by JerryB
'sqlstmt = "SELECT * FROM orders where [invoice] = 1002" THIS WORKS
sqlstmt = "SELECT * FROM orders where [invoice] = '" & ticket & "'" 'THIS DOESN'T WORK

Why are you adding single quotes around ticket if it just wants a number? Why not:

sqlstmt = "SELECT * FROM orders where [invoice] = " & ticket

Only one expression can be specified in the select list when the s

Update COntract
Set DispositionDate = (SELECT Disp_Date,Contract_ID
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
This Query gives me an Error as
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
My Select Query does return more then 1 record. is their any way to perform
this Query in a better way as the select Query retrieves more then 200,000
records.
LinkedServer = Linked server from one Server to another
Thanks
samayWhat is the purpose of Contract_ID in the inner SELECT?
Look at the query you're running. You're saying, basically, "... SET
some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
the SET statement supposed to do with it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> Update COntract
> Set DispositionDate = (SELECT Disp_Date,Contract_ID
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> This Query gives me an Error as
> Only one expression can be specified in the select list when the subquery
is
> not introduced with EXISTS.
> My Select Query does return more then 1 record. is their any way to
perform
> this Query in a better way as the select Query retrieves more then 200,000
> records.
> LinkedServer = Linked server from one Server to another
> Thanks
> samay|||Absolutely right My mistake
I have updated my Query and if i run the below Query it Gives ame an Error as
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Update JAX_EOL_COntract
Set dDispositionDate = (SELECT Disp_Date
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
please advice
Thanks
"Aaron [SQL Server MVP]" wrote:
> What is the purpose of Contract_ID in the inner SELECT?
> Look at the query you're running. You're saying, basically, "... SET
> some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
> the SET statement supposed to do with it?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
> wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> >
> > Update COntract
> > Set DispositionDate = (SELECT Disp_Date,Contract_ID
> > FROM LinkedServer.Closed_Contract
> > where Disp_Date is not Null)
> >
> > This Query gives me an Error as
> > Only one expression can be specified in the select list when the subquery
> is
> > not introduced with EXISTS.
> >
> > My Select Query does return more then 1 record. is their any way to
> perform
> > this Query in a better way as the select Query retrieves more then 200,000
> > records.
> >
> > LinkedServer = Linked server from one Server to another
> >
> > Thanks
> > samay
>
>|||Well if there are two dates in Closed_Contract where disp_date is not null,
let's say 20041110 and 20041108, which one do you want? You can use SELECT
MIN() or SELECT MAX() or some other condition which enforces only one row to
be returned.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:92C047DD-0247-4754-B9E3-1F006F30FCC8@.microsoft.com...
> Absolutely right My mistake
> I have updated my Query and if i run the below Query it Gives ame an Error
as
> Subquery returned more than 1 value. This is not permitted when the
subquery
> follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
> The statement has been terminated.
> Update JAX_EOL_COntract
> Set dDispositionDate = (SELECT Disp_Date
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> please advice
> Thanks
> "Aaron [SQL Server MVP]" wrote:
> > What is the purpose of Contract_ID in the inner SELECT?
> >
> > Look at the query you're running. You're saying, basically, "... SET
> > some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What
is
> > the SET statement supposed to do with it?
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "KritiVerma@.hotmail.com"
<KritiVermahotmailcom@.discussions.microsoft.com>
> > wrote in message
news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> > >
> > > Update COntract
> > > Set DispositionDate = (SELECT Disp_Date,Contract_ID
> > > FROM LinkedServer.Closed_Contract
> > > where Disp_Date is not Null)
> > >
> > > This Query gives me an Error as
> > > Only one expression can be specified in the select list when the
subquery
> > is
> > > not introduced with EXISTS.
> > >
> > > My Select Query does return more then 1 record. is their any way to
> > perform
> > > this Query in a better way as the select Query retrieves more then
200,000
> > > records.
> > >
> > > LinkedServer = Linked server from one Server to another
> > >
> > > Thanks
> > > samay
> >
> >
> >|||Following up on Aaron's comment, possibly you need to add a restriction to
the subquery so it only returns the record for the current contract.
Something like
Update JAX_EOL_COntract JC
Set dDispositionDate = (SELECT Disp_Date
FROM LinkedServer.Closed_Contract CC
where Disp_Date is not Null and JC.ContractId = CC.ContractId)
Mike A.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:echAR10xEHA.2016@.TK2MSFTNGP15.phx.gbl...
> Well if there are two dates in Closed_Contract where disp_date is not
> null,
> let's say 20041110 and 20041108, which one do you want? You can use
> SELECT
> MIN() or SELECT MAX() or some other condition which enforces only one row
> to
> be returned.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
> wrote in message
> news:92C047DD-0247-4754-B9E3-1F006F30FCC8@.microsoft.com...
>> Absolutely right My mistake
>> I have updated my Query and if i run the below Query it Gives ame an
>> Error
> as
>> Subquery returned more than 1 value. This is not permitted when the
> subquery
>> follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
>> The statement has been terminated.
>> Update JAX_EOL_COntract
>> Set dDispositionDate = (SELECT Disp_Date
>> FROM LinkedServer.Closed_Contract
>> where Disp_Date is not Null)
>> please advice
>> Thanks
>> "Aaron [SQL Server MVP]" wrote:
>> > What is the purpose of Contract_ID in the inner SELECT?
>> >
>> > Look at the query you're running. You're saying, basically, "... SET
>> > some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What
> is
>> > the SET statement supposed to do with it?
>> >
>> > --
>> > http://www.aspfaq.com/
>> > (Reverse address to reply.)
>> >
>> >
>> >
>> >
>> > "KritiVerma@.hotmail.com"
> <KritiVermahotmailcom@.discussions.microsoft.com>
>> > wrote in message
> news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
>> > >
>> > > Update COntract
>> > > Set DispositionDate = (SELECT Disp_Date,Contract_ID
>> > > FROM LinkedServer.Closed_Contract
>> > > where Disp_Date is not Null)
>> > >
>> > > This Query gives me an Error as
>> > > Only one expression can be specified in the select list when the
> subquery
>> > is
>> > > not introduced with EXISTS.
>> > >
>> > > My Select Query does return more then 1 record. is their any way to
>> > perform
>> > > this Query in a better way as the select Query retrieves more then
> 200,000
>> > > records.
>> > >
>> > > LinkedServer = Linked server from one Server to another
>> > >
>> > > Thanks
>> > > samay
>> >
>> >
>> >
>|||> Following up on Aaron's comment, possibly you need to add a restriction to
> the subquery so it only returns the record for the current contract.
> Something like
> Update JAX_EOL_COntract JC
> Set dDispositionDate = (SELECT Disp_Date
> FROM LinkedServer.Closed_Contract CC
> where Disp_Date is not Null and JC.ContractId = CC.ContractId)
Yes, this will work, assuming a one-to-one relationship (which, based on
table names, seems likely).

Only one expression can be specified in the select list when the s

Update COntract
Set DispositionDate = (SELECT Disp_Date,Contract_ID
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
This Query gives me an Error as
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
My Select Query does return more then 1 record. is their any way to perform
this Query in a better way as the select Query retrieves more then 200,000
records.
LinkedServer = Linked server from one Server to another
Thanks
samay
What is the purpose of Contract_ID in the inner SELECT?
Look at the query you're running. You're saying, basically, "... SET
some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
the SET statement supposed to do with it?
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> Update COntract
> Set DispositionDate = (SELECT Disp_Date,Contract_ID
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> This Query gives me an Error as
> Only one expression can be specified in the select list when the subquery
is
> not introduced with EXISTS.
> My Select Query does return more then 1 record. is their any way to
perform
> this Query in a better way as the select Query retrieves more then 200,000
> records.
> LinkedServer = Linked server from one Server to another
> Thanks
> samay

Only one expression can be specified in the select list when the s

Update COntract
Set DispositionDate = (SELECT Disp_Date,Contract_ID
FROM LinkedServer.Closed_Contract
where Disp_Date is not Null)
This Query gives me an Error as
Only one expression can be specified in the select list when the subquery is
not introduced with EXISTS.
My Select Query does return more then 1 record. is their any way to perform
this Query in a better way as the select Query retrieves more then 200,000
records.
LinkedServer = Linked server from one Server to another
Thanks
samayWhat is the purpose of Contract_ID in the inner SELECT?
Look at the query you're running. You're saying, basically, "... SET
some_date = (SELECT '20041110', 5) ... " ... what is that ,5 for? What is
the SET statement supposed to do with it?
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:7FA9D7FF-A197-41FB-A931-780369D33180@.microsoft.com...
> Update COntract
> Set DispositionDate = (SELECT Disp_Date,Contract_ID
> FROM LinkedServer.Closed_Contract
> where Disp_Date is not Null)
> This Query gives me an Error as
> Only one expression can be specified in the select list when the subquery
is
> not introduced with EXISTS.
> My Select Query does return more then 1 record. is their any way to
perform
> this Query in a better way as the select Query retrieves more then 200,000
> records.
> LinkedServer = Linked server from one Server to another
> Thanks
> samay

Only get dates

Can anyone help me create an expression for a column in a query that holds
only the date portion of a smalldatetime column? I've tried DatePart and had
a look around for other functions but I couldn't find anything. Thanks in
advance,
On Thu, 26 Aug 2004 22:19:04 -0700, Gary K wrote:

>Can anyone help me create an expression for a column in a query that holds
>only the date portion of a smalldatetime column? I've tried DatePart and had
>a look around for other functions but I couldn't find anything. Thanks in
>advance,
Hi Gary,
Use CONVERT to convert it to character, using a format without time (or
using a character column short enough that the time won't fit). If you
need it in datetime format without the time (you'll get the default time:
midnight), cast the result back to datetime format.
SELECT getdate()
SELECT CONVERT (varchar(8), GETDATE(), 112)
SELECT CAST(CONVERT (varchar(8), GETDATE(), 112) AS datetime)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo, that takes care of the main problem I had. I still have to
convert it back into a date variable but thats easy now.
"Hugo Kornelis" wrote:

> On Thu, 26 Aug 2004 22:19:04 -0700, Gary K wrote:
>
> Hi Gary,
> Use CONVERT to convert it to character, using a format without time (or
> using a character column short enough that the time won't fit). If you
> need it in datetime format without the time (you'll get the default time:
> midnight), cast the result back to datetime format.
> SELECT getdate()
> SELECT CONVERT (varchar(8), GETDATE(), 112)
> SELECT CAST(CONVERT (varchar(8), GETDATE(), 112) AS datetime)
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Only for Gurus -- Analysis MDX Query help needed

I need a expression to get the name of the Member of a dimension.
For example .. if I have a Time Dimension ... and Child in hierarchy are Months.. I would like the result as
January
February
.....
December
Any Ideas ?http://www.winnetmag.com/Article/ArticleID/16311/16311.html

HTH