Compare and check date

[hoachen]

Order# InputDate StartDate EndDate
1 20090914 20090812 20090831
2 20090914 20090915 20100302
3 20090701 20090701 20091031

I am not sure how to run a query that I can compare and check the above
dates. The datatype on inputdate is Text, which I converted into number.
The datatype for startDate and endDate is: number.
my question is: how can I compare and check the date. I want the check
against the startDate with the inputDate. If the startDate more than 6 weeks
of inputDate, then it will not display on the query that I run. Otherwise,
show on the query. I don't really care about the endDate

If someone can help me with this I would be very very appreciated.

Cheer,
hoachen

 

[Jeff Boyce]

Those may look like dates to you, but to Access, the way you've defined
them, they look like character strings.

If you want to use Access' date/time-related functions, you need to 'feed'
them date/time values.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Order# InputDate StartDate EndDate
1 20090914 20090812 20090831
2 20090914 20090915 20100302
3 20090701 20090701 20091031

I am not sure how to run a query that I can compare and check the above
dates. The datatype on inputdate is Text, which I converted into number.
The datatype for startDate and endDate is: number.
my question is: how can I compare and check the date. I want the check
against the startDate with the inputDate. If the startDate more than 6 weeks
of inputDate, then it will not display on the query that I run. Otherwise,
show on the query. I don't really care about the endDate

If someone can help me with this I would be very very appreciated.

Cheer,
hoachen

Rather than converting to Number (which will sort correctly since it's
yyyymmdd, but won't support any date/time functions), convert it to a Date:

CDate(Format(InputDate, "@@@@/@@/@@"))

will generate a Date/Time value.

Give that you can use

WHERE [StartDate] <= DateAdd('ww", 6, [InputDate])

will display only those records where the dates are six weeks or less apart.

 

[MGFoster]

Order# InputDate StartDate EndDate
1 20090914 20090812 20090831
2 20090914 20090915 20100302
3 20090701 20090701 20091031

I am not sure how to run a query that I can compare and check the above
dates. The datatype on inputdate is Text, which I converted into number.
The datatype for startDate and endDate is: number.
my question is: how can I compare and check the date. I want the check
against the startDate with the inputDate. If the startDate more than 6 weeks
of inputDate, then it will not display on the query that I run. Otherwise,
show on the query. I don't really care about the endDate

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can compare them like this:

WHERE CDate(Format(InputDate,"####-##-##")) < DateAdd("ww",-5,
CDate(Format(StartDate,"####-##-##")))

You may want to change the -5 to -6, depending on whether or not you
want the weeks to be inclusive or not.

Unfortunately, this method requires the whole table to be scanned, 'cuz
of the formatting and data conversion. This could be fixed by importing
the data into a table w/ predefined columns of DateTime data types.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSrAMCYechKqOuFEgEQLdBACg4Y0tqOLdoRnw8wKhfvSD6ltYs9cAnRLu
C/Ht1BCoJuJy0ZMzjihn7bBW
=zYc1
-----END PGP SIGNATURE-----