Access 2000:

I creating a report that has a record source built by the user who selects the
WHERE values. An example is:

SELECT * FROM CHARGELOG WHERE STDATE Between #10/27/2003# And #11/2/2003# And
VehicleID='00000000' And BattID='LKO500HF'.

I need to use the records returned to populate text boxes, but the data requires
further manipulation. I attempting to use expressions in the control source
property or apply the expressions in the body of the query that is the record
source of the report. The problem is that some of the expressions always return
the 2nd falsepart value in the "IIf" statement. These are the statements where
2 or more expressions are combined to be True. Such As:
IIf((DatePart("d",[STDATE])=2) And
(Max([NDT])>Max([STT])),Max([NDT]),Max([STT])) as MAXTemp,
IIf((DatePart("d",[STDATE])=2) And [CT]="FL","Yes","No") as FullChg,
IIf((DatePart("d",[STDATE])=2) And [CT]="EQ" And
(DateDiff("s",[STTIME],[NDTIME])>18000),"Yes","No") as EQChg

Each one of these expressions populates a specific control and has to be
modified to be used for other controls for each day of the week within the same
report.
What is wrong with these IIf statements? Can I combine expressions in the IIf
statement like the examples above?

I also have tried these values in the Record Source Query but the query (below)
has the same problem and has to have values for each day of the week.
SELECT [STDATE], [VEHICLEID], [BATTID], IIf(Weekday([STDATE])=2,Sum([AHRET]),"")
as ChgRtn,
COUNT([VehicleID]) AS SumVID, MIN([STSOC]) AS MinSTSOC,
IIf(DatePart("d",[STDATE])=2 And Max([NDT])>Max([STT]), Max([NDT]), Max([STT]))
as MAXTemp,
IIf((DatePart("d",[STDATE])=2) And [CT]="FL","Yes","No") as FullChg,
IIf((DatePart("d",[STDATE])=2) And [CT]="EQ" And
(DateDiff("s",[STTIME],[NDTIME])>18000),"Yes","No") as EQChg
FROM CHARGELOG
WHERE STDATE= #10/27/2003# And VehicleID='00000000' And BattID='LKO500HF'
GROUP BY STDATE, VEHICLEID, BATTID, IIf( (DatePart("d",[STDATE])=2) And
[CT]="FL","Yes","No") ,
IIf((DatePart("d",[STDATE])=2) And [CT]="EQ" And
(DateDiff("s",[STTIME],[NDTIME])>18000),"Yes","No")

I also tried an IF...THEN statement in the report's class object but get an
error that it can't find the field referred to in the expression.
If DatePart("d", Report_Charge_Weekly.STDATE) = 2 And
(DMax(Report_Charge_Weekly.NDT, "charge_weekly_recordsource") >
DMax(Report_Charge_Weekly.STT,"charge_weekly_recor dsource")) Then
txtMaxTemp = DMax(Report_Charge_Weekly.NDT, "charge_weekly_recordsource")
Else
txtMaxTemp = DMax(Report_Charge_Weekly.STT, "charge_weekly_recordsource")
End If

How do I write these expressions properly or I going about this all wrong?
Here is some sample data I working with.

BattID STDATE STTIME NDDATE NDTIME STVT NDVT ENDNI MAXMI STSOC ENSOC STT
NDT AHRET KWRET BT CL BTCAP CT CSC CTC P CHGID VehicleID
LKO500HF 10/27/2003 05:13:51 10/27/2003 12:13:51 37.6 37.6 0 0 71.3 71.3 50 59 5
0 1 18 100 FL 0 4 B 34463 00000000
LKO500HF 10/27/2003 10:13:51 10/27/2003 12:13:51 37.6 37.6 0 0 71.3 71.3 48 59 5
0 1 18 100 FL 0 4 B 34463 00000000
LKO500HF 10/28/2003 09:13:51 10/27/2003 12:13:51 37.6 37.6 0 0 71.3 71.3 57 59 4
0 1 18 100 FL 0 4 B 34463 00000000
LKO500HF 10/28/2003 10:13:51 10/27/2003 12:13:51 37.6 37.6 0 0 71.3 71.3 51 59 5
0 1 18 100 FL 0 4 B 34463 00000000
LKO500HF 10/29/2003 07:13:51 10/27/2003 12:13:51 37.6 37.6 0 0 71.3 71.3 51 59 5
0 1 18 100 FL 0 4 B 34463 00000000
LKO500HF 10/29/2003 10:13:51 10/27/2003 12:13:51 37.6 37.6 0 0 71.3 71.3 51 59 5
0 1 18 100 FL 0 4 B 34463 00000000

You CAN combine expressions in a iif function - what is a bit
confusing is how you're grouping the data - which includes the iif
statement. Without a copy of your table and select it's difficult to
replicate the problem. What I would suggest is one of two approaches:
1. seperate the iif expression and determine which part is returning a
false - this will help in the debugging of the statement

2. create a multi level set of queries - that is - create a query
getting the data from the table and grouping as you require, then have
a second query call this query to do your data manipulations...this
should reduce the complexity without much penalty in performance