Need syntax help with my IIF statement

[ruthcali]

i am using Access97.

i need help with some syntax. i think i am close.

i made a table called tblMonths with 3 columns:
Month From TO
January January 1 January 31
etc.

then i created a form called frmRptTotals and on the form i made a list box called cboMonth with the 3 columns above. The 1st column is visible and the other 2 are invisible.

Then i made another list box called cboYear where i entered years (using Value list).

Then i made a text box called txtDate1 with the value: =[cboMonth].[Column](1) & ", " & [cboYear]

then i made another text box called txtDate2 with the value: =[cboMonth].[Column](2) & ", " & [cboYear]

Then in my report, i have a text box:
=IIf([project_issued_date] Between [Forms]![frmRptTotals]![txtDate1] And [Forms]![frmRptTotals]![txtDate2],1,0)

But, everything turns up 0! help

i have a feeling there is a problem with my syntax. do i need to type ...between & "'" & Forms![frmRptTotals]!...

thanks,
ruth

 

[PaulF]

problem may lie in the fact that you are trying to filter a date field with strings... look into CDate() function to convert your strings to dates.

PaulF

 

[ruthcali]

Hi Paul,
thanks for writing. i figured it out! i changed the data in my table.

before,
in column 2(the To field), i had January 1
in column 3(the From field), i had January 31

i changed the format to 01/01 in column 2 (To) and
and 01/31 in column 3 (From).


And it works now. (well, at least, so far-so good!).

i guess Access wanted the number format instead of the words.

but, i don't really understand it since, as a test, i created a text box on my form with a control source of =isdate(txtDate1). That value was -1 (true) which means txtDate1 was a date field.

can you explain that?
well, thanks again for writing,
ruth

 

[MichaelRed]

"Then i made a text box called txtDate1 with the value: =[cboMonth].[Column](1) & ", " & [cboYear]"

Shouldn't .[Column](1) be .Column(2)?




MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[ruthcali]

Hi Michael,

remember, list boxes start counting at zero.

my list box has 3 columns:
column(0) January
column(1) 01/01
column(2) 01/31


so
txtDate1 =[cboMonth].[Column](1) & ", " & [cboYear]
has a value of 01/01/1999

and txtDate2 =[cboMonth].[Column](2) & ", " & [cboYear]
has a value of 01/31/1999

 

[MichaelRed]

O.K. My error.

Still, I think it could be easier.

Consider that you start with JUST the cbo selections for Month and Year (each has only a single column)

To "form the start date, it is just:

StrtDt = DateValue([cboMnth] & " 1, " & [cboYear])
EndDt = DateAdd("d", -1, DateAdd("m", 1, StrtDt))

StrtDt is not much different than what you have, except that we do not need the other columns. The First of any month is "always" MonthName 1, Year
where Month name is Jan | January (for the first month) and throughout the year, the "1, " is a constant and the year may be expressed as a two or four digit value. By using the DataValue, we can coerce the calculation to be a date without the date delimiters.

The end date is a little less straight forward, but a little more reliable (what are you doing about the YEAP Year?). The end of any month is - with some thought the first of the next month sans one day. This is the calculation shown for EndDt.

MyDate = DateAdd(interval, N_Intervals, Start Date)

Returns the date calculated from the Start Date, by adding the Number of Intervals in N_Intervals where interval is a string expression resolving to one of the codes listed in the Ms. Access docs. Here, we use "m" for month and "d" for day, so the inner part of the second calculation adds one month to the start (Jan 1, 2000) to yield Feb 1, 2000) while the outter part adds a negative one day to step backwards to the last day of the month we started with (and it "knows" about leap year!). So we never need to store any values except that which the user selects directly from the "combo boxes". Also, other than the generation of StrtDt, we do not need to know or manipulate the date format.

I could go on, but I know you're tired of reading by this time.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[ruthcali]

Wow. that is amazing. you are a wonderful programmer! i really appreciate your wisdom.

i thought my way was clever, but your way blows mine away!

thank you very much.

Here's another question: how would you recommend going about creating a report that shows all the months on one page?

i got the Monthly report to work so the user choses a month and year and then sees the report for that time frame.

but, i need to also create a Yearly report comparing all the months for a given year. so the Yearly report would look something like this:
REPORT FOR 1999
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec YTD

# of Projects
# Cancelled
# of Employees
etc.


How can i go about creating that?

The Monthly report that i made was a bit difficult to produce. (I'm scared to think about the Yearly report!) In the query that the Monthly report is based on, i have many fields with IIF statements.

then, in the Monthly report, all of my data is contained in the report footer section. Most of those text boxes use the Sum function.

i know it is hairy, but following is the SQL of the query my Monthly report is based on. The Forms![frmRptTotals] is the form where the user choses the month and year.

any advice you have about creating a Yearly report would be very appreciated!

SELECT dbo_Engineering_Request.*, tblUserInput.*, tblEngineer.*, IIf([er_issued_date] Between [Forms]![frmRptTotals]![txtDate1] And [Forms]![frmRptTotals]![txtDate2],1,0) AS CountERIssued, IIf([er_issued_date] Between [Forms]![frmRptTotals]![txtDate1] And [Forms]![frmRptTotals]![txtDate2],[scheduled_rft_date]-[er_issued_date]) AS AvgReqInterval, IIf(([er_issued_date] Between [Forms]![frmRptTotals]![txtDate1] And [Forms]![frmRptTotals]![txtDate2]) And ([project_name] Not Like &quot;*Equipment*&quot; And [project_name] Not Like &quot;*eqpt*&quot; And [project_name] Not Like &quot;*OPC*&quot,1,0) AS SiteReq, IIf([er_issued_date] Between [Forms]![frmRptTotals]![txtDate1] And [Forms]![frmRptTotals]![txtDate2] And ([SiteSurveyDate]-[er_issued_date])<11,1,0) AS SiteComplTime, IIf(([er_issued_date] Between [Forms]![frmRptTotals]![txtDate1] And [Forms]![frmRptTotals]![txtDate2]) And ([project_name] Not Like &quot;*Mux*&quot; And [project_name] Not Like &quot;*decom*&quot; And [project_name] Not Like &quot;*reconfig*&quot,1,0) AS TERReq, IIf([ter_issued_date] Between [Forms]![frmRptTotals]![txtDate1] And [Forms]![frmRptTotals]![txtDate2],1,0) AS TERIssued, IIf([ter_issued_date] Between [Forms]![frmRptTotals]![txtDate1] And [Forms]![frmRptTotals]![txtDate2],IIf(([ter_issued_date]+33)<[scheduled_rft_date],1,0)) AS TERIssuedOnTime, IIf(([er_issued_date] Between [Forms]![frmRptTotals]![txtDate1] And [Forms]![frmRptTotals]![txtDate2]) And ([project_name] Not Like &quot;*Eqpt*&quot; And [project_name] Not Like &quot;*Equipment*&quot,1,0) AS ISpecReq, IIf([specs_sent_date] Between [Forms]![frmRptTotals]![txtDate1] And [Forms]![frmRptTotals]![txtDate2],1,0) AS ISpecIssued, IIf([specs_sent_date] Between [Forms]![frmRptTotals]![txtDate1] And [Forms]![frmRptTotals]![txtDate2],IIf(([specs_sent_date]+30)<[scheduled_rft_date],1,0)) AS ISpecIssuedOnTime, IIf([snf_sent_date] Between [Forms]![frmRptTotals]![txtDate1] And [Forms]![frmRptTotals]![txtDate2],1,0) AS NumERSCompleted
FROM tblEngineer RIGHT JOIN (dbo_Engineering_Request LEFT JOIN tblUserInput ON dbo_Engineering_Request.er_no = tblUserInput.er_no) ON tblEngineer.EngID = tblUserInput.Engineer
WHERE (((dbo_Engineering_Request.city_cd)=&quot;wdc&quot);

 

[MichaelRed]

Ruth,

I received this latest @4:55PM on Fri. 12.22.00.

It is a bit much for the next 5 min. I will try to look at it some next week.

You should be at a party or at home w/ family, not posting to Tek-Tips. I might have already gone, but my kids took the car to go ?????????

Talk to you 'later'

Michael



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[raskew]

Another technique:

(1) Function Dateloop(), see below, will create a string of month/year combinations which you can use as the Row Source for your combo box. You can modify the code to add or delete dates.

EXAMPLE: 12/00; 11/00; 10/00; 09/00; 08/00; 07/00; 06/00; 05/00; 04/00; 03/00; 02/00; 01/00;

In your form's OnLoad event, place the following (tailored to the name of your combo box)


Private Sub Form_Open(Cancel As Integer)
Me![cboMoYr].RowSource = dateloop
End Sub


(2) These two lines will furnish your start and end dates. Say you'd selected September 2000 (09/00)

StartDate = datevalue(Me!cboMoYr) returns #9/1/2000#
EndDate = DateSerial(year(datevalue(Me!cboMoYr )), Month(datevalue Me!cboMoYr )) + 1, 0) returns #9/30/2000#

Using the DateSerial() function in this manner eliminates any problems with Leap Years

You could, if you wanted, show the EndDate as:
EndDate = DateSerial(year(datevalue([StartDate])), Month([StartDate])) + 1, 0)

(3) Place a command button (cmdPrintReportx) on your form and include the code shown below as the button's OnClick event.

(4) Place an option group (grpPreviewPrint ) on your form with the options being Preview (1) and Print (2). Set default to preview.

(5) Place this code in the report's On No Data event:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No data found! Closing report."
Cancel = True
End Sub

Place the following code in a module.

****************************************

Function dateloop() As String
'creates a string to be used as a combo-box's value list
Dim datehold As Date, strSQL As String
Dim n As Integer, i As Integer

n = 11
strSQL = &quot;&quot;

For i = 0 To n
datehold = DateSerial(year(Date), Month(Date) - i, 1)
strSQL = strSQL &amp; Format(datehold, &quot;mm&quot &amp; &quot;/&quot;
strSQL = strSQL &amp; Format(datehold, &quot;yy&quot &amp; &quot;; &quot;
Next i

dateloop = strSQL

End Function

*****************************************

This is the code for the command button's On-Click event:

Private Sub cmdPrintReportx_Click()
On Error GoTo Err_cmdPrintReportx_Click

Dim Where As String
Dim F As Form
Dim j As Integer
Dim DocName As String
Dim StartDate As Date, EndDate As Date

Set F = Screen.ActiveForm

Where = &quot;&quot;

If IsNull(F![cboMoYr]) Then
docmd.Beep
MsgBox &quot;Please select a month/year and then try again.&quot;
Exit Sub
End If

DocName = &quot;MyReport&quot; 'change to your report name
StartDate = DateValue(F!cboMoYr)
EndDate = DateSerial(year(DateValue(F!cboMoYr)), Month(DateValue(F!cboMoYr)) + 1, 0)
Where = &quot; [project_issued_date] >= #&quot; &amp; StartDate &amp; &quot;# and [project_issued]<= #&quot; &amp; EndDate &amp; &quot;#&quot;

On Error Resume Next
'If not data is found, the reports OnNoData event will display
'a message box. However, it also generates an error which must
'be cleared.

If Me![grpPreviewPrint] = 2 Then 'optPreviewPrint set to print
docmd.OpenReport DocName, acNormal, , Where
If Err = 2501 Then Err.Clear 'if no data found
Else
docmd.OpenReport DocName, acViewPreview, , Where 'optPreviewPrint set to preview
If Err = 2501 Then Err.Clear 'if no data found
End If
Exit_cmdPrintReportx_Click:
Exit Sub

Err_cmdPrintReportx_Click:
MsgBox Error$
Resume Exit_cmdPrintReportx_Click

End Sub

 

[raskew]

Re your annual report:

From your example, it appears your data items are all based on counts. Since we don't have access to your data, here's an example that uses the Orders table in the Northwind Database. You should be able to adapt this technique to your situation. In this example, the goal is to display a report for all 12 months of 1995 and show the following monthly counts:

# of orders
# of orders shipped via shipper #3
# of orders with freight >= $15.00

You could attack this using either code or queries. In this instance, I believe that code is simpler, but I'll show both techniques.

Code: Function CountMake() show below creates table z_tblTempHold, and a recordset of Orders records with an Order year of 1995. It creates a record for each month and populates it with the month, number of orders, number of orders shipped via shipper #3 and number of orders with freight charges >= $15.00.

Queries: There may be a simpler way, but I found it was necessary to create three queries, each performing a different count (total orders, orders shipped via #3, and orders with freight charges….). Having done that, I created a master query the incorporates the results of the three subqueries.

Output: Whether you use Function CountMake() or xqryCountAll, you end up with essentially identical data, i.e.

Sort_ CountOf_ CountOf_ CountOf_
Month OrderID Shippers Freight
Jan 31 16 24
Feb 29 8 21
Mar 33 7 21
Apr 28 10 15
May 33 11 25
Jun 32 9 23
Jul 30 9 22
Aug 33 8 23
Sep 33 14 26
Oct 37 6 29
Nov 37 11 30
Dec 35 13 23

The trick now was to reverse the rows and columns to conform to your desired format. Not being a cross-tab whiz, I found the easiest and quickest way was to pull the table into Excel, copy the data, then move to a blank cell and Paste Special, selecting the Transform option. Once you get the data looking the way you want, save it, close Excel, return to Access and import the data. Now it's just a matter of using the Report Wizard to create the report for you, using the transformed table as the source.

While this may all look very complex, I think you'll find that if you copy/paste the routines into the Northwind database and test them, it'll be much clearer.

CODE EXAMPLE:

Function CountMake()
'*******************************************
'Name: CountMake (Function)
'Purpose: Counts 1995 orders, by month, also
' totalling # of orders shipped via
' shipper 3 and # of freight charges
' >= $15.00.
'Tables(s): Northwind (table) Orders
'Output: (table)z_tblTempHold - 12 records
'To Test: from debug window: ? CountMake <enter>
'*******************************************

Dim db As Database, strSQL As String
Dim n As Integer, test As Variant, tblName As String
Dim rs As Recordset, rs2 As Recordset
Dim orderhold As Integer, freighthold As Integer
Dim Monthhold As String, shipperhold As Integer

Set db = CurrentDb

' Trap for any errors.
On Error Resume Next

tblName = &quot;z_tblTempHold&quot;

'Does table tblname exist? If true, delete it;

test = db.TableDefs(tblName).Name
If Err <> 3265 Then 'the specified table exists
DoCmd.DeleteObject acTable, tblName
End If

'SQL string to create new table

strSQL = &quot;&quot;
strSQL = &quot;CREATE TABLE &quot; _
&amp; tblName _
&amp; &quot;(TheMonth TEXT (3), Orders NUMBER, Shippers NUMBER, Freight Number); &quot;

' either of these commands creates the table
'DoCmd.RunSQL strSQL
db.Execute strSQL

'SQL string to query (table) Orders

strSQL = &quot;SELECT OrderDate, ShipVia, Freight FROM Orders &quot; _
&amp; &quot;WHERE ((Year([OrderDate]) = 1995))ORDER BY OrderDate;&quot;

Set rs = db.OpenRecordset(strSQL) 'the source table
Set rs2 = db.OpenRecordset(tblName) 'the newly created, empty table

' Get number of records in recordset
n = 0
If Not rs.BOF Then
rs.MoveLast
n = rs.RecordCount '391 if table is unaltered
rs.MoveFirst
End If

If n > 0 Then 'process will bomb on an empty recordset

'prepare to loop through table &quot;Orders&quot;
orderhold = 0
shipperhold = 0
freighthold = 0
Do While Not rs.EOF
Monthhold = Format(rs!OrderDate, &quot;mmm&quot
Do While Format(rs!OrderDate, &quot;mmm&quot = Monthhold And Not rs.EOF
orderhold = orderhold + 1 'count each order
'counts orders Shipped Via shipper #3
shipperhold = shipperhold + IIf(rs!ShipVia = 3, 1, 0)
'counts orders with freight charges >=$15.00
freighthold = freighthold + IIf(rs!Freight >= 15#, 1, 0)
rs.MoveNext
If rs.EOF Then Exit Do
Loop
'add a new record recapping the captured data
rs2.AddNew
rs2!TheMonth = Monthhold
rs2!Orders = orderhold
rs2!Shippers = shipperhold
rs2!Freight = freighthold
rs2.Update

'reset the counters
orderhold = 0
shipperhold = 0
freighthold = 0
Loop
End If
rs.Close
rs2.Close
db.Close
Set db = Nothing

End Function

QUERY EXAMPLE:

Three subqueries:

(1) xqryCountOrders:

SELECT Format([OrderDate],&quot;mmm&quot AS SortMonth, Count(Orders.OrderID) AS CountOfOrderID, Month([OrderDate]) AS sortorder
FROM Orders
GROUP BY Format([OrderDate],&quot;mmm&quot, Year([OrderDate]), Month([OrderDate])
HAVING (((Year([OrderDate]))=1995))
ORDER BY Month([OrderDate]);

(2) xqryCountShippers:

SELECT Format([OrderDate],&quot;mmm&quot AS SortMonth, Count(Orders.OrderID) AS CountOfShippers
FROM Orders
GROUP BY Format([OrderDate],&quot;mmm&quot, Year([OrderDate]), Month([OrderDate]), Orders.ShipVia
HAVING (((Year([OrderDate]))=1995) AND ((Orders.ShipVia)=3))
ORDER BY Month([OrderDate]);

(3) xqryCountFreight:

SELECT Format([OrderDate],&quot;mmm&quot AS SortMonth, Count(Orders.OrderID) AS CountOfFreight
FROM Orders
WHERE (((Orders.Freight)>=15))
GROUP BY Format([OrderDate],&quot;mmm&quot, Year([OrderDate]), Month([OrderDate])
HAVING (((Year([OrderDate]))=1995))
ORDER BY Month([OrderDate]);

MAIN QUERY: xqryCountAll:

SELECT xqryCountOrders.SortMonth, xqryCountOrders.CountOfOrderID, xqryCountShippers.CountOfShippers, xqryCountFreight.CountOfFreight
FROM (xqryCountOrders LEFT JOIN xqryCountShippers ON xqryCountOrders.SortMonth = xqryCountShippers.SortMonth) LEFT JOIN xqryCountFreight ON xqryCountOrders.SortMonth = xqryCountFreight.SortMonth
ORDER BY xqryCountOrders.sortorder;

 

[ruthcali]

Oh my goodness, Raskew, I don't know what to say. I just got in after the holidays and I find these awesome posts from you! Look what Santa brought me!

Thank you so much. i can't wait to try your advice.

You're so nice to help me like this. i really appreciate it.

Ruth