Compare and check date


H

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
 
Ad

Advertisements

J

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
 
Ad

Advertisements

J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top