sql vba query expression syntax error

Question

I'm trying to run the following through vba sq in access but get an error "Extra ) in query expression '0)'". Following is the query expression:

mnthfld = "SELECT IIf([issuedate] <= #" & useDateUpper & "#," & _
    "IIf([expirydate] <= #" & useDateUpper & "#, 0, IIf([expirydate <=#" & useDateUpper & "#," & _
    "([expirydate]-#" & useDateUpper & "#+1)/([expirydate]-[effectivedate]+1),[grosspremium]),0),0) as 'EP M" & _
    Format(useDateLower, "mm yyyy") & "' FROM tblEpData"

Any help is appreciated. My hunch is that I'm using brackets wrongly in the subtraction and division parts.

UPDATE:

Snapshot of the mnthfield string on one iteration:

IIf([issuedate] <= #11/30/2015#,IIf([expirydate] <= #11/30/2015#, 0, IIf([expirydate <=#11/30/2015#,([expirydate]-#11/30/2015#+1)/([expirydate]-[effectivedate]+1),[grosspremium]),0,0)) as [EP M11 2015]

immediately after you set mnthfld, use msgbox mnthfld to display the overall string you've concocted and post that so it's easy to detect any syntax errors... — Dave
– Dave, Commented May 18, 2016 at 12:01
The select and from commands are omitted from the snapshot, however that is not the cause of the error. — zkhan
– zkhan, Commented May 18, 2016 at 12:36

Mincong Huang · Accepted Answer · 2016-05-18 13:05:11Z

1

Add a ] in statement [expirydate <=#" & useDateUpper and remove the ,0 from [grosspremium]),0 should work. Final query looks like :

SELECT 
IIf
(
    [issuedate] <= #11/30/2015#, 
    IIf
    (
        [expirydate] <= #11/30/2015#, 
        0, 
        IIf
        (
            [expirydate] <=#11/30/2015#,
            ([expirydate]-#"11/30/2015"#+1)/([expirydate]-[effectivedate]+1),
            [grosspremium]
        )
    ),
    0
) as [EP M11 2015]

edited May 18, 2016 at 13:05

answered May 18, 2016 at 12:55

Mincong Huang

5,5728 gold badges45 silver badges64 bronze badges

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

2 Comments

Dave Over a year ago

Still missing the closing ] on [expirydate] on the final nested IIf ;)

Mincong Huang Over a year ago

You're right, @Dave, but it is forgetten from the question :(

Dave · Accepted Answer · 2016-05-18 12:53:53Z

1

Each instance of IIf requires 3 parameters (bool_test, value_if_true, value_if_false)

I've converted the below string to be syntactically correct; you'll need to review it to make sure it's what you expected it to be doing though!

IIf([issuedate]<= #11/30/2015#,IIf([expirydate] <= #11/30/2015#, 0, IIf([expirydate] <=#11/30/2015#,(([expirydate]-#11/30/2015#+1)/([expirydate]-[effectivedate]+1)),[grosspremium])),0) as [EP M11 2015]

answered May 18, 2016 at 12:53

Dave

4,3562 gold badges26 silver badges33 bronze badges

Collectives™ on Stack Overflow

sql vba query expression syntax error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related