Report Parameters

[mithani]

Hi All,

I have designed report screen where user can choose:

1. Report Type
2. Project
3. Supplier
4. Product Type
5. Date From
6. Date to

User will select report type and then the other are optional. If he didn't select anything then should get complete report of the selected report type.

I am trying the below code but showing error. please see the attached jpg.

DoCmd.OpenReport "Purchase Report", acViewPreview, , IIf(IsNull(Me.PROJECT), ",", "[Project_code]='" & Me.PROJECT) _
    & "'And & ([InvoiceDate] Between #" & Me.DateFrom & "# And #" & Me.DateTo & "#)"

Any help?

Thanks

mithani

 

[shudini]

In the Value_if_true part of your iif statement, you are returning a comma "," if the value of me.PROJECT is null. Do you mind my asking why? A comma has no place being there. Also, after the word And you have an ampersand "&" which does not seem to be in place.

Just for clarification, what do you want to accomplish with the iif statement?

 

[mithani]

Shudini,

My form consisted on:

1. Report Type
combo, just to select report type.

2. Project
User will enter project code to get the particular project or else if leave blank then need all project
3. Supplier
same as project...
4. Product Type
same as project...

I hope thats what you were asking.

Your help will be really apprecaited.

mithani

 

[mithani]

Hi Shudini,

if i use this parameters I get report of any particular project. But what about if I need full report with all projects?


DoCmd.OpenReport "Purchase Report", acViewPreview, , "[Project_code]='" & Me.PROJECT & "' And ([InvoiceDate] Between #" & Me.DateFrom & "# And #" & Me.DateTo & "#)"

Thanks

mithani

 

[shudini]

what you really need to do is to dynamically build the where statement before you run the DoCmd function. This way it will include only the requested parameters and based on what they select, you can build a string that will only filter what you want.

 

[mithani]

Thanks Shudini,

Can you tell me the parameter code?

Thanks mate

mithani

 

[shudini]

Dim strWhere as string
Dim Count as Integer
Count=0

If not nz(me.project,"")="" then 'THIS IS ONLY NECESSARY SINCE I DON'T KNOW IF THE VALUE OF THE COMBO IS NULL OR BLANK
   strWhere="[Project_Code]='" & me.project & "'"
   Count=count+1
End If

If not nz(me.supplier,"")="" then
   strWhere=strWhere & iif(count>0," and ","") & "[[COLOR="Red"]Supplier_Code[/COLOR]]='" & me.supplier & "'"
count=count+1
End If

If not nz(me.ItemType,"")="" then
      strWhere=strWhere & iif(count>0," and ","") & "[[COLOR="Red"]ItemType[/COLOR]]='" & me.ItemType & "'"
count=count+1
End If

'I don't know what your requirements are regarding dates.
'Are they optional, both required, one required?
'depending on that is how you would set up the coding, 
'but it would be similar to what you have above

DoCmd.OpenReport [COLOR="#ff0000"]me.ReportType[/COLOR], acViewPreview, , strWhere

Good Luck

 

[mithani]

Thanks Shudini

Regards

mithani