Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Change records in Access (accdb) load

Dear fellow Qlikviewers,

I've recently switched companies and am now working with MS Access in combination with Qlikview.

Currently I have the following piece of script

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\Req_SRA01_Results.accdb];

SQL SELECT

    `Account Manager`,

    `Billed Weight`,

    `Commercial product`,

    `Legal Entity`,

    `Origin Country`,

    `Revenue Period`,

    'SVE_Group'

if(mid('SVE_Group',8,3)='DFY','DFY',if(mid('SVE_Group',8,3)='EPL','EPL',if(mid('SVE_Group',8,3)='EPA','EPA',if(mid('SVE_Group',8,2)='SV','SV','?')))) as 'SVE_Group_2'

    Tradelane,

FROM tblResults;

The problem arises with the bold part. I try to add an extra record based on a record available in the ACCDB.

However, when I try this, I get the following message:

SQL##f - SqlState: 37000, ErrorCode: 4294964196, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''SVE_Group' if(mid('SVE_Group',8,3)='DFY','DFY',if(mid('SVE_Group',8,3)='EPL','EPL',if(mid('SVE_Group',8,3)='EPA','EPA',if(mid('SVE_Group',8,2)='SV','SV','?'))))'.

Am I doing something wrong? Clearly I am, but what?

Any help is appreciated.

Kind regards,

Stefan

1 Solution

Accepted Solutions
ashfaq_haseeb
Champion III
Champion III

Hi,

Try below

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\Req_SRA01_Results.accdb];

load

    `Account Manager`,

    `Billed Weight`,

    `Commercial product`,

    `Legal Entity`,

    `Origin Country`,

    `Revenue Period`,

    'SVE_Group'

if(mid('SVE_Group',8,3)='DFY','DFY',if(mid('SVE_Group',8,3)='EPL','EPL',if(mid('SVE_Group',8,3)='EPA','EPA',if(mid('SVE_Group',8,2)='SV','SV','?')))) as 'SVE_Group_2'

    Tradelane;

sql select *

FROM tblResults;

Regards

ASHFAQ

View solution in original post

3 Replies
ashfaq_haseeb
Champion III
Champion III

Hi,

Try below

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\Req_SRA01_Results.accdb];

load

    `Account Manager`,

    `Billed Weight`,

    `Commercial product`,

    `Legal Entity`,

    `Origin Country`,

    `Revenue Period`,

    'SVE_Group'

if(mid('SVE_Group',8,3)='DFY','DFY',if(mid('SVE_Group',8,3)='EPL','EPL',if(mid('SVE_Group',8,3)='EPA','EPA',if(mid('SVE_Group',8,2)='SV','SV','?')))) as 'SVE_Group_2'

    Tradelane;

sql select *

FROM tblResults;

Regards

ASHFAQ

Anonymous
Not applicable
Author

Works like a charm, thanks so much for the quick response as well!

ashfaq_haseeb
Champion III
Champion III

Welcome

Regards

ASHFAQ