date conversion

[B Briant]

I need to write an access query that will pull records from a table if
the start or end dates input by the user are within the start and end
dates in the db. I can't use BETWEEN because either of the dates in
the db may be outside those input. Basically it's something like:

select where input.startdate <= tbl.startdate or input.endate >=
tbl.enddate

so that if the query date are start 04/01/2000 and end 09/31/2000 it
will return records with start 01/01/2000 and 06/31/2001 because the
input dates are between the record dates.

Is this possible??

There is a DATE function in access that will convert a date to a
serial number which would let me do the math but I think it needs the
year, month and day to be entered as separate parameters:
DATE(year,month,day). Is there anything that will let me just convert
a date to an number in a query?

Thanks for the help.
b

[Pieter Linden]

So what's wrong with:

SELECT Staff.StaffID, Staff.StaffName, Staff.MgrID, Staff.Level
FROM Staff
WHERE (((Staff.MgrID) Not Between 3 And 4));
if you use NOT BETWEEN x AND y, you'll get all the values that are not
in that range...

say I have {1,2,3,4,5} as my entire range of X, and I ask for

SELECT X
FROM Table
WHERE Table.X NOT BETWEEN 2 AND 4;

I get {1,5} as the result set. Is that what you were after?

[Bradley]

You can seperate a date by using

Year(myDate)
Month(myDate)
Day(myDate)

and put it back together using

DateSerial(myYear, myMonth, myDay)

Don't forget the # symbols around your dates in the SQL statement, or
convert the string returned to a date :)

eg. SELECT * FROM myTable WHERE myTable![StartDate] >= CDate([Enter Start
Date]) OR myTable![EndDate] <= CDate([Enter End Date])

--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response

"B Briant" <br*****@rogers.com> wrote in message
news:8e**************************@posting.google.c om...

I need to write an access query that will pull records from a table if
the start or end dates input by the user are within the start and end
dates in the db. I can't use BETWEEN because either of the dates in
the db may be outside those input. Basically it's something like:

select where input.startdate <= tbl.startdate or input.endate >=
tbl.enddate

so that if the query date are start 04/01/2000 and end 09/31/2000 it
will return records with start 01/01/2000 and 06/31/2001 because the
input dates are between the record dates.

Is this possible??

There is a DATE function in access that will convert a date to a
serial number which would let me do the math but I think it needs the
year, month and day to be entered as separate parameters:
DATE(year,month,day). Is there anything that will let me just convert
a date to an number in a query?

Thanks for the help.
b