Problem With Query Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chels - 10 Jul 2007 20:12 GMT

This is probably not very hard but I am very new to access and am trying to
build an entire database.

I am making a query from a table and I need to pull out records that are
done in April in the Month field but I want it to leave out the records that
are 13/1 and 13/2 in the Sheet No field. It will let me filter the April
records taking out the 13/1 or the 13/2 but if i try to ask it to leave out
both 13/1 and 13/2 it will no longer only take the records from the month of
April.

Any suggestions on how to learn more about expressions as well would help!

Signature

Chels

Reply to this Message

Jeff Boyce - 10 Jul 2007 20:21 GMT

We're not there, so a bit more explanation might help us visualize your
situation.

For instance, you mention "13/1" and "13/2" in a SheetNo field ... are these
"text", or division?

If you post the SQL statement of your query, it could provide more clues.

One possibility is that you are using two rows of selection criteria, but
only putting the relevant criteria into one row.

Regards

Jeff Boyce
Microsoft Office/Access MVP

> This is probably not very hard but I am very new to access and am trying
> to
[quoted text clipped - 11 lines]
>
> Any suggestions on how to learn more about expressions as well would help!

Reply to this Message

Chels - 10 Jul 2007 20:34 GMT

The Sheet No and Month fields are "text" fields. What is the SQL statement of
my query? I am really new at this!

Signature

Chels

> We're not there, so a bit more explanation might help us visualize your
> situation.
[quoted text clipped - 27 lines]
> >
> > Any suggestions on how to learn more about expressions as well would help!

Reply to this Message

Chris2 - 10 Jul 2007 20:43 GMT

> The Sheet No and Month fields are "text" fields. What is the SQL statement of
> my query? I am really new at this!

SELECT <your column names here>
FROM <your table name here>
WHERE [Month] = "April"
AND [Sheet No] NOT IN ("13/1", "13/2")

Sincerely,

Chris O.

Reply to this Message

John W. Vinson - 11 Jul 2007 00:00 GMT

>The Sheet No and Month fields are "text" fields. What is the SQL statement of
>my query? I am really new at this!

Open the Query in design view and select View from the menu; select SQL from
the dropdown list. Copy and paste the SQL text to a message here.

John W. Vinson [MVP]

Reply to this Message

Chels - 10 Jul 2007 20:26 GMT

I forgot to mention I am using Access 2000, Thanks!

Signature

Chels

> This is probably not very hard but I am very new to access and am trying to
> build an entire database.
[quoted text clipped - 7 lines]
>
> Any suggestions on how to learn more about expressions as well would help!

Reply to this Message

John W. Vinson - 10 Jul 2007 21:04 GMT

>This is probably not very hard but I am very new to access and am trying to
>build an entire database.
[quoted text clipped - 7 lines]
>
>Any suggestions on how to learn more about expressions as well would help!

Please open the query you're using in SQL view and post the SQL text here.

I'd store the date/time data in a Date/Time field, not a Month field; and you
could use a criterion on Sheet No of

NOT IN ("13/1", "13/2")

John W. Vinson [MVP]

Reply to this Message

Chels - 11 Jul 2007 12:44 GMT

SELECT [T&A].UNIT, [T&A].Description, [T&A].[Sheet No], [T&A].Month,
[T&A].PROC, [T&A].FREQ, [T&A].Remarks, [T&A].[PAGE No], [T&A].[Rev Date],
[T&A].Shutdown, [T&A].DONE
FROM [T&A]
WHERE ((Not ([T&A].[Sheet No])="13/1") AND (([T&A].Month)="April"))
ORDER BY [T&A].[Place Holder];

Does that help? There is no date or time in the month field just the name of
the month each test is performed in.

Signature

Chels

> >This is probably not very hard but I am very new to access and am trying to
> >build an entire database.
[quoted text clipped - 16 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 11 Jul 2007 20:10 GMT

>Does that help? There is no date or time in the month field just the name of
>the month each test is performed in.

So you empty and restart the database each year? How can you tell whether
"April" means April this year, last year, or next year!?

Glad the NOT IN clue worked for you.

John W. Vinson [MVP]

Reply to this Message

Chels - 11 Jul 2007 12:46 GMT

Thank you so much!!! The NOT IN ("13/1", "13/2") in the Sheet No field and
"April" in the Month field worked!

Signature

Chels

> >This is probably not very hard but I am very new to access and am trying to
> >build an entire database.
[quoted text clipped - 16 lines]
>
> John W. Vinson [MVP]

Reply to this Message

Chris2 - 12 Jul 2007 04:36 GMT

> Thank you so much!!! The NOT IN ("13/1", "13/2") in the Sheet No field and
> "April" in the Month field worked!

Dear Chels,

You're welcome.

Sincerely,

Chris O.

Reply to this Message