generating a report

[rikklaney1]

I have a question about generating a report. In the criteria of the query my report is based on I have this...

[forms]![subfrmCustom_report]![serial]

which works great to look up the record for that serial number. Now is it possible to modify this so that if I leave that field on the form blank it will return all the records? I tried this...

iif(not isnull([forms]![subfrmcustom_report]![serial],[forms]![subfrmcustom_report]![serial],)

but no luck. Where am I going wrong?

 

[sneuberg]

For the case where everything is to be shown or where IsNull([forms]![subfrmcustom_report]![serial]) is true iif needs to return something that will display everything. One thing that will do this is the name of the table field. Let's say for example the name of the table field is SerialNumber, then your iif would be like:

IIf(IsNull([Forms]![subfrmcustom_report]![serial]),[SerialNumber], [Forms]![subfrmcustom_report]![serial])

 

[rikklaney1]

Awesome, that did it. Thanks

 

[rikklaney1]

another related question it would appear that I can set columns in a query to show/not show in a similar fashion as on a form with something like this

If Forms!subfrmCustom_report!Check19.Value = True Then Query!customrptqry!column17.ColumnHidden = True


but it doesn't work. How do I know the names of my columns in a query? It doesn't seem to be numerical like a listbox but it also doesn't seem to be the "field" title in the query itself.

 

[sneuberg]

I don't know about hiding columns in a query, but if your form is in data sheet view you can do something like that. Maybe https://msdn.microsoft.com/en-us/library/office/aa224064(v=office.11).aspx will help you.

Or google "ms access hide columns in datasheet view" and see what you find.

 

[arnelgp]

supposed that your query is already showing, and you want to hide some columns through the use of VBA:

If Forms!subfrmCustom_report!Check19.Value = True Then
DOCMD.SelectObject acQuery, "NameOfYourQuery", False
SCREEN.ActiveDatasheet!ColumnToHide.ColumnHidden = True
End If

 

[rikklaney1]

Better clarification on what I mean. How would I call this value in vba when opening the query?

 

[arnelgp]

How would I call this value in vba when opening the query?

is CustomRPTQry be used in a report, or just being displayed as datasheet?
if datasheet:

DoCmd.docmd.OpenQuery "CustomRPTQry",acViewNormal
If Forms!subfrmCustom_report!Check19.Value = True Then
DOCMD.SelectObject acQuery, "CustomRPTQry", False
SCREEN.ActiveDatasheet!ColumnToHide.ColumnHidden = True
End If

 

[rikklaney1]

No it is used in a report.

 

[arnelgp]

use your report's Load event, to set its control's width to 0:

private sub Report_Load()
If Forms!subfrmCustom_report!Check19.Value = True Then
Me.yourControlName.Width = 0
End If

 

[rikklaney1]

I thought maybe I could eliminate it from the query altogether on demand but I did figure out how to just hide it on the report. It leaves some blank areas but it works.

 

[arnelgp]

do remove some blank areas, on your report design view select all controls and on the menu Arrange->Table, select Tabular or stacked.
then when you set the column of one of your control to zero, the controls to the right will adjust to the left and fill those blank areas.

 

[rikklaney1]

Sweet. What about subreports? Can I filter them the same as a subform? I tried this...

Me.boomsub.Report.Filter = "[Station] like 'b*'"
Me.boomsub.Report.FilterOn = True

but no luck.

 

[rikklaney1]

Nevermind, looks like I can do the filter idea through the grouping options on the report.