Filter Out Records From Reports (Access 2010)

Topic

New Reply

I have created a database for my railroad collection.

One of the fields contains the name of the railroad displayed on the side of each item in my inventory. Not all items have a railroad name.

I want to create a report that sorts – by railroad – all the items in my collection that have a railroad name. I do not want any of the items that do not have a railroad name show up in the report.

I’m sure I need to create some type of filter, but all the online suggestions tell me I need to use the “Filter” function. For some reason, the “Filter” function is greyed out in my report so I can’t use it.

This would be a permanent filter for a given report. I do not need to tell the database which records I want to include or exclude.

Anyone have a suggestion on how to accomplish this?

Thanks.

Reply | Quote

Replies

You could develop a query based upon the table in question and use that as the source for the report.
A general form of this query would be:
SELECT * FROM Tablename WHERE RailroadName is NOT Null

Reply | Quote

Sorry, my database skills are lacking.

I used the Query Wizard to create a query and came up with this:

How do I tell the report to use this query to filter out records that have a null in the Road_Name_Description field?

Or can I use the “Filter On Load” property on the Report Property Sheet to create the filter? The problem with that – I think – is I don’t have the Road_Name_Description as a field in the report. I only want to use it to exclude null records and then sort the remaining records according to the road name description.

Thanks,

Paul

Reply | Quote

Put this SQL in the record source of the report.
SELECT * FROM Tablename WHERE Road_Name_Description is NOT Null
Substitute tablename for your tablename.

Reply | Quote

I did what patt suggested and get this error:

When I click OK Access crashes.

Help!

Reply | Quote

your query above shows ” around the field name, take them out. As I said earlier use this SQL as the record source of the report.

What is the SQL you are using?

If you still have problems, why don’t you compact and zip the database.

Reply | Quote

Okay – here’s my syntax:

SELECT * FROM My Train Collection WHERE Group is NOT null

I saved the form and clicked on “Form View” which gives me the following error dialog box:

When I click on the OK, it gives me the same error message again.

Clicking OK one more time finally closes the error dialog box.

I’m not sure I know how to answer your question of “What is the SQL you are using?”. Sorry, my Access skills are lacking to be able to give you an answer to that question.

I deleted a bunch of records; deleted all the forms except the one we’re working on; compacted the database; zipped it; and attached it. Hope that will help.

Reply | Quote

Before I even have a look at you database, change the following:
SELECT * FROM My Train Collection WHERE Group is NOT null
to
SELECT * FROM [My Train Collection] WHERE [Group] is NOT null

Note the square brackets, it is not advisable to put spaces in object names.
Note. Group is also included in square brackets because some words confuse the query engine, like Group, because there is a clause Group By.

Reply | Quote

Thanks – that solved that problem.

I agree about not using spaces in object names. Unfortunately by the time I realized that was a bad thing, I was too far into the process to change it. I may eventually try to change those spaces to underscores.

Now if I could figure out why the group totals show the correct number of items, but the total cost is only of the last item in the group. I think the word total needs to be in there someplace but no matter where I inserted it into the equation, it gives me an error.

Attached the updated version with the brackets.

Reply | Quote

If I changed all the spaces to underscores would that negate the need for the braces?

Reply | Quote

Yes it would – but as you suspected, it is a sizeable amount of tedious work, and will temporarily break things till you have completed the renaming.

Reply | Quote

Just for interest sake I created a table with a space in the name, then a query, a form and a macro that used this table. I then renamed the table to not have eh space. The query and the form still worked – the source was changed to the new name. The macro didn’t work. I haven’t tested reports or VBA scripts. So renaming won’t necessarily be a problem if you only have queries or forms referring to the table, however testing of all objects that refer to the table would have to be corrected. It might not be as tedious as Wendell says, but it could still give you problems. This is in Access 2016.

Having said all that, I wouldn’t do it on an operational database, but i might on my own simple database. But then I wouldn’t use spaces!

Make a copy of the database and experiment.

Eliminate spare time: start programming PowerShell

Reply | Quote

I don’t have any macros in my database – just forms besides the main table and now queries for the forms.

I never significantly change things in a database. I always make a copy and work from that. If everything works great, then I make a backup of the backup and test, test and test. If everything works, I then delete the original database and one of the copies.

I’m using Access 2010 and have noticed that then I change something that is linked, I get a message asking if I want to apply the change to other objects (not sure of the exact words), so I click Yes and apparently it makes changes to everything that is linked to the item I’m changing.

I will definitely change the main table’s name to make it easier in the furure.

THanks.

Reply | Quote

access-mdb is correct as long as the Access Name AutoCorrect is turned on (File/Options/Current Database) – but I tend to keep that turned off as it has been suspected of being a source of database corruption, so I was envisioning a strictly manual effort. As noted, be sure you have a good backup of the database before using that feature – and it will make short work of renaming things. That also will help with renaming fields where you have spaces, or you have a potential reserved word.

Reply | Quote

To add to what Wendell said about the Access’s built in option to Perform name AutoCorrupt, aka, Perform name AutoCorrect.

It is fine to enable Track name AutoCorrect info. I NEVER use the Perform name AutoCorrect without first testing it on a copy. I have yet to see it do a 100% complete rename correctly. It will often rename things you did not want to change. Unless you do a FULL application test immediately after the AutoCorrect happened, you may not find the issues it created for days. This comes from expericne helping otehr cleanup the mess after an performing an AutoCorrupt (aka AutoCorrect).

Another liimit with AutoCorrect:

I always split every database from the start. All my table are linked. When I make a change to the back end tables, it will NOT AutoCorrect the front end database’s forms, reports, queries, ,etc.

Reply | Quote

I hope I don’t get in trouble for somewhat changing the topic of this thread. The people who have replied to this thread have been extremely helpful and have allowed my database to get closer to what I want.

For each group in a form, I have the following text box – the purpose of which is to sum the number of items in the group and also provide a total cost of the items in the group.

Currently the text box is as follows (using the “Sold by” form for this example):

=Sum([Qty]) & ” items purchased from ” & [Sold_By] & ” at a total cost of ” & Format([Purch_Total],”$ #,##0.00″)

Qty is correct; however, the Purch_Total is only including the Purch_Total from the first item – regardless of how many items are in the group.

I need to Purch_Total to be the total Purch_Total of all the items in the group.

I need to add the sum function to the Purch_Total part of the expression, but no matter where I place it, I get an error that says the expression is incorrect.

Reply | Quote

Went into the database and changed the name of the table that shows the fields and other in by replacing the spaces with underscores.

Then checked all my forms and report. The record source was automatically updated!

Reply | Quote

Went into the database and changed the name of the table that shows the fields and other in by replacing the spaces with underscores.

Then checked all my forms and report. The record source was automatically updated!

This is because you have Name Auto-Corrupt … oops Auto-Correct turned on.

When I first looked at your example I tried renamed the then box control name Qty to txtQty and it really missed things up! I have to delete the file and unzip it and start over so I could to test it.

I NEVER Lease the Name Auto-Correct turned on. I have see it destroy many databases. It just did it to me!

Reply | Quote

Have you tried this way?
=Sum([Qty]) & ” items purchased from ” & [Sold_By] & ” at a total cost of ” & Format(Sum([Purch_Total]),”$ #,##0.00″)

That’s the way I would go about it, though I don’t know the data source for your form. If the sum of the quantity works it would seem it should.

Reply | Quote

Tried doing it that way, but still getting an invalid expression.

I’ve attached a very slimed down version of my database so you can see who the form works.

Hope that helps.

Reply | Quote

This worked for me with your example:

Code:

=Sum([Qty]) & ” items purchased from ” & [Sold_By] & ” at a total cost of ” & Format(Sum([Purch_Total]),”Currency”)

Reply | Quote

Thanks HiTechCoach – that worked for me.

Now I’m displaying totals for each group.

Reply | Quote

You’re welcome.

Glad we could assist.

Reply | Quote

I want to say a special thanks to all the folks who have contributed answers to this thread.

Because of your answers, I now have a database that is performing like I had always expected:

1. Totals are correct for each group on a given form
2. Records that have no entry in some fields are not displayed/printed on a form.

Again, thanks to all!

Reply | Quote

You’re welcome.

Glad we could assist.

Reply | Quote

I have one additional question:

Can I use the ampersand ‘&’ as data in fields – primarily in the railroad names field?

I know it’s bad policy to use symbols as field names (have changed all of mine), but not sure about using symbols in data in fields.

Thanks,

Reply | Quote

With object names you need to avoid using characters that are considered reserved for the system to use for special things. I consider spaces to be part of this as the system uses a space to determine the start and end of object names, VBA commands, etc..

There are fine as data within literal string or as data stored within a field in a table or variable (not the variable or field’s name). I find it safe to use ASCII characters from 32 – 126. See: http://www.asciitable.com/

ASCII 38 = &. It is within the safe range above. I have many customers that use & in names, descriptions, comments, etc all the time.

Reply | Quote

When you say “object names” would the names of forms and reports be considered as object names? if so, then I guess – to be safe – I need to change the spaces in those to underscores.

Thanks

Reply | Quote

When you say “object names” would the names of forms and reports be considered as object names? if so, then I guess – to be safe – I need to change the spaces in those to underscores.

Thanks

That is correct.

What you see in the Navigation Pane as all objects.

Reply | Quote

While the ampersand ‘&’ is within the “safe” range, Access does not like it – nor any other punctuation marks such as a comma, slash – within a field name. It thinks those are separate items within a field.

If I use “Spokane & Seattle Railroad” it thinks thats 2 separate railroads.

The problem was solved by placing any items that use punctuation within quotes so “Seattle & Spokane” within quotes kept it as one railroad.

Learned something new today! Scary!

Reply | Quote

While the ampersand ‘&’ is within the “safe” range, Access does not like it – nor any other punctuation marks such as a comma, slash – within a field name. It thinks those are separate items within a field.

That is correct.

It is fine as data inside a field, but not the field’s name.

Reply | Quote

You are correct – the & symbol is used to perform text concatenation, so you should avoid that sort of thing, or put it in quotes as you discovered. Somewhere in the depths of Access Help, that is documented, but you can also find a list by searching for “Access special characters” – here’s a sample https://support.microsoft.com/en-us/help/826763/error-message-when-you-use-special-characters-in-access-databases

Reply | Quote

Since you folks have been so helpful here, I need help as follows:

Trying to create a report that contains all the information about each item in my collection on one page.

So far so good, but I’m up against an error/block that I can not get past.

I want to display the purchase price following – in parenthesis – the percent the purchase price was of the MSRP.

For example, if the MSRP is $50 and I paid $25 it would display as follows:

Purchase Price (% MSRP): $25.00 (50 %)

No matter how I construct the source control it gives me some type of error.

The data fields:

Purchase price: Defined as a currency field with 2 decimal places
Pct_MSRP_Purch_Price: Defined as a number (field size: single) and formatted as #.#%

The data is stored as (in my example): 50

I’m sure this is not the most efficient way of doing this (I should be doing a calculation to determine the percentage), but it’s set up so it works for me.

Help!

Reply | Quote

You are correct – storing a calculated value in a table is generally considered a bad practice and goes against one of the rules of database normalization. One of the reasons that is so is because of the possibility of either data entry errors, or changes to the MSRP or Purchase Price. All that aside, you should be able to simply place a text box on your report that displays the Pct_MSRP_Purch_Price value and set the format for it to display as a percentage.

Reply | Quote

It’s not the problem of being able to display the percentage, but trying to include in one box BOTH the pruchase price AND the percent of the MSRP.

I can easily display both separately, but when I try to combine them into one text box, that’s when I get the errors. I’m sure there’s a way to display both in one text box.

I’ve attached an abbreviated copy of the database.

On the report form “My Train Collection Catalog” there are four fields where I’m having a problem. Each one of them has a text label with % in it:

Purchase Price (% MSRP)
Best Value (% MSRP)
Greenburg Value (% MSRP):
MIT Value Price (% MSRP):

In each case, I want to display a currency amount followed in parenthesis with the percentage.

For example, for the Purchase Price (% MSRP) I want the following displayed:

Purchase Price (% MSRP): $12.34 (76 %)

I would think the control source for this would look like:

= [Purch_Price] & “(” & [Pct_MSRP_Purch_Price] & ” %)”

That gives me an error. I’ve tried replacing the ampersands with pluses but that still gives me an error.

Once I get the correct control source equation, I can then work on the formatting.

Thanks,

Reply | Quote

paulp575,

Since this new question is not related to the original topic of filtering you really should post a new question. This makes it much better for everyone to follow and learn. Searching posts for answers is also much easier with one topic/question per thread.

TIP For all online forums like this one: Adding an unrelated question to an existing thread is not seen by the forum software as new post with no replies. That means it has a high chance of not getting seen by people who answer posts by looking first for unanswered ones (0 replies). If I had not originally replied or subscribed to the topic, I would have missed your additional question.

It’s not the problem of being able to display the percentage, but trying to include in one box BOTH the pruchase price AND the percent of the MSRP.

I can easily display both separately, but when I try to combine them into one text box, that’s when I get the errors. I’m sure there’s a way to display both in one text box.

I’ve attached an abbreviated copy of the database.

On the report form “My Train Collection Catalog” there are four fields where I’m having a problem. Each one of them has a text label with % in it:

Purchase Price (% MSRP)
Best Value (% MSRP)
Greenburg Value (% MSRP):
MIT Value Price (% MSRP):

In each case, I want to display a currency amount followed in parenthesis with the percentage.

For example, for the Purchase Price (% MSRP) I want the following displayed:

Purchase Price (% MSRP): $12.34 (76 %)

I would think the control source for this would look like:

= [Purch_Price] & “(” & [Pct_MSRP_Purch_Price] & ” %)”

That gives me an error. I’ve tried replacing the ampersands with pluses but that still gives me an error.

Once I get the correct control source equation, I can then work on the formatting.

Thanks,

What you want to do is definitely a doable thing. I do similar things in my Accounting software.

I am not where I can looked at your attachment at this time.

I do have a few questions:

1) What is the error? Is it displaying #Name?

2) Have you changed the control name that is getting the error so it does not match any fields in the record source or in the calculation? This is a common issue.

ALERT: Turn off Auto Name correct before attempting to rename the control!

Reply | Quote

OK. Took a look and sure enough it was a control name issue.

Was able to fix it once Auto Name Corrupt (my database) was turned off.

FYI: I tired it with Auto Name Corrupt turned on. It also renamed something it shouldn’t have changed. This is exactly why I say it will cause database corruption! BEWARE of this BUG … oops … feature.

46813-My-Train-Collection-One-page-form_HiTechCoach

Reply | Quote

Thanks for reminding me not to change the topic of a thread. The reason I did this was because I had previously posted two other topics in the WS forums and didn’t get any responses. Once I posted here, I got helpful responses.

Exactly where do I check the status of AutoCorrupt? I see AutoCorrect several places, but not sure which AutoCorrect option I should uncheck.

Reply | Quote

Thanks for reminding me not to change the topic of a thread. [/QUOTE]

I would never recommend changing the topic of a thread. Even it it was possible to do it. Except maybe to fix a typo.

The reason I did this was because I had previously posted two other topics in the WS forums and didn’t get any responses. Once I posted here, I got helpful responses.

When did you posted other topics on this site in the forum Databases?

Exactly where do I check the status of AutoCorrupt? I see AutoCorrect several places, but not sure which AutoCorrect option I should uncheck.

Uncheck the top Auto Name Correct of the three options to turn them all off.

Reply | Quote

I am going create a new thread on how to get a valid expression/equation in a form’s text box.

Sorry for taking this thread in a different direction.

Reply | Quote

I am going create a new thread on how to get a valid expression/equation in a form’s text box.

Sorry for taking this thread in a different direction.

Did you see the attachment I posted that had it working?

The issue you had was the control name having the same name as a field and caused a circular reference, like the error in Excel, that was confusing Access.

I changed the name of every control I place on a form by adding prefix. This solves the issue.

Reply | Quote

Solved!

Thanks to all who have helped!

I just created another related post here:

http://windowssecrets.com/forums/showthread//179656-If-Then-Statement-in-Report-Text-Box-(Access-2010)

Reply | Quote

You’re welcome. Glad we could assist.

Reply | Quote