Re: [MS_AccessPros] Parameter Query Listbox

 

Thanks Crystal. I'll try and digest this and see if I can get it to work.

--- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@...> wrote:
>
> 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]
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (5)

Recent Activity:

• New Members 5

Visit Your Group

MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___