By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

date conversion

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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?
Nov 12 '05 #2

P: n/a

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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.