Show all records

[Jacques Latoison]

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.

 

[Dirk Goldgar]

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))

 

[Jacques Latoison]

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?

 

[Jacques Latoison]

I tried the below, but I get the below error when the query attempts to
execute:

Undefined function "WHERE" in expression.

 

[Dirk Goldgar]

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]

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.

 

[Jacques Latoison]

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)

 

[Dirk Goldgar]

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

 

[Jacques Latoison]

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

 

[Dirk Goldgar]

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.

 

[Jacques Latoison]

I hadn't changed anything in the form.
It only changed after the query change.
The combo box still works, and returns the correct values.

 

[Dirk Goldgar]

I hadn't changed anything in the form.
It only changed after the query change.
The combo box still works, and returns the correct values.

Something's obviously wrong, but I don't see what it is. Do you want to
send me your database so I can have a look at it? If it's small, just
compact it, zip it up and e-mail it to me. If it's large, create a cut-down
copy containing only the elements necessary to demonstrate the problem,
compact and zip that and send it to me. I'll have a look at it, time
permitting. You can send it to the address derived by removing NO SPAM and
".invalid" from the reply address of this message. If that address isn't
visible to you, you can get my address from my web site, which is listed in
my sig. Do *not* post my real address in the newsgroup -- I don't want to
be buried in spam and viruses.

 

[Jacques Latoison]

I appreciate the offer.
Let me look into that.

Something's obviously wrong, but I don't see what it is. Do you want to
send me your database so I can have a look at it? If it's small, just
compact it, zip it up and e-mail it to me. If it's large, create a
cut-down copy containing only the elements necessary to demonstrate the
problem, compact and zip that and send it to me. I'll have a look at it,
time permitting. You can send it to the address derived by removing NO
SPAM and ".invalid" from the reply address of this message. If that
address isn't visible to you, you can get my address from my web site,
which is listed in my sig. Do *not* post my real address in the
newsgroup -- I don't want to be buried in spam and viruses.

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

(please reply to the newsgroup)