Converting query to SQL pass through issue

mattkorguk

Registered User.
Local time
Today, 22:36
Joined
Jun 26, 2007
Messages
301
Hi All,
I have the following query field with criteria and I need to be able to transfer the same to SQL but I'm a little stuck! :banghead:
On the form is a drop down where users can select whether they want zero clawback records displayed or not just a yes/ no dropdown.
Code:
Field - IncZero: IIf([Clawback]=0,0,1)
 
Criteria - ((IIf([Clawback]=0,0,1))>=(IIf([forms]![frmEWS-OS-Master]![selClawback]="Yes",0,1)))

I'm building the PT in VB but can't seem to figure out the above. Here's what I have so far, trying to include the above part which isn't working!
Code:
[SIZE=2]... [/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]END[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CASE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHEN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]dbo[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Corres Addr2] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]IS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL)[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]THEN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]' '[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ELSE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]LTRIM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]RTRIM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]dbo[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Corres Addr2] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]', '[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]END[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CASE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHEN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]dbo[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Corres Addr3] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]IS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL)[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]THEN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]' '[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ELSE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]LTRIM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]RTRIM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]dbo[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Corres Addr3] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]', '[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]END[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CASE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHEN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]dbo[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Corres Addr4] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]IS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL)[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]THEN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]' '[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ELSE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]LTRIM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]RTRIM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]dbo[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Corres Addr4] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]', '[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]END[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CASE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHEN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]dbo[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Corres PCode] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]IS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL)[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]THEN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]' '[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ELSE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]LTRIM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]RTRIM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]dbo[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Corres Pcode][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]END[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] FullAddr[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]CASE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHEN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]dbo[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][EWS-Data][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Clawback] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 0[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]THEN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 0 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ELSE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 1 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]END[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] IncZero[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [case] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]Parties [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] CRs [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Sales Rep] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] CRs[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Party Id] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][EWS-Data] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Case Id] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [EWS-Data][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Case Id] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]Parties [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] PA [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Administrator [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] PA[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Party Id] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]Parties [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Supv [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Supv [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Supv[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Party Id][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Sales Rep] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]LIKE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'%'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][EWS-Data][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Resolved [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 0[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Administrator [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]LIKE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'%'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][EWS-Data][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Date Received] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]BETWEEN[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]CONVERT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]DATETIME[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'01/01/1901'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 102[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]CONVERT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]DATETIME[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'08/15/2012'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 102[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][case][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]Supv [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]LIKE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'%'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]when[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] inczero[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]0 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]then[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 0 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]else[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 1 [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]when[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'yes'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'Yes'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]then[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 0 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]else[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 1 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]end[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]ORDER[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]BY[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [EWS-Data][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][Date Received][/SIZE]
Any pointers welcomed as ever.
Thanks,
Matt
 
Why are you using - LIKE "%" as the criteria for [Sales Rep], Administrator, and Supv? This will select all values (the hard and extremely inefficient way) so you don't need the three criteria at all and removing them may elminate the need to make this a pass-through.

I'm trying to understand the criteria you are trying to add. It looks like you have to add an argument to the where clause dynamically depending on the value of a form field.

Code:
strSQL = blah blah
If [forms]![frmEWS-OS-Master]![selClawback]="Yes" Then
    strSQL = strSQL & " AND Clawback = 0 "
End If
strSQL = strSQL & " Order By ....."
 
Thank you for the reply Pat, the LIKE statements are taken from dropdown field options on the form, so users can restrict the records based on those selections.
I'm trying to add criteria as you mentioned, so far I have this;
Code:
 & "AND (CASE WHEN (dbo.[EWS-Data].[Clawback] = 1) THEN 0 ELSE -1 END = " & strCB & ")" _
& "AND (CASE WHEN (dbo.[EWS-Data].[In Progress] = 1) THEN 0 ELSE -1 END = " & strPro & ") "
This seems to work, but only when both criteria are the same, a little frustrating.
The strPro and strCB are also string variables, if they select 'yes' on the dropdown it populates the above with a 1.
 
I got it to work, so that's good enough for the moment, I may revisit...:rolleyes:
I went down this route to build the one that causing issues;
Code:
If [Forms]![frmEWS-OS-Master]![selClawback] = "Yes" Then
    strCB = "1"
Else
    strCB = "0"
End If
 
If [Forms]![frmEWS-OS-Master]![selInProg] = "Yes" Then
    strPro = Null
Else
    strPro = "AND (CASE WHEN (dbo.[EWS-Data].[In Progress] = 1) THEN 1 ELSE 0 END = 0) "
End If
And then added this to the end of the TSQL criteria;
Code:
& "AND (CASE WHEN (dbo.[EWS-Data].[Clawback] = " & strCB & ") THEN 0 ELSE 1 END = 1)" _
                      & strPro
 

Users who are viewing this thread

Back
Top Bottom