iif statement as criteria on sub report

D

dark11984

Registered User.
Local time
Today, 18:36
Joined
Mar 3, 2008
Messages
129
A little help please.:confused:

I have a form where i select a contract number(cbocmsref and a month(cbofldate). Once selected i hit a button and my report opens. i want the main repor tot be as per cbocmdref (i got this part working) and the sub report criteria to be as per the cbofldate on the form.

Each contract can either be quarterly or monthly. if quarterly i want the criteria on the subreport to be cbofldate and the next two months after it. If the contract is monthly then i only want the criteria to eb cbofldate.

I've tried putting this iif statement in the date criteria but does not work.

Code: 
IIf(forms!frmrepfilter5!cbocmsref.column(2)="Quarterly",(TblFuelLevy.FLDate)=forms!frmrepfilter5!cbofldate And (TblFuelLevy.FLDate)=DateAdd("m",1,forms!frmrepfilter5!cbofldate) And (TblFuelLevy.FLDate)=DateAdd("m",2,forms!frmrepfilter5!cbofldate),forms!frmrepfilter5!cbofldate)

Thanks
 
Couple of things to note regarding your problem:

1. Unfortunately the True/False arguments of the IIF() function returns a string so it will "convert" the AND (or any statement) to a string rather than returning an operator or statement.
2. The recordsource of a report/subreport can only be amended at design time.

So in order to change the WHERE argument of the subreport you would do it in code either by opening the form in design view and amending it or (even better) amend the querydef. The advantage of the latter is that you wouldn't get the "Do you want to save changes, Yes/No/Cancel" dialog box when you close the report.

Something like this:
Code: 
    Dim qdf As QueryDef
    
    ' Save the SELECT part WITHOUT the WHERE clause here
    Const sqlString = "SELECT * FROM [[COLOR=Red][B]TableName[/B][/COLOR]] "
    
    ' Set the qdf object to the querydef of the subreport
    Set qdf = CurrentDb.QueryDefs("[COLOR=Red][B]NameOfQuery[/B][/COLOR]")
    
    ' Append the WHERE clause to the sql string
    qdf.SQL = sqlString & "WHERE " &  IIf(Forms!frmrepfilter5!cbocmsref.Column(2) = "Quarterly",  "TblFuelLevy.FLDate BETWEEN #" & _
                                        Forms!frmrepfilter5!cbofldate  & "# AND #" & DateAdd("m", 2, Forms!frmrepfilter5!cbofldate)  & "#", _
                                        "TblFuelLevy.FLDate = #" &  Forms!frmrepfilter5!cbofldate & "#")
    
    ' Then open the report here
    DoCmd.OpenReport "NameOfReport"
 
Last edited:
I realised i made a typo in my post. Should've read:

if quarterly i want the criteria on the subreport to be cbofldate and the previous two months before it
Click to expand...

So i have changed the dateadd funtions to -2 rather then 2.

After doing this i have two problems.
1. the date that ends up being the criteria in the query is in US format MM/DD/YYYY. I need it to be DD/MM/YYYY. I've changed the SQL Statement to below but it is not chnaging the date format, not sure where else i should change it? All other dates in related tables are either in the shortdate format or mmm-yy.

Code: 
qdf.SQL = sqlString & "WHERE " & IIf(Forms!frmrepfilter5!CboCMSRef.Column(2) = "Quarterly", "TblFuelLevy.FLDate BETWEEN #" & _
                                        Format(Forms!frmrepfilter5!CboFLMonth, "dd/mm/yyyy") & "# AND #" & DateAdd("m", -2, Format(Forms!frmrepfilter5!CboFLMonth, "dd/mm/yyyy")) & "#", _
                                        "TblFuelLevy.FLDate = #" & Format(Forms!frmrepfilter5!CboFLMonth, "dd/mm/yyyy") & "#")

2. I also need to add in another criteria that tblFuelLevy.FLType = "Metro". This criteria will be constant in the query. Where would i put this?

Cheers
 
1. The hash characters implicitly "casts" the string to a US date and you need the US format if you want to query date fields. Change it back to "mm/dd/yyyy". To get the format you want in the query results use the Format() function in the query design view to format that field. Here's a link:

http://www.techonthenet.com/excel/formulas/format_date.php

2. Put it just after WHERE like this:
Code: 
" WHERE tblfuellevy.fltype = 'Metro' AND " & IIF(...
Take note of the single quotes in red for strings.
 
I go tthe metro part to work. but i'm having trouble the date format.

I've entered the date format into the designview of the query and i get:
Expr1: Format([FLDate],"mm/dd/yyyy") this gets me the rigth results, however if i go out and run the report again from the form it asks for a paremeter value for Expr1. Then if i go back into the query dessign view it has reset back to FLDate.
 
Set it as:

Expr1: Format([FLDate],"dd/mm/yyyy")

Just like you initially wanted. By the way, in case you didn't know, you can call Expr1 something else.
 
If i set the format as DD/MM/YYYY it does not return any results because the criteria from VBA is asking for MM/DD/YYYY.

The data that exists in the table is in DD/MM/YYYY format so wouldn't have to chang format to MM/DD/YYYY?

yeah, i knew you could change expr1 to something else, so say i change it to FLMonth. I then have to change my control source for the the month text box on the subreport from FLDate to FLmonth. I then run the report again and the query has reset the format() function back to FLDate.

Thanks.
 
Run the code and then paste the sql statement of the query here. You can get in the query's SQL VIEW.
 
dark11984 said:
If i set the format as DD/MM/YYYY it does not return any results because the criteria from VBA is asking for MM/DD/YYYY.

The data that exists in the table is in DD/MM/YYYY format so wouldn't have to chang format to MM/DD/YYYY?
Click to expand...

VBA wants mm/dd/yyyy for the sql.

It doesn't matter about the date format in the table. Date data is actually a Double data type and that is what the code works with. The value is the same no matter what display format is chosen for the table.
 
This is what i have when i don't format the date field in the query.

Code: 
SELECT TblFuelLevy.CMSRef, TblFuelLevy.Site, TblFuelLevy.FLdate, TblFuelLevy.FLType, TblFuelLevy.FLRate, TblFuelLevy.BaseRate, TblFuelLevy.ActualRate, TblFuelLevy.CostVariance, TblFuelLevy.CostVariancePer, TblFuelLevy.FLPayableRate, TblFuelLevy.CoreFleetSpend, TblFuelLevy.OutsideHireSpend, TblFuelLevy.DemurrageSpend, TblFuelLevy.AdminSpend, TblFuelLevy.FLPayable
FROM TblFuelLevy
WHERE tblfuellevy.fltype = 'metro' and TblFuelLevy.FLdate BETWEEN #1/10/2009# AND #1/08/2009#;

This is what i have when i do format it. This one gives me the results i need in the query but as i mentioned in my previous post it doesn't display in the report because i need to chnage my control source and also the query resets back to the SQL above once i exit.
Code: 
SELECT TblFuelLevy.CMSRef, TblFuelLevy.Site, Format([FLDate],"mm/dd/yyyy") AS Expr1, TblFuelLevy.FLType, TblFuelLevy.FLRate, TblFuelLevy.BaseRate, TblFuelLevy.ActualRate, TblFuelLevy.CostVariance, TblFuelLevy.CostVariancePer, TblFuelLevy.FLPayableRate, TblFuelLevy.CoreFleetSpend, TblFuelLevy.OutsideHireSpend, TblFuelLevy.DemurrageSpend, TblFuelLevy.AdminSpend, TblFuelLevy.FLPayable
FROM TblFuelLevy
WHERE (((Format([FLDate],"mm/dd/yyyy")) Between #1/10/2009# And #1/8/2009#) AND ((TblFuelLevy.FLType)='metro'));
 
Don't reformat the field. Access will deal with the underlying date datatype.
Change the comparison dates to #mm/dd/yyyy#

Presumably youwill ultimately change the comparison to accept a date from a control.
Use this to format a date from a dd/mm/yyyy textbox for sql:
Format([textbox], "\#mm/dd/yyyy\#")
 
This is where Galaxiom is talking about, don't do this:
Code: 
WHERE ((([COLOR=Red][B]Format([/B][/COLOR][FLDate],"mm/dd/yyyy")[COLOR=Red][B])[/B][/COLOR]
Do this:
Code: 
WHERE [FLDate]=...
Just like the way I wrote it originally.

Post your db so we can have a look.
 
ok i'm totally confused now... lol.

I've attached my DB.

Thanks.
 

Attachments

You must log in or register to reply here.

Similar threads

S
Solved Using a Does Not Contain Criteria in an IIf Statement
Replies
9
Views
569
Pat Hartman
P
T
Cannot Sum a Calculated Control in the Report Footer
Replies
11
Views
1,092
The_Doc_Man
The_Doc_Man
M
How to show the value of criteria on query to report
Replies
5
Views
614
xavier.batlle
X
A
If statement in Continuous sub form
Replies
1
Views
343
arnelgp
arnelgp
G
Solved Data from form not showing on Statement of Account report
2
Replies
21
Views
1,474
GaP42
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom