ADO SQL statement coming back wrong

[David]

Someone please help. I have a peice of code (below) that when run from
VB returns a value of -2 but when I run the exact same SQL statement
from Access it produces the correct value of 7. Can anyone help?

Table: tblResp
Column 1: VarName (Char, 8)
Column 2: Resp (Char, 40)

VarName Resp
M5d "0"
M5h1 "-9"
CGF4a "0"
CGF4b "0"
CGF4c "0"
CGF4d "0"
CGF4e "0"
CGF4f "7"

SQL Code: Select Sum(CDBL(tblRes p.Resp)) As Response From tblResp
Where Resp Not Like '-*'

[Steve Gerrard]

Re: ADO SQL statement coming back wrong

For some reason, when using ADO (with Access files, anywasy), the LIKE keyword
uses the %
character instead of the *.

"David" <dhodgkins@ches tnut.org> wrote in message
news:83b47ff1.0 309291214.33a94 db5@posting.goo gle.com...[color=blue]
> Someone please help. I have a peice of code (below) that when run from
> VB returns a value of -2 but when I run the exact same SQL statement
> from Access it produces the correct value of 7. Can anyone help?
>
> Table: tblResp
> Column 1: VarName (Char, 8)
> Column 2: Resp (Char, 40)
>
> VarName Resp
> M5d "0"
> M5h1 "-9"
> CGF4a "0"
> CGF4b "0"
> CGF4c "0"
> CGF4d "0"
> CGF4e "0"
> CGF4f "7"
>
> SQL Code: Select Sum(CDBL(tblRes p.Resp)) As Response From tblResp
> Where Resp Not Like '-*'[/color]

[Jeramie]

Re: ADO SQL statement coming back wrong

Are you executing the code against Access or against SQLServer? If
SQLServer, you need to use the CONVERT() TSQL function call. In
general, you cannot mix and match VB functions with SQL (unless you're
only doing Access databases...).

If you're doing VB stuff, will want to stick to ANSI SQL.

I hope this is helpful...
Jeramie

dhodgkins@chest nut.org (David) wrote in message news:<83b47ff1. 0309291214.33a9 4db5@posting.go ogle.com>...[color=blue]
> Someone please help. I have a peice of code (below) that when run from
> VB returns a value of -2 but when I run the exact same SQL statement
> from Access it produces the correct value of 7. Can anyone help?
>
> Table: tblResp
> Column 1: VarName (Char, 8)
> Column 2: Resp (Char, 40)
>
> VarName Resp
> M5d "0"
> M5h1 "-9"
> CGF4a "0"
> CGF4b "0"
> CGF4c "0"
> CGF4d "0"
> CGF4e "0"
> CGF4f "7"
>
> SQL Code: Select Sum(CDBL(tblRes p.Resp)) As Response From tblResp
> Where Resp Not Like '-*'[/color]

[nachiket2k2]

same problem again. thnx 4 solution.

Dear Steve Gerrard,

I was facing the same problem of 'LIKE *' in ADO sql string that david was facing. Thank you very much for your guidence. i worked very hard to solve this problem but no one was knowing. now u helped me. thnx. :) (people developed ADO/MS Access are careless, i should say. they dont know how hard the programmers work to make software, using ADO/Access. they should solve this problem.)

Are you executing the code against Access or against SQLServer? If
SQLServer, you need to use the CONVERT() TSQL function call. In
general, you cannot mix and match VB functions with SQL (unless you're
only doing Access databases...).

If you're doing VB stuff, will want to stick to ANSI SQL.

I hope this is helpful...
Jeramie

dhodgkins@chest nut.org (David) wrote in message news:<83b47ff1. 0309291214.33a9 4db5@posting.go ogle.com>...[color=blue]
> Someone please help. I have a peice of code (below) that when run from
> VB returns a value of -2 but when I run the exact same SQL statement
> from Access it produces the correct value of 7. Can anyone help?
>
> Table: tblResp
> Column 1: VarName (Char, 8)
> Column 2: Resp (Char, 40)
>
> VarName Resp
> M5d "0"
> M5h1 "-9"
> CGF4a "0"
> CGF4b "0"
> CGF4c "0"
> CGF4d "0"
> CGF4e "0"
> CGF4f "7"
>
> SQL Code: Select Sum(CDBL(tblRes p.Resp)) As Response From tblResp
> Where Resp Not Like '-*'[/color]

[nachiket2k2]

same problem again, thnx 4 solution.

Dear Steve Gerrard,

I was facing the same problem of 'LIKE *' in ADO sql string that david was facing. Thank you very much for your guidence. i worked very hard to solve this problem but no one was knowing. now u helped me. thnx. (people developed ADO/MS Access are careless, i should say. they dont know how hard the programmers work to make software, using ADO/Access. they should solve this problem.)

For some reason, when using ADO (with Access files, anywasy), the LIKE keyword
uses the %
character instead of the *.

"David" <dhodgkins@ches tnut.org> wrote in message
news:83b47ff1.0 309291214.33a94 db5@posting.goo gle.com...[color=blue]
> Someone please help. I have a peice of code (below) that when run from
> VB returns a value of -2 but when I run the exact same SQL statement
> from Access it produces the correct value of 7. Can anyone help?
>
> Table: tblResp
> Column 1: VarName (Char, 8)
> Column 2: Resp (Char, 40)
>
> VarName Resp
> M5d "0"
> M5h1 "-9"
> CGF4a "0"
> CGF4b "0"
> CGF4c "0"
> CGF4d "0"
> CGF4e "0"
> CGF4f "7"
>
> SQL Code: Select Sum(CDBL(tblRes p.Resp)) As Response From tblResp
> Where Resp Not Like '-*'[/color]