[mosher]

I have developed an MS Access DB for tracking jobs. I have included a Checkbox to be "checked" when a job is closed.

Here is my dilemna:
1. I want to run reports without the closed job showing up.


2. I want the input form to de-list the item after it is closed.


Thanks for any and all help,

[downwitchyobadself]

Greetings and welcome, mosher.

You have a couple of choices here. In part it depends on how you're printing your report, and what kind of form you're talking about.

Are you using macros? I assume you're not using code. Or are you just opening the report from the db window?

One way or the other, you can either:

1. Create a query, in which you set the closed field (which I'll now call IsClosed for the ease of discussion) criteria to false, or

2. Send through the macro or write into the report property sheet Filter "IsClosed = False"

Either one of these will show you only the un-checked jobs.

Similarly, you can put a filter on the form, or base the form on an (inputable) query. But remember: if your closed jobs don't show up in the form any more, you'll have to go back into the table to change your mind .

If you need help doing any of these things, post back with some specifics (a field and a table name or two, a description of your form) and we'll help you out. BOL.

[mosher]

Thank you for the quick reply. I tried all of your suggestions but not sure how to perform a couple of them.
For example; how do I create a query, in which I set the "closed job" field to false?

2. I tried to make a report Filter "JobClosed = False"
but it did not work?

3. Not sure how to perform this one at all: You can put a filter on the form, or base the form on an (inputable) query. But remember: if your closed jobs don't show up in the form any more, you'll have to go back into the table to change your mind .

Further suggestions are appreciated - this must be an easy process but I'm new to Access and there is no book here at work.

[Anne Troy]

Go to the Query tab, hit the New button and choose Design view.

Choose the table from which you want to base your query.

You'll get a box with the table's fields (column headings) in it. There's also one with an asterisk (*). Click and drag the asterisk down into the first column. Now, pick the one that says "Closed Job" and drag that into the second column. Remove the checkmark that says "Show". On the criteria line, type =FALSE and hit enter. Go up to the toolbar and hit the exclamation point (!) button to run the query. If the box is checkmarked, it is a closed job, and therefore is TRUE. You don't want to see the closed jobs, so you choose FALSE (unchecked).

You can then save the query.

[downwitchyobadself]

Dreamy is 422% right. Since you're new to Access, we'll try to take it easy. If you'll permit me, I'm going to assume that you're new to databases as well. So, as far as terminology goes, there may be some clearing up to do. A table, I think, you understand: just holds a bunch of data. Everything in one record is related to everything else; in a customer table, for example, Access "knows" that the first name, the last name, and the phone number belong together. Well, queries take advantage of just that fact. I think "query" is a fairly lousy word for it, frankly, because it implies the question, whereas what's really important is the response.

A query in its simplest form is really just a different view of a table. Forget about databases; say you had a collection of fruit. Apples, oranges, etc. And different colors. Say you wanted to know how many red bananas you had. That would imply two kinds of criteria: fruit name = "banana" and color = "red".

In the query design window (also known as the QBE window, for "Query By Example"), you have two areas: the one up top, greyish usually, shows you which tables you're using, and the one below, divided into columns, shows you which fields will be included in your query. When you create a query (the "new" button, followed by "design view", of which D.B. spoke), Access prompts you to add the table(s) on which you want to base it. (To extend our previous example, if you had a collection of fruit, and a collection of kites, you might want to see all the reds in both; that would be two tables, so to speak, but more on that another time).

Adding the star, which you can do with click-and-drag, or just by double-clicking on it, is Access/SQL's (SQL = "Select query language", the word-form of the aforementioned QBE) form of adding ALL the fields in the table at once. You could alternatively just show certain fields; add each one individually by click-and-drag or double-clicking, and you'll see what I mean.

Below the field names which take up the columns, you'll see "Table", "Sort", "Show" and "Criteria". Table is the name of the table from which the field comes, easy enough. Sort is used if you want to see your records in a certain order, such as alphabetical for text fields, numerical for number fields, etc. Ascending, descending, and none are your options. Show, the check box, means show it in the "resultset", what you get when you run (which fruits are bananas and red, which kites and fruit are red, etc.). Not showing a field would be for when you only added it to use it as criteria; i.e. in a query for all red kites and fruit, you wouldn't necessarily need to see the "color" field, as you might name the query RedKitesAndFruits, but you would still want to add criteria on it. Dreamboat is telling you to uncheck "Show" for JobClosed because adding the * means you've already shown the JobClosed field once; the second instance is just to add its criteria. (You can't check * for False, as it's multiple things, you see.) And finally, criteria is what records to include or exclude. (If you had a column called "color" in our ongoing example, "red" would go on the criteria line.) You may use "=False", just plain "False", or "<>True"; all those criteria will return the same result.

So once you make your query, and save it with a name like "qryOpenJobs" or some such, open the report or form (same procedure for both here) in Design view: from the database window, click on Forms or Reports, click the name of said object, and click "design", the triangle button. If you see something that looks like your form, but with a grid drawn over it and such, you're in the right place. If you don't see a second window called Form or Report, with some tabs saying Format Data Event Other All, then hunt for a little finger pointing to a square in the toolbars above. Click it. Select the "All" tab if it's not selected. The first item in that list will be called Record Source. Click in the line next to it, type in the name of your query. It will auto-fill. Close the form or report, say "Yes" to the save of course. Now, just open your Form/Report in regular ol' view (Open for forms, Preview for reports). The records you see will be the same records you see in your query when you hit the "!". Magic!

Filters, by the way, are just another version of queries, applied at run-time rather than underneath, and adaptable. But more on that another day.

I hope you'll excuse the long-windedness if this explanation is too simplistic; one never knows how much help to give. Speaking of help, Access provides some fairly good stuff; either context-based (click F1 in lots of places to find out about them), or through its sample databases, which can give you lots of examples that you can poke around in of what you're trying to do. (To see the samples, go to help > Index or Search and type "northwind" or "sample".) Getting started in Access is a pain, but once you get the hang of it you can get some pretty powerful stuff, pretty quickly. Feel free to borrow & steal from those sample databases; that's what they're there for.

BOL, post back if you need more aiuto.

[Anne Troy]

... in a separate post, which I'm deleting:

I have been too busy to say thanks! You resoved my MS Access problems on April 6 and I appreciated the help very much!