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
7  11409 
Answers interleaved in your post (below):
mark wrote: 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
These statements would be correct as parts of the SELECT clause of a
query (except for the Max evaluation - that doesn't fit w/ the other
statements). IOW, you can't use them in a TextBox's ControlSource
property. You must use only one of the above IIf() functions in one
TextBox ControlSource at a time. The IIf() must be preceeded by an
equal sign. E.g.:
TextBox properties
------------------
ControlSource: =IIf((DatePart("d",[STDATE])=2) And [CT]="FL","Yes","No")
Name: FullChg
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,
Usually a SUM() function is wrapped around the IIf() function. E.g.:
Sum(IIf(Weekday(STDATE)=2, AHRET)
By not putting in the "false" expression the Sum() will sum a NULL value
- which is ignored by the SUM() function.
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")
< snip >
This example has a WHERE clause that only indicates one day: 10/27/2003.
You're aware of that, right?
For each day of the week on the report you need to group (in the report)
the STDATE by the day. Open the Group/Sort dialog box. Put STDATE in a
"Field Expression" grid box. Set its "Group On" value to "Day."
You could do the same thing in the RecordSource query's SELECT clause -
just use:
DatePart("d", [STDATE]) As WkDay
then you could use the WkDay column as a Group/Sort column & set Group
On to "Interval."
By grouping the WkDay you would no longer need to check it's value in
the IIf() function.
Unless, . . . .
You want to use different columns from CHANGELOG for different week
days. IOW, if WeekDay = 2 use the AHRET column to get the ChgRtn
value; if WeekDay = 3 the use the XYZ column to get the ChgRtn; if
WeekDay = 4 the use the ABCD column to get the ChgRtn; ... etc.
Is this what you want to do? I would probably try to get the query to
produce what I want & then just format the report to accept the columns
of the query.
HTH,
MGFoster:::mgf
Oakland, CA (USA)
Thanks for your reply. It is helping but I am still stuck. I have responded to
your words, also interleaved in the post below:
MGFoster wrote: Answers interleaved in your post (below):
mark wrote:
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
These statements would be correct as parts of the SELECT clause of a
query (except for the Max evaluation - that doesn't fit w/ the other
statements). IOW, you can't use them in a TextBox's ControlSource
property. You must use only one of the above IIf() functions in one
TextBox ControlSource at a time. The IIf() must be preceeded by an
equal sign. E.g.:
TextBox properties
------------------
ControlSource: =IIf((DatePart("d",[STDATE])=2) And [CT]="FL","Yes","No")
Name: FullChg
I have tried using one IIf statement in the controlsource of each textbox, just
like the one you mentioned above.
Do you mean that this IIf statement will not work in the controlsource of a
textbox or in the SELECT stmt. because it has MAX in it?:
IIf((DatePart("d",[STDATE])=2) And
(Max([NDT])>Max([STT])),Max([NDT]),Max([STT])) as MAXTemp
If so, then what would I do? I found the DMax and DMix Functions. 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,
Usually a SUM() function is wrapped around the IIf() function. E.g.:
Sum(IIf(Weekday(STDATE)=2, AHRET)
By not putting in the "false" expression the Sum() will sum a NULL value
- which is ignored by the SUM() function.
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")
< snip >
This example has a WHERE clause that only indicates one day: 10/27/2003.
You're aware of that, right?
I forgot to change the WHERE stmt. back to the following after some testing:
WHERE STDATE Between #10/27/2003# And #11/2/2003# And VehicleID='00000000' And
BattID='LKO500HF';
For each day of the week on the report you need to group (in the report)
the STDATE by the day. Open the Group/Sort dialog box. Put STDATE in a
"Field Expression" grid box. Set its "Group On" value to "Day."
You could do the same thing in the RecordSource query's SELECT clause -
just use:
DatePart("d", [STDATE]) As WkDay
then you could use the WkDay column as a Group/Sort column & set Group
On to "Interval."
By grouping the WkDay you would no longer need to check it's value in
the IIf() function.
Unless, . . . .
You want to use different columns from CHANGELOG for different week
days. IOW, if WeekDay = 2 use the AHRET column to get the ChgRtn
value; if WeekDay = 3 the use the XYZ column to get the ChgRtn; if
WeekDay = 4 the use the ABCD column to get the ChgRtn; ... etc.
Is this what you want to do? I would probably try to get the query to
produce what I want & then just format the report to accept the columns
of the query.
Here is the new query:
SELECT DatePart("d", [STDATE]) AS WkDay, [VEHICLEID], [BATTID], Sum([AHRET]) AS
ChgRtn, COUNT([VehicleID]) AS SumVID, MIN([STSOC]) AS MinSTSOC,
IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp,
IIf([CT]="FL","Yes","No") AS FullChg,
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No") AS EQChg
FROM CHARGELOG
WHERE STDATE Between #10/27/2003# And #11/2/2003# And VehicleID='00000000' And
BattID='LKO500HF'
GROUP BY [STDATE], [VEHICLEID], [BATTID], IIf([CT]="FL","Yes","No"),
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No");
I changed the query by adding "DatePart("d", [STDATE]) As WkDay" and removing
the date check from the IIf stmts. and the records are grouping by date and not
just returning records with a STDATE of Monday.
There is a problem though, where records exist that have more than one value
that the IIf statements are looking at I am getting multiple records for a given
STDATE, (ie. In the test records below, if the CT field has an "EQ" value and a
"FL" value then it returns 2 records. All I want is if just one record that
says if "EQ" + the datediff > 5 hours exists then "Yes", otherwise "No".). I
need one record for each day of the week then I need to place the fields in text
boxes (or labels) according to the day of the week they fall in.
To explain it further; There are two records for 10/27/03. One has a CT="EQ" and
the other ="FL". So for the expression, (IIf([CT]="FL","Yes","No") AS FullChg)
in the select statement I get two records returned where all field values are
the same except FullChg says "Yes" in one and "No" in the other.
In the report I have planned, the fields returned from the SELECT stmt. are
shown vertically under its day of the week - horizontally. The VehicleID and
BattID fields are constant so will be listed as a header. I added the Wkday
field from the SELECT stmt. in Group/Sort on the report and Group On to
Interval, but how do I use this Group/Sort on Wkday for this report? Maybe the
report should be formatted differently to make it easier and to use the
Group/Sort? I'm not sure how though. The report will always be just one page.
Example:
Monday Tuesday Wednesday etc...
ChgRtn
SumVID
MinSTSOC
MaxTemp
FullChg
EQChg
Sample Records I am testing 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 61.3 71.3 50 59 5
0 1 18 100 EQ 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 69 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 68.3 71.3 57 59 4
0 1 18 100 FC 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 FC 0 4 B 34463 00000000
LKO500HF 10/29/2003 07:13:51 10/27/2003 12:13:51 37.6 37.6 0 0 21.3 71.3 51 59 5
0 1 18 100 EQ 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
Thank you,
Mark
HTH,
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I've summarized here to avoid an illegible post.
===
I thought you were creating a report formatted something like this:
Monday (values)
ChgRtn x
SumVID x
MinSTSOC x
MaxTemp x
FullChg x
EQChg x
Tuesday
ChgRtn x
SumVID x
MinSTSOC x
MaxTemp x
FullChg x
EQChg x
What you're really doing is creating a quasi Pivot Table rpt. "Quasi"
since you're not making the same calculations for each grid cell.
In this case you should not group by the WkDay value in the report's
Group/Sort dialog box.
The query can still return the WkDay value - then the report won't
have to evaluate STDATE with the WeekDay function (see below).
You'll need to create 30 TextBoxes (5 weekdays X 6 values) in the
report's Detail section. I.e., you'd create a grid on the report that
has the WeekDay names across the top and then have 6 TextBoxes under
each WeekDay name for the values you want expressed. E.g.:
Monday Tuesday Wednesday
ChgRtn x x x
SumVID x x x
MinSTSOC x x x
MaxTemp x x x
FullChg x x x
EQChg x x x
==
I've reformatted your query so the GROUP BY columns are before the
aggregate columns. In this case the GROUPing would be: For each
WkDay there will be one VehicleID, that will have one (or possibly
more?) BattID that will have one, or more, FullChg (probably only one)
and one, or more, EQChg (probably only one). Each row will have the
Sum(Ahret), Count(VehicleID), the Min(StSoc), and either the Max(NDT)
or the Max(STT) depending on which value is the greater of the two.
SELECT DatePart("d", [STDATE]) AS WkDay,
[VEHICLEID],
[BATTID],
IIf([CT]="FL","Yes","No") AS FullChg,
IIf([CT]="EQ" And
DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No") AS EQChg,
SUM([AHRET]) AS ChgRtn,
COUNT(*) AS SumVID,
MIN([STSOC]) AS MinSTSOC,
IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp
FROM CHARGELOG
WHERE STDATE Between #10/27/2003# And #11/2/2003#
And VehicleID='00000000'
And BattID='LKO500HF'
GROUP BY DatePart("d", [STDATE]), [VEHICLEID], [BATTID],
IIf([CT]="FL","Yes","No"),
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No")
Note that you have to GROUP BY the results of the DatePart() function
which is a number, instead of the STDATE, which is a date.
Then you'd set up the report's control sources like this (use same
TextBox names, but with numbers to identify which weekday they go
under):
Under Monday column:
ChgRtn2: =IIf(WkDay=2, ChgRtn)
SumVID2: =IIf(WkDay=2, SumVid)
MinSTSOC2: =IIf(WkDay=2, MinSTSOC)
MaxTemp2: =IIf(WkDay=2, MaxTemp)
FullChg2: =IIf(WkDay=2, FullChg)
EQChg2: =IIf(WkDay=2, EQChg)
Under Tuesday column:
ChgRtn3: =IIf(WkDay=3, ChgRtn)
SumVID3: =IIf(WkDay=3, SumVid)
MinSTSOC3: =IIf(WkDay=3, MinSTSOC)
MaxTemp3: =IIf(WkDay=3, MaxTemp)
FullChg3: =IIf(WkDay=3, FullChg)
EQChg3: =IIf(WkDay=3, EQChg)
.... etc. for the other week day columns.
HTH,
- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP6NzW4echKqOuFEgEQJnowCffI1AozerzAHWUXR6t9OTuX xTwJQAnRfO
H8fI2woRatCDL+M5p/XDBhQ+
=W83V
-----END PGP SIGNATURE-----
mark wrote: Thanks for your reply. It is helping but I am still stuck. I have
responded to your words, also interleaved in the post below:
MGFoster wrote:
Answers interleaved in your post (below):
mark wrote:
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
These statements would be correct as parts of the SELECT clause of a
query (except for the Max evaluation - that doesn't fit w/ the other
statements). IOW, you can't use them in a TextBox's ControlSource
property. You must use only one of the above IIf() functions in one
TextBox ControlSource at a time. The IIf() must be preceeded by an
equal sign. E.g.:
TextBox properties
------------------
ControlSource: =IIf((DatePart("d",[STDATE])=2) And [CT]="FL","Yes","No")
Name: FullChg
I have tried using one IIf statement in the controlsource of each
textbox, just like the one you mentioned above.
Do you mean that this IIf statement will not work in the controlsource
of a textbox or in the SELECT stmt. because it has MAX in it?:
IIf((DatePart("d",[STDATE])=2) And
(Max([NDT])>Max([STT])),Max([NDT]),Max([STT])) as MAXTemp
If so, then what would I do? I found the DMax and DMix Functions.
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,
Usually a SUM() function is wrapped around the IIf() function. E.g.:
Sum(IIf(Weekday(STDATE)=2, AHRET)
By not putting in the "false" expression the Sum() will sum a NULL
value - which is ignored by the SUM() function.
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")
< snip >
This example has a WHERE clause that only indicates one day:
10/27/2003. You're aware of that, right?
I forgot to change the WHERE stmt. back to the following after some
testing:
WHERE STDATE Between #10/27/2003# And #11/2/2003# And
VehicleID='00000000' And BattID='LKO500HF';
For each day of the week on the report you need to group (in the
report) the STDATE by the day. Open the Group/Sort dialog box. Put
STDATE in a "Field Expression" grid box. Set its "Group On" value to
"Day."
You could do the same thing in the RecordSource query's SELECT clause
- just use:
DatePart("d", [STDATE]) As WkDay
then you could use the WkDay column as a Group/Sort column & set Group
On to "Interval."
By grouping the WkDay you would no longer need to check it's value in
the IIf() function.
Unless, . . . .
You want to use different columns from CHANGELOG for different week
days. IOW, if WeekDay = 2 use the AHRET column to get the ChgRtn
value; if WeekDay = 3 the use the XYZ column to get the ChgRtn; if
WeekDay = 4 the use the ABCD column to get the ChgRtn; ... etc.
Is this what you want to do? I would probably try to get the query to
produce what I want & then just format the report to accept the
columns of the query.
Here is the new query:
SELECT DatePart("d", [STDATE]) AS WkDay, [VEHICLEID], [BATTID],
Sum([AHRET]) AS ChgRtn, COUNT([VehicleID]) AS SumVID, MIN([STSOC]) AS
MinSTSOC,
IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp,
IIf([CT]="FL","Yes","No") AS FullChg,
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No") AS
EQChg
FROM CHARGELOG
WHERE STDATE Between #10/27/2003# And #11/2/2003# And
VehicleID='00000000' And BattID='LKO500HF'
GROUP BY [STDATE], [VEHICLEID], [BATTID], IIf([CT]="FL","Yes","No"),
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No");
I changed the query by adding "DatePart("d", [STDATE]) As WkDay" and
removing the date check from the IIf stmts. and the records are grouping
by date and not just returning records with a STDATE of Monday.
There is a problem though, where records exist that have more than one
value that the IIf statements are looking at I am getting multiple
records for a given STDATE, (ie. In the test records below, if the CT
field has an "EQ" value and a "FL" value then it returns 2 records. All
I want is if just one record that says if "EQ" + the datediff > 5 hours
exists then "Yes", otherwise "No".). I need one record for each day of
the week then I need to place the fields in text boxes (or labels)
according to the day of the week they fall in.
To explain it further; There are two records for 10/27/03. One has a
CT="EQ" and the other ="FL". So for the expression,
(IIf([CT]="FL","Yes","No") AS FullChg) in the select statement I get two
records returned where all field values are the same except FullChg says
"Yes" in one and "No" in the other.
In the report I have planned, the fields returned from the SELECT stmt.
are shown vertically under its day of the week - horizontally. The
VehicleID and BattID fields are constant so will be listed as a header.
I added the Wkday field from the SELECT stmt. in Group/Sort on the
report and Group On to Interval, but how do I use this Group/Sort on
Wkday for this report? Maybe the report should be formatted differently
to make it easier and to use the Group/Sort? I'm not sure how though.
The report will always be just one page.
Example:
Monday Tuesday Wednesday etc...
ChgRtn
SumVID
MinSTSOC
MaxTemp
FullChg
EQChg
Sample Records I am testing 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 61.3 71.3
50 59 5 0 1 18 100 EQ 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
69 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 68.3 71.3
57 59 4 0 1 18 100 FC 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 FC 0 4 B 34463 00000000
LKO500HF 10/29/2003 07:13:51 10/27/2003 12:13:51 37.6 37.6 0 0 21.3 71.3
51 59 5 0 1 18 100 EQ 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
Thank you,
Mark
HTH,
MGFoster:::mgf
Oakland, CA (USA)
Thank you for your help. It helps a lot to have someone to bounce ideas and
talk to when stuck.
There is a problem with this plan though. I was trying the same report plan as
you have layed out and found that the MaxTemp IIf statement
(IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp) and the EQChg IIf
statement (IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No")
AS EQChg) will result in a return multiple records if there are multiple records
in the ChargeLog table and there a multiple outcomes. For example the 27th has
a max(NDT)>max(STT) for one record and another record has a max(STT)>max(NDT).
This is also true if there are multiple records on a given day and the EQChg IIf
statement evaluates to Yes in one record and No in another. This results in the
aggregate functions in the query to not work. It looks like the aggregates and
IIf statements are working on each record individually rather than all records
for a specified STDATE, VehicleID, and BattID.
In the report layout you described, and I have been working on, don't I need a
query that returns one record for each specified STDATE, VehicleID, and BattID?
Here is the result set returned:
27 00000000 LKO500HF No Yes 5 1 61.3 80
27 00000000 LKO500HF Yes No 5 1 71.3 79
28 00000000 LKO500HF No No 9 2 68.3 59
29 00000000 LKO500HF No Yes 5 1 21.3 59
29 00000000 LKO500HF Yes No 5 1 71.3 59
Each of these records is based on 2 records for the 27th, 2 records for the
28th, 2 records for the 29th.
The 28th has only one record because the MaxTemp and EQChg IIf statements both
have the same outcome on both records.
Here are the 6 records the above result set came from:
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 5:13:51 AM 10/27/2003 12:13:51 PM 37.6
37.6 0 0 61.3 71.3 80 78 5 0 1 18 100 EQ
0 4 B 34463 00000000
LKO500HF 10/27/2003 10:13:51 AM 10/27/2003 12:13:51 PM 37.6
37.6 0 0 71.3 71.3 69 79 5 0 1 18 100 FL
0 4 B 34463 00000000
LKO500HF 10/28/2003 9:13:51 AM 10/27/2003 12:13:51 PM 37.6
37.6 0 0 68.3 71.3 57 59 4 0 1 18 100 FC
0 4 B 34463 00000000
LKO500HF 10/28/2003 10:13:51 AM 10/27/2003 12:13:51 PM 37.6
37.6 0 0 71.3 71.3 51 59 5 0 1 18 100 FC
0 4 B 34463 00000000
LKO500HF 10/29/2003 7:13:50 AM 10/27/2003 12:13:51 PM 37.6
37.6 0 0 21.3 71.3 51 59 5 0 1 18 100 EQ
0 4 B 34463 00000000
LKO500HF 10/29/2003 7:15:52 AM 10/27/2003 12:13:51 PM 37.6
37.6 0 0 71.3 71.3 51 59 5 0 1 18 100 FL
0 4 B 34463 00000000
This is the part that really has me stuck.
Maybe I should leave the query alone and then perform new calculations, in the
report controls, on the recordset again?
MGFoster wrote: -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I've summarized here to avoid an illegible post.
===
I thought you were creating a report formatted something like this:
Monday (values)
ChgRtn x
SumVID x
MinSTSOC x
MaxTemp x
FullChg x
EQChg x
Tuesday
ChgRtn x
SumVID x
MinSTSOC x
MaxTemp x
FullChg x
EQChg x
What you're really doing is creating a quasi Pivot Table rpt. "Quasi"
since you're not making the same calculations for each grid cell.
In this case you should not group by the WkDay value in the report's
Group/Sort dialog box.
The query can still return the WkDay value - then the report won't
have to evaluate STDATE with the WeekDay function (see below).
You'll need to create 30 TextBoxes (5 weekdays X 6 values) in the
report's Detail section. I.e., you'd create a grid on the report that
has the WeekDay names across the top and then have 6 TextBoxes under
each WeekDay name for the values you want expressed. E.g.:
Monday Tuesday Wednesday
ChgRtn x x x
SumVID x x x
MinSTSOC x x x
MaxTemp x x x
FullChg x x x
EQChg x x x
==
I've reformatted your query so the GROUP BY columns are before the
aggregate columns. In this case the GROUPing would be: For each
WkDay there will be one VehicleID, that will have one (or possibly
more?) BattID that will have one, or more, FullChg (probably only one)
and one, or more, EQChg (probably only one). Each row will have the
Sum(Ahret), Count(VehicleID), the Min(StSoc), and either the Max(NDT)
or the Max(STT) depending on which value is the greater of the two.
SELECT DatePart("d", [STDATE]) AS WkDay,
[VEHICLEID],
[BATTID],
IIf([CT]="FL","Yes","No") AS FullChg,
IIf([CT]="EQ" And
DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No") AS EQChg,
SUM([AHRET]) AS ChgRtn,
COUNT(*) AS SumVID,
MIN([STSOC]) AS MinSTSOC,
IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp
FROM CHARGELOG
WHERE STDATE Between #10/27/2003# And #11/2/2003#
And VehicleID='00000000'
And BattID='LKO500HF'
GROUP BY DatePart("d", [STDATE]), [VEHICLEID], [BATTID],
IIf([CT]="FL","Yes","No"),
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No")
Note that you have to GROUP BY the results of the DatePart() function
which is a number, instead of the STDATE, which is a date.
Then you'd set up the report's control sources like this (use same
TextBox names, but with numbers to identify which weekday they go
under):
Under Monday column:
ChgRtn2: =IIf(WkDay=2, ChgRtn)
SumVID2: =IIf(WkDay=2, SumVid)
MinSTSOC2: =IIf(WkDay=2, MinSTSOC)
MaxTemp2: =IIf(WkDay=2, MaxTemp)
FullChg2: =IIf(WkDay=2, FullChg)
EQChg2: =IIf(WkDay=2, EQChg)
Under Tuesday column:
ChgRtn3: =IIf(WkDay=3, ChgRtn)
SumVID3: =IIf(WkDay=3, SumVid)
MinSTSOC3: =IIf(WkDay=3, MinSTSOC)
MaxTemp3: =IIf(WkDay=3, MaxTemp)
FullChg3: =IIf(WkDay=3, FullChg)
EQChg3: =IIf(WkDay=3, EQChg)
... etc. for the other week day columns.
HTH,
- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP6NzW4echKqOuFEgEQJnowCffI1AozerzAHWUXR6t9OTuX xTwJQAnRfO
H8fI2woRatCDL+M5p/XDBhQ+
=W83V
-----END PGP SIGNATURE-----
mark wrote:
Thanks for your reply. It is helping but I am still stuck. I have
responded to your words, also interleaved in the post below:
MGFoster wrote:
Answers interleaved in your post (below):
mark wrote:
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
These statements would be correct as parts of the SELECT clause of a
query (except for the Max evaluation - that doesn't fit w/ the other
statements). IOW, you can't use them in a TextBox's ControlSource
property. You must use only one of the above IIf() functions in one
TextBox ControlSource at a time. The IIf() must be preceeded by an
equal sign. E.g.:
TextBox properties
------------------
ControlSource: =IIf((DatePart("d",[STDATE])=2) And [CT]="FL","Yes","No")
Name: FullChg
I have tried using one IIf statement in the controlsource of each
textbox, just like the one you mentioned above.
Do you mean that this IIf statement will not work in the controlsource
of a textbox or in the SELECT stmt. because it has MAX in it?:
IIf((DatePart("d",[STDATE])=2) And
(Max([NDT])>Max([STT])),Max([NDT]),Max([STT])) as MAXTemp
If so, then what would I do? I found the DMax and DMix Functions.
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,
Usually a SUM() function is wrapped around the IIf() function. E.g.:
Sum(IIf(Weekday(STDATE)=2, AHRET)
By not putting in the "false" expression the Sum() will sum a NULL
value - which is ignored by the SUM() function.
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")
< snip >
This example has a WHERE clause that only indicates one day:
10/27/2003. You're aware of that, right?
I forgot to change the WHERE stmt. back to the following after some
testing:
WHERE STDATE Between #10/27/2003# And #11/2/2003# And
VehicleID='00000000' And BattID='LKO500HF';
For each day of the week on the report you need to group (in the
report) the STDATE by the day. Open the Group/Sort dialog box. Put
STDATE in a "Field Expression" grid box. Set its "Group On" value to
"Day."
You could do the same thing in the RecordSource query's SELECT clause
- just use:
DatePart("d", [STDATE]) As WkDay
then you could use the WkDay column as a Group/Sort column & set
Group On to "Interval."
By grouping the WkDay you would no longer need to check it's value in
the IIf() function.
Unless, . . . .
You want to use different columns from CHANGELOG for different week
days. IOW, if WeekDay = 2 use the AHRET column to get the ChgRtn
value; if WeekDay = 3 the use the XYZ column to get the ChgRtn; if
WeekDay = 4 the use the ABCD column to get the ChgRtn; ... etc.
Is this what you want to do? I would probably try to get the query
to produce what I want & then just format the report to accept the
columns of the query.
Here is the new query:
SELECT DatePart("d", [STDATE]) AS WkDay, [VEHICLEID], [BATTID],
Sum([AHRET]) AS ChgRtn, COUNT([VehicleID]) AS SumVID, MIN([STSOC]) AS
MinSTSOC,
IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp,
IIf([CT]="FL","Yes","No") AS FullChg,
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No") AS
EQChg
FROM CHARGELOG
WHERE STDATE Between #10/27/2003# And #11/2/2003# And
VehicleID='00000000' And BattID='LKO500HF'
GROUP BY [STDATE], [VEHICLEID], [BATTID], IIf([CT]="FL","Yes","No"),
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No");
I changed the query by adding "DatePart("d", [STDATE]) As WkDay" and
removing the date check from the IIf stmts. and the records are
grouping by date and not just returning records with a STDATE of Monday.
There is a problem though, where records exist that have more than one
value that the IIf statements are looking at I am getting multiple
records for a given STDATE, (ie. In the test records below, if the CT
field has an "EQ" value and a "FL" value then it returns 2 records.
All I want is if just one record that says if "EQ" + the datediff > 5
hours exists then "Yes", otherwise "No".). I need one record for each
day of the week then I need to place the fields in text boxes (or
labels) according to the day of the week they fall in.
To explain it further; There are two records for 10/27/03. One has a
CT="EQ" and the other ="FL". So for the expression,
(IIf([CT]="FL","Yes","No") AS FullChg) in the select statement I get
two records returned where all field values are the same except
FullChg says "Yes" in one and "No" in the other.
In the report I have planned, the fields returned from the SELECT
stmt. are shown vertically under its day of the week - horizontally.
The VehicleID and BattID fields are constant so will be listed as a
header. I added the Wkday field from the SELECT stmt. in Group/Sort
on the report and Group On to Interval, but how do I use this
Group/Sort on Wkday for this report? Maybe the report should be
formatted differently to make it easier and to use the Group/Sort? I'm
not sure how though. The report will always be just one page.
Example:
Monday Tuesday Wednesday etc...
ChgRtn
SumVID
MinSTSOC
MaxTemp
FullChg
EQChg
Sample Records I am testing 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 61.3
71.3 50 59 5 0 1 18 100 EQ 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 69 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 68.3
71.3 57 59 4 0 1 18 100 FC 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 FC 0 4 B 34463 00000000
LKO500HF 10/29/2003 07:13:51 10/27/2003 12:13:51 37.6 37.6 0 0 21.3
71.3 51 59 5 0 1 18 100 EQ 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
Thank you,
Mark
HTH,
MGFoster:::mgf
Oakland, CA (USA)
I tried using IIf statements within each cell on the report against the
reformatted query but all it does is look at the first record for each date and
does not sum - Same as the query itself. Also, for each set of results under
each day it starts a new page. So only Monday is on page one and Tuesday is on
a second page.
MGFoster wrote: -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I've summarized here to avoid an illegible post.
===
I thought you were creating a report formatted something like this:
Monday (values)
ChgRtn x
SumVID x
MinSTSOC x
MaxTemp x
FullChg x
EQChg x
Tuesday
ChgRtn x
SumVID x
MinSTSOC x
MaxTemp x
FullChg x
EQChg x
What you're really doing is creating a quasi Pivot Table rpt. "Quasi"
since you're not making the same calculations for each grid cell.
In this case you should not group by the WkDay value in the report's
Group/Sort dialog box.
The query can still return the WkDay value - then the report won't
have to evaluate STDATE with the WeekDay function (see below).
You'll need to create 30 TextBoxes (5 weekdays X 6 values) in the
report's Detail section. I.e., you'd create a grid on the report that
has the WeekDay names across the top and then have 6 TextBoxes under
each WeekDay name for the values you want expressed. E.g.:
Monday Tuesday Wednesday
ChgRtn x x x
SumVID x x x
MinSTSOC x x x
MaxTemp x x x
FullChg x x x
EQChg x x x
==
I've reformatted your query so the GROUP BY columns are before the
aggregate columns. In this case the GROUPing would be: For each
WkDay there will be one VehicleID, that will have one (or possibly
more?) BattID that will have one, or more, FullChg (probably only one)
and one, or more, EQChg (probably only one). Each row will have the
Sum(Ahret), Count(VehicleID), the Min(StSoc), and either the Max(NDT)
or the Max(STT) depending on which value is the greater of the two.
SELECT DatePart("d", [STDATE]) AS WkDay,
[VEHICLEID],
[BATTID],
IIf([CT]="FL","Yes","No") AS FullChg,
IIf([CT]="EQ" And
DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No") AS EQChg,
SUM([AHRET]) AS ChgRtn,
COUNT(*) AS SumVID,
MIN([STSOC]) AS MinSTSOC,
IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp
FROM CHARGELOG
WHERE STDATE Between #10/27/2003# And #11/2/2003#
And VehicleID='00000000'
And BattID='LKO500HF'
GROUP BY DatePart("d", [STDATE]), [VEHICLEID], [BATTID],
IIf([CT]="FL","Yes","No"),
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No")
Note that you have to GROUP BY the results of the DatePart() function
which is a number, instead of the STDATE, which is a date.
Then you'd set up the report's control sources like this (use same
TextBox names, but with numbers to identify which weekday they go
under):
Under Monday column:
ChgRtn2: =IIf(WkDay=2, ChgRtn)
SumVID2: =IIf(WkDay=2, SumVid)
MinSTSOC2: =IIf(WkDay=2, MinSTSOC)
MaxTemp2: =IIf(WkDay=2, MaxTemp)
FullChg2: =IIf(WkDay=2, FullChg)
EQChg2: =IIf(WkDay=2, EQChg)
Under Tuesday column:
ChgRtn3: =IIf(WkDay=3, ChgRtn)
SumVID3: =IIf(WkDay=3, SumVid)
MinSTSOC3: =IIf(WkDay=3, MinSTSOC)
MaxTemp3: =IIf(WkDay=3, MaxTemp)
FullChg3: =IIf(WkDay=3, FullChg)
EQChg3: =IIf(WkDay=3, EQChg)
... etc. for the other week day columns.
HTH,
- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP6NzW4echKqOuFEgEQJnowCffI1AozerzAHWUXR6t9OTuX xTwJQAnRfO
H8fI2woRatCDL+M5p/XDBhQ+
=W83V
-----END PGP SIGNATURE-----
mark wrote:
Thanks for your reply. It is helping but I am still stuck. I have
responded to your words, also interleaved in the post below:
MGFoster wrote:
Answers interleaved in your post (below):
mark wrote:
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
These statements would be correct as parts of the SELECT clause of a
query (except for the Max evaluation - that doesn't fit w/ the other
statements). IOW, you can't use them in a TextBox's ControlSource
property. You must use only one of the above IIf() functions in one
TextBox ControlSource at a time. The IIf() must be preceeded by an
equal sign. E.g.:
TextBox properties
------------------
ControlSource: =IIf((DatePart("d",[STDATE])=2) And [CT]="FL","Yes","No")
Name: FullChg
I have tried using one IIf statement in the controlsource of each
textbox, just like the one you mentioned above.
Do you mean that this IIf statement will not work in the controlsource
of a textbox or in the SELECT stmt. because it has MAX in it?:
IIf((DatePart("d",[STDATE])=2) And
(Max([NDT])>Max([STT])),Max([NDT]),Max([STT])) as MAXTemp
If so, then what would I do? I found the DMax and DMix Functions.
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,
Usually a SUM() function is wrapped around the IIf() function. E.g.:
Sum(IIf(Weekday(STDATE)=2, AHRET)
By not putting in the "false" expression the Sum() will sum a NULL
value - which is ignored by the SUM() function.
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")
< snip >
This example has a WHERE clause that only indicates one day:
10/27/2003. You're aware of that, right?
I forgot to change the WHERE stmt. back to the following after some
testing:
WHERE STDATE Between #10/27/2003# And #11/2/2003# And
VehicleID='00000000' And BattID='LKO500HF';
For each day of the week on the report you need to group (in the
report) the STDATE by the day. Open the Group/Sort dialog box. Put
STDATE in a "Field Expression" grid box. Set its "Group On" value to
"Day."
You could do the same thing in the RecordSource query's SELECT clause
- just use:
DatePart("d", [STDATE]) As WkDay
then you could use the WkDay column as a Group/Sort column & set
Group On to "Interval."
By grouping the WkDay you would no longer need to check it's value in
the IIf() function.
Unless, . . . .
You want to use different columns from CHANGELOG for different week
days. IOW, if WeekDay = 2 use the AHRET column to get the ChgRtn
value; if WeekDay = 3 the use the XYZ column to get the ChgRtn; if
WeekDay = 4 the use the ABCD column to get the ChgRtn; ... etc.
Is this what you want to do? I would probably try to get the query
to produce what I want & then just format the report to accept the
columns of the query.
Here is the new query:
SELECT DatePart("d", [STDATE]) AS WkDay, [VEHICLEID], [BATTID],
Sum([AHRET]) AS ChgRtn, COUNT([VehicleID]) AS SumVID, MIN([STSOC]) AS
MinSTSOC,
IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp,
IIf([CT]="FL","Yes","No") AS FullChg,
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No") AS
EQChg
FROM CHARGELOG
WHERE STDATE Between #10/27/2003# And #11/2/2003# And
VehicleID='00000000' And BattID='LKO500HF'
GROUP BY [STDATE], [VEHICLEID], [BATTID], IIf([CT]="FL","Yes","No"),
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No");
I changed the query by adding "DatePart("d", [STDATE]) As WkDay" and
removing the date check from the IIf stmts. and the records are
grouping by date and not just returning records with a STDATE of Monday.
There is a problem though, where records exist that have more than one
value that the IIf statements are looking at I am getting multiple
records for a given STDATE, (ie. In the test records below, if the CT
field has an "EQ" value and a "FL" value then it returns 2 records.
All I want is if just one record that says if "EQ" + the datediff > 5
hours exists then "Yes", otherwise "No".). I need one record for each
day of the week then I need to place the fields in text boxes (or
labels) according to the day of the week they fall in.
To explain it further; There are two records for 10/27/03. One has a
CT="EQ" and the other ="FL". So for the expression,
(IIf([CT]="FL","Yes","No") AS FullChg) in the select statement I get
two records returned where all field values are the same except
FullChg says "Yes" in one and "No" in the other.
In the report I have planned, the fields returned from the SELECT
stmt. are shown vertically under its day of the week - horizontally.
The VehicleID and BattID fields are constant so will be listed as a
header. I added the Wkday field from the SELECT stmt. in Group/Sort
on the report and Group On to Interval, but how do I use this
Group/Sort on Wkday for this report? Maybe the report should be
formatted differently to make it easier and to use the Group/Sort? I'm
not sure how though. The report will always be just one page.
Example:
Monday Tuesday Wednesday etc...
ChgRtn
SumVID
MinSTSOC
MaxTemp
FullChg
EQChg
Sample Records I am testing 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 61.3
71.3 50 59 5 0 1 18 100 EQ 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 69 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 68.3
71.3 57 59 4 0 1 18 100 FC 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 FC 0 4 B 34463 00000000
LKO500HF 10/29/2003 07:13:51 10/27/2003 12:13:51 37.6 37.6 0 0 21.3
71.3 51 59 5 0 1 18 100 EQ 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
Thank you,
Mark
HTH,
MGFoster:::mgf
Oakland, CA (USA)
I figured out a way to remove the duplicates caused by the Yes/No IIf
statements. Instead of returning a Yes or No response I changed the return
value to a numeric and then summed the result. This allows for a single record
for each day, vehicleID, and BatteryID. (The query is at the bottom.) Then I
Changed the ControlSource values to:
Full Charge: =IIf([WkDay]=2 And [FullChg]>0,"Yes","No")
EQ Charge: =IIf([WkDay]=2 And [EQChg]>0,"Yes","No")
So any value > 0 is Yes and any number = 0 is No.
SELECT DISTINCT Weekday([STDATE]) AS WkDay, [VEHICLEID], [BATTID], SUM([AHRET])
AS ChgRtn, COUNT(*) AS SumVID, MIN([STSOC]) AS MinSTSOC,
Sum(IIf([CT]="FL",1,0)) AS FullChg,
Sum(IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,1,0)) AS EQChg,
IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp
FROM CHARGELOG
WHERE STDATE Between #10/27/2003# And #11/2/2003# And VehicleID='00000000' And
BattID='LKO500HF'
GROUP BY Weekday([STDATE]), [VEHICLEID], [BATTID]
Now I am trying to determine why, when I run the report, it is returning
multiple tables (2 per page)? Each day's values for Charge Return, No. of
Charges, MinSOC, and MaxTemp all show by themselves on another table rather than
all showing in the same single table. The Full Charge and EQ Charge values show
up on the first table and every other table though. I am testing every property
I can to determine why but no solution.
Thanks,
mark
mark wrote: Thank you for your help. It helps a lot to have someone to bounce ideas
and talk to when stuck.
There is a problem with this plan though. I was trying the same report
plan as you have layed out and found that the MaxTemp IIf statement
(IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp) and the
EQChg IIf statement (IIf([CT]="EQ" And
DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No") AS EQChg) will result
in a return multiple records if there are multiple records in the
ChargeLog table and there a multiple outcomes. For example the 27th has
a max(NDT)>max(STT) for one record and another record has a
max(STT)>max(NDT). This is also true if there are multiple records on a
given day and the EQChg IIf statement evaluates to Yes in one record and
No in another. This results in the aggregate functions in the query to
not work. It looks like the aggregates and IIf statements are working
on each record individually rather than all records for a specified
STDATE, VehicleID, and BattID.
In the report layout you described, and I have been working on, don't I
need a query that returns one record for each specified STDATE,
VehicleID, and BattID?
Here is the result set returned:
27 00000000 LKO500HF No Yes 5 1 61.3 80
27 00000000 LKO500HF Yes No 5 1 71.3 79
28 00000000 LKO500HF No No 9 2 68.3 59
29 00000000 LKO500HF No Yes 5 1 21.3 59
29 00000000 LKO500HF Yes No 5 1 71.3 59
Each of these records is based on 2 records for the 27th, 2 records for
the 28th, 2 records for the 29th.
The 28th has only one record because the MaxTemp and EQChg IIf
statements both have the same outcome on both records.
Here are the 6 records the above result set came from:
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 5:13:51 AM 10/27/2003 12:13:51 PM
37.6 37.6 0 0 61.3 71.3 80 78 5 0 1 18
100 EQ 0 4 B 34463 00000000
LKO500HF 10/27/2003 10:13:51 AM 10/27/2003 12:13:51 PM
37.6 37.6 0 0 71.3 71.3 69 79 5 0 1 18
100 FL 0 4 B 34463 00000000
LKO500HF 10/28/2003 9:13:51 AM 10/27/2003 12:13:51 PM
37.6 37.6 0 0 68.3 71.3 57 59 4 0 1 18
100 FC 0 4 B 34463 00000000
LKO500HF 10/28/2003 10:13:51 AM 10/27/2003 12:13:51 PM
37.6 37.6 0 0 71.3 71.3 51 59 5 0 1 18
100 FC 0 4 B 34463 00000000
LKO500HF 10/29/2003 7:13:50 AM 10/27/2003 12:13:51 PM
37.6 37.6 0 0 21.3 71.3 51 59 5 0 1 18
100 EQ 0 4 B 34463 00000000
LKO500HF 10/29/2003 7:15:52 AM 10/27/2003 12:13:51 PM
37.6 37.6 0 0 71.3 71.3 51 59 5 0 1 18
100 FL 0 4 B 34463 00000000
This is the part that really has me stuck.
Maybe I should leave the query alone and then perform new calculations,
in the report controls, on the recordset again?
MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I've summarized here to avoid an illegible post.
===
I thought you were creating a report formatted something like this:
Monday (values)
ChgRtn x
SumVID x
MinSTSOC x
MaxTemp x
FullChg x
EQChg x
Tuesday
ChgRtn x
SumVID x
MinSTSOC x
MaxTemp x
FullChg x
EQChg x
What you're really doing is creating a quasi Pivot Table rpt. "Quasi"
since you're not making the same calculations for each grid cell.
In this case you should not group by the WkDay value in the report's
Group/Sort dialog box.
The query can still return the WkDay value - then the report won't
have to evaluate STDATE with the WeekDay function (see below).
You'll need to create 30 TextBoxes (5 weekdays X 6 values) in the
report's Detail section. I.e., you'd create a grid on the report that
has the WeekDay names across the top and then have 6 TextBoxes under
each WeekDay name for the values you want expressed. E.g.:
Monday Tuesday Wednesday
ChgRtn x x x
SumVID x x x
MinSTSOC x x x
MaxTemp x x x
FullChg x x x
EQChg x x x
==
I've reformatted your query so the GROUP BY columns are before the
aggregate columns. In this case the GROUPing would be: For each
WkDay there will be one VehicleID, that will have one (or possibly
more?) BattID that will have one, or more, FullChg (probably only one)
and one, or more, EQChg (probably only one). Each row will have the
Sum(Ahret), Count(VehicleID), the Min(StSoc), and either the Max(NDT)
or the Max(STT) depending on which value is the greater of the two.
SELECT DatePart("d", [STDATE]) AS WkDay,
[VEHICLEID],
[BATTID],
IIf([CT]="FL","Yes","No") AS FullChg,
IIf([CT]="EQ" And
DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No") AS EQChg,
SUM([AHRET]) AS ChgRtn,
COUNT(*) AS SumVID,
MIN([STSOC]) AS MinSTSOC,
IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp
FROM CHARGELOG
WHERE STDATE Between #10/27/2003# And #11/2/2003#
And VehicleID='00000000'
And BattID='LKO500HF'
GROUP BY DatePart("d", [STDATE]), [VEHICLEID], [BATTID],
IIf([CT]="FL","Yes","No"),
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No")
Note that you have to GROUP BY the results of the DatePart() function
which is a number, instead of the STDATE, which is a date.
Then you'd set up the report's control sources like this (use same
TextBox names, but with numbers to identify which weekday they go
under):
Under Monday column:
ChgRtn2: =IIf(WkDay=2, ChgRtn)
SumVID2: =IIf(WkDay=2, SumVid)
MinSTSOC2: =IIf(WkDay=2, MinSTSOC)
MaxTemp2: =IIf(WkDay=2, MaxTemp)
FullChg2: =IIf(WkDay=2, FullChg)
EQChg2: =IIf(WkDay=2, EQChg)
Under Tuesday column:
ChgRtn3: =IIf(WkDay=3, ChgRtn)
SumVID3: =IIf(WkDay=3, SumVid)
MinSTSOC3: =IIf(WkDay=3, MinSTSOC)
MaxTemp3: =IIf(WkDay=3, MaxTemp)
FullChg3: =IIf(WkDay=3, FullChg)
EQChg3: =IIf(WkDay=3, EQChg)
... etc. for the other week day columns.
HTH,
- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP6NzW4echKqOuFEgEQJnowCffI1AozerzAHWUXR6t9OTuX xTwJQAnRfO
H8fI2woRatCDL+M5p/XDBhQ+
=W83V
-----END PGP SIGNATURE-----
mark wrote:
Thanks for your reply. It is helping but I am still stuck. I have
responded to your words, also interleaved in the post below:
MGFoster wrote:
Answers interleaved in your post (below):
mark wrote:
> 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
These statements would be correct as parts of the SELECT clause of a
query (except for the Max evaluation - that doesn't fit w/ the other
statements). IOW, you can't use them in a TextBox's ControlSource
property. You must use only one of the above IIf() functions in one
TextBox ControlSource at a time. The IIf() must be preceeded by an
equal sign. E.g.:
TextBox properties
------------------
ControlSource: =IIf((DatePart("d",[STDATE])=2) And
[CT]="FL","Yes","No")
Name: FullChg
I have tried using one IIf statement in the controlsource of each
textbox, just like the one you mentioned above.
Do you mean that this IIf statement will not work in the
controlsource of a textbox or in the SELECT stmt. because it has MAX
in it?:
IIf((DatePart("d",[STDATE])=2) And
(Max([NDT])>Max([STT])),Max([NDT]),Max([STT])) as MAXTemp
If so, then what would I do? I found the DMax and DMix Functions.
> 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,
Usually a SUM() function is wrapped around the IIf() function. E.g.:
Sum(IIf(Weekday(STDATE)=2, AHRET)
By not putting in the "false" expression the Sum() will sum a NULL
value - which is ignored by the SUM() function.
> 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")
< snip >
This example has a WHERE clause that only indicates one day:
10/27/2003. You're aware of that, right?
I forgot to change the WHERE stmt. back to the following after some
testing:
WHERE STDATE Between #10/27/2003# And #11/2/2003# And
VehicleID='00000000' And BattID='LKO500HF';
For each day of the week on the report you need to group (in the
report) the STDATE by the day. Open the Group/Sort dialog box. Put
STDATE in a "Field Expression" grid box. Set its "Group On" value
to "Day."
You could do the same thing in the RecordSource query's SELECT
clause - just use:
DatePart("d", [STDATE]) As WkDay
then you could use the WkDay column as a Group/Sort column & set
Group On to "Interval."
By grouping the WkDay you would no longer need to check it's value
in the IIf() function.
Unless, . . . .
You want to use different columns from CHANGELOG for different week
days. IOW, if WeekDay = 2 use the AHRET column to get the ChgRtn
value; if WeekDay = 3 the use the XYZ column to get the ChgRtn; if
WeekDay = 4 the use the ABCD column to get the ChgRtn; ... etc.
Is this what you want to do? I would probably try to get the query
to produce what I want & then just format the report to accept the
columns of the query.
Here is the new query:
SELECT DatePart("d", [STDATE]) AS WkDay, [VEHICLEID], [BATTID],
Sum([AHRET]) AS ChgRtn, COUNT([VehicleID]) AS SumVID, MIN([STSOC]) AS
MinSTSOC,
IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp,
IIf([CT]="FL","Yes","No") AS FullChg,
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No")
AS EQChg
FROM CHARGELOG
WHERE STDATE Between #10/27/2003# And #11/2/2003# And
VehicleID='00000000' And BattID='LKO500HF'
GROUP BY [STDATE], [VEHICLEID], [BATTID], IIf([CT]="FL","Yes","No"),
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No");
I changed the query by adding "DatePart("d", [STDATE]) As WkDay" and
removing the date check from the IIf stmts. and the records are
grouping by date and not just returning records with a STDATE of Monday.
There is a problem though, where records exist that have more than
one value that the IIf statements are looking at I am getting
multiple records for a given STDATE, (ie. In the test records below,
if the CT field has an "EQ" value and a "FL" value then it returns 2
records. All I want is if just one record that says if "EQ" + the
datediff > 5 hours exists then "Yes", otherwise "No".). I need one
record for each day of the week then I need to place the fields in
text boxes (or labels) according to the day of the week they fall in.
To explain it further; There are two records for 10/27/03. One has a
CT="EQ" and the other ="FL". So for the expression,
(IIf([CT]="FL","Yes","No") AS FullChg) in the select statement I get
two records returned where all field values are the same except
FullChg says "Yes" in one and "No" in the other.
In the report I have planned, the fields returned from the SELECT
stmt. are shown vertically under its day of the week - horizontally.
The VehicleID and BattID fields are constant so will be listed as a
header. I added the Wkday field from the SELECT stmt. in Group/Sort
on the report and Group On to Interval, but how do I use this
Group/Sort on Wkday for this report? Maybe the report should be
formatted differently to make it easier and to use the Group/Sort?
I'm not sure how though. The report will always be just one page.
Example:
Monday Tuesday Wednesday etc...
ChgRtn
SumVID
MinSTSOC
MaxTemp
FullChg
EQChg
Sample Records I am testing 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 61.3
71.3 50 59 5 0 1 18 100 EQ 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 69 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 68.3
71.3 57 59 4 0 1 18 100 FC 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 FC 0 4 B 34463 00000000
LKO500HF 10/29/2003 07:13:51 10/27/2003 12:13:51 37.6 37.6 0 0 21.3
71.3 51 59 5 0 1 18 100 EQ 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
Thank you,
Mark
HTH,
MGFoster:::mgf
Oakland, CA (USA)
I figured out a way to remove the duplicates caused by the Yes/No IIf
statements. Instead of returning a Yes or No response I changed the return
value to a numeric and then summed the result. This allows for a single record
for each day, vehicleID, and BatteryID. (The query is at the bottom.) Then I
Changed the ControlSource values to:
Full Charge: =IIf([WkDay]=2 And [FullChg]>0,"Yes","No")
EQ Charge: =IIf([WkDay]=2 And [EQChg]>0,"Yes","No")
So any value > 0 is Yes and any number = 0 is No.
SELECT DISTINCT Weekday([STDATE]) AS WkDay, [VEHICLEID], [BATTID], SUM([AHRET])
AS ChgRtn, COUNT(*) AS SumVID, MIN([STSOC]) AS MinSTSOC,
Sum(IIf([CT]="FL",1,0)) AS FullChg,
Sum(IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,1,0)) AS EQChg,
IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp
FROM CHARGELOG
WHERE STDATE Between #10/27/2003# And #11/2/2003# And VehicleID='00000000' And
BattID='LKO500HF'
GROUP BY Weekday([STDATE]), [VEHICLEID], [BATTID]
Now I am trying to determine why, when I run the report, it is returning
multiple tables (2 per page)? The entire report is set in the Detail Section.
Each day's values for Charge Return, No. of Charges, MinSOC, and MaxTemp all
show by themselves on another table rather than all showing in the same single
table. The Full Charge and EQ Charge values show up on the first table and
every other table though. I am testing every property I can to determine why
but no solution.
mark wrote: Thank you for your help. It helps a lot to have someone to bounce ideas
and talk to when stuck.
There is a problem with this plan though. I was trying the same report
plan as you have layed out and found that the MaxTemp IIf statement
(IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp) and the
EQChg IIf statement (IIf([CT]="EQ" And
DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No") AS EQChg) will result
in a return multiple records if there are multiple records in the
ChargeLog table and there a multiple outcomes. For example the 27th has
a max(NDT)>max(STT) for one record and another record has a
max(STT)>max(NDT). This is also true if there are multiple records on a
given day and the EQChg IIf statement evaluates to Yes in one record and
No in another. This results in the aggregate functions in the query to
not work. It looks like the aggregates and IIf statements are working
on each record individually rather than all records for a specified
STDATE, VehicleID, and BattID.
In the report layout you described, and I have been working on, don't I
need a query that returns one record for each specified STDATE,
VehicleID, and BattID?
Here is the result set returned:
27 00000000 LKO500HF No Yes 5 1 61.3 80
27 00000000 LKO500HF Yes No 5 1 71.3 79
28 00000000 LKO500HF No No 9 2 68.3 59
29 00000000 LKO500HF No Yes 5 1 21.3 59
29 00000000 LKO500HF Yes No 5 1 71.3 59
Each of these records is based on 2 records for the 27th, 2 records for
the 28th, 2 records for the 29th.
The 28th has only one record because the MaxTemp and EQChg IIf
statements both have the same outcome on both records.
Here are the 6 records the above result set came from:
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 5:13:51 AM 10/27/2003 12:13:51 PM
37.6 37.6 0 0 61.3 71.3 80 78 5 0 1 18
100 EQ 0 4 B 34463 00000000
LKO500HF 10/27/2003 10:13:51 AM 10/27/2003 12:13:51 PM
37.6 37.6 0 0 71.3 71.3 69 79 5 0 1 18
100 FL 0 4 B 34463 00000000
LKO500HF 10/28/2003 9:13:51 AM 10/27/2003 12:13:51 PM
37.6 37.6 0 0 68.3 71.3 57 59 4 0 1 18
100 FC 0 4 B 34463 00000000
LKO500HF 10/28/2003 10:13:51 AM 10/27/2003 12:13:51 PM
37.6 37.6 0 0 71.3 71.3 51 59 5 0 1 18
100 FC 0 4 B 34463 00000000
LKO500HF 10/29/2003 7:13:50 AM 10/27/2003 12:13:51 PM
37.6 37.6 0 0 21.3 71.3 51 59 5 0 1 18
100 EQ 0 4 B 34463 00000000
LKO500HF 10/29/2003 7:15:52 AM 10/27/2003 12:13:51 PM
37.6 37.6 0 0 71.3 71.3 51 59 5 0 1 18
100 FL 0 4 B 34463 00000000
This is the part that really has me stuck.
Maybe I should leave the query alone and then perform new calculations,
in the report controls, on the recordset again?
MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I've summarized here to avoid an illegible post.
===
I thought you were creating a report formatted something like this:
Monday (values)
ChgRtn x
SumVID x
MinSTSOC x
MaxTemp x
FullChg x
EQChg x
Tuesday
ChgRtn x
SumVID x
MinSTSOC x
MaxTemp x
FullChg x
EQChg x
What you're really doing is creating a quasi Pivot Table rpt. "Quasi"
since you're not making the same calculations for each grid cell.
In this case you should not group by the WkDay value in the report's
Group/Sort dialog box.
The query can still return the WkDay value - then the report won't
have to evaluate STDATE with the WeekDay function (see below).
You'll need to create 30 TextBoxes (5 weekdays X 6 values) in the
report's Detail section. I.e., you'd create a grid on the report that
has the WeekDay names across the top and then have 6 TextBoxes under
each WeekDay name for the values you want expressed. E.g.:
Monday Tuesday Wednesday
ChgRtn x x x
SumVID x x x
MinSTSOC x x x
MaxTemp x x x
FullChg x x x
EQChg x x x
==
I've reformatted your query so the GROUP BY columns are before the
aggregate columns. In this case the GROUPing would be: For each
WkDay there will be one VehicleID, that will have one (or possibly
more?) BattID that will have one, or more, FullChg (probably only one)
and one, or more, EQChg (probably only one). Each row will have the
Sum(Ahret), Count(VehicleID), the Min(StSoc), and either the Max(NDT)
or the Max(STT) depending on which value is the greater of the two.
SELECT DatePart("d", [STDATE]) AS WkDay,
[VEHICLEID],
[BATTID],
IIf([CT]="FL","Yes","No") AS FullChg,
IIf([CT]="EQ" And
DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No") AS EQChg,
SUM([AHRET]) AS ChgRtn,
COUNT(*) AS SumVID,
MIN([STSOC]) AS MinSTSOC,
IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp
FROM CHARGELOG
WHERE STDATE Between #10/27/2003# And #11/2/2003#
And VehicleID='00000000'
And BattID='LKO500HF'
GROUP BY DatePart("d", [STDATE]), [VEHICLEID], [BATTID],
IIf([CT]="FL","Yes","No"),
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No")
Note that you have to GROUP BY the results of the DatePart() function
which is a number, instead of the STDATE, which is a date.
Then you'd set up the report's control sources like this (use same
TextBox names, but with numbers to identify which weekday they go
under):
Under Monday column:
ChgRtn2: =IIf(WkDay=2, ChgRtn)
SumVID2: =IIf(WkDay=2, SumVid)
MinSTSOC2: =IIf(WkDay=2, MinSTSOC)
MaxTemp2: =IIf(WkDay=2, MaxTemp)
FullChg2: =IIf(WkDay=2, FullChg)
EQChg2: =IIf(WkDay=2, EQChg)
Under Tuesday column:
ChgRtn3: =IIf(WkDay=3, ChgRtn)
SumVID3: =IIf(WkDay=3, SumVid)
MinSTSOC3: =IIf(WkDay=3, MinSTSOC)
MaxTemp3: =IIf(WkDay=3, MaxTemp)
FullChg3: =IIf(WkDay=3, FullChg)
EQChg3: =IIf(WkDay=3, EQChg)
... etc. for the other week day columns.
HTH,
- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP6NzW4echKqOuFEgEQJnowCffI1AozerzAHWUXR6t9OTuX xTwJQAnRfO
H8fI2woRatCDL+M5p/XDBhQ+
=W83V
-----END PGP SIGNATURE-----
mark wrote:
Thanks for your reply. It is helping but I am still stuck. I have
responded to your words, also interleaved in the post below:
MGFoster wrote:
Answers interleaved in your post (below):
mark wrote:
> 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
These statements would be correct as parts of the SELECT clause of a
query (except for the Max evaluation - that doesn't fit w/ the other
statements). IOW, you can't use them in a TextBox's ControlSource
property. You must use only one of the above IIf() functions in one
TextBox ControlSource at a time. The IIf() must be preceeded by an
equal sign. E.g.:
TextBox properties
------------------
ControlSource: =IIf((DatePart("d",[STDATE])=2) And
[CT]="FL","Yes","No")
Name: FullChg
I have tried using one IIf statement in the controlsource of each
textbox, just like the one you mentioned above.
Do you mean that this IIf statement will not work in the
controlsource of a textbox or in the SELECT stmt. because it has MAX
in it?:
IIf((DatePart("d",[STDATE])=2) And
(Max([NDT])>Max([STT])),Max([NDT]),Max([STT])) as MAXTemp
If so, then what would I do? I found the DMax and DMix Functions.
> 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,
Usually a SUM() function is wrapped around the IIf() function. E.g.:
Sum(IIf(Weekday(STDATE)=2, AHRET)
By not putting in the "false" expression the Sum() will sum a NULL
value - which is ignored by the SUM() function.
> 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")
< snip >
This example has a WHERE clause that only indicates one day:
10/27/2003. You're aware of that, right?
I forgot to change the WHERE stmt. back to the following after some
testing:
WHERE STDATE Between #10/27/2003# And #11/2/2003# And
VehicleID='00000000' And BattID='LKO500HF';
For each day of the week on the report you need to group (in the
report) the STDATE by the day. Open the Group/Sort dialog box. Put
STDATE in a "Field Expression" grid box. Set its "Group On" value
to "Day."
You could do the same thing in the RecordSource query's SELECT
clause - just use:
DatePart("d", [STDATE]) As WkDay
then you could use the WkDay column as a Group/Sort column & set
Group On to "Interval."
By grouping the WkDay you would no longer need to check it's value
in the IIf() function.
Unless, . . . .
You want to use different columns from CHANGELOG for different week
days. IOW, if WeekDay = 2 use the AHRET column to get the ChgRtn
value; if WeekDay = 3 the use the XYZ column to get the ChgRtn; if
WeekDay = 4 the use the ABCD column to get the ChgRtn; ... etc.
Is this what you want to do? I would probably try to get the query
to produce what I want & then just format the report to accept the
columns of the query.
Here is the new query:
SELECT DatePart("d", [STDATE]) AS WkDay, [VEHICLEID], [BATTID],
Sum([AHRET]) AS ChgRtn, COUNT([VehicleID]) AS SumVID, MIN([STSOC]) AS
MinSTSOC,
IIf(Max([NDT])>Max([STT]),Max([NDT]),Max([STT])) AS MAXTemp,
IIf([CT]="FL","Yes","No") AS FullChg,
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No")
AS EQChg
FROM CHARGELOG
WHERE STDATE Between #10/27/2003# And #11/2/2003# And
VehicleID='00000000' And BattID='LKO500HF'
GROUP BY [STDATE], [VEHICLEID], [BATTID], IIf([CT]="FL","Yes","No"),
IIf([CT]="EQ" And DateDiff("s",[STTIME],[NDTIME])>18000,"Yes","No");
I changed the query by adding "DatePart("d", [STDATE]) As WkDay" and
removing the date check from the IIf stmts. and the records are
grouping by date and not just returning records with a STDATE of Monday.
There is a problem though, where records exist that have more than
one value that the IIf statements are looking at I am getting
multiple records for a given STDATE, (ie. In the test records below,
if the CT field has an "EQ" value and a "FL" value then it returns 2
records. All I want is if just one record that says if "EQ" + the
datediff > 5 hours exists then "Yes", otherwise "No".). I need one
record for each day of the week then I need to place the fields in
text boxes (or labels) according to the day of the week they fall in.
To explain it further; There are two records for 10/27/03. One has a
CT="EQ" and the other ="FL". So for the expression,
(IIf([CT]="FL","Yes","No") AS FullChg) in the select statement I get
two records returned where all field values are the same except
FullChg says "Yes" in one and "No" in the other.
In the report I have planned, the fields returned from the SELECT
stmt. are shown vertically under its day of the week - horizontally.
The VehicleID and BattID fields are constant so will be listed as a
header. I added the Wkday field from the SELECT stmt. in Group/Sort
on the report and Group On to Interval, but how do I use this
Group/Sort on Wkday for this report? Maybe the report should be
formatted differently to make it easier and to use the Group/Sort?
I'm not sure how though. The report will always be just one page.
Example:
Monday Tuesday Wednesday etc...
ChgRtn
SumVID
MinSTSOC
MaxTemp
FullChg
EQChg
Sample Records I am testing 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 61.3
71.3 50 59 5 0 1 18 100 EQ 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 69 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 68.3
71.3 57 59 4 0 1 18 100 FC 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 FC 0 4 B 34463 00000000
LKO500HF 10/29/2003 07:13:51 10/27/2003 12:13:51 37.6 37.6 0 0 21.3
71.3 51 59 5 0 1 18 100 EQ 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
Thank you,
Mark
HTH,
MGFoster:::mgf
Oakland, CA (USA)
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: neblackcat |
last post by:
Would anyone like to comment on the following idea?
I was just going to offer it as a new PEP until it was suggested that
I post it here for comment & consideration against PEP 308.
I'm far...
|
by: Bengt Richter |
last post by:
We have where syntax in combination with suite expression syntax (bear with me,
I think a good synergy will emerge ;-)
...
|
by: Martin Johansen |
last post by:
In C,
what do you call that which is separated by semicolon?
what is the difference between an expression and a statement?
Thank you.
|
by: Fredrik Tolf |
last post by:
Take a look at this C snippet:
#include <stdio.h>
int test(int *var)
{
return(*var += 5);
}
int main(void)
|
by: Együd Csaba |
last post by:
Hi All,
I'd like to "compress" the following two filter expressions into one -
assuming that it makes sense regarding query execution performance.
.... where (adate LIKE "2004.01.10 __:30" or...
|
by: Luke Matuszewski |
last post by:
I am designing the library, which will hidden all its functions within
singleton object ... So for clients
they will use it like . eg.
system.getElementWithId('ruler');
At library side, i...
|
by: Neroku |
last post by:
Hello, i would like to know what the serious definition of statements
and expressions is:
i know an expression are evaluated to a value, i.e:
1 == 2
5+7
foo( 1,2)
and a statement is...
|
by: G .Net |
last post by:
Hi
Can anybody help with a problem with Expressions for DataColumns
Suppose I have a table with three fields A, B and C.
I want an expression in column C so that
C = 2 * A if A is not...
|
by: raylopez99 |
last post by:
Took a look at all the fuss about "lambda expressions" from Jon
Skeet's excellent book "C# in Depth". Jon has an example, reproduced
below (excerpt) on lambda expressions.
My n00b take: it's...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |
