If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Show all records
I have a form that has drop lists.
Users will use a list to choose an item, then the associated query will filter by that item. Two things: 1) How do I get the list to make the query show all records. The list is made up of records from another query. 2) How do I do number 1) when one form uses two lists called TimeStart and TimeEnd to tell the query to filter records between two dates. How do I make the date version show all records as well. Speaking on 1), currently when the form comes up, it has no records because the query hasn't received anything from the users to filter by. Once they choose something from the drop-down lists, the the query will refresh and show the filter records. How do I get it to show all the records. |
#2
|
|||
|
|||
Show all records
"Jacques Latoison" Jacques Latoison at hotmail dot com wrote in message
... I have a form that has drop lists. Users will use a list to choose an item, then the associated query will filter by that item. Two things: 1) How do I get the list to make the query show all records. The list is made up of records from another query. 2) How do I do number 1) when one form uses two lists called TimeStart and TimeEnd to tell the query to filter records between two dates. How do I make the date version show all records as well. Speaking on 1), currently when the form comes up, it has no records because the query hasn't received anything from the users to filter by. Once they choose something from the drop-down lists, the the query will refresh and show the filter records. How do I get it to show all the records. By "drop lists", do you mean combo boxes? In principle, your query should apply criteria like this: WHERE ((SomeField = Forms!YourForm!cboYourCombo) OR (Forms!YourForm!cboYourCombo Is Null)) -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#3
|
|||
|
|||
Show all records
A couple issues with that.
What's in the Combo box comes from another query or table. How would I add an option to clear the list? Yes, I did mean a combo box (the users call it a drop down list). I assume I can put the below in the criteria field, or should I make and expression. I assume the SomeField would by the field in question in the query? By "drop lists", do you mean combo boxes? In principle, your query should apply criteria like this: WHERE ((SomeField = Forms!YourForm!cboYourCombo) OR (Forms!YourForm!cboYourCombo Is Null)) -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#4
|
|||
|
|||
Show all records
I tried the below, but I get the below error when the query attempts to
execute: Undefined function "WHERE" in expression. By "drop lists", do you mean combo boxes? In principle, your query should apply criteria like this: WHERE ((SomeField = Forms!YourForm!cboYourCombo) OR (Forms!YourForm!cboYourCombo Is Null)) -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#5
|
|||
|
|||
Show all records
"Jacques Latoison" Jacques Latoison at hotmail dot com wrote in message
... A couple issues with that. What's in the Combo box comes from another query or table. How would I add an option to clear the list? You don't clear the list, you clear the combo. For unbound controls, if you delete the value displayed in the control, the control's value will be Null. Of course, yoou can also set up a RowSource query that includes a selection for "(unfiltered)", with a vaule of Null. But that's more complicated. Yes, I did mean a combo box (the users call it a drop down list). I assume I can put the below in the criteria field, or should I make and expression. You wouldn't put it directly in the Criteria cell of a query grid, because what I posted was a snippet of SQL. In a criteria cell of a query design grid, under the field to be filtered, you'd put something like: [Forms]![YourForm]![cboYourCombo] OR ([Forms]![YourForm]![cboYourCombo Is Null]) That should be entered all on one line. Access will be able to make sense of it. But if you're going to do this for multiple fields, do them all at once, before changing views. When you switch to some other view and then back to design view, Access will have rearranged things in the design grid and may have made it harder to make subsequent adjustments correctly. I assume the SomeField would by the field in question in the query? Right. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#6
|
|||
|
|||
Show all records
"Jacques Latoison" Jacques Latoison at hotmail dot com wrote in message
... I tried the below, but I get the below error when the query attempts to execute: Undefined function "WHERE" in expression. See my reply to your previous message. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#7
|
|||
|
|||
Show all records
Ok. I put this:
[Forms]![YourForm]![cboYourCombo] OR ([Forms]![YourForm]![cboYourCombo Is Null]) into the criteria field of the query and saved it. When I re-opened it in design view. It only showed this: [Forms]![YourForm]![cboYourCombo] and everything after the OR was turned into another field at the end of the columns in the query, with IS NULL in its criteria. Opening the form shows everything, initially. Choosing an item in the combo box now clears the forms lists completely. In other words, it now does the exact reverse - gives me everything or nothing. I have to admit, I thought it was funny. You don't clear the list, you clear the combo. For unbound controls, if you delete the value displayed in the control, the control's value will be Null. Of course, yoou can also set up a RowSource query that includes a selection for "(unfiltered)", with a vaule of Null. But that's more complicated. Yes, I did mean a combo box (the users call it a drop down list). I assume I can put the below in the criteria field, or should I make and expression. You wouldn't put it directly in the Criteria cell of a query grid, because what I posted was a snippet of SQL. In a criteria cell of a query design grid, under the field to be filtered, you'd put something like: [Forms]![YourForm]![cboYourCombo] OR ([Forms]![YourForm]![cboYourCombo Is Null]) That should be entered all on one line. Access will be able to make sense of it. But if you're going to do this for multiple fields, do them all at once, before changing views. When you switch to some other view and then back to design view, Access will have rearranged things in the design grid and may have made it harder to make subsequent adjustments correctly. I assume the SomeField would by the field in question in the query? Right. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#8
|
|||
|
|||
Show all records
"Jacques Latoison" Jacques Latoison at hotmail dot com wrote in message
... Ok. I put this: [Forms]![YourForm]![cboYourCombo] OR ([Forms]![YourForm]![cboYourCombo Is Null]) into the criteria field of the query and saved it. When I re-opened it in design view. It only showed this: [Forms]![YourForm]![cboYourCombo] and everything after the OR was turned into another field at the end of the columns in the query, with IS NULL in its criteria. Opening the form shows everything, initially. Choosing an item in the combo box now clears the forms lists completely. In other words, it now does the exact reverse - gives me everything or nothing. I have to admit, I thought it was funny. That implies that there is no record that matches the combo box. If you think that is not correct, please post the SQL view of the query, the actual name of the form, and the following properties of the combo box: Name Row Source Bound Column -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#9
|
|||
|
|||
Show all records
The SQL view shows what you typed, but the graphical portrays it as two
seperate fields. SELECT tblAccts.AccountID, tblAccts.AccountType, tblAccts.BankName, tblAccts.AccountNumber, tblAccts.BankRoutingNumber, tblTransactions.AccountID, tblTransactions.TransType, tblTransactions.CheckNu, tblTransactions.TransDate, tblTransactions.TransactionID, tblTransactions.DateCleared, tblTransactions.PayeeID, tblTransactions.TransDesc, tblTransactions.Withdrawal, tblTransactions.Deposit, tblTransactions.ClearedOnline, tblTransactions.ClearedOnStatement, tblTransactions.Taxable, tblTransactions.TransNote, tblTransactions.TransProb FROM tblAccts INNER JOIN tblTransactions ON tblAccts.AccountID = tblTransactions.AccountID WHERE (((tblAccts.AccountID)=[Forms]![frmLedgers]![lsbAccounts])) OR ((([Forms]![frmLedgers]![lsbAccounts]) Is Null)) ORDER BY tblTransactions.TransDate, tblTransactions.TransactionID; Form Name: frmLedgers Row Source: SELECT [qryAcctBalances].[AccountID], [qryAcctBalances].[AccountName], [qryAcctBalances].[BankName], [qryAcctBalances].[AccountNumber] FROM [qryAcctBalances] ORDER BY [AccountID]; Bound Column: 1 That implies that there is no record that matches the combo box. If you think that is not correct, please post the SQL view of the query, the actual name of the form, and the following properties of the combo box: Name Row Source Bound Column -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#10
|
|||
|
|||
Show all records
"Jacques Latoison" Jacques Latoison at hotmail dot com wrote in message
... The SQL view shows what you typed, but the graphical portrays it as two seperate fields. SELECT tblAccts.AccountID, tblAccts.AccountType, tblAccts.BankName, tblAccts.AccountNumber, tblAccts.BankRoutingNumber, tblTransactions.AccountID, tblTransactions.TransType, tblTransactions.CheckNu, tblTransactions.TransDate, tblTransactions.TransactionID, tblTransactions.DateCleared, tblTransactions.PayeeID, tblTransactions.TransDesc, tblTransactions.Withdrawal, tblTransactions.Deposit, tblTransactions.ClearedOnline, tblTransactions.ClearedOnStatement, tblTransactions.Taxable, tblTransactions.TransNote, tblTransactions.TransProb FROM tblAccts INNER JOIN tblTransactions ON tblAccts.AccountID = tblTransactions.AccountID WHERE (((tblAccts.AccountID)=[Forms]![frmLedgers]![lsbAccounts])) OR ((([Forms]![frmLedgers]![lsbAccounts]) Is Null)) ORDER BY tblTransactions.TransDate, tblTransactions.TransactionID; Form Name: frmLedgers Row Source: SELECT [qryAcctBalances].[AccountID], [qryAcctBalances].[AccountName], [qryAcctBalances].[BankName], [qryAcctBalances].[AccountNumber] FROM [qryAcctBalances] ORDER BY [AccountID]; Bound Column: 1 That looks correct to me, so I have to wonder if your form's recordsource query returns any records for the AccountID you selected in the combo box. Bear in mind that, because the query an inner join, no account will be returned that doesn't have at least one matching record in tblTransactions. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
|
Thread Tools | |
Display Modes | |
|
|