"Invalid Procedure Call" Error

[Purdue2479]

I am getting an "Invalid procedure call" error when trying to run this query in Access. If I remove the Distinct it runs fine, but I need it in there to remove duplicates. Also, When I remove the expression Mid([SIDE_DRUG_DESC],(InStr([SIDE_DRUG_DESC],"("))+1,InStr([SIDE_DRUG_DESC],")")-InStr([SIDE_DRUG_DESC],"(")-1) AS ParseTxt with Distinct in place it runs, but I need the parsed text in the query. Any help would be appreciated. thanks

SELECT Distinct RGCNSEQ4_GCNSEQNO_MSTR.GCN_SEQNO, RGCNSEQ4_GCNSEQNO_MSTR.GCRT, tbl_RGenName_StrTbl.GNN60_PLUS, Mid([SIDE_DRUG_DESC],(InStr([SIDE_DRUG_DESC],"("))+1,InStr([SIDE_DRUG_DESC],")")-InStr([SIDE_DRUG_DESC],"(")-1) AS ParseTxt, RSIDEDD0_DRUG_DESC.SIDE_DRUG_DESC
FROM (RSIDEDD0_DRUG_DESC INNER JOIN ((RSIDEGC0_GCNSEQNO_LINK INNER JOIN RGCNSEQ4_GCNSEQNO_MSTR ON RSIDEGC0_GCNSEQNO_LINK.GCN_SEQNO = RGCNSEQ4_GCNSEQNO_MSTR.GCN_SEQNO) INNER JOIN RSIDEMA3_MSTR ON RSIDEGC0_GCNSEQNO_LINK.SIDE = RSIDEMA3_MSTR.SIDE) ON RSIDEDD0_DRUG_DESC.SIDE = RSIDEMA3_MSTR.SIDE) INNER JOIN tbl_RGenName_StrTbl ON RGCNSEQ4_GCNSEQNO_MSTR.GCN_SEQNO = tbl_RGenName_StrTbl.GCN_SEQNO
WHERE (((InStr([SIDE_DRUG_DESC],"("))>"0"));

 

[WayneRyan]

Purdue,

I think the problem lies with this line:

Mid([SIDE_DRUG_DESC],(InStr([SIDE_DRUG_DESC],"("))+1,InStr([SIDE_DRUG_DESC],")")-InStr([SIDE_DRUG_DESC],"(")-1) AS ParseTxt,

Do all of the [SIDE_DRUG_DESC] fields have the "(" and ")" in them?

If not, you'll be trying to get --> Mid([SIDE_DRUG_DESC], 1, -1)

Which won't work too well.

I'd suggest writing a little VBA function and making that column of the query

Parse([SIDE_DRUG_DESC]) As ParseTxt

I don't think the problem is with your usage of Distinct.

Wayne