Word question : If/then in strQuery?

[sbgdcg]

I use VBA to do a dynamic mail merge. One operative part of my code is as follows:

strQuery = "SELECT * FROM `Appeal 1$` WHERE (`F14` < '75') And (`F1` IS NOT NULL)"

Is it possible to put an if/else in the above? I ask because, sometimes F14 has numbers, and sometimes text. If text, I'd like "F14" to change to "F13."

I'd greatly appreciate any tips, help, feedback. Thanks!!!

 

[Macropod]

In that case, there is an issue with what is actually in N5, not the rest of the code per se. Nor does it matter whether whatever is in N5 gets there as a result of a formula. Without seeing that part of the workbook, there's not much more I can contribute.

 

[sbgdcg]

One major stumbling block is that even when I replace the formula in N5 with a real number, like 76, the script still goes to the Else, in essence counting that 76 as IsNumeric = false. I've tried changing N5 to cells(5,14), and no luck. For completeness (and even though I'm a little embarrassed by the code), here is the N5 formula:

=IF(A5="","",IFERROR(IF(COUNTIFS('Appeal 1'!$T$5:$T$20,"*")=0,"No change",(
(IF(SUM('Appeal 1'!$R$5:$R$20)=0,"No change",(
IF('Appeal 1'!$R$5+'Appeal 1'!$R$6+'Appeal 1'!$R$7+'Appeal 1'!$R$8+'Appeal 1'!$R$9+'Appeal 1'!$R$10+'Appeal 1'!$R$11+'Appeal 1'!$R$12+'Appeal 1'!$R$13+'Appeal 1'!$R$14+'Appeal 1'!$R$15+'Appeal 1'!$R$16+'Appeal 1'!$R$17+'Appeal 1'!$R$18+'Appeal 1'!$R$19+'Appeal 1'!$R$20=0,"",
IF(ISBLANK('Appeal 1'!$R$5),"0",(IF(ISBLANK('Appeal 1'!$T$5),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$5,1)='Appeal 1'!$T$5,0,'Appeal 1'!$J$5)))))
+IF(ISBLANK('Appeal 1'!$R$6),"0",(IF(ISBLANK('Appeal 1'!$T$6),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$6,1)='Appeal 1'!$T$6,0,'Appeal 1'!$J$5))))
+IF(ISBLANK('Appeal 1'!$R$7),"0",(IF(ISBLANK('Appeal 1'!$T$7),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$7,1)='Appeal 1'!$T$7,0,'Appeal 1'!$J$5))))
+IF(ISBLANK('Appeal 1'!$R$8),"0",(IF(ISBLANK('Appeal 1'!$T$8),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$8,1)='Appeal 1'!$T$8,0,'Appeal 1'!$J$5))))
+IF(ISBLANK('Appeal 1'!$R$9),"0",(IF(ISBLANK('Appeal 1'!$T$9),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$9,1)='Appeal 1'!$T$9,0,'Appeal 1'!$J$5))))
+IF(ISBLANK('Appeal 1'!$R$10),"0",(IF(ISBLANK('Appeal 1'!$T$10),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$10,1)='Appeal 1'!$T$10,0,'Appeal 1'!$J$5))))
+IF(ISBLANK('Appeal 1'!$R$11),"0",(IF(ISBLANK('Appeal 1'!$T$11),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$11,1)='Appeal 1'!$T$11,0,'Appeal 1'!$J$5))))
+IF(ISBLANK('Appeal 1'!$R$12),"0",(IF(ISBLANK('Appeal 1'!$T$12),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$12,1)='Appeal 1'!$T$12,0,'Appeal 1'!$J$5))))
+IF(ISBLANK('Appeal 1'!$R$13),"0",(IF(ISBLANK('Appeal 1'!$T$13),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$13,1)='Appeal 1'!$T$13,'Appeal 1'!$J$5,0))))
+IF(ISBLANK('Appeal 1'!$R$14),"0",(IF(ISBLANK('Appeal 1'!$T$14),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$14,1)='Appeal 1'!$T$14,'Appeal 1'!$J$5,0))))
+IF(ISBLANK('Appeal 1'!$R$15),"0",(IF(ISBLANK('Appeal 1'!$T$15),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$15,1)='Appeal 1'!$T$15,'Appeal 1'!$J$5,0))))
+IF(ISBLANK('Appeal 1'!$R$16),"0",(IF(ISBLANK('Appeal 1'!$T$16),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$16,1)='Appeal 1'!$T$16,'Appeal 1'!$J$5,0))))
+IF(ISBLANK('Appeal 1'!$R$17),"0",(IF(ISBLANK('Appeal 1'!$T$17),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$17,1)='Appeal 1'!$T$17,'Appeal 1'!$J$5,0))))
+IF(ISBLANK('Appeal 1'!$R$18),"0",(IF(ISBLANK('Appeal 1'!$T$18),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$18,1)='Appeal 1'!$T$18,'Appeal 1'!$J$5,0))))
+IF(ISBLANK('Appeal 1'!$R$19),"0",(IF(ISBLANK('Appeal 1'!$T$19),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$19,1)='Appeal 1'!$T$19,'Appeal 1'!$J$5,0))))
+IF(ISBLANK('Appeal 1'!$R$20),"0",(IF(ISBLANK('Appeal 1'!$T$20),"0",IF(MID(INDEX('Examinee Score Results'!$P$2:$P$2500,MATCH(A5,'Examinee Score Results'!$E$2:$E$2500,0)),'Appeal 1'!$R$20,1)='Appeal 1'!$T$20,'Appeal 1'!$J$5,0)))))
+M5)))),
"Error - Check UC"))

 

[Macropod]

Posting the formula without the context of the workbook to which it belongs really isn't all that useful, though I do note you have all the 0 outputs expressed as text (i.e. "0" instead of 0). Even in Excel, an ISNUMBER formula test on such an output will return FALSE. Simply replacing all the "0"s with 0s would fix that and, maybe, your mailmerge. Of course, if whatever the formula returns in other 'numeric' circumstances is similarly-quoted, there'll be more to it than that.

 

[sbgdcg]

Thanks for noticing those issues. I understand it's not very helpful without the whole workbook. I would definitely post it, but there are very tight restrictions here on transmission of files, which unfortunately leaves me in a bind.

What I did do was try to create a workaround for the automation, so I made it manual. I really like how it mail merges on launch, which I think balances the confirmation the user must provide to run it. Nevertheless, this is a workaround that fixes the problem. Thanks again for all of your help with this endeavor!

    Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String


    ' Promt
    strPrompt = "Did you provide any Universal Credits?"
 
    ' Dialog's Title
    strTitle = "Universal Credit Check"
 
    'Display MessageBox
    iRet = MsgBox(strPrompt, vbYesNo, strTitle)
 
    ' Check pressed button
 
 ' set this to be the query for your data source
 ' if you need to sort or filter, you need to add
 ' the appropriate ORDER BY and WHERE clauses

    If iRet = vbNo Then
 strQuery = "SELECT * FROM `Appeal 1$` WHERE (`F13` < '75') And (`F1` IS NOT NULL)"
        MsgBox "You are using scores from ""Post-Appeal % Score"" (column M). You are NOT including Universal Credits."
    Else
  strQuery = "SELECT * FROM `Appeal 1$` WHERE (`F14` < '75') And (`F1` IS NOT NULL)"
  MsgBox "You are using scores from ""Post-Universal Credit"" (column N).  You ARE including Universal Credits."
    End If

 

[mrmmickle1]

Glad you were able to get your issue solved! Thanks for posting the resolution.

 

[sbgdcg]

Thank you and Macropod for your time and effort. I only came to a solution because of the solutions you both presented here. I'm marking the thread as solved, if possible. Otherwise, officially: case closed