Date field greater than a certain date in two fields

[kevsvette]

I have a table that has 2-columns with dates in them and a 3rd column with peoples names. I want to use access to get rid of all records except associated with certain people (which I've figured out) AND that has a date in either of the 2-date columns that's greater than 12/2/2017. There are some blanks in either of the date fields that I also need to exclude.

Help!

So basically if the name field name matches what I want & the 1st date field is 12/3/2017 & the 2nd date field is null or <=12/2/2017 I need to retain it or vice versa. Only if both date fields are null or the the 2 dates in both fields are <=12/2/2017 do I want to exclude it.

Can this be done?

 

[xenou]

First create a select query to confirm you are selecting the right records for deletion.
As a starter, try fiddling with this one:

select *
from Table1
where 
	(NameField not in ('name1','name2','name3'))
	and 
	(Nz(DateField1, #12/3/2017#) < #12/2/2017#)
	and 
	(Nz(DateField2, #12/3/2017#) < #12/2/2017#)

Once it works, change it to a delete query:

[COLOR="#FF0000"]delete[/COLOR] *
from Table1
where 
	(NameField not in ('name1','name2','name3'))
	and 
	(Nz(DateField1, #12/3/2017#) < #12/2/2017#)
	and 
	(Nz(DateField2, #12/3/2017#) < #12/2/2017#)

Back up the database first and save it in case of screwups!

 

[kevsvette]

Ok I should have said that I'm new to access... I wish I could share the sql query but its business related. I'm not sure how to input your recommendation... I meant to update my initial post, but was out of time or something so below is my updated ?

I have a table that has 2-columns with dates in them and a 3rd column with people’s names. I want to use access to get rid of all records except associated with certain people (which I've figured out) AND that also has a date in either of the two 2-date columns that's greater than 12/2/2017. There are some blanks in either of the date fields that I also need to exclude.

1st the Name field has to be one of 10 people that I'm interested in retaining & further ...

if 1st date field is 7/4/2018 & the 2nd is blank ... keep the record because of 1^st date
if 1st date field is 8/1/2016 & the 2nd is 1/1/2018 ... keep the record because of 2^nd date
if both date fields are blank ... exclude it, no dates
if both date fields have dated prior to 12/2/2017 ... exclude it

Can this be done in design view as its easier for me.

 

[kevsvette]

This is the SQL Query that I have currently which limits the “RefTable” to just the 10-people that I want to retain out of gosh know how many. I’ve changed the Field Names and highlighted in red those that I’m interested in…

SELECT RefTABLE.[FIELD NAME], RefTABLE.[FIELD NAME], RefTABLE.[PERSON OF INTEREST FIELD NAME], RefTABLE.[FIELD NAME], RefTABLE.[1^st DATE FIELD NAME], RefTABLE.[2^nd DATE FIELD NAME], RefTABLE.[FIELD NAME], RefTABLE.[FIELD NAME], RefTABLE.FIELD NAME, RefTABLE.FIELD NAME, RefTABLE.[FIELD NAME], RefTABLE.FIELD NAME, RefTABLE.FIELD NAME, RefTABLE.FIELD NAME
FROM RefTABLE
WHERE (((RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME" Or (RefTABLE.[PERSON OF INTEREST FIELD NAME])="NAME"));

… but I’d like to additionally limit it to only those 10-individuals along with a date of >12/2/2017 in either the 1^st DATE FIELD NAME OR the 2^nd DATE FIELD NAME.
Can this be done in design view as I don’t understand a lot of the syntax since I’m new to Access.

My attempts has excluded records that I know that should be included because one field or the other has a date less than my specified date.

Hope this helps… The only thing I’ve been able to find in my searches is how to limit 1 date field… not 2 with dependencies on whether or not one of the two fields has a date that is greater than a specified date.

 

[stumac]

It can be done in the design grid but it will be far more complex than using SQL - especially if you continue to use 'OR' for every Name - instead consider using IN. So in one line of the criteria you would enter

IN("Name1","Name2","Name3",...)

What you are trying to do looks fairly straight forward:

Select [Field1], [Field2], [Field3], [Person of Interest Field], [1st Date], [2nd Date], [Field4]
From RefTable
Where [Person of Interest Field] IN ("Name1", "Name2", "Name3", "Name4", "Name5", "Name6") AND ((Nz([1st Date], #13/3/2017#) < #12/2/2017#) AND (Nz([2nd Date], #13/3/2017#) < #12/2/2017#))