Convert SQL 6.5 query into Ms Access 97 query

[wendywam]

Hi
Please help me convert the following sql query into ms access 97 query.
Your help is appreciated.

select convert(int,c.ref2)
from acthist a,acthist c
WHERE a.ref1 = '123456'
and c.ref1 = 'asd123'
and c.ref2 <> '-1'
AND c.actdate > = 'somedate value'
and isnumeric(c.ref2) = 1
group by c.Ref2

 

[lupins46]

select clng(c.ref)

from acthist a,acthist c
WHERE a.ref1 = '123456'
and c.ref1 = 'asd123'
and c.ref2 <> '-1'

AND c.actdate > = #somedate#

and isnumeric(c.ref2) = true

group by c.Ref2

 

[Crowley16]

SELECT Cint(c.ref2) FROM acthist AS a, acthist AS c
WHERE a.ref1 = '123456' and c.ref1 = 'asd123' and c.ref2 <> '-1'AND c.actdate > = #somedate value# and isnumeric(c.ref2);

 

[wendywam]

Thanks guys it works both ways.
Now, How do I insert a record into one table using two select queries joined by a UNION. This should be in access.

e.g.
insert into Temp
select val1 from table1 where val2 = '123456'
UNION
select clng(c.ref)
from acthist a,acthist c
WHERE a.ref1 = '123456'
and c.ref1 = 'asd123'
and c.ref2 <> '-1'
AND c.actdate > = #somedate#
and isnumeric(c.ref2) = true
group by c.Ref2

I am getting syntax error(missing operator)
I dont know whether i am missing brackets somewhere.

 

[Crowley16]

your union queries need to have the same fields...

so, just make sure the 2 union selects have the same number of fields, create an empty field if necessary and give the 2 fields the SAME NAME

and you need () around the selects, I think...

(
select val1 AS val from table1 where val2 = '123456'
UNION
select clng(c.ref) AS val
from acthist a,acthist c...
)

 

[PHV]

insert into Temp
should be:
insert into Temp (NameOfField)
and Temp must be an existing table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[wendywam]

Hi Guys
I have tried everything you advised me with but it wouldnt work.

insert into Temp (val)
(
select val1 AS val from table1 where val2 = '123456'
UNION
select clng(c.ref) AS val
from acthist a,acthist c...
)

Temp is an Existing table.

 

[PHV]

Try to create a saved Union query and then INSERT from it.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Crowley16]

you're forgetting the AS between acthist a, and acthist c