Problem putting Query SQL into Form VB

[DahMurf]

I have this in a query and it results in almost 4,000 records from Table1

SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.RunIndex = Table2.RunIndex WHERE (((Right([Table1.RunIndex],6))="070108") AND ((Table2.RunIndex) is null));

I then tried to put this SQL into the VB behind my form.

GetRecs = "SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.RunIndex = Table2.RunIndex WHERE Right([Table1.RunIndex],6)= "070108"  And Table2.RunIndex is null;"
Set nr = CurrentDb.openRecordset(GetRecs)

When I run it, it is successful but it doesn’t return any records.
I’m trying to pull all of the records from table1 where the right 6 digits of the RunIndex = 070108 and where there are no corresponding record on table2. Any suggestions on how to make this work in the VB behind the form or what I may have done wrong?
TIA

 

[hwkranger]

Try...

SELECT
 *
FROM
 TABLE1 T1 LEFT JOIN Table2 T2
  ON T1.RunIndex = T2.RunIndex
WHERE
 (RIGHT(T1.RunIndex, 6) = '070108')
  AND
 (T2.RunIndex is null)

Mabye the parans and single quotes will help.

Randall Vollen
Merrill Lynch

 

[PHV]

GetRecs = "SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.RunIndex = Table2.RunIndex WHERE Right([Table1.RunIndex],6)= [!]'[/!]070108[!]'[/!] AND Table2.RunIndex is null;"
Set nr = CurrentDb.openRecordset(GetRecs)

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

 

[DahMurf]

THANK YOU THANK YOU THANK YOU!
Cleaning up the code worked! I was beating my head against the desk!

Now I can go home!

 

[barny2006]

above suggestions are all cool.
or you can try this:

SELECT * FROM Table1 WHERE Right([Table1.RunIndex],6)= '070108' AND
(EXISTS (SELECT RunIndex from table2 where table1.RunIndex = table2.RunIndex) = false);