Alternative to IN( ) statement/clause in Jet SQL? 1

[kjv1611]

I am continually getting a Syntax error on the following SQL statement in Access VBA. Best I can guess is that it is occurring perhaps because there is no IN( ) statement usage in jet SQL, which I assume is what is really used when I use DoCmd.RunSQL in Access VBA.

Here is the current code:

DELETE [tblAll].[*]
FROM [tblAll]
WHERE
   (([tblALL].[Case Number] IN (
      SELECT [tblHist_Data].[Case Number]
      FROM [tblHist_Data]
    ))
 AND ([tblALL].[Date] IN (SELECT  [tblHist_Data].[Date])));

It checks out fine when I parse it in MS SQL Server Mgmnt Studio just to check, but in Access VBA, it constantly gives a Syntax error. Also, if I put it into an Access Query, I get a Syntax Error.

I thought I'd attempt to create it via an Access Query in order to see where I was going wrong, and the only difference I noticed right away was that the IN() Function was not available in the query expression builder.

Am I on the right track, or is some other teensy weensy typical syntax error sneaking in that I just cannot seem to track down?

Thanks in advance for any help!

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Hi,

Make sure your () are pared properly

and

where's the FROM Clause???

AND ([tblALL].[Date] IN (SELECT  [tblHist_Data].[Date])));

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Remou]

I have a feeling that you need at join on case number and date, rather than what you show, as it is (even corrected to add the missing table), the case will be deleted if the date in in the history table, even if the date is not associated with the case. More on the lines of:

DELETE * FROM tblAll INNER JOIN tblHist_Data
(ON tblAll.[Case Number]=tblHist_Data.[Case Number])
AND (tblAll.[Date]=tblHist_Data.[Date])

 

[kjv1611]

Thanks for the suggestions.

I think the missing FROM statement may very well be the key. I'll give it a look first thing Monday morning, and post back.

As for the JOIN instead of the IN clause, I had already tried that, and just was getting nowhere. If the FROM correction doesn't do it, I'll give that a shot as well.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

DELETE [!]tblAll.[/!]*
FROM tblAll INNER JOIN tblHist_Data
ON tblAll.[Case Number]=tblHist_Data.[Case Number]
AND tblAll.[Date]=tblHist_Data.[Date]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

PHV . . .

Yes your right. I saw it after I posted. I red flagged very early this mourning for removal. I see they havn't gotton to it yet . ..

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

kjv1611 . . .

Try the following. I havn't tested it yet:

[blue]Delete [TA].[*] 
FROM [tblAll] AS [purple][b]TA[/b][/purple] 
WHERE ([TA].[Case Number] IN (SELECT [HD].[Case Number] 
         			         FROM [tblHist_Data] AS [b]HD[/b] 
				              WHERE ([[purple][b]TA[/b][/purple]].[Date] = [[b]HD[/b]].[Date])));[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[kjv1611]

Thanks for all the suggestions so far.

Skip,
I first tried adding back in the "FROM" in the second "IN" clause, but that still did not clear up the syntax - probably was still missing some parentheses, as suggested, so I may still go back to that one if need be, later.

PHV / AceMan1,
For some reason, Access didn't like that one. It gives me the error message:

Run-time error '3086'
Could not delete from specified tables.

Yes, the table names are correct. I'm guessing that perhaps you cannot delete from one table using an INNER JOIN on two? I dunno for sure.

AceMan1,
How can I say this...?
THANK YOU THANK YOU THANK YOU THANK YOU!!!!


That last one worked excellently!

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

I guess I was on the right track to begin with, but I just wasn't quite there. Lots still to learn!

--

"If to err is human, then I must be some kind of human!" -Me

 

[lameid]

FYI all, in a related thread we killed the error by using distinctrow.

thread705-1488556

DELETE DISTINCTROW [tblAll].* 
FROM [tblAll] INNER JOIN [tblHist_Data] 
ON [tblAll].[Case Number] = [tblHist_Data].[Case Number] 
AND [tblAll].[Date] = [tblHist_Data].[Date]