A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Show all records



 
 
Thread Tools Display Modes
  #1  
Old November 5th, 2009, 08:08 PM posted to microsoft.public.access.forms
Jacques Latoison[_2_]
external usenet poster
 
Posts: 10
Default 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  
Old November 5th, 2009, 08:23 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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  
Old November 5th, 2009, 09:19 PM posted to microsoft.public.access.forms
Jacques Latoison[_2_]
external usenet poster
 
Posts: 10
Default 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  
Old November 5th, 2009, 09:28 PM posted to microsoft.public.access.forms
Jacques Latoison[_2_]
external usenet poster
 
Posts: 10
Default 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  
Old November 5th, 2009, 09:34 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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  
Old November 5th, 2009, 09:35 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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  
Old November 5th, 2009, 09:56 PM posted to microsoft.public.access.forms
Jacques Latoison[_2_]
external usenet poster
 
Posts: 10
Default 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  
Old November 5th, 2009, 10:01 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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  
Old November 5th, 2009, 10:24 PM posted to microsoft.public.access.forms
Jacques Latoison[_2_]
external usenet poster
 
Posts: 10
Default 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  
Old November 5th, 2009, 10:31 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.