Reports, Queries & VBA question... (1 Viewer)

[Dansam]

Hi everyone in the forums
Not sure where this thread needs to go, it falls into a number of camps I believe!
I have built a database and got all the functionality working (importing data, updating the system etc.) and now come to a head scratcher with the reports which is my least favourite part of Access.
I have a swictchboard with 7, yes 7 criteria to generate a report from a single table. Is there a clean way I can create a query at runtime without resorting to what I can only describe as the the mother of all IF...THEN statements?
The criteria is Name (text) , Main Membership Status (text), Second Membership Status (text), Year Joined (text), Year Left (text), Retired (yes/no), Contactable (yes/no)
The year joined / year left is a text field here as other info is held in the field with the year at the end encapsulated in brackets.
Any and all help will be thankfully received and gratefully applied!

 

[mike60smart]

Hi everyone in the forums
Not sure where this thread needs to go, it falls into a number of camps I believe!
I have built a database and got all the functionality working (importing data, updating the system etc.) and now come to a head scratcher with the reports which is my least favourite part of Access.
I have a swictchboard with 7, yes 7 criteria to generate a report from a single table. Is there a clean way I can create a query at runtime without resorting to what I can only describe as the the mother of all IF...THEN statements?
The criteria is Name (text) , Main Membership Status (text), Second Membership Status (text), Year Joined (text), Year Left (text), Retired (yes/no), Contactable (yes/no)
The year joined / year left is a text field here as other info is held in the field with the year at the end encapsulated in brackets.
Any and all help will be thankfully received and gratefully applied!

Can you upload a zipped copy of the database?

 

[plog]

You didn't really state an issue or explain what the method you think you have to implement is implementing. So my guess is you want to create a dynamic report with criteria input by the users on a form. If that's the case then you can do that with a DoCmd.OpenReport call

DoCmd.OpenReport method (Access)

Office VBA reference topic

docs.microsoft.com

You build a generic report with all your data on it, then you open the report using that command with which you can customize criteria.

Also, I fear you don't have your table set up properly. First, a year is a number but it seems you are storing it as text? That's a red flag. Then another is that you have just one table.

 

[Dansam]

Can you upload a zipped copy of the database?

Hi Mike, no I can't as there is personally identifiable data within it

 

[mike60smart]

If you remove any confidential data and then upload?

 

[Dansam]

You didn't really state an issue or explain what the method you think you have to implement is implementing. So my guess is you want to create a dynamic report with criteria input by the users on a form. If that's the case then you can do that with a DoCmd.OpenReport call

DoCmd.OpenReport method (Access)

Office VBA reference topic

docs.microsoft.com

You build a generic report with all your data on it, then you open the report using that command with which you can customize criteria.

Also, I fear you don't have your table set up properly. First, a year is a number but it seems you are storing it as text? That's a red flag. Then another is that you have just one table.

Hi, the database has a number of table in it, but I am only generating the report from one (sub reports already in there and working correct)
The year field has more than just one field in it, for example for member name = Jon Smith, Year Joined = Secretary (2010), Year Left = Secretary (2020); these fields are unforunately imported from a different system which is very inflexible

 

[plog]

You need to store discrete pieces of data discretely. That means every piece of data goes into its own field not jammed into one field with other pieces of data. You're Access database needs to store those pieces of data in their own fields.

Let me cut you off right there, I know what you are about to say--and it doesn't matter the manner in which another system stores data. If you are importing data then you need to import it correctly. That might mean bringing the imported data into temporary tables, running queries to break out data properly, then other queries to actually move the data into the properly structured tables in your database.

 

[Pat Hartman]

The built in switchboard is very useful. I use it in virtually every application I build - I have a customized version though so it supports more than 8 items and some other things. However, it is still a generic tool. There is no way for you to customize it to collect multiple variables. The code would get truly ugly.

The way to open reports is via a form so the stitchboard opens a form and the form collects the arguments then the form opens the report.

Here are a few screen shots to give you some idea of how you can run multiple reports from a single form with multiple parameters. The first gray form shows a list of reports. As you click on a report, the right side of the screen that shows the criteria changes to show what the selecyed report uses. I'm not sure you can see it but required criteria have labels with bold and underline. Other criteria fields are optional. For the green form, the reports have fixed criteria so the user is not prompted, the form just gathers the required data and passes it as necessary. The last picture is my custom Switchboard form.

 

[Auntiejack56]

Hiya,
So much good advice here, so not sure if this little snippet helps, but one way to remove the mother of all if statements is:
Start your filter criteria with something that is always true, so that you don't have to do much parsing. I use "1=1".
A typical criteria statement for your filter can then be done as:
strWHERE = "1=1"
strWHERE = strWHERE & iif(len(me.Criteria1)=0,""," AND Field1 = '" me.Criteria1 & "'"
strWHERE = strWHERE & iif(len(me.Criteria2)=0,""," AND Field2 = '" me.Criteria2 & "'"

and so on, you could have a zillion criteria, all optional, and do it all in a zillion and one lines.

Jack

 

[bastanu]

@Dansam - look at the Split or InStr functions to extract the years from the joined data. Are there always only two years (joined and left)?
Here is a small example with one of the many ways of extracting that info.
Cheers,

 

[Pat Hartman]

Auntiejack,
Your solution has syntax errors.

 

[Auntiejack56]

Apologies:
strWHERE = "1=1"
strWHERE = strWHERE & IIf(Len(Me.Criteria1) = 0, "", " AND Field1 = '" & Me.Criteria1 & "'")
strWHERE = strWHERE & IIf(Len(Me.Criteria2) = 0, "", " AND Field2 = '" & Me.Criteria2 & "'")

Jack