General discussion

Locked

MS Access Query

By garybrett.ecl ·
Hi anyone able to answer this nugget...?.I have an access 97 DataBase (45mb).I have run a query to find out who has given us business in the past year.Fine:Out of 3000 customers we had 500 giving us business in past year(I used the >#31/12/99# function).What I cannot do however is run a query on who has not given us business in past year.How do find data when there is nothing to search on(ie:Date business given).I have tried to work out how to remove the 500(business given)from the total 3000(customers)but am getting very confused.Is there an easy way to acheive this.Thanx for your help(hoping!!!)...Gary

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

MS Access Query

by lumberjack In reply to MS Access Query

Sorry if you have tried this already - how about testing for a null entry? ie Sales = ""
or Sales = null ?

Collapse -

MS Access Query

by garybrett.ecl In reply to MS Access Query

The question was auto-closed by TechRepublic

Collapse -

MS Access Query

by csn In reply to MS Access Query

Another option, assuming customer and sales info are kept in seperate tables but share a key, is to use the Access 97 'Find Unmatched' wizard. (Last option when you select 'New' query).

Use the custumer table as input to the first screen, the query you already did as input to the second screen and link the tables/queries by the key in the third screen.

You may want to take a look (SQL view) at the resulting query to familiarize yourself with SQL.

Good luck!

Collapse -

MS Access Query

by garybrett.ecl In reply to MS Access Query

The question was auto-closed by TechRepublic

Collapse -

MS Access Query

by TranMan In reply to MS Access Query

Gary, If the date value is null for those "non-business" customers, you will need to use IS NULL in the criteria line (just put null, Access will fill in the IS). In using Null, you always need to remember that Null is not equal to anything--including another null (no equal signs allowed). This also means when you do a join on two fields, and either one of them is null, OR both of them are null, because null <> null, you will not get an output row. Good Luck. Paul

Collapse -

MS Access Query

by garybrett.ecl In reply to MS Access Query

The question was auto-closed by TechRepublic

Collapse -

MS Access Query

by RichTee In reply to MS Access Query

Just modify your parameter to:
"Not >#31/12/99# OR Null"
This will give you everything from your requested date backward and all nulls (none field entries).

Collapse -

MS Access Query

by garybrett.ecl In reply to MS Access Query

The question was auto-closed by TechRepublic

Collapse -

MS Access Query

by SanKulPune In reply to MS Access Query

Hello Gary,

You may use the following expression where you used the function (>#31/12/99#)

(not (<#1/1/01# AND >#31/12/99#)).

This will locate all entries, including last year, that are not in the current year.

To restrict to last year only, use expression

(<#1/1/00# AND >#31/12/98#)

Your feedback will be highly appreciated.

With regards,
SanganakSakha
Simple And Effective Solutions To Small Problems, No QuickFixes.

Collapse -

MS Access Query

by garybrett.ecl In reply to MS Access Query

The question was auto-closed by TechRepublic

Back to Software Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums