Problems with subquery

[deangelis]

Hi,
The problem is that i've to specified two fields before the IN.
This is the code in VB:
"SELECT trajectory_id, traj_type, att_time FROM TRAJ_SCENARIO WHERE scenario_id = '"& _
scenarioName & "~bak' AND ((trajectory_id, att_time) NOT IN (SELECT trajectory_id, att_time from" & _
" TRAJ_SCENARIO WHERE scenario_id = '" & scenarioName & "~bak' and trajectory_id = '" & _
TrajName & "' and att_time = " & AttTime & ")) ORDER BY trajectory_id"

The error is on the ",".
The question is:
Is it possible to specify two fields before the NOT IN statement????


Ciao da
N@poleone

 

[KenReay]

Hi

You could try

"SELECT trajectory_id, traj_type, att_time FROM TRAJ_SCENARIO WHERE scenario_id = '"& _
scenarioName & "~bak' AND ((trajectory_id & att_time) NOT IN (SELECT trajectory_id & att_time As A from" & _
" TRAJ_SCENARIO WHERE scenario_id = '" & scenarioName & "~bak' and trajectory_id = '" & _
TrajName & "' and att_time = " & AttTime & ")) ORDER BY trajectory_id"


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[deangelis]

Sorry, maybe ow i'm OT.
The Idea of concatenating the two fields is wonderful.
Now the problem is:
is it possible in MS Access???
Your solution returs me a error in VB and I don't know if is fault of VB or Access
This is the new code:

"SELECT trajectory_id, traj_type, att_time FROM TRAJ_SCENARIO WHERE scenario_id = '" & _
scenarioName & "~bak' AND ((trajectory_id & Str$(AttTime)) NOT IN (SELECT trajectory_id & Str$(att_time) as A from" & _
" TRAJ_SCENARIO WHERE scenario_id = '" & scenarioName & "~bak' and trajectory_id = '" & _
TrajName & "' and att_time = " & AttTime & ")) ORDER BY trajectory_id"



Ciao da
N@poleone

 

[KenReay]

Hi

Yes you can do this in Access VBA (I have done it)

It would have helped if you had stated the error message, it it a runtime of a compile time error?

You need to consider two steps here

1. can you build the SQL string in code without any errors

2. you need to be able to execute the string as (jet?) sql

at which point does it fail?

are you running your vb application against a jet/access databse (.mdb)?



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[deangelis]

The error is at runtime: error number 3061. I think it is the "&" character into the string. Now I'll try with the Chr(0026).

Ciao da
N@poleone

 

[KenReay]

Hi

Having read your SQL more carefull, I notice some column names which suggest to me they are date/time types, in this case you need to bound the values with #, so taking a snippit from your SQL:

and att_time = #" & AttTime & "#))

Please Note also that Access expects to get dates in american format or ANSI format no matter wheer in the world you are or what your regional settings, so you may need a variation on:

and att_time = #" & Format("yyyy/mm/dd hh:mm:ss",AttTime) & "#))

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[deangelis]

Thanks for all your suggestions.
I resolved the problem with the functio Chr to write the character "&".

Ciao da
N@poleone