Getting different results while running the same stored Access SQL query directly and through VBA excel

Question

Hey the following stored access query is giving POEventCode with "NA" when it is run through VBA excel and is giving the correct result when directly run from access.Why is this anomaly?

SELECT D.[Event Code] & D.[Week Code] AS POEventCode, *  
INTO A
FROM (SELECT IIf([Advertisement type]='Tabloide','TAB',
    IIf([Advertisement type]='Flyer','FLY','NA')) AS [Event Code], 
    IIf(([Start Date]>[SD] And [Start Date]<[ED]), 
        [Month] & Right([WM_WK],2),'NA')  
        AS [Week Code], *  
    FROM [Feature A-Traits] AS C, [Event Code] AS B) 
AS D
WHERE (D.[Week Code]) Not Like '*NA*'  
    And (D.[Event Code]) Not Like '*NA*';

That is a pretty confusing looking SQL, what is it you're trying to achieve? — Matt Donnan
– Matt Donnan, Commented Dec 27, 2012 at 9:05

Lord Peter · Accepted Answer · 2012-12-27 09:34:53Z

5

If you are using ADO running your query through VBA Excel then your wildcards need to be % not *, so:- Not Like '%NA%'

answered Dec 27, 2012 at 9:34

Lord Peter

3,5012 gold badges36 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Smandoli Over a year ago

I tried to up vote this and then down vote (deciding I wasn't certain the validity of the answer after all). I ended up with a net down vote, which was not intentional. I seem to be unable to fix the problem right now, and I apologize.

Lord Peter Over a year ago

@Smandoli - no problem. Please post again with more information if you can't get the Excel query to work with % wildcards (you will need them in both places in your query).

Collectives™ on Stack Overflow

Getting different results while running the same stored Access SQL query directly and through VBA excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related