Converting query to SQL pass through issue (1 Viewer)

mattkorguk

Registered User.
Local time
Today, 19:27
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:27
Joined
Feb 19, 2002
Messages
42,973
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 ....."
 

mattkorguk

Registered User.
Local time
Today, 19:27
Joined
Jun 26, 2007
Messages
301
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.
 

mattkorguk

Registered User.
Local time
Today, 19:27
Joined
Jun 26, 2007
Messages
301
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

Top Bottom