If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Compare and check date
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 |
#2
|
|||
|
|||
Compare and check date
On Tue, 15 Sep 2009 13:33:02 -0700, hoachen
wrote: 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. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Compare and check date
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 "hoachen" wrote in message ... 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 |
#4
|
|||
|
|||
Compare and check date
hoachen wrote:
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/AwUBSrAMCYechKqOuFEgEQLdBACg4Y0tqOLdoRnw8wKhfvSD6l tYs9cAnRLu C/Ht1BCoJuJy0ZMzjihn7bBW =zYc1 -----END PGP SIGNATURE----- |
Thread Tools | |
Display Modes | |
|
|