SQL IF Statement

>If there are people with both dates of < and > 06/01/2003
Spell out how your table has people that can have more than one date.  This will affect the query that's generated.

The date or person is not the primary key. They key is something else.

heartburn,
     can you please put that into the correct syntax?

tablename = Test
Fieldnames = date, name
 Thanks.

I'm getting a data type mismatch in criteria expression. My date field is type Date/Time, and my name field is text.

It's valid SQL. Maybe Access doesn't like the way the date string is formatted? try '06/01/2003'.

select name from test as a
where date < #06/01/2003#
and not exists (select b.name from test as b where a.name=b.name and b.date > #06/01/2003#)

use # to delimit date in access

Also, using Date as the name of a field is a MAJOR error, since Date is a RESERVED word in Access (the name of the standard system function that returns the system Date for Today), so you MUST enclose your field name in [...], like this:

select name from test as a
where [date] < #06/01/2003#
and not exists (select b.name from test as b where a.name=b.name and b.[date] > #06/01/2003#)

AW

select distinct test1.name from Test as Test1,Test as test2
where  test1.name=test2.name and test1.date < '06/01/03'

Try this one

Select [People],[Date]
from Table
Group by [People],Date]
Having (Not(Date > '06/01/2003)) And (Not(Date < '06/01/2003'))

Don't make your own life so difficult. Just change it to another more meaningful and less painful fieldname....

like "date_payday"

anyway, access is not like foxpro which is so limited in fieldname length.

Select [Date], // Use [] to enclose date since it is a call name
From Test,
Where [Date] < '06/01/2003'

Most Access databases won't allow you to use the single quote ' as a date delimiter.  MS Access uses the # symbol to surround dates, much like in Visual Basic code.

So your query ought to be:

SELECT [Name], [Date]
FROM Test
WHERE [Date] < #6/1/2003#

Since the date field can only hold one value, you won't need to check for > #6/1/2003#.  However, if you actually have two date fields, say Date1 and Date2, it would be:

SELECT [Name], [Date1]
FROM Test
WHERE [Date1] < #6/1/2003# and not ([Date2] > #6/1/2003#)

Try this

SELECT * FROM Test WHERE Date > '2003/06/01 00:00:00' AND Date <= '2003/06/01 23:59:59'

Should work ;-)

Cheers Pieter