ACCESS FEED: Re: [MS_AccessPros] Parameter Query Listbox

Hi John,

Create a Report Filter (WhereCondition clause of OpenReport)
~~~

design a report to show the information (for ALL records)

set up a form to be used as a Report Menu with, for example:
comboboxes to choose specific criteria
a textbox or combo for Date1
a textbox or combo for Date2
a command button to launch the report

It is best to build a filter string for the report (as opposed to embedding a parameter in a query)--in this way, you can use textboxes, comboboxes, listboxes, etc to make it easier for the user to choose criteria and you can ignore criteria when it has not been specified...

~~~
here is an example that tests criteria and builds a filter string to use as a parameter in OpenReport for the WHERECONDITION clause

DoCmd.OpenReport reportname[, View][, FilterName][, WhereCondition]

ie:

DoCmd.OpenReport "MyReport", acViewPreview,, "city IN ('Ct','JHB')"
~~~
********************************
********************************

I like to avoid dynamic criteria in queries ... and actually almost anywhere -- code writes a lot of SQL <smile>

This is used a lot:

CODE
      DoCmd.OpenReport "ReportName", acPreview, , varCriteria

WHERE
varCriteria is how to limit the records. For instance -->
"CustomerID = " & Me.CustomerID
OR
"PartID = " & me.PartID & " AND OrderDate =#" & me.OrderDate & "#"

varCriteria is the WHERE clause of an SQL statement without the word "where"

another advantage of using controls on forms is that you can ignore the criteria is the user didn't fill anything out because you can test for that in your code.

~~~
assuming you are in the code behind the ReportMenu form... here is a general case:

CODE
'~~~~~~~~~~~~~~~~~~~~~~~~~~
   'tell Access you are going to create a variable
   dim varFilter as variant

   'initialize the variable
   varFilter = null

   'substitute YOUR controlname in here after "Me."
   'we are testing to see if it is filled out
   'if it is, we are going to make varFilter hold the criteria
   If not IsNull(me.text_controlname ) Then
      varFilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
   end if

   'test the next control
   If not IsNull(me.date_controlname ) Then
      'if we alread have criteria, add AND to specify that and more
      varFilter = (varFilter + " AND ") _
        & "[DateFieldname]= #" & me.controlname_for_date & "#"
   end if

   'test the next control
   If not IsNull(me.numeric_controlname ) Then
      varFilter = (varFilter + " AND ") _
        & "[NumericFieldname]= " & me.controlname_for_number
   end if

   if not IsNull(varFilter) then
       DoCmd.OpenReport "ReportName", acViewPreview, , varFilter
   else
       DoCmd.OpenReport "ReportName", acViewPreview
   endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'SPACE UNDERSCORE at the end of a line means that the statement is continued on the next line
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have used:

TextFieldname to show how text needs to be delimited - with single quote marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a filter control is filled out.

If it is, we are going to see if we first need to add AND (if the filter string already says something)
Then we are going to add the criteria for that filter
varFilter = (varFilter + " AND ") & ... ' some other criteria

make sure that the referenced fields are in the underlying RecordSet for the report -- and it is sometimes necessary for the fields to be in controls on the report (the Visible property can be No)

For a Date Range, you would do:

CODE
   If not IsNull(me.date1_controlname ) Then
      varFilter = (varFilter + " AND ") _
        & "[DateFieldname]>= #" & me.controlname_for_date1 & "#"
   end if

   If not IsNull(me.date2_controlname ) Then
      varFilter = (varFilter + " AND ") _
        & "[DateFieldname] <= #" & me.controlname_for_date2 & "#"
   end if

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you are not using the American Date Format, then you may wish to use the ISO 8601 representation of the date:

CODE
Format(me.date_controlname, "\#yyyy\-mm\-dd\#")

instead of
"#" & me.date_controlname & "#"

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

CODE
'~~~~~~~~~~~~~~~~~~
   if len(trim(nz(Me.Filter,""))) > 0 then
      me.Label_Criteria.Caption = Me.Filter
      me.Label_Criteria.Visible = true
   else
      me.Label_Criteria.Visible = false
   end if
'~~~~~~~~~~~~~~~~~~

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Difference between + and &
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

& and + are both Operators

The standard Concatenation Operator is ampersand (&). If a term that is concatenated is Null (has no data; unknown), all terms will display if you use ampersand.

The Addition Operator is the plus sign (+) … but, even if one of the terms has a value, the result will be Null if any term is Null (kind of like multiplying by 0). As in math, what is enclosed in parentheses will be evaluated first.

Null + "anything" = Null
Null & "anything = "anything"

"something " + "anything" = "something anything"
"something " & "anything" = "something anything"
no difference because both of the terms have a value

Null + "" = Null
Null & "" = ""

(Null + " ") & "Lastname" = "Lastname"
(Null & " ") & "Lastname" = " Lastname"
in the second case, the parentheses do not make a difference, each term is concatenated -- and note the space in the result before Lastname

Do you see the difference between using + and using & ? For instance, if you want to add a space between first and last name but you are not sure that first name will be filled out, you can do this:

(Firstname + " ") & Lastname

What is in the parentheses is evaluated first -- then it is concatenated to what comes next

You might also want to do this:

(Firstname + " ") & (Middlename + " ") & Lastname

Combining + and & in an expression gives you a way to make the result look right without having to test if something is not filled out.

What if firstname is filled but nothing else? There will be a space at the end. Usually, this is not a problem but if you want to chop it off, you can wrap the whole expression in the Trim function, which truncates leading and trailing spaces.

Trim((Firstname + " ") & (Middlename + " ") & Lastname)

for more on delimiters and other fundamental topics, read Access Basics*. All your fields come from tblJob. Your data structure needs to be normalized. Especially focus on the chapters on Normalization and Relationships in Access Basics

*Access Basics by Crystal
http://www.AccessMVP.com/strive4peace

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

also, you are using reserved words. TYPE is a reserved word; do not use it as a name of any of your objects (fields, tables, controls, aliases, etc)

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

Warm Regards,
Crystal

*
   (: have an awesome day :)
*

________________________________
From: yshopper
Subject: Re: [MS_AccessPros] Parameter Query Listbox

Report will work as well. How would I go about doing that?

Thanks

--- In Crystal <strive4peace.> wrote:
>
> Hi John,
>
> basing a form on a parameter query is not a good idea as forms are primarily used to change data. See the code in the thread, "Filter a continuous form", which has been discussed today. It shows how to set a form filter based on unbound criteria controls that the user can specify
>
>
> Are you wanting to display information only? If so, a Report is best
>
> Warm Regards,
> Crystal
>
> *
>    (: have an awesome day :)
> *
>
>
>
> ________________________________
> From: yshopper
>
> I need to create a Form based on a Parameter query. The Parameter can be selected from multiple choices. Table tbl_Items has a Binding Status field that is a lookup from Table tbl_BindingStatus.
>
> I tried basing the query on just the Items table, but that did not work. I alos tried a query using both tables, but don't think I entering the Criteria correctly.
>
> I did find a way to do this w/o a Listbox, but would like to get this way to work.
>
> Any help or tips are appreciated.
>
> Thanks,
> John
>

[Non-text portions of this message have been removed]