Switch and IIF both give me an extra data Line.

[Guest]

If I use this line:
Expr26: IIF([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])
I get two duplicate line in my out put. One with this expression with the
correct data (the data I expect) then I also get an exact duplicate line with
this expression and a null in this column.
That made sense to me as the if clause goes: IIF( expression, true, false)
and the false may appear as a Null in the data.
SO i converted to a Switch statment:
Expr26: Switch([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE
UNITS])
I got the same results and that does not make sense to me as the expression
is:
Switch( expression, expression)
Can anyone explain what i am mission or at least a way to do this so I get
an answer only when the expression is satisfied?
I requested this same thing last week and now I can't find the question let
alone the answers, so I home this one stays.

 

[Ken Snell [MVP]]

What is the full SQL statement of the query that you're using? An IIf
statement by itself will not create two records in a query.

 

[Guest]

If you should try to run a portion of the code, the Date is in the form
ddMMMyyyy.
The code give the correct answers until expression 26 is added, so I'm
not quite sure what I did not give, expression 26 is complete, but the query
is:
SELECT DISTINCT CONSUMP_MDDTL.BUS_UNIT, CONSUMP_MDDTL.ROADMAP_BUS_UNIT,
CONSUMP_MDDTL.APP_NAME,
CONSUMP_MDDTL.HOST_ID,
CONSUMP_MDDTL.REGIONAL_NAME,
CONSUMP_MDDTL.BR_NUMBER,
CONSUMP_MDDTL.ARC,
CONSUMP_MDDTL.CNTRCT_CD,
CONSUMP_MDDTL.USAGE_DT,
Str$([CONSUMP_MDDTL]![TOTAL_SU])+" - "+Str$([CONSUMP_MDDTL_HIST]![TOTAL_SU])
AS Expr12,
[CONSUMP_MDDTL]![TOTAL_SU]-[CONSUMP_MDDTL_HIST]![TOTAL_SU]
AS Expr25,
IIf([CONSUMP_MDDTL]![FBGIG]<>[CONSUMP_MDDTL_HIST]![FBGIG],([CONSUMP_MDDTL]![FBGIG])-([CONSUMP_MDDTL_HIST]![FBGIG]),Null)
AS Expr1,
IIf([CONSUMP_MDDTL]![STORGIG]<>[CONSUMP_MDDTL_HIST]![STORGIG],([CONSUMP_MDDTL]![STORGIG])-([CONSUMP_MDDTL_HIST]![STORGIG]),Null)
AS Expr2,
IIf([CONSUMP_MDDTL]![SOFTWARE_CURRENCY]<>[CONSUMP_MDDTL_HIST]![SOFTWARE_CURRENCY],LCase$([CONSUMP_MDDTL]![SOFTWARE_CURRENCY])+Space$(2)+LCase$([CONSUMP_MDDTL_HIST]![SOFTWARE_CURRENCY]),Null)
AS Expr3,
IIf([CONSUMP_MDDTL]![STORAGE_MGMT]<>[CONSUMP_MDDTL_HIST]![STORAGE_MGMT],LCase$([CONSUMP_MDDTL]![STORAGE_MGMT])+Space$(1)+LCase$([CONSUMP_MDDTL_HIST]![STORAGE_MGMT]),Null)
AS Expr4,
IIf([CONSUMP_MDDTL]![FBMGMT]<>[CONSUMP_MDDTL_HIST]![FBMGMT],LCase$([CONSUMP_MDDTL]![FBMGMT])+Space$(1)+LCase$([CONSUMP_MDDTL_HIST]![FBMGMT]),Null)
AS Expr5,
IIf([CONSUMP_MDDTL]![DATABASE_ENGINEERING]<>[CONSUMP_MDDTL_HIST]![DATABASE_ENGINEERING],([CONSUMP_MDDTL]![DATABASE_ENGINEERING])-([CONSUMP_MDDTL_HIST]![DATABASE_ENGINEERING]),Null)
AS Expr6,
IIf([CONSUMP_MDDTL]![MWARE_TYPE1]<>[CONSUMP_MDDTL_HIST]![MWARE_TYPE1],([CONSUMP_MDDTL]![MWARE_TYPE1])-([CONSUMP_MDDTL_HIST]![MWARE_TYPE1]),Null)
AS Expr7,
IIf([CONSUMP_MDDTL]![MWARE_NBR_INSTNCS_TYPE1]<>[CONSUMP_MDDTL_HIST]![MWARE_NBR_INSTNCS_TYPE1],([CONSUMP_MDDTL]![MWARE_NBR_INSTNCS_TYPE1])-([CONSUMP_MDDTL_HIST]![MWARE_NBR_INSTNCS_TYPE1]),Null)
AS Expr8,
IIf([CONSUMP_MDDTL]![MWARE_NBR_INSTNCS_TYPE2]<>[CONSUMP_MDDTL_HIST]![MWARE_NBR_INSTNCS_TYPE2],([CONSUMP_MDDTL]![MWARE_NBR_INSTNCS_TYPE2])-([CONSUMP_MDDTL_HIST]![MWARE_NBR_INSTNCS_TYPE2]),Null)
AS Expr9,
IIf([CONSUMP_MDDTL]![MWARE_TYPE1]<>[CONSUMP_MDDTL_HIST]![MWARE_TYPE1],([CONSUMP_MDDTL]![MWARE_TYPE1])-([CONSUMP_MDDTL_HIST]![MWARE_TYPE1]),Null)
AS Expr10,
IIf([CONSUMP_MDDTL]![MWARE_TYPE2]<>[CONSUMP_MDDTL_HIST]![MWARE_TYPE2],([CONSUMP_MDDTL]![MWARE_TYPE2])-([CONSUMP_MDDTL_HIST]![MWARE_TYPE2]),Null)
AS Expr11,
IIf([CONSUMP_MDDTL]![BATCH_MGMT]<>[CONSUMP_MDDTL_HIST]![BATCH_MGMT],([CONSUMP_MDDTL]![BATCH_MGMT])-([CONSUMP_MDDTL_HIST]![BATCH_MGMT]),Null)
AS Expr13,
IIf([CONSUMP_MDDTL]![CAPACITY_PLANNING]<>[CONSUMP_MDDTL_HIST]![CAPACITY_PLANNING],LCase$([CONSUMP_MDDTL]![CAPACITY_PLANNING])+Space$(1)+LCase$([CONSUMP_MDDTL_HIST]![CAPACITY_PLANNING]),Null)
AS Expr14,
IIf([CONSUMP_MDDTL]![STRATEGIC_LEVERAGE]<>[CONSUMP_MDDTL_HIST]![STRATEGIC_LEVERAGE],LCase$([CONSUMP_MDDTL]![STRATEGIC_LEVERAGE])+Space$(1)+LCase$([CONSUMP_MDDTL_HIST]![STRATEGIC_LEVERAGE]),Null)
AS Expr15,
IIf([CONSUMP_MDDTL]![SUPPORT_LEVEL]<>[CONSUMP_MDDTL_HIST]![SUPPORT_LEVEL],LCase$([CONSUMP_MDDTL]![SUPPORT_LEVEL])+Space$(2)+LCase$([CONSUMP_MDDTL_HIST]![SUPPORT_LEVEL]),Null)
AS Expr16,
IIf([CONSUMP_MDDTL]![DATABASE_ENGINEERING]<>[CONSUMP_MDDTL_HIST]![DATABASE_ENGINEERING],([CONSUMP_MDDTL]![DATABASE_ENGINEERING])-([CONSUMP_MDDTL_HIST]![DATABASE_ENGINEERING]),Null)
AS Expr17,
IIf([CONSUMP_MDDTL]![DATABASE_ADMINISTRATION]<>[CONSUMP_MDDTL_HIST]![DATABASE_ADMINISTRATION],([CONSUMP_MDDTL]![DATABASE_ADMINISTRATION])-([CONSUMP_MDDTL_HIST]![DATABASE_ADMINISTRATION]),Null)
AS Expr18,
IIf([CONSUMP_MDDTL]![DATABASE_MANAGEMENT]<>[CONSUMP_MDDTL_HIST]![DATABASE_MANAGEMENT],([CONSUMP_MDDTL]![DATABASE_MANAGEMENT])-([CONSUMP_MDDTL_HIST]![DATABASE_MANAGEMENT]),Null)
AS Expr19,
IIf([CONSUMP_MDDTL]![DB_SCHEMAS]<>[CONSUMP_MDDTL_HIST]![DB_SCHEMAS],([CONSUMP_MDDTL]![DB_SCHEMAS])-([CONSUMP_MDDTL_HIST]![DB_SCHEMAS]),Null)
AS Expr20,
IIf([CONSUMP_MDDTL]![DB_GIG]<>[CONSUMP_MDDTL_HIST]![DB_GIG],([CONSUMP_MDDTL]![DB_GIG])-([CONSUMP_MDDTL_HIST]![DB_GIG]),Null)
AS Expr21,
IIf([CONSUMP_MDDTL]![DB_OBJECTS]<>[CONSUMP_MDDTL_HIST]![DB_OBJECTS],([CONSUMP_MDDTL]![DB_OBJECTS])-([CONSUMP_MDDTL_HIST]![DB_OBJECTS]),Null)
AS Expr22,
IIf([CONSUMP_MDDTL]![OS_PLATFORM_TYPE]<>[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE],LCase$([CONSUMP_MDDTL]![OS_PLATFORM_TYPE])+Space$(2)+LCase$([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]),Null)
AS Expr23,
IIf([CONSUMP_MDDTL]![MWARE_SUPPORT_TYPE1]<>[CONSUMP_MDDTL_HIST]![MWARE_SUPPORT_TYPE1],LCase$([CONSUMP_MDDTL]![MWARE_SUPPORT_TYPE1])+Space$(2)+LCase$([CONSUMP_MDDTL_HIST]![MWARE_SUPPORT_TYPE1]),Null)
AS Expr24,

Switch([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS]) AS
Expr26,

CONSUMP_MDDTL.OS_PLATFORM_TYPE
FROM
[Service Unit Conversion table],
CONSUMP_MDDTL INNER JOIN CONSUMP_MDDTL_HIST ON CONSUMP_MDDTL.HOST_ID =
CONSUMP_MDDTL_HIST.HOST_ID
WHERE
(((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND (([CONSUMP_MDDTL]![TOTAL_SU])<>[CONSUMP_MDDTL_HIST]![TOTAL_SU])
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8)))))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND (([CONSUMP_MDDTL]![FBGIG])<>[CONSUMP_MDDTL_HIST]![FBGIG]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.STORGIG)<>[CONSUMP_MDDTL_HIST]![STORGIG]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL.SOFTWARE_CURRENCY)<>[CONSUMP_MDDTL_HIST]![SOFTWARE_CURRENCY]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.STORAGE_MGMT)<>[CONSUMP_MDDTL_HIST]![STORAGE_MGMT]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.FBMGMT)<>[CONSUMP_MDDTL_HIST]![FBMGMT]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.MWARE_TYPE1)<>[CONSUMP_MDDTL_HIST]![MWARE_TYPE1]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.BATCH_MGMT)<>[CONSUMP_MDDTL_HIST]![BATCH_MGMT]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL_HIST.CAPACITY_PLANNING)<>[CONSUMP_MDDTL_HIST]![CAPACITY_PLANNING]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL.STRATEGIC_LEVERAGE)<>[CONSUMP_MDDTL_HIST]![STRATEGIC_LEVERAGE]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.SUPPORT_LEVEL)<>[CONSUMP_MDDTL_HIST]![SUPPORT_LEVEL]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL.DATABASE_ENGINEERING)<>[CONSUMP_MDDTL_HIST]![DATABASE_ENGINEERING]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL.DATABASE_ADMINISTRATION)<>[CONSUMP_MDDTL_HIST]![DATABASE_ADMINISTRATION]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL.DATABASE_MANAGEMENT)<>[CONSUMP_MDDTL_HIST]![DATABASE_MANAGEMENT]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.DB_SCHEMAS)<>[CONSUMP_MDDTL_HIST]![DB_SCHEMAS])) OR
(((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.DB_GIG)<>[CONSUMP_MDDTL_HIST]![DB_GIG]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.DB_OBJECTS)<>[CONSUMP_MDDTL_HIST]![DB_OBJECTS]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND
((CONSUMP_MDDTL.OS_PLATFORM_TYPE)<>[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE])
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8)))))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND ((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL.MWARE_SUPPORT_TYPE1)<>[CONSUMP_MDDTL_HIST]![MWARE_SUPPORT_TYPE1]));

What is the full SQL statement of the query that you're using? An IIf
statement by itself will not create two records in a query.

--

Ken Snell
<MS ACCESS MVP>

If I use this line:
Expr26: IIF([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])
I get two duplicate line in my out put. One with this expression with the
correct data (the data I expect) then I also get an exact duplicate line
with
this expression and a null in this column.
That made sense to me as the if clause goes: IIF( expression, true, false)
and the false may appear as a Null in the data.
SO i converted to a Switch statment:
Expr26: Switch([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE
UNITS])
I got the same results and that does not make sense to me as the
expression
is:
Switch( expression, expression)
Can anyone explain what i am mission or at least a way to do this so I get
an answer only when the expression is satisfied?
I requested this same thing last week and now I can't find the question
let
alone the answers, so I home this one stays.

 

[Ken Snell [MVP]]

The issue is that you are using a Cartesian "join" for your tables. Note the
FROM clause in your query:

FROM
[Service Unit Conversion table],
CONSUMP_MDDTL INNER JOIN CONSUMP_MDDTL_HIST ON CONSUMP_MDDTL.HOST_ID =
CONSUMP_MDDTL_HIST.HOST_ID

What your query is doing is matching every record in the [Service Unit
Conversion table] table with every record returned by the join of
CONSUMP_MDDTL and CONSUMP_MDDTL_HIST tables. This indeed can produce
duplicate records in your output.

I believe you need to revise how you're joining the tables.

--

Ken Snell
<MS ACCESS MVP>

If you should try to run a portion of the code, the Date is in the form
ddMMMyyyy.
The code give the correct answers until expression 26 is added, so I'm
not quite sure what I did not give, expression 26 is complete, but the
query
is:
SELECT DISTINCT CONSUMP_MDDTL.BUS_UNIT, CONSUMP_MDDTL.ROADMAP_BUS_UNIT,
CONSUMP_MDDTL.APP_NAME,
CONSUMP_MDDTL.HOST_ID,
CONSUMP_MDDTL.REGIONAL_NAME,
CONSUMP_MDDTL.BR_NUMBER,
CONSUMP_MDDTL.ARC,
CONSUMP_MDDTL.CNTRCT_CD,
CONSUMP_MDDTL.USAGE_DT,
Str$([CONSUMP_MDDTL]![TOTAL_SU])+" -
"+Str$([CONSUMP_MDDTL_HIST]![TOTAL_SU])
AS Expr12,
[CONSUMP_MDDTL]![TOTAL_SU]-[CONSUMP_MDDTL_HIST]![TOTAL_SU]
AS Expr25,
IIf([CONSUMP_MDDTL]![FBGIG]<>[CONSUMP_MDDTL_HIST]![FBGIG],([CONSUMP_MDDTL]![FBGIG])-([CONSUMP_MDDTL_HIST]![FBGIG]),Null)
AS Expr1,
IIf([CONSUMP_MDDTL]![STORGIG]<>[CONSUMP_MDDTL_HIST]![STORGIG],([CONSUMP_MDDTL]![STORGIG])-([CONSUMP_MDDTL_HIST]![STORGIG]),Null)
AS Expr2,
IIf([CONSUMP_MDDTL]![SOFTWARE_CURRENCY]<>[CONSUMP_MDDTL_HIST]![SOFTWARE_CURRENCY],LCase$([CONSUMP_MDDTL]![SOFTWARE_CURRENCY])+Space$(2)+LCase$([CONSUMP_MDDTL_HIST]![SOFTWARE_CURRENCY]),Null)
AS Expr3,
IIf([CONSUMP_MDDTL]![STORAGE_MGMT]<>[CONSUMP_MDDTL_HIST]![STORAGE_MGMT],LCase$([CONSUMP_MDDTL]![STORAGE_MGMT])+Space$(1)+LCase$([CONSUMP_MDDTL_HIST]![STORAGE_MGMT]),Null)
AS Expr4,
IIf([CONSUMP_MDDTL]![FBMGMT]<>[CONSUMP_MDDTL_HIST]![FBMGMT],LCase$([CONSUMP_MDDTL]![FBMGMT])+Space$(1)+LCase$([CONSUMP_MDDTL_HIST]![FBMGMT]),Null)
AS Expr5,
IIf([CONSUMP_MDDTL]![DATABASE_ENGINEERING]<>[CONSUMP_MDDTL_HIST]![DATABASE_ENGINEERING],([CONSUMP_MDDTL]![DATABASE_ENGINEERING])-([CONSUMP_MDDTL_HIST]![DATABASE_ENGINEERING]),Null)
AS Expr6,
IIf([CONSUMP_MDDTL]![MWARE_TYPE1]<>[CONSUMP_MDDTL_HIST]![MWARE_TYPE1],([CONSUMP_MDDTL]![MWARE_TYPE1])-([CONSUMP_MDDTL_HIST]![MWARE_TYPE1]),Null)
AS Expr7,
IIf([CONSUMP_MDDTL]![MWARE_NBR_INSTNCS_TYPE1]<>[CONSUMP_MDDTL_HIST]![MWARE_NBR_INSTNCS_TYPE1],([CONSUMP_MDDTL]![MWARE_NBR_INSTNCS_TYPE1])-([CONSUMP_MDDTL_HIST]![MWARE_NBR_INSTNCS_TYPE1]),Null)
AS Expr8,
IIf([CONSUMP_MDDTL]![MWARE_NBR_INSTNCS_TYPE2]<>[CONSUMP_MDDTL_HIST]![MWARE_NBR_INSTNCS_TYPE2],([CONSUMP_MDDTL]![MWARE_NBR_INSTNCS_TYPE2])-([CONSUMP_MDDTL_HIST]![MWARE_NBR_INSTNCS_TYPE2]),Null)
AS Expr9,
IIf([CONSUMP_MDDTL]![MWARE_TYPE1]<>[CONSUMP_MDDTL_HIST]![MWARE_TYPE1],([CONSUMP_MDDTL]![MWARE_TYPE1])-([CONSUMP_MDDTL_HIST]![MWARE_TYPE1]),Null)
AS Expr10,
IIf([CONSUMP_MDDTL]![MWARE_TYPE2]<>[CONSUMP_MDDTL_HIST]![MWARE_TYPE2],([CONSUMP_MDDTL]![MWARE_TYPE2])-([CONSUMP_MDDTL_HIST]![MWARE_TYPE2]),Null)
AS Expr11,
IIf([CONSUMP_MDDTL]![BATCH_MGMT]<>[CONSUMP_MDDTL_HIST]![BATCH_MGMT],([CONSUMP_MDDTL]![BATCH_MGMT])-([CONSUMP_MDDTL_HIST]![BATCH_MGMT]),Null)
AS Expr13,
IIf([CONSUMP_MDDTL]![CAPACITY_PLANNING]<>[CONSUMP_MDDTL_HIST]![CAPACITY_PLANNING],LCase$([CONSUMP_MDDTL]![CAPACITY_PLANNING])+Space$(1)+LCase$([CONSUMP_MDDTL_HIST]![CAPACITY_PLANNING]),Null)
AS Expr14,
IIf([CONSUMP_MDDTL]![STRATEGIC_LEVERAGE]<>[CONSUMP_MDDTL_HIST]![STRATEGIC_LEVERAGE],LCase$([CONSUMP_MDDTL]![STRATEGIC_LEVERAGE])+Space$(1)+LCase$([CONSUMP_MDDTL_HIST]![STRATEGIC_LEVERAGE]),Null)
AS Expr15,
IIf([CONSUMP_MDDTL]![SUPPORT_LEVEL]<>[CONSUMP_MDDTL_HIST]![SUPPORT_LEVEL],LCase$([CONSUMP_MDDTL]![SUPPORT_LEVEL])+Space$(2)+LCase$([CONSUMP_MDDTL_HIST]![SUPPORT_LEVEL]),Null)
AS Expr16,
IIf([CONSUMP_MDDTL]![DATABASE_ENGINEERING]<>[CONSUMP_MDDTL_HIST]![DATABASE_ENGINEERING],([CONSUMP_MDDTL]![DATABASE_ENGINEERING])-([CONSUMP_MDDTL_HIST]![DATABASE_ENGINEERING]),Null)
AS Expr17,
IIf([CONSUMP_MDDTL]![DATABASE_ADMINISTRATION]<>[CONSUMP_MDDTL_HIST]![DATABASE_ADMINISTRATION],([CONSUMP_MDDTL]![DATABASE_ADMINISTRATION])-([CONSUMP_MDDTL_HIST]![DATABASE_ADMINISTRATION]),Null)
AS Expr18,
IIf([CONSUMP_MDDTL]![DATABASE_MANAGEMENT]<>[CONSUMP_MDDTL_HIST]![DATABASE_MANAGEMENT],([CONSUMP_MDDTL]![DATABASE_MANAGEMENT])-([CONSUMP_MDDTL_HIST]![DATABASE_MANAGEMENT]),Null)
AS Expr19,
IIf([CONSUMP_MDDTL]![DB_SCHEMAS]<>[CONSUMP_MDDTL_HIST]![DB_SCHEMAS],([CONSUMP_MDDTL]![DB_SCHEMAS])-([CONSUMP_MDDTL_HIST]![DB_SCHEMAS]),Null)
AS Expr20,
IIf([CONSUMP_MDDTL]![DB_GIG]<>[CONSUMP_MDDTL_HIST]![DB_GIG],([CONSUMP_MDDTL]![DB_GIG])-([CONSUMP_MDDTL_HIST]![DB_GIG]),Null)
AS Expr21,
IIf([CONSUMP_MDDTL]![DB_OBJECTS]<>[CONSUMP_MDDTL_HIST]![DB_OBJECTS],([CONSUMP_MDDTL]![DB_OBJECTS])-([CONSUMP_MDDTL_HIST]![DB_OBJECTS]),Null)
AS Expr22,
IIf([CONSUMP_MDDTL]![OS_PLATFORM_TYPE]<>[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE],LCase$([CONSUMP_MDDTL]![OS_PLATFORM_TYPE])+Space$(2)+LCase$([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]),Null)
AS Expr23,
IIf([CONSUMP_MDDTL]![MWARE_SUPPORT_TYPE1]<>[CONSUMP_MDDTL_HIST]![MWARE_SUPPORT_TYPE1],LCase$([CONSUMP_MDDTL]![MWARE_SUPPORT_TYPE1])+Space$(2)+LCase$([CONSUMP_MDDTL_HIST]![MWARE_SUPPORT_TYPE1]),Null)
AS Expr24,

Switch([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS]) AS
Expr26,

CONSUMP_MDDTL.OS_PLATFORM_TYPE
FROM
[Service Unit Conversion table],
CONSUMP_MDDTL INNER JOIN CONSUMP_MDDTL_HIST ON CONSUMP_MDDTL.HOST_ID =
CONSUMP_MDDTL_HIST.HOST_ID
WHERE
(((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND (([CONSUMP_MDDTL]![TOTAL_SU])<>[CONSUMP_MDDTL_HIST]![TOTAL_SU])
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8)))))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND (([CONSUMP_MDDTL]![FBGIG])<>[CONSUMP_MDDTL_HIST]![FBGIG]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.STORGIG)<>[CONSUMP_MDDTL_HIST]![STORGIG]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL.SOFTWARE_CURRENCY)<>[CONSUMP_MDDTL_HIST]![SOFTWARE_CURRENCY]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.STORAGE_MGMT)<>[CONSUMP_MDDTL_HIST]![STORAGE_MGMT]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.FBMGMT)<>[CONSUMP_MDDTL_HIST]![FBMGMT]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.MWARE_TYPE1)<>[CONSUMP_MDDTL_HIST]![MWARE_TYPE1]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.BATCH_MGMT)<>[CONSUMP_MDDTL_HIST]![BATCH_MGMT]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL_HIST.CAPACITY_PLANNING)<>[CONSUMP_MDDTL_HIST]![CAPACITY_PLANNING]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL.STRATEGIC_LEVERAGE)<>[CONSUMP_MDDTL_HIST]![STRATEGIC_LEVERAGE]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.SUPPORT_LEVEL)<>[CONSUMP_MDDTL_HIST]![SUPPORT_LEVEL]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL.DATABASE_ENGINEERING)<>[CONSUMP_MDDTL_HIST]![DATABASE_ENGINEERING]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL.DATABASE_ADMINISTRATION)<>[CONSUMP_MDDTL_HIST]![DATABASE_ADMINISTRATION]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL.DATABASE_MANAGEMENT)<>[CONSUMP_MDDTL_HIST]![DATABASE_MANAGEMENT]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.DB_SCHEMAS)<>[CONSUMP_MDDTL_HIST]![DB_SCHEMAS])) OR
(((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.DB_GIG)<>[CONSUMP_MDDTL_HIST]![DB_GIG]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL.DB_OBJECTS)<>[CONSUMP_MDDTL_HIST]![DB_OBJECTS]))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND
((CONSUMP_MDDTL.OS_PLATFORM_TYPE)<>[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE])
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8)))))
OR (((CONSUMP_MDDTL.CNTRCT_CD)="AFF")
AND
((CONSUMP_MDDTL.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND ((CONSUMP_MDDTL.BILLABLE)="Y")
AND
((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL.MWARE_SUPPORT_TYPE1)<>[CONSUMP_MDDTL_HIST]![MWARE_SUPPORT_TYPE1]));

What is the full SQL statement of the query that you're using? An IIf
statement by itself will not create two records in a query.

--

Ken Snell
<MS ACCESS MVP>

If I use this line:
Expr26: IIF([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])
I get two duplicate line in my out put. One with this expression with
the
correct data (the data I expect) then I also get an exact duplicate
line
with
this expression and a null in this column.
That made sense to me as the if clause goes: IIF( expression, true,
false)
and the false may appear as a Null in the data.
SO i converted to a Switch statment:
Expr26: Switch([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion table]![SERVICE LEVEL],[Service Unit Conversion
table]![SERVICE
UNITS])
I got the same results and that does not make sense to me as the
expression
is:
Switch( expression, expression)
Can anyone explain what i am mission or at least a way to do this so I
get
an answer only when the expression is satisfied?
I requested this same thing last week and now I can't find the question
let
alone the answers, so I home this one stays.

 

[Guest]

I shortened up the code and wrote it without the "Join" that Access likes to
use. Ran it and it worked, then added the "Switch." Once the "Switch" is
added to the code an extra line is returned with the correct data and a null
added where one of the three amount should appear. What should appear is 122
(Service Units) 172 (Service Units) 138 (Service Units). That also puzzles
me. I might even be able to understand if there were two incorrect lines
returned, as there are three possible choices in the Service Unit conversion
Table. I still would consider doing this another way if you have any idea.

SELECT
DISTINCT
CONSUMP_MDDTL_HIST_1.HOST_ID,
CONSUMP_MDDTL_HIST.APP_NAME,
CONSUMP_MDDTL_HIST.REGIONAL_NAME,
CONSUMP_MDDTL_HIST.TOTAL_SU,
CONSUMP_MDDTL_HIST.USAGE_DT,
Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])
AS Expr1,
CONSUMP_MDDTL_HIST_1.HOST_ID, CONSUMP_MDDTL_HIST_1.APP_NAME,
CONSUMP_MDDTL_HIST_1.TOTAL_SU, CONSUMP_MDDTL_HIST_1.USAGE_DT
FROM [Service Unit Conversion table],
CONSUMP_MDDTL_HIST,
CONSUMP_MDDTL_HIST AS CONSUMP_MDDTL_HIST_1
WHERE
(((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL_HIST_1.TOTAL_SU)<>[CONSUMP_MDDTL_HIST].[TOTAL_SU])
AND
((CONSUMP_MDDTL_HIST_1.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND CONSUMP_MDDTL_HIST.HOST_ID = CONSUMP_MDDTL_HIST_1.HOST_ID);

 

[Ken Snell [MVP]]

The Switch expression that you're using seems less than useful?

Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])

What is supposed to happen if the comparison is False? You'll not get any
value from this expression in that case. The Switch function is used to
display one value based on another expression being True; which implies that
the expression needs to have enough arguments to ensure that you'll get a
value from at least one of the comparisons.

 

[Guest]

???????????????? So far we are in complete agreement. The switch has three
choices, it makes the correct choice and then adds a null line. SO we have
went full circle. I don't know why it is adding the extra line. Which was and
still is the question. I tend to agree the Switch statment is less then
useful as well, So another solution is always welcome, but as I go through
the functions I can't seem to fine any function short of hard coding that
would help. and a hard coded clause is against all reasonable code build, I'm
sure you would agree.???????????

So do you have another idea or are we stuck?

The Switch expression that you're using seems less than useful?

Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])

What is supposed to happen if the comparison is False? You'll not get any
value from this expression in that case. The Switch function is used to
display one value based on another expression being True; which implies that
the expression needs to have enough arguments to ensure that you'll get a
value from at least one of the comparisons.

--

Ken Snell
<MS ACCESS MVP>

I shortened up the code and wrote it without the "Join" that Access likes
to
use. Ran it and it worked, then added the "Switch." Once the "Switch" is
added to the code an extra line is returned with the correct data and a
null
added where one of the three amount should appear. What should appear is
122
(Service Units) 172 (Service Units) 138 (Service Units). That also puzzles
me. I might even be able to understand if there were two incorrect lines
returned, as there are three possible choices in the Service Unit
conversion
Table. I still would consider doing this another way if you have any idea.

SELECT
DISTINCT
CONSUMP_MDDTL_HIST_1.HOST_ID,
CONSUMP_MDDTL_HIST.APP_NAME,
CONSUMP_MDDTL_HIST.REGIONAL_NAME,
CONSUMP_MDDTL_HIST.TOTAL_SU,
CONSUMP_MDDTL_HIST.USAGE_DT,
Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])
AS Expr1,
CONSUMP_MDDTL_HIST_1.HOST_ID, CONSUMP_MDDTL_HIST_1.APP_NAME,
CONSUMP_MDDTL_HIST_1.TOTAL_SU, CONSUMP_MDDTL_HIST_1.USAGE_DT
FROM [Service Unit Conversion table],
CONSUMP_MDDTL_HIST,
CONSUMP_MDDTL_HIST AS CONSUMP_MDDTL_HIST_1
WHERE
(((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL_HIST_1.TOTAL_SU)<>[CONSUMP_MDDTL_HIST].[TOTAL_SU])
AND
((CONSUMP_MDDTL_HIST_1.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND CONSUMP_MDDTL_HIST.HOST_ID = CONSUMP_MDDTL_HIST_1.HOST_ID);

 

[Ken Snell [MVP]]

Don't know about coming full circle, but if the Switch function doesn't find
a match, it outputs a Null value. Try this in the Immediate Window:

ken="k"
?Switch(ken="s","output")
Null

So, if you are attempting to filter out records when there is no match in
the Switch function, that will not work. You will still get an output from
it.

But perhaps that is not the issue of which you post? Although it seems to
be, based on your first post (excerpt from that post):

If I use this line:
Expr26: IIF([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])
I get two duplicate line in my out put. One with this expression with the
correct data (the data I expect) then I also get an exact duplicate line
with
this expression and a null in this column.
That made sense to me as the if clause goes: IIF( expression, true, false)
and the false may appear as a Null in the data.
SO i converted to a Switch statment:
Expr26: Switch([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE
UNITS])
I got the same results and that does not make sense to me as the expression
is:
Switch( expression, expression)

You haven't posted examples of the data and the query's output, so I cannot
"see" what type of "null line" you are getting, nor do I understand what you
are wanting as the result. But I still believe that the Cartesian join is
part of the issue....

--

Ken Snell
<MS ACCESS MVP>

???????????????? So far we are in complete agreement. The switch has three
choices, it makes the correct choice and then adds a null line. SO we have
went full circle. I don't know why it is adding the extra line. Which was
and
still is the question. I tend to agree the Switch statment is less then
useful as well, So another solution is always welcome, but as I go through
the functions I can't seem to fine any function short of hard coding that
would help. and a hard coded clause is against all reasonable code build,
I'm
sure you would agree.???????????

So do you have another idea or are we stuck?

The Switch expression that you're using seems less than useful?

Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])

What is supposed to happen if the comparison is False? You'll not get any
value from this expression in that case. The Switch function is used to
display one value based on another expression being True; which implies
that
the expression needs to have enough arguments to ensure that you'll get a
value from at least one of the comparisons.

--

Ken Snell
<MS ACCESS MVP>

I shortened up the code and wrote it without the "Join" that Access
likes
to
use. Ran it and it worked, then added the "Switch." Once the "Switch"
is
added to the code an extra line is returned with the correct data and a
null
added where one of the three amount should appear. What should appear
is
122
(Service Units) 172 (Service Units) 138 (Service Units). That also
puzzles
me. I might even be able to understand if there were two incorrect
lines
returned, as there are three possible choices in the Service Unit
conversion
Table. I still would consider doing this another way if you have any
idea.

SELECT
DISTINCT
CONSUMP_MDDTL_HIST_1.HOST_ID,
CONSUMP_MDDTL_HIST.APP_NAME,
CONSUMP_MDDTL_HIST.REGIONAL_NAME,
CONSUMP_MDDTL_HIST.TOTAL_SU,
CONSUMP_MDDTL_HIST.USAGE_DT,
Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])
AS Expr1,
CONSUMP_MDDTL_HIST_1.HOST_ID, CONSUMP_MDDTL_HIST_1.APP_NAME,
CONSUMP_MDDTL_HIST_1.TOTAL_SU, CONSUMP_MDDTL_HIST_1.USAGE_DT
FROM [Service Unit Conversion table],
CONSUMP_MDDTL_HIST,
CONSUMP_MDDTL_HIST AS CONSUMP_MDDTL_HIST_1
WHERE
(((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL_HIST_1.TOTAL_SU)<>[CONSUMP_MDDTL_HIST].[TOTAL_SU])
AND
((CONSUMP_MDDTL_HIST_1.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND CONSUMP_MDDTL_HIST.HOST_ID = CONSUMP_MDDTL_HIST_1.HOST_ID);

 

[Guest]

Ken, I'm confuse by your answer, there are only three possible answers as I
stated last time 122, 138, 172. There are no Nulls in the data being
compared. I thought I rewrote the code without the Join as I put an equals in
the Where clause, but maybe that is what you are calling a join?

As I don't see a method of attaching the data to this I'll attemp to copy
some output

(output - no Switch)
APP_NAME CONSUMP_MDDTL_HIST.TOTAL_SU CONSUMP_MDDTL_HIST.USAGE_DT Original
Host ID CONSUMP_MDDTL_HIST_1.TOTAL_SU CONSUMP_MDDTL_HIST_1.USAGE_DT
ACCESS-MR 1099.99 01-Jul-2005 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 bos61822 1158.26 01-Aug-2005
ACCESS-MR 1331.25 01-Jul-2005 bos63949 1331.75 01-Aug-2005
ACTS-MR 228.74 01-Jul-2005 acts 243.66 01-Aug-2005

Notice a single occurance of each Host ID

(With the addition of the Switch.)
APP_NAME CONSUMP_MDDTL_HIST.TOTAL_SU CONSUMP_MDDTL_HIST.USAGE_DT This row is
the problem Original Host
ID CONSUMP_MDDTL_HIST_1.TOTAL_SU CONSUMP_MDDTL_HIST_1.USAGE_DT
ACCESS-MR 1099.99 01-Jul-2005 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1099.99 01-Jul-2005 138 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 bos61822 1158.26 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 138 bos61822 1158.26 01-Aug-2005

Notice multipal occurances of each Host ID

Don't know about coming full circle, but if the Switch function doesn't find
a match, it outputs a Null value. Try this in the Immediate Window:

ken="k"
?Switch(ken="s","output")
Null

So, if you are attempting to filter out records when there is no match in
the Switch function, that will not work. You will still get an output from
it.

But perhaps that is not the issue of which you post? Although it seems to
be, based on your first post (excerpt from that post):

If I use this line:
Expr26: IIF([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])
I get two duplicate line in my out put. One with this expression with the
correct data (the data I expect) then I also get an exact duplicate line
with
this expression and a null in this column.
That made sense to me as the if clause goes: IIF( expression, true, false)
and the false may appear as a Null in the data.
SO i converted to a Switch statment:
Expr26: Switch([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE
UNITS])
I got the same results and that does not make sense to me as the expression
is:
Switch( expression, expression)

You haven't posted examples of the data and the query's output, so I cannot
"see" what type of "null line" you are getting, nor do I understand what you
are wanting as the result. But I still believe that the Cartesian join is
part of the issue....

--

Ken Snell
<MS ACCESS MVP>

???????????????? So far we are in complete agreement. The switch has three
choices, it makes the correct choice and then adds a null line. SO we have
went full circle. I don't know why it is adding the extra line. Which was
and
still is the question. I tend to agree the Switch statment is less then
useful as well, So another solution is always welcome, but as I go through
the functions I can't seem to fine any function short of hard coding that
would help. and a hard coded clause is against all reasonable code build,
I'm
sure you would agree.???????????

So do you have another idea or are we stuck?

The Switch expression that you're using seems less than useful?

Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])

What is supposed to happen if the comparison is False? You'll not get any
value from this expression in that case. The Switch function is used to
display one value based on another expression being True; which implies
that
the expression needs to have enough arguments to ensure that you'll get a
value from at least one of the comparisons.

--

Ken Snell
<MS ACCESS MVP>


I shortened up the code and wrote it without the "Join" that Access
likes
to
use. Ran it and it worked, then added the "Switch." Once the "Switch"
is
added to the code an extra line is returned with the correct data and a
null
added where one of the three amount should appear. What should appear
is
122
(Service Units) 172 (Service Units) 138 (Service Units). That also
puzzles
me. I might even be able to understand if there were two incorrect
lines
returned, as there are three possible choices in the Service Unit
conversion
Table. I still would consider doing this another way if you have any
idea.

SELECT
DISTINCT
CONSUMP_MDDTL_HIST_1.HOST_ID,
CONSUMP_MDDTL_HIST.APP_NAME,
CONSUMP_MDDTL_HIST.REGIONAL_NAME,
CONSUMP_MDDTL_HIST.TOTAL_SU,
CONSUMP_MDDTL_HIST.USAGE_DT,
Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])
AS Expr1,
CONSUMP_MDDTL_HIST_1.HOST_ID, CONSUMP_MDDTL_HIST_1.APP_NAME,
CONSUMP_MDDTL_HIST_1.TOTAL_SU, CONSUMP_MDDTL_HIST_1.USAGE_DT
FROM [Service Unit Conversion table],
CONSUMP_MDDTL_HIST,
CONSUMP_MDDTL_HIST AS CONSUMP_MDDTL_HIST_1
WHERE
(((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND ((CONSUMP_MDDTL_HIST_1.TOTAL_SU)<>[CONSUMP_MDDTL_HIST].[TOTAL_SU])
AND
((CONSUMP_MDDTL_HIST_1.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND CONSUMP_MDDTL_HIST.HOST_ID = CONSUMP_MDDTL_HIST_1.HOST_ID);

 

[Ken Snell [MVP]]

No, I didn't say there were Nulls in your data. What I said was that the
Switch function will return a Null value if the
[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE] value does not equal the [Service
Unit Conversion table]![SERVICE LEVEL] value for a record.

Again, I believe the problem is because the cartesian query is matching
records against each other that you don't really want to match. When the
[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE] value does not equal the [Service
Unit Conversion table]![SERVICE LEVEL] value for a record, then the query
displays a Null value for the Expr26 field. So you need to restructure your
query to eliminate the possibility of there being no match for any record,
or else change the Switch function to have more argument pairs so that at
least one will be true for every record in the query, or else go back to the
IIf function setup with a True and False argument expression.
--

Ken Snell
<MS ACCESS MVP>

Ken, I'm confuse by your answer, there are only three possible answers as
I
stated last time 122, 138, 172. There are no Nulls in the data being
compared. I thought I rewrote the code without the Join as I put an equals
in
the Where clause, but maybe that is what you are calling a join?

As I don't see a method of attaching the data to this I'll attemp to copy
some output

(output - no Switch)
APP_NAME CONSUMP_MDDTL_HIST.TOTAL_SU CONSUMP_MDDTL_HIST.USAGE_DT Original
Host ID CONSUMP_MDDTL_HIST_1.TOTAL_SU CONSUMP_MDDTL_HIST_1.USAGE_DT
ACCESS-MR 1099.99 01-Jul-2005 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 bos61822 1158.26 01-Aug-2005
ACCESS-MR 1331.25 01-Jul-2005 bos63949 1331.75 01-Aug-2005
ACTS-MR 228.74 01-Jul-2005 acts 243.66 01-Aug-2005

Notice a single occurance of each Host ID

(With the addition of the Switch.)
APP_NAME CONSUMP_MDDTL_HIST.TOTAL_SU CONSUMP_MDDTL_HIST.USAGE_DT This row
is
the problem Original Host
ID CONSUMP_MDDTL_HIST_1.TOTAL_SU CONSUMP_MDDTL_HIST_1.USAGE_DT
ACCESS-MR 1099.99 01-Jul-2005 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1099.99 01-Jul-2005 138 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 bos61822 1158.26 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 138 bos61822 1158.26 01-Aug-2005

Notice multipal occurances of each Host ID

Don't know about coming full circle, but if the Switch function doesn't
find
a match, it outputs a Null value. Try this in the Immediate Window:

ken="k"
?Switch(ken="s","output")
Null

So, if you are attempting to filter out records when there is no match in
the Switch function, that will not work. You will still get an output
from
it.

But perhaps that is not the issue of which you post? Although it seems to
be, based on your first post (excerpt from that post):

If I use this line:
Expr26: IIF([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])
I get two duplicate line in my out put. One with this expression with the
correct data (the data I expect) then I also get an exact duplicate line
with
this expression and a null in this column.
That made sense to me as the if clause goes: IIF( expression, true,
false)
and the false may appear as a Null in the data.
SO i converted to a Switch statment:
Expr26: Switch([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion table]![SERVICE LEVEL],[Service Unit Conversion
table]![SERVICE
UNITS])
I got the same results and that does not make sense to me as the
expression
is:
Switch( expression, expression)

You haven't posted examples of the data and the query's output, so I
cannot
"see" what type of "null line" you are getting, nor do I understand what
you
are wanting as the result. But I still believe that the Cartesian join is
part of the issue....

--

Ken Snell
<MS ACCESS MVP>

???????????????? So far we are in complete agreement. The switch has
three
choices, it makes the correct choice and then adds a null line. SO we
have
went full circle. I don't know why it is adding the extra line. Which
was
and
still is the question. I tend to agree the Switch statment is less then
useful as well, So another solution is always welcome, but as I go
through
the functions I can't seem to fine any function short of hard coding
that
would help. and a hard coded clause is against all reasonable code
build,
I'm
sure you would agree.???????????

So do you have another idea or are we stuck?

:

The Switch expression that you're using seems less than useful?

Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE
UNITS])

What is supposed to happen if the comparison is False? You'll not get
any
value from this expression in that case. The Switch function is used
to
display one value based on another expression being True; which
implies
that
the expression needs to have enough arguments to ensure that you'll
get a
value from at least one of the comparisons.

--

Ken Snell
<MS ACCESS MVP>


I shortened up the code and wrote it without the "Join" that Access
likes
to
use. Ran it and it worked, then added the "Switch." Once the
"Switch"
is
added to the code an extra line is returned with the correct data
and a
null
added where one of the three amount should appear. What should
appear
is
122
(Service Units) 172 (Service Units) 138 (Service Units). That also
puzzles
me. I might even be able to understand if there were two incorrect
lines
returned, as there are three possible choices in the Service Unit
conversion
Table. I still would consider doing this another way if you have any
idea.

SELECT
DISTINCT
CONSUMP_MDDTL_HIST_1.HOST_ID,
CONSUMP_MDDTL_HIST.APP_NAME,
CONSUMP_MDDTL_HIST.REGIONAL_NAME,
CONSUMP_MDDTL_HIST.TOTAL_SU,
CONSUMP_MDDTL_HIST.USAGE_DT,
Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE
UNITS])
AS Expr1,
CONSUMP_MDDTL_HIST_1.HOST_ID, CONSUMP_MDDTL_HIST_1.APP_NAME,
CONSUMP_MDDTL_HIST_1.TOTAL_SU, CONSUMP_MDDTL_HIST_1.USAGE_DT
FROM [Service Unit Conversion table],
CONSUMP_MDDTL_HIST,
CONSUMP_MDDTL_HIST AS CONSUMP_MDDTL_HIST_1
WHERE
(((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL_HIST_1.TOTAL_SU)<>[CONSUMP_MDDTL_HIST].[TOTAL_SU])
AND
((CONSUMP_MDDTL_HIST_1.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND CONSUMP_MDDTL_HIST.HOST_ID = CONSUMP_MDDTL_HIST_1.HOST_ID);

 

[Guest]

I replyed to this yesterday and it seems to be missing: there is not option
for a no match and the IFF statment give the same results. SO how would you
resturcture it?

No, I didn't say there were Nulls in your data. What I said was that the
Switch function will return a Null value if the
[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE] value does not equal the [Service
Unit Conversion table]![SERVICE LEVEL] value for a record.

Again, I believe the problem is because the cartesian query is matching
records against each other that you don't really want to match. When the
[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE] value does not equal the [Service
Unit Conversion table]![SERVICE LEVEL] value for a record, then the query
displays a Null value for the Expr26 field. So you need to restructure your
query to eliminate the possibility of there being no match for any record,
or else change the Switch function to have more argument pairs so that at
least one will be true for every record in the query, or else go back to the
IIf function setup with a True and False argument expression.
--

Ken Snell
<MS ACCESS MVP>

Ken, I'm confuse by your answer, there are only three possible answers as
I
stated last time 122, 138, 172. There are no Nulls in the data being
compared. I thought I rewrote the code without the Join as I put an equals
in
the Where clause, but maybe that is what you are calling a join?

As I don't see a method of attaching the data to this I'll attemp to copy
some output

(output - no Switch)
APP_NAME CONSUMP_MDDTL_HIST.TOTAL_SU CONSUMP_MDDTL_HIST.USAGE_DT Original
Host ID CONSUMP_MDDTL_HIST_1.TOTAL_SU CONSUMP_MDDTL_HIST_1.USAGE_DT
ACCESS-MR 1099.99 01-Jul-2005 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 bos61822 1158.26 01-Aug-2005
ACCESS-MR 1331.25 01-Jul-2005 bos63949 1331.75 01-Aug-2005
ACTS-MR 228.74 01-Jul-2005 acts 243.66 01-Aug-2005

Notice a single occurance of each Host ID

(With the addition of the Switch.)
APP_NAME CONSUMP_MDDTL_HIST.TOTAL_SU CONSUMP_MDDTL_HIST.USAGE_DT This row
is
the problem Original Host
ID CONSUMP_MDDTL_HIST_1.TOTAL_SU CONSUMP_MDDTL_HIST_1.USAGE_DT
ACCESS-MR 1099.99 01-Jul-2005 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1099.99 01-Jul-2005 138 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 bos61822 1158.26 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 138 bos61822 1158.26 01-Aug-2005

Notice multipal occurances of each Host ID

Don't know about coming full circle, but if the Switch function doesn't
find
a match, it outputs a Null value. Try this in the Immediate Window:

ken="k"
?Switch(ken="s","output")
Null

So, if you are attempting to filter out records when there is no match in
the Switch function, that will not work. You will still get an output
from
it.

But perhaps that is not the issue of which you post? Although it seems to
be, based on your first post (excerpt from that post):

If I use this line:
Expr26: IIF([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE UNITS])
I get two duplicate line in my out put. One with this expression with the
correct data (the data I expect) then I also get an exact duplicate line
with
this expression and a null in this column.
That made sense to me as the if clause goes: IIF( expression, true,
false)
and the false may appear as a Null in the data.
SO i converted to a Switch statment:
Expr26: Switch([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion table]![SERVICE LEVEL],[Service Unit Conversion
table]![SERVICE
UNITS])
I got the same results and that does not make sense to me as the
expression
is:
Switch( expression, expression)

You haven't posted examples of the data and the query's output, so I
cannot
"see" what type of "null line" you are getting, nor do I understand what
you
are wanting as the result. But I still believe that the Cartesian join is
part of the issue....

--

Ken Snell
<MS ACCESS MVP>


???????????????? So far we are in complete agreement. The switch has
three
choices, it makes the correct choice and then adds a null line. SO we
have
went full circle. I don't know why it is adding the extra line. Which
was
and
still is the question. I tend to agree the Switch statment is less then
useful as well, So another solution is always welcome, but as I go
through
the functions I can't seem to fine any function short of hard coding
that
would help. and a hard coded clause is against all reasonable code
build,
I'm
sure you would agree.???????????

So do you have another idea or are we stuck?

:

The Switch expression that you're using seems less than useful?

Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE
UNITS])

What is supposed to happen if the comparison is False? You'll not get
any
value from this expression in that case. The Switch function is used
to
display one value based on another expression being True; which
implies
that
the expression needs to have enough arguments to ensure that you'll
get a
value from at least one of the comparisons.

--

Ken Snell
<MS ACCESS MVP>


I shortened up the code and wrote it without the "Join" that Access
likes
to
use. Ran it and it worked, then added the "Switch." Once the
"Switch"
is
added to the code an extra line is returned with the correct data
and a
null
added where one of the three amount should appear. What should
appear
is
122
(Service Units) 172 (Service Units) 138 (Service Units). That also
puzzles
me. I might even be able to understand if there were two incorrect
lines
returned, as there are three possible choices in the Service Unit
conversion
Table. I still would consider doing this another way if you have any
idea.

SELECT
DISTINCT
CONSUMP_MDDTL_HIST_1.HOST_ID,
CONSUMP_MDDTL_HIST.APP_NAME,
CONSUMP_MDDTL_HIST.REGIONAL_NAME,
CONSUMP_MDDTL_HIST.TOTAL_SU,
CONSUMP_MDDTL_HIST.USAGE_DT,
Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE
UNITS])
AS Expr1,
CONSUMP_MDDTL_HIST_1.HOST_ID, CONSUMP_MDDTL_HIST_1.APP_NAME,
CONSUMP_MDDTL_HIST_1.TOTAL_SU, CONSUMP_MDDTL_HIST_1.USAGE_DT
FROM [Service Unit Conversion table],
CONSUMP_MDDTL_HIST,
CONSUMP_MDDTL_HIST AS CONSUMP_MDDTL_HIST_1
WHERE
(((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL_HIST_1.TOTAL_SU)<>[CONSUMP_MDDTL_HIST].[TOTAL_SU])
AND
((CONSUMP_MDDTL_HIST_1.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND CONSUMP_MDDTL_HIST.HOST_ID = CONSUMP_MDDTL_HIST_1.HOST_ID);

 

[Ken Snell [MVP]]

Tell me the following information so that I can suggest an expression for
you:

-- what are the possible values for
[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]

-- what are the possible values for [Service Unit Conversion
table]![SERVICE LEVEL]

-- what is the value that is to be used in the query if
[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE] equals [Service Unit Conversion
table]![SERVICE LEVEL]

-- what is the value that is to be used in the query if
[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE] does not equal [Service Unit
Conversion table]![SERVICE LEVEL]

--

Ken Snell
<MS ACCESS MVP>

I replyed to this yesterday and it seems to be missing: there is not option
for a no match and the IFF statment give the same results. SO how would
you
resturcture it?

No, I didn't say there were Nulls in your data. What I said was that the
Switch function will return a Null value if the
[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE] value does not equal the [Service
Unit Conversion table]![SERVICE LEVEL] value for a record.

Again, I believe the problem is because the cartesian query is matching
records against each other that you don't really want to match. When the
[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE] value does not equal the [Service
Unit Conversion table]![SERVICE LEVEL] value for a record, then the query
displays a Null value for the Expr26 field. So you need to restructure
your
query to eliminate the possibility of there being no match for any
record,
or else change the Switch function to have more argument pairs so that at
least one will be true for every record in the query, or else go back to
the
IIf function setup with a True and False argument expression.
--

Ken Snell
<MS ACCESS MVP>

Ken, I'm confuse by your answer, there are only three possible answers
as
I
stated last time 122, 138, 172. There are no Nulls in the data being
compared. I thought I rewrote the code without the Join as I put an
equals
in
the Where clause, but maybe that is what you are calling a join?

As I don't see a method of attaching the data to this I'll attemp to
copy
some output

(output - no Switch)
APP_NAME CONSUMP_MDDTL_HIST.TOTAL_SU CONSUMP_MDDTL_HIST.USAGE_DT
Original
Host ID CONSUMP_MDDTL_HIST_1.TOTAL_SU CONSUMP_MDDTL_HIST_1.USAGE_DT
ACCESS-MR 1099.99 01-Jul-2005 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 bos61822 1158.26 01-Aug-2005
ACCESS-MR 1331.25 01-Jul-2005 bos63949 1331.75 01-Aug-2005
ACTS-MR 228.74 01-Jul-2005 acts 243.66 01-Aug-2005

Notice a single occurance of each Host ID

(With the addition of the Switch.)
APP_NAME CONSUMP_MDDTL_HIST.TOTAL_SU CONSUMP_MDDTL_HIST.USAGE_DT This
row
is
the problem Original Host
ID CONSUMP_MDDTL_HIST_1.TOTAL_SU CONSUMP_MDDTL_HIST_1.USAGE_DT
ACCESS-MR 1099.99 01-Jul-2005 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1099.99 01-Jul-2005 138 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 bos61822 1158.26 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 138 bos61822 1158.26 01-Aug-2005

Notice multipal occurances of each Host ID
:

Don't know about coming full circle, but if the Switch function
doesn't
find
a match, it outputs a Null value. Try this in the Immediate Window:

ken="k"
?Switch(ken="s","output")
Null

So, if you are attempting to filter out records when there is no match
in
the Switch function, that will not work. You will still get an output
from
it.

But perhaps that is not the issue of which you post? Although it seems
to
be, based on your first post (excerpt from that post):

If I use this line:
Expr26: IIF([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE
UNITS])
I get two duplicate line in my out put. One with this expression with
the
correct data (the data I expect) then I also get an exact duplicate
line
with
this expression and a null in this column.
That made sense to me as the if clause goes: IIF( expression, true,
false)
and the false may appear as a Null in the data.
SO i converted to a Switch statment:
Expr26: Switch([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion table]![SERVICE LEVEL],[Service Unit Conversion
table]![SERVICE
UNITS])
I got the same results and that does not make sense to me as the
expression
is:
Switch( expression, expression)

You haven't posted examples of the data and the query's output, so I
cannot
"see" what type of "null line" you are getting, nor do I understand
what
you
are wanting as the result. But I still believe that the Cartesian join
is
part of the issue....

--

Ken Snell
<MS ACCESS MVP>


???????????????? So far we are in complete agreement. The switch has
three
choices, it makes the correct choice and then adds a null line. SO
we
have
went full circle. I don't know why it is adding the extra line.
Which
was
and
still is the question. I tend to agree the Switch statment is less
then
useful as well, So another solution is always welcome, but as I go
through
the functions I can't seem to fine any function short of hard coding
that
would help. and a hard coded clause is against all reasonable code
build,
I'm
sure you would agree.???????????

So do you have another idea or are we stuck?

:

The Switch expression that you're using seems less than useful?

Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE
UNITS])

What is supposed to happen if the comparison is False? You'll not
get
any
value from this expression in that case. The Switch function is
used
to
display one value based on another expression being True; which
implies
that
the expression needs to have enough arguments to ensure that you'll
get a
value from at least one of the comparisons.

--

Ken Snell
<MS ACCESS MVP>


I shortened up the code and wrote it without the "Join" that
Access
likes
to
use. Ran it and it worked, then added the "Switch." Once the
"Switch"
is
added to the code an extra line is returned with the correct data
and a
null
added where one of the three amount should appear. What should
appear
is
122
(Service Units) 172 (Service Units) 138 (Service Units). That
also
puzzles
me. I might even be able to understand if there were two
incorrect
lines
returned, as there are three possible choices in the Service Unit
conversion
Table. I still would consider doing this another way if you have
any
idea.

SELECT
DISTINCT
CONSUMP_MDDTL_HIST_1.HOST_ID,
CONSUMP_MDDTL_HIST.APP_NAME,
CONSUMP_MDDTL_HIST.REGIONAL_NAME,
CONSUMP_MDDTL_HIST.TOTAL_SU,
CONSUMP_MDDTL_HIST.USAGE_DT,
Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE
UNITS])
AS Expr1,
CONSUMP_MDDTL_HIST_1.HOST_ID, CONSUMP_MDDTL_HIST_1.APP_NAME,
CONSUMP_MDDTL_HIST_1.TOTAL_SU, CONSUMP_MDDTL_HIST_1.USAGE_DT
FROM [Service Unit Conversion table],
CONSUMP_MDDTL_HIST,
CONSUMP_MDDTL_HIST AS CONSUMP_MDDTL_HIST_1
WHERE
(((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL_HIST_1.TOTAL_SU)<>[CONSUMP_MDDTL_HIST].[TOTAL_SU])
AND
((CONSUMP_MDDTL_HIST_1.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND CONSUMP_MDDTL_HIST.HOST_ID = CONSUMP_MDDTL_HIST_1.HOST_ID);

 

[Guest]

Thanks Ken, but I found someone local that was able to diagnose "my" issue.
He says, just because I can do this in SQL, does not mean Access can do it.
He came up with what appears to be the correct way to do this in Access and
sufice it to say, I was not attempting to do it that way. I thought it should
work just like my original SQL statment and it does not. I was trying to do
this all in one statment and it appears I needed to break this down in
simpler steps. He says what I was doing will always throw the null. SO for
later users think simple steps not what you learned to do 20 years ago in SQL
class. (;-{)

Thanks for the attempt.

Tell me the following information so that I can suggest an expression for
you:

-- what are the possible values for
[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]

-- what are the possible values for [Service Unit Conversion
table]![SERVICE LEVEL]

-- what is the value that is to be used in the query if
[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE] equals [Service Unit Conversion
table]![SERVICE LEVEL]

-- what is the value that is to be used in the query if
[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE] does not equal [Service Unit
Conversion table]![SERVICE LEVEL]

--

Ken Snell
<MS ACCESS MVP>

I replyed to this yesterday and it seems to be missing: there is not option
for a no match and the IFF statment give the same results. SO how would
you
resturcture it?

No, I didn't say there were Nulls in your data. What I said was that the
Switch function will return a Null value if the
[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE] value does not equal the [Service
Unit Conversion table]![SERVICE LEVEL] value for a record.

Again, I believe the problem is because the cartesian query is matching
records against each other that you don't really want to match. When the
[CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE] value does not equal the [Service
Unit Conversion table]![SERVICE LEVEL] value for a record, then the query
displays a Null value for the Expr26 field. So you need to restructure
your
query to eliminate the possibility of there being no match for any
record,
or else change the Switch function to have more argument pairs so that at
least one will be true for every record in the query, or else go back to
the
IIf function setup with a True and False argument expression.
--

Ken Snell
<MS ACCESS MVP>



Ken, I'm confuse by your answer, there are only three possible answers
as
I
stated last time 122, 138, 172. There are no Nulls in the data being
compared. I thought I rewrote the code without the Join as I put an
equals
in
the Where clause, but maybe that is what you are calling a join?

As I don't see a method of attaching the data to this I'll attemp to
copy
some output

(output - no Switch)
APP_NAME CONSUMP_MDDTL_HIST.TOTAL_SU CONSUMP_MDDTL_HIST.USAGE_DT
Original
Host ID CONSUMP_MDDTL_HIST_1.TOTAL_SU CONSUMP_MDDTL_HIST_1.USAGE_DT
ACCESS-MR 1099.99 01-Jul-2005 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 bos61822 1158.26 01-Aug-2005
ACCESS-MR 1331.25 01-Jul-2005 bos63949 1331.75 01-Aug-2005
ACTS-MR 228.74 01-Jul-2005 acts 243.66 01-Aug-2005

Notice a single occurance of each Host ID

(With the addition of the Switch.)
APP_NAME CONSUMP_MDDTL_HIST.TOTAL_SU CONSUMP_MDDTL_HIST.USAGE_DT This
row
is
the problem Original Host
ID CONSUMP_MDDTL_HIST_1.TOTAL_SU CONSUMP_MDDTL_HIST_1.USAGE_DT
ACCESS-MR 1099.99 01-Jul-2005 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1099.99 01-Jul-2005 138 bos62798 1092.78 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 bos61822 1158.26 01-Aug-2005
ACCESS-MR 1165.46 01-Jul-2005 138 bos61822 1158.26 01-Aug-2005

Notice multipal occurances of each Host ID
:

Don't know about coming full circle, but if the Switch function
doesn't
find
a match, it outputs a Null value. Try this in the Immediate Window:

ken="k"
?Switch(ken="s","output")
Null

So, if you are attempting to filter out records when there is no match
in
the Switch function, that will not work. You will still get an output
from
it.

But perhaps that is not the issue of which you post? Although it seems
to
be, based on your first post (excerpt from that post):

If I use this line:
Expr26: IIF([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE
UNITS])
I get two duplicate line in my out put. One with this expression with
the
correct data (the data I expect) then I also get an exact duplicate
line
with
this expression and a null in this column.
That made sense to me as the if clause goes: IIF( expression, true,
false)
and the false may appear as a Null in the data.
SO i converted to a Switch statment:
Expr26: Switch([CONSUMP_MDDTL_HIST]![OS_PLATFORM_TYPE]=[Service Unit
Conversion table]![SERVICE LEVEL],[Service Unit Conversion
table]![SERVICE
UNITS])
I got the same results and that does not make sense to me as the
expression
is:
Switch( expression, expression)

You haven't posted examples of the data and the query's output, so I
cannot
"see" what type of "null line" you are getting, nor do I understand
what
you
are wanting as the result. But I still believe that the Cartesian join
is
part of the issue....

--

Ken Snell
<MS ACCESS MVP>


???????????????? So far we are in complete agreement. The switch has
three
choices, it makes the correct choice and then adds a null line. SO
we
have
went full circle. I don't know why it is adding the extra line.
Which
was
and
still is the question. I tend to agree the Switch statment is less
then
useful as well, So another solution is always welcome, but as I go
through
the functions I can't seem to fine any function short of hard coding
that
would help. and a hard coded clause is against all reasonable code
build,
I'm
sure you would agree.???????????

So do you have another idea or are we stuck?

:

The Switch expression that you're using seems less than useful?

Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE
UNITS])

What is supposed to happen if the comparison is False? You'll not
get
any
value from this expression in that case. The Switch function is
used
to
display one value based on another expression being True; which
implies
that
the expression needs to have enough arguments to ensure that you'll
get a
value from at least one of the comparisons.

--

Ken Snell
<MS ACCESS MVP>


I shortened up the code and wrote it without the "Join" that
Access
likes
to
use. Ran it and it worked, then added the "Switch." Once the
"Switch"
is
added to the code an extra line is returned with the correct data
and a
null
added where one of the three amount should appear. What should
appear
is
122
(Service Units) 172 (Service Units) 138 (Service Units). That
also
puzzles
me. I might even be able to understand if there were two
incorrect
lines
returned, as there are three possible choices in the Service Unit
conversion
Table. I still would consider doing this another way if you have
any
idea.

SELECT
DISTINCT
CONSUMP_MDDTL_HIST_1.HOST_ID,
CONSUMP_MDDTL_HIST.APP_NAME,
CONSUMP_MDDTL_HIST.REGIONAL_NAME,
CONSUMP_MDDTL_HIST.TOTAL_SU,
CONSUMP_MDDTL_HIST.USAGE_DT,
Switch([CONSUMP_MDDTL_HIST_1]![OS_PLATFORM_TYPE]=[Service Unit
Conversion
table]![SERVICE LEVEL],[Service Unit Conversion table]![SERVICE
UNITS])
AS Expr1,
CONSUMP_MDDTL_HIST_1.HOST_ID, CONSUMP_MDDTL_HIST_1.APP_NAME,
CONSUMP_MDDTL_HIST_1.TOTAL_SU, CONSUMP_MDDTL_HIST_1.USAGE_DT
FROM [Service Unit Conversion table],
CONSUMP_MDDTL_HIST,
CONSUMP_MDDTL_HIST AS CONSUMP_MDDTL_HIST_1
WHERE
(((CONSUMP_MDDTL_HIST.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-2,Now()),4,8))))
AND
((CONSUMP_MDDTL_HIST_1.TOTAL_SU)<>[CONSUMP_MDDTL_HIST].[TOTAL_SU])
AND
((CONSUMP_MDDTL_HIST_1.USAGE_DT)=(CDate("01-"+Mid(DateAdd("m",-1,Now()),4,8))))
AND CONSUMP_MDDTL_HIST.HOST_ID = CONSUMP_MDDTL_HIST_1.HOST_ID);