6 Months Due

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am looking for a way to calculate a date start + 6 months and then sorted
by the current month we are in. If this can be done in a query please post
the expression or technique to accomplish it. Thanks in advance.
 
So far I have setup the query with the date start and created an expression
that has the following: DateDue: DateAdd('m',6,[DateInstalled])
But that only shows me the 6 months in advance I need to be able to see what
dates are due in the current month instead of ALL the entry dates. Please
tell me if I am doing this wrong still a newbie at this.
 
Hello,

I am looking for a way to calculate a date start + 6 months and then sorted
by the current month we are in. If this can be done in a query please post
the expression or technique to accomplish it. Thanks in advance.
Click to expand...

What do you mean by "sorted by the current month"? The term "Sort" in
Access (and databases in general) means "put a set of records into a
specific sequential order" - and I don't see how you can sort data "by
the current month".

If your table has a date field [Date Start], you can create a Query
with a calculated field by typing

DueDate: DateAdd("m", 6, [Date Start])

You can sort by this field, or - perhaps this is what you're asking -
search for records in a given date range; e.g. you could put as a
criterion on DueDate

BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)

to get all records where DueDate falls within the current calendar
month.

John W. Vinson[MVP]
 
John Vinson said:
BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)
Click to expand...

I have added the code to the "DateAdd("m", 6, [Date Start])" and it is very
close to what I am looking for. My result is a -1 for the true fields and 0
for false. How would you filter it to show only the true results? In other
words I have 'DateEntry' + 6 months and expires this month?

John Vinson said:
Hello,

I am looking for a way to calculate a date start + 6 months and then sorted
by the current month we are in. If this can be done in a query please post
the expression or technique to accomplish it. Thanks in advance.
Click to expand...

What do you mean by "sorted by the current month"? The term "Sort" in
Access (and databases in general) means "put a set of records into a
specific sequential order" - and I don't see how you can sort data "by
the current month".

If your table has a date field [Date Start], you can create a Query
with a calculated field by typing

DueDate: DateAdd("m", 6, [Date Start])

You can sort by this field, or - perhaps this is what you're asking -
search for records in a given date range; e.g. you could put as a
criterion on DueDate

BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)

to get all records where DueDate falls within the current calendar
month.

John W. Vinson[MVP]
Click to expand...
 
John Vinson said:
BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)
Click to expand...

I have added the code to the "DateAdd("m", 6, [Date Start])" and it is very
close to what I am looking for. My result is a -1 for the true fields and 0
for false. How would you filter it to show only the true results? In other
words I have 'DateEntry' + 6 months and expires this month?
Click to expand...

I'm sorry, you haven't mentioned any yes/no fields before, and I have
no idea how your table is structured.

You can put a criterion of either True or -1 on a yes/no field in a
query to select records where the field is true.

Could you open your query in SQL view and copy and paste the SQL to a
message here? Perhps it would also help to describe the structure of
your table.

John W. Vinson[MVP]
 
I believe that John Vinson wanted you to place that as criteria under the
calculated field. In the query design view (the grid) that would look
something like the following.

Field: EventDate: DateAdd("m", 6, [Date Start])
Criteria: BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)

What you seem to have done is the following
Field: EventDate: DateAdd("m", 6, [Date Start]) BETWEEN
DateSerial(Year(Date()), Month(Date()), 1) AND DateSerial(Year(Date()),
Month(Date()) + 1, 0)

In that case you would need to put True in the criteria. The first option
is probably a better way to accomplish your goal.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

blink said:
John Vinson said:
BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)
Click to expand...

I have added the code to the "DateAdd("m", 6, [Date Start])" and it is
very
close to what I am looking for. My result is a -1 for the true fields and
0
for false. How would you filter it to show only the true results? In
other
words I have 'DateEntry' + 6 months and expires this month?

John Vinson said:
Hello,

I am looking for a way to calculate a date start + 6 months and then
sorted
by the current month we are in. If this can be done in a query please
post
the expression or technique to accomplish it. Thanks in advance.
Click to expand...

What do you mean by "sorted by the current month"? The term "Sort" in
Access (and databases in general) means "put a set of records into a
specific sequential order" - and I don't see how you can sort data "by
the current month".

If your table has a date field [Date Start], you can create a Query
with a calculated field by typing

DueDate: DateAdd("m", 6, [Date Start])

You can sort by this field, or - perhaps this is what you're asking -
search for records in a given date range; e.g. you could put as a
criterion on DueDate

BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)

to get all records where DueDate falls within the current calendar
month.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Thanks Johns,

That is exactly what I was looking for!

John Spencer said:
I believe that John Vinson wanted you to place that as criteria under the
calculated field. In the query design view (the grid) that would look
something like the following.

Field: EventDate: DateAdd("m", 6, [Date Start])
Criteria: BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)

What you seem to have done is the following
Field: EventDate: DateAdd("m", 6, [Date Start]) BETWEEN
DateSerial(Year(Date()), Month(Date()), 1) AND DateSerial(Year(Date()),
Month(Date()) + 1, 0)

In that case you would need to put True in the criteria. The first option
is probably a better way to accomplish your goal.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

blink said:
John Vinson said:
BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)
Click to expand...

I have added the code to the "DateAdd("m", 6, [Date Start])" and it is
very
close to what I am looking for. My result is a -1 for the true fields and
0
for false. How would you filter it to show only the true results? In
other
words I have 'DateEntry' + 6 months and expires this month?

John Vinson said:
On Tue, 30 Jan 2007 09:53:00 -0800, blink

Hello,

I am looking for a way to calculate a date start + 6 months and then
sorted
by the current month we are in. If this can be done in a query please
post
the expression or technique to accomplish it. Thanks in advance.

What do you mean by "sorted by the current month"? The term "Sort" in
Access (and databases in general) means "put a set of records into a
specific sequential order" - and I don't see how you can sort data "by
the current month".

If your table has a date field [Date Start], you can create a Query
with a calculated field by typing

DueDate: DateAdd("m", 6, [Date Start])

You can sort by this field, or - perhaps this is what you're asking -
search for records in a given date range; e.g. you could put as a
criterion on DueDate

BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0)

to get all records where DueDate falls within the current calendar
month.

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top