CODEWITHSUNDEEP
vbams-access
zkhan
zkhan

Reputation: 157

sql vba query expression syntax error

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]

Upvotes: 0

Views: 62

Answers (2)

Mincong Huang
Mincong Huang

Reputation: 5552

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]

Upvotes: 1

Dave
Dave

Reputation: 4356

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]

Upvotes: 1

Related Questions