Query Form

I have exported some data from the local MLS system into an Access database 
and want to run some statistics on the sales information, using a number of 
different queries which will feed graphs in Excel, etc...

I have a form set up for choosing the data which include the city, the 
development, what kind of property it is (condo, single family residence, 
etc.)  

The problem I have is why no selection is made.  For example, the query 
looks for a city selection from a drop-down box;  if I choose a city, the 
query runs perfectly.  If I do not choose a city - leaving it blank - the 
query produces no results.  I want "no city selected" or "all cities" to 
produce a query that has all cities in it.

I've tried various 'iif' statements in the criteria - and nothing has 
worked.  I've tried checking the drop-down for null and for ""   I've created 
a text box with a value equal to the dropdown and tried to query on that - 
and that has not worked.  I even tried doing that with all the cities typed 
into it, and that didn't work either.  

I have seven different feilds that might or might not hold criteria.  (City, 
development, property type, land type, sales date, size of house, size of 
lot.)  There must be a way to hand a situation where no selection is made:  
it would be  real pain to have to code SQL for every possible combination of 
these seven inputs!

Ideas would be highly appreciated!


0
Utf
3/8/2010 6:20:01 PM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
1014 Views

Similar Articles

[PageSpeed] 33

"dave_b" <daveb@discussions.microsoft.com> wrote in message 
news:201DA1BC-370B-4093-95B8-4D9B72694AB6@microsoft.com...
>I have exported some data from the local MLS system into an Access database
> and want to run some statistics on the sales information, using a number 
> of
> different queries which will feed graphs in Excel, etc...
>
> I have a form set up for choosing the data which include the city, the
> development, what kind of property it is (condo, single family residence,
> etc.)
>
> The problem I have is why no selection is made.  For example, the query
> looks for a city selection from a drop-down box;  if I choose a city, the
> query runs perfectly.  If I do not choose a city - leaving it blank - the
> query produces no results.  I want "no city selected" or "all cities" to
> produce a query that has all cities in it.
>
> I've tried various 'iif' statements in the criteria - and nothing has
> worked.  I've tried checking the drop-down for null and for ""   I've 
> created
> a text box with a value equal to the dropdown and tried to query on that -
> and that has not worked.  I even tried doing that with all the cities 
> typed
> into it, and that didn't work either.
>
> I have seven different feilds that might or might not hold criteria. 
> (City,
> development, property type, land type, sales date, size of house, size of
> lot.)  There must be a way to hand a situation where no selection is made:
> it would be  real pain to have to code SQL for every possible combination 
> of
> these seven inputs!


In principle, you can code your query SQL like this:

------ start of example SQL ------
    SELECT ...
    WHERE
        ((City = Forms!YourForm!cboCity)
        OR (Forms!YourForm!cboCity Is Null))
    AND
        ((PropertyType = Forms!YourForm!cboPropertyType)
        OR (Forms!YourForm!cboPropertyType Is Null))
    ...
------ end of example SQL ------

It is often simpler, if you will be opening a form or report from the form 
where the criteria controls reside, to build a filter string on the fly and 
apply it as the Where-Condition argument for the DoCmd.OpenForm or 
DoCmd.OpenReport call.  When taking that approach, the code looks something 
like this:

'------ start of example code ------

    Dim strFilter As String

    If Not IsNull(Me.cboCity) Then
        strFilter = strFilter & " AND (" & _
            "City = "" & Me.cboCity & """)"
    End If

    If Not IsNull(Me.cboPropertyType) Then
        strFilter = strFilter & " AND (" & _
            "PropertyType = "" & Me.cboPropertyType& """)"
    End If

    ' ... other criteria appended as needed ...

    If Len(strFilter) > 0 Then
        ' Trim off leading " AND ".
        strFilter = Mid$(strFilter, 6)
    End If

    DoCmd.OpenReport "rptProperties", WhereCondition:=strFilter

'------ end of example code ------

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

0
Dirk
3/8/2010 6:55:03 PM
Reply:

Similar Artilces:

Spreadsheet on form
For Spreadsheet control on a userform, are the followings possible how?: -defining scrolling area -validating a cell -adding comment to a cell Please guide me -- Message posted from http://www.ExcelForum.com ...

Show only queries w/table source combo box
Is it possible to display only query names in a combo box where the queries source contains the table [Mstr List]? Thank you very much for any help. On Thu, 3 Jun 2010 05:13:46 -0700, Billy B <BillyB@discussions.microsoft.com> wrote: To do that you would have to write some VBA code to iterate over the Querydefs collection, inspect the SQL property of each Querydef object, and add the ones you like to the dropdown. Here is an off-the-cuff example. You would put this code in the Form_Load event: dim qd as dao.querydef for each qd in currentdb.querydefs if instr(qd.sql, &q...

Form parameter Query
I'm hoping someone can help me with this . I'm trying to get my query to use a form to get the query parameters. When I run the query, it displays the text "[Forms]![frmEE]![cboEE]" as a prompt rather than displaying the form where the user can make a selection. I'm using the build wizard to construct the following query: SELECT Employees.* FROM Employees WHERE (((Employees.LastName)=[Forms]![frmEE]![cboEE])); -- Any and all contributions are greatly appreciated ... Best Regards Thanks Karl. You've been a big help. Have a good one. "KARL DEWEY"...

Update query
They scare me! I do have a copy of the db. Here's what I'm trying to say: Find Contacts who are Board Members and make them Gala Members as well. Here's my query to find Board Members: SELECT Contacts.LastName, Contacts.ContactID FROM Contacts INNER JOIN (Groups INNER JOIN GroupMembers ON Groups.GroupID = GroupMembers.GroupID) ON Contacts.ContactID = GroupMembers.ContactID WHERE (((Groups.GroupName)="Board of Directors") AND ((GroupMembers.GMemberEnd) Is Null)); The Groups.GroupID for "Gala" is 12. GroupMembers contains: GroupMemberID, GroupID, ContactID,G...

Queries & Reports
QUESTION . First I will enter info into TBL Radio Receipt, from there I want the information to jump to TBL Radio Info and prompt me to complete this table. Then it will jump to either TBL New Radio Information OR TBL Repair Information. This will need to be completed. How does this happen, I mean-- How do I create the relationships to reflect this path? PS How do I attach a pic of my problem? Jeannette Cunningham already posted a response to your question back on 12/21, but perhaps you didn't see/understand it so we can try again. Your question is a little hard to understand, so...

Hopefully a simple combobox SQL query
Hi there. I've got a series of comboboxes: Manufacturer Category Sub-Category What I'm doing is making each box populate based on what is input into the one above. This I'm fine with. What happens when you select is that the top line of the combobox is empty and then the Select Distinct results appear below. What I'm would like to happen is that the box has a default of <all> in it and then the select results come below when you click on the drop down. I've tried setting the box default to <all> but the boxes still have a blank top line. Can anyone assist?...

Parameter Query Using Combo Box
There are no recorsd being returned when i run my parameter query. SELECT TrackingID, DocumentNumber,DocumentType, RequestDate,DueDate, ProgramID, AssignedTo, Author, StatusDate,Subject,Notes FROM tblDocTracking WHERE (((tblDocTracking.ProgramID)=[forms]![frmProgramSelect]![ProgramID])); I have frmProgramSelect open. I am not sure what i am doing wrong. Can you help? Be sure the control ProgramID is out of focus, to be sure that the value you keyed in has been "updated". If the control is not updated (committed), its last committed value, probably a null, will ...

Need to determine length of field for query
I need to use a query that will show me in the address field all those records where the address is longer than 35 characters. I want to be able to shorten those to fit in a specific label. I can then use this syntax to also abbreviate city names that are too long for the label -- Barry On Sat, 20 Feb 2010 13:55:01 -0800, Barry <barry@discussions.microsoft.com> wrote: >I need to use a query that will show me in the address field all those >records where the address is longer than 35 characters. I want to be able to >shorten those to fit in a specific label...

Form w/ combo box from query based on lookup field not alphabetizi
I have a query that includes a lookup field (combo box). I included an ORDER BY clause in the query for the lookup field. The field sorts the lookup list alphabetically and in the query. I'm happy with that. I created a main form w/ a linked form based on the query. The linked form contains the lookup field. However, the lookup table is still not alphabetised. Why? Please help in alphabetising the field on the linked form. All help is prematurely appreciated. -- Thanks, Karen Table sorting is VERY strange - I think Bill Gates understands it and no one else without a MVP...

Please Help! How to enable cell overwrite AFTER the query is created
How do I check and/or change to overwrite the target cell? I've already created the query and I can't find where to edit the overwrite option (Excel 2002) Select a cell in the external data range. Choose Data>Import External Data> Data Range Properties Change the setting for 'If the number of rows in the data range changes' Click OK Kipp wrote: > How do I check and/or change to overwrite the target cell? I've already created the query and I can't find where to edit the overwrite option (Excel 2002) -- Debra Dalgleish Excel FAQ, Tips & Book List http://...

Dynamic Headings in a crosstab query
I have a sales report containing [PartNumber], [TransactionDate], [Qty], [CostCenter], [UnitofMeasure], [TransactionAmount] with data from the last 12 months. The table is linked to a report created in Crystal Report. Creating a crosstab to sum [Qty] by month[TransactionDate] is no problem. [CostCenter], [UnitofMeasure], [TransactionAmount] become Row Headings. But I would like the column heading to read Jul '06 (This Month -12), Aug '06(This Month -11), Sep '06(This Month -10), ... Jun '07(This Month -1). ). And change dynamically when the report changes. Is this possib...

How to Restrict Users to view/go to Query Design/Sql mode in MDE/M
I want to secure my Access MDE file so that My users can not go to Design / SQL view of the query. Currently after opening query through command button provided by me on form , any user can press right click button & Go to design/Sql view of the query & fiddle with it. I want to keep enable right click mouse button but disable "Query Design" option. Like for forms & reports design option is not available after creation of MDE file. I am not in a position to use Security & group wizard provided by Access. Please Guide. Thanks . Girish Disabled the default short...

how to make an order form for a Flower sale?
we have a local club that sells flowers each year. I need to make the order form that will allow me to order several various kinds of flowers for several different people. When you open Excel, go to the standard toolbar and hit File -> New. If using Excel 2003, you might get options that say "on my computer" and "Templates from Office Online". You can find a Sales Invoice template under the "on my computer" which can be easily changed to a sales order template. In other versions of Excel, look within the tabs containing templates when you choose File ...

Incremental values in Orders form
I had a Orders form for me to key in the data for the particular orders into my order table. I had a field call the "InvoiceID". The order form allows me to key in the InvoiceID. Now i want to have a button to allows me to increase the values in the InvoiceID by 1. But each time i go for a new order, it is a new record, all the values in the order form is "null". How do i retrieve the InvoiceID value from the previous record. -- Message posted via http://www.accessmonster.com Use the BeforeInsert event procedure of the form to look up the highest number used so far, a...

Pop up form meeting text box requierment
I want certain forms to pop up when certain words are entered into a sub form control text box. Example: Where the "Employee History" form has in the "situation" contorl text box the word from the list box "Accident", I want the "300 Form" to pop up and when that form is completed I want the 301 Form to pop up. All three table are linked in the Employee history table at the actionID control. Thanks in advance. You're going to have to trap the changes to the control(s) in question, evaluate them and then act accordingly. probably a case state...

COUNTIF formula query
Hi, I have a column of approx 200 text strings that were originally taken from a html page. I'm trying to determine how many of these strings are in bold format. I thought the easiest way would be to do a countif on the <b> tag to see how many times it appears. My data is in a1:a199, so in a202 I put in the formula =COUNTIF(A1:A199,"<b>") - however this returns the result 199, which is incorrect. Using Find from the tools option, the result should be 62. Can anyone tell me if it's possible to use COUNTIF to check if a cell *contains* a certain value, as...

Query the last 5 dates
How do I query the last 5 dates in a query even if dates in between are missing. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201003/1 On Mon, 22 Mar 2010 23:01:17 GMT, "turks67 via AccessMonster.com" <u48755@uwe> wrote: >How do I query the last 5 dates in a query even if dates in between are >missing. Use the TOP VALUES property of the query, and sort by descending date. That said... you're assuming that we have a lot more knowledge of the structure of your table and your query than we can ...

Help with a Query 02-29-08
Let me paraphrase a query I want to do. First, I have a table called 'Components' This table has these fields: RecNo (Primary key) FileRec (Foreign key to Files Table) StandardRec (Foreign key to StandardTypes table) RefDes (String) AssemRec (Self-join to a RecNo) Here is my paraphrase: UPDATE Components SET AssemRec = the primary key in same table where in the refdes concatenation '<$-' + Refdes + '>' = RefDes AND the FileRec's are the same. So as an example, here is sample data. RecNo = 5 FileRec = 10 StandardRec = 17 RefDes = <$-156AF> AssemRec ...

User Form Appears Blank
I have created a user form that opens automaticaly when my workbook opens, but only a blank screen shows up. How can i fix this? Sub callUF() UserForm1.Show End Sub Is there anyway to have the User Form show up as a Sheet in the Work Book? Use the workbook open event. From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the workbook name and click on + to expand it. Within that you should see the following VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) This Workbook ...

Select Case and Tabular Forms
It's a little complicated to explain what I want my end result to be, but right now, I have a tabular form with a field that I want to use to update an unbound text box, I've named Ability. In doing some tests to see if this would work, I tried writing a really basic sequence: Select Case [Ability ID] Case 1 Ability = 1 Case 2 Ability = 2 Case 3 Ability = 3 Case 4 Ability = 4 Case 5 Ability = 5 Case 6 Ability = 6 End Select The problem is, when I run this Select...Case statement, it only pulls the number from the...

Populate fields in forms with data from CRM 3.0?
Hi all. Is it possible to gather data from CRM 3.0 ("Accounts" in our case) and use it to populate a drop down field in a SharePoint form? We're running MOSS 2007 Enterprise so BDC is an option if need be. I haven't checked out the downloadable web parts for CRM connectivity yet but as far as I can tell they're not for this kind of functionality. Hi Martin, Create a webservice on top of Microsoft CRM. And use a JavaScript to get the values, and populate the drop down lists. A point to start with is the Microsoft CRM 3.0 SDK (Chapter Client Programming Guide, Clie...

query by means of a form to be filled in
Hi ! I'm using MS Access 2002/2003. I have a table A with fields F1, ..., FN, and additional fields. I want to show in a result form all rows of A with given values v1, ..., vN for the fields F1, ..., FN. But I want the values to be entered in controls in another entry form. The controls are of the types that are produced automatically by the MS Access forms assistent for a query of A. What solution do you suggest ? Thank you very much. Peter On Tue, 21 Aug 2007 22:36:13 +0200, "pschrader" <none@none.de> wrote: >Hi ! > >I'm using MS Access 2002/2003....

Using forms in a shared powerpoint
Hi, I have a feedback form in a Powerpoint which stores the inputted data in a text file. It works perfectly and does what I want it to. EXCEPT I want to use the same form with a group of users and whenever I try this an error occurs. When they press Submit when someone else has it open a run-time error occurs. Does anyone know if I can get around this or should just give up! This is the kind of thing I am doing: http://cws.internet.com/article/4529-.htm Thanks Maybe try using FreeFile instead od #1? Not sure that even then you can have it open on more that one PC at a ...

Access 2007 Question
I have an access database that has a bunch of email addresses in it. I want to strip it down to just the username. What criteria will I use in my update query to get the desired results. I want to take joe@bob.com and strip the @bob.com off to make it just joe. Any thoughts? what I would simply do but may be tricky for you is take the data from that table and paste it into excel. once in excel you can use formulas to strip down the data in the cell. then you can take the data from the excel table/spread- sheet back into that access table. or link access to that excel...

How to set up relationship query for minerals database
Good morning everyone, I'm using Access 2003 and am working on a minerals database. The main table has a field COM_MIN (common minerals) which shows the minerals occurring at a particular location e.g. ID Lat Long Com_Min 123 -27.5 148.66 scheelite, gypsum, galena My next table has individual minerals and their properties ID MINERAL Density Streak etc 257 Scheelite 7.2 Gray 1286 Gypsum 2.6 White 44 Galena 6.7 ...