Exclude query

  • Thread starter Thread starter Roland
  • Start date Start date
R

Roland

I am trying to create a query to exclude data from 1 table.
ie
Exclude rows If [DATE]="25-12-2003" and [PRESENT]="no",
but include If [DATE]="25-12-2003" and [PRESENT]="yes"

I thought it would be simple, but no such luck as it
removed all rows with [DATE]="25-12-2003", whatever
[PRESENT] had in it!

The only messy way I have thought of doig this was to have
2 queries for each condition, then merge both results to
make 1 table! AND then carry on with the rest of the
process.

Finaly, I would prefer to do this all from 1 button click
that is called "RUN" on a form!
 
Hi,


Easier to do it in SQL view:



SELECT *
FROM somewhere
WHERE NOT( [date]=#25-12-2003# AND present = 'no' )




Assuming present is a string (varchar) field. If it is Boolean, then try,
instead

....
WHERE NOT( [date]=#25-12-2003# AND present = 0 )



That should return records where ( the date is NOT the 25th December 2003)
OR those where (present = 'yes' ( whatever the date is)) .



Hoping it may help,
Vanderghast, Access MVP
 
Thanks for that Michel, but it didn't QUITE work, maybe
because of the way I explained and the sample I used.
I also need to inclued rows where [DATE]= any other date.
(btw, [PRESENT]=null)

Here is exactly what I got atm
----------
'Defines Variables
Dim DelQry As String
Dim AddQry As String

'Specifies Queries
DelQry = "DELETE tbl_overduede2.* FROM tbl_overduede2;"
AddQry = "INSERT INTO tbl_overduede2 SELECT * FROM
lnktbl_overduede2 WHERE NOT([Bodybuilder Complete]=#0001-
01-01# AND [Dealer Code]=null);"

'Runs Queries
DoCmd.SetWarnings False
DoCmd.RunSQL DelQry
DoCmd.RunSQL AddQry
DoCmd.SetWarnings True
----------
lnktbl_overduede2 is a CSV file.
I hope this explaines it a bit better

-----Original Message-----
Hi,


Easier to do it in SQL view:



SELECT *
FROM somewhere
WHERE NOT( [date]=#25-12-2003# AND present = 'no' )




Assuming present is a string (varchar) field. If it is Boolean, then try,
instead

....
WHERE NOT( [date]=#25-12-2003# AND present = 0 )



That should return records where ( the date is NOT the 25th December 2003)
OR those where (present = 'yes' ( whatever the date is)) .



Hoping it may help,
Vanderghast, Access MVP




I am trying to create a query to exclude data from 1 table.
ie
Exclude rows If [DATE]="25-12-2003" and [PRESENT]="no",
but include If [DATE]="25-12-2003" and [PRESENT]="yes"

I thought it would be simple, but no such luck as it
removed all rows with [DATE]="25-12-2003", whatever
[PRESENT] had in it!

The only messy way I have thought of doig this was to have
2 queries for each condition, then merge both results to
make 1 table! AND then carry on with the rest of the
process.

Finaly, I would prefer to do this all from 1 button click
that is called "RUN" on a form!
Click to expand...


.
Click to expand...
 
Hi,



SELECT *
FROM somewhere
WHERE NOT Nz( [date]=#12-25-2003# AND present = 'no', 0 )

would keep any record where the date is either null, either <> 25 Dec 2003,
and the record that are equal to that date, but where present is null, or
equal to anything else than 'no'

present='no' ?
True False
Null

[date]=25th Dec 2003 ?

True False True True

False True True True

Null True True True





So only one of the nine possible case is eliminated.



Hoping it may help,
Vanderghast, Access MVP



Roland said:
Thanks for that Michel, but it didn't QUITE work, maybe
because of the way I explained and the sample I used.
I also need to inclued rows where [DATE]= any other date.
(btw, [PRESENT]=null)

Here is exactly what I got atm
----------
'Defines Variables
Dim DelQry As String
Dim AddQry As String

'Specifies Queries
DelQry = "DELETE tbl_overduede2.* FROM tbl_overduede2;"
AddQry = "INSERT INTO tbl_overduede2 SELECT * FROM
lnktbl_overduede2 WHERE NOT([Bodybuilder Complete]=#0001-
01-01# AND [Dealer Code]=null);"

'Runs Queries
DoCmd.SetWarnings False
DoCmd.RunSQL DelQry
DoCmd.RunSQL AddQry
DoCmd.SetWarnings True
----------
lnktbl_overduede2 is a CSV file.
I hope this explaines it a bit better

-----Original Message-----
Hi,


Easier to do it in SQL view:



SELECT *
FROM somewhere
WHERE NOT( [date]=#25-12-2003# AND present = 'no' )




Assuming present is a string (varchar) field. If it is Boolean, then try,
instead

....
WHERE NOT( [date]=#25-12-2003# AND present = 0 )



That should return records where ( the date is NOT the 25th December 2003)
OR those where (present = 'yes' ( whatever the date is)) .



Hoping it may help,
Vanderghast, Access MVP




I am trying to create a query to exclude data from 1 table.
ie
Exclude rows If [DATE]="25-12-2003" and [PRESENT]="no",
but include If [DATE]="25-12-2003" and [PRESENT]="yes"

I thought it would be simple, but no such luck as it
removed all rows with [DATE]="25-12-2003", whatever
[PRESENT] had in it!

The only messy way I have thought of doig this was to have
2 queries for each condition, then merge both results to
make 1 table! AND then carry on with the rest of the
process.

Finaly, I would prefer to do this all from 1 button click
that is called "RUN" on a form!
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top