Enter Parameter Value (1 Viewer)

[Zippyfrog]

Currently I have a report that is based off of a query, and the query has a field that asks for a parameter value when it is run. I know why it comes up, but I don't want to see it come up. Is there a code that I can use that will automatically fill in a null value when box pops up asking for a parameter value?

 

[georgedwilkinson]

Create a different, similar, query with the value already provided.

 

[Zippyfrog]

Does making an extra query for each situation greatly increase the size of the database?

 

[georgedwilkinson]

Does making an extra query for each situation greatly increase the size of the database?

Do you have a space problem?

You should make a query if you need it.

 

[boblarson]

A BETTER way is to supply your report with a GENERIC query (1). Then, OPEN it with criteria if you want it and none if you don't.

To open with criteria, use:

DoCmd.OpenReport "YourReportName", acViewPreview, , "[YourFieldToLimitBy]=" & Me!ControlOnFormYouAreOpeningFrom

You can use multiple criteria (not just one like I have shown here) and then you have to remember to use single quotes for text ( "[YourFieldToLimitBy] = '" & Me!Control & "'" ) or if dates ("[YourFieldToLimitBy] =#" & Me!Control & "#"

 

[boblarson]

In fact, here's a sample I have on my site to have a generic report and open with different criteria and different criteria types:

http://downloads.btabdevelopment.com/Samples/reports/Sample-GenericReport.zip

 

[Zippyfrog]

Thanks. That sample really helps. Conceptually, that is exactly what I want to have happen - different sets of criteria for the query to be filtered by. Currently I have a different query for each scenario. I will try that this weekend and see how it works.

 

[Zippyfrog]

I tried it this morning, and it works beautifully! That little piece of code is going to really change the way my database is setup and will make it a lot more simple. Thanks again!

 

[boblarson]

I tried it this morning, and it works beautifully! That little piece of code is going to really change the way my database is setup and will make it a lot more simple. Thanks again!

 

[Zippyfrog]

I have one other question about what you did. Is there a way for me to do an "and" statement so that the query is then filtered by two fields? Something like this:

Dim stDocName As String

stDocName = "rptTrackRecords"
DoCmd.OpenReport stDocName, acPreview, , "[Gender]='" & Me.ComboGender & "'" And "[Type]='" & Me.ComboRaceType & "'"

Basically I want to try an "And" statement, and the Gender and Type filtering work separately, but when I combine, I get a type mismatch. Can I not do what I am trying?

 

[boblarson]

You just had two too many double quotes. The And and the [Type]=' goes in the double quotes.

DoCmd.OpenReport stDocName, acPreview, , "[Gender]='" & Me.ComboGender & "' And [Type]='" & Me.ComboRaceType & "'"

 

[Zippyfrog]

Thanks once again!

 

[Zippyfrog]

I have one more question about this setup - how do I put in a criteria so that only the records that don't have null values show up? I have a field called final time, and if that value is null, I don't want it to show up.

 

[datAdrenaline]

[Final Time] Is Not Null