Add a number of months to a date

[Deuce Sapp]

I have a table with service due dates and a field for service interval. I
am trying to create a query that will add the interval (number of months) to
the due date. So, if I have 7/1/04 as the date and I add "3" I want it to
be 10/1/04, not 7/4/04.

Any ideas?

 

[Dirk Goldgar]

I have a table with service due dates and a field for service
interval. I am trying to create a query that will add the interval
(number of months) to the due date. So, if I have 7/1/04 as the date
and I add "3" I want it to be 10/1/04, not 7/4/04.

Any ideas?

See the DateAdd function in the VB online help. If you have trouble
locating the help topic, just type the function name into a module, put
the cursor on it, and press F1.

 

[Deuce Sapp]

Dirk,

That's exactly what I need to do, but I can't figure out how to use that
inside a Query. I tried to figure it out without asking, but I'm having a
hard time. Can you help me narrow my search to an appropriate phrase, or
point me in the direction of a tutorial? I have been trying to find "using
modules within a query."

Thanks.

 

[Deuce Sapp]

Okay, my idiocy seems to be confirmed. I see DateAdd as a constant in the
Query builder, but when I try to use it like this:
newdate: DateAdd("m", [contracts]![INTERVAL] ,[contracts]![duedate])

I get an error:
Compile error. in query expression 'DateAdd("m", [contracts]![INTERVAL]
,[contracts]![duedate])'.

 

[Dirk Goldgar]

Okay, my idiocy seems to be confirmed. I see DateAdd as a constant
in the Query builder, but when I try to use it like this:
newdate: DateAdd("m", [contracts]![INTERVAL] ,[contracts]![duedate])

I get an error:
Compile error. in query expression 'DateAdd("m",
[contracts]![INTERVAL] ,[contracts]![duedate])'.

Is [contracts] a table that is participating in the query? If not, you
can't write such an expression. If so, the correct syntax would be

newdate: DateAdd("m", [contracts].[INTERVAL] ,[contracts].[duedate])

but I'd expect the syntax you used, with the bang (!) instead of the dot
(.), to work.

It would help if you posted the SQL view of the query.

 

[Deuce Sapp]

Dirk,

Yes, contracts is the only table in the query. I tried changing the bangs
to dots with the same result. Here is the SQL view:

SELECT contracts.duedate,
DateAdd("m",[contracts].[INTERVAL],[contracts].[duedate]) AS newdate
FROM contracts;


Deuce

Okay, my idiocy seems to be confirmed. I see DateAdd as a constant
in the Query builder, but when I try to use it like this:
newdate: DateAdd("m", [contracts]![INTERVAL] ,[contracts]![duedate])

I get an error:
Compile error. in query expression 'DateAdd("m",
[contracts]![INTERVAL] ,[contracts]![duedate])'.

Is [contracts] a table that is participating in the query? If not, you
can't write such an expression. If so, the correct syntax would be

newdate: DateAdd("m", [contracts].[INTERVAL] ,[contracts].[duedate])

but I'd expect the syntax you used, with the bang (!) instead of the dot
(.), to work.

It would help if you posted the SQL view of the query.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,

Yes, contracts is the only table in the query. I tried changing the
bangs to dots with the same result. Here is the SQL view:

SELECT contracts.duedate,
DateAdd("m",[contracts].[INTERVAL],[contracts].[duedate]) AS newdate
FROM contracts;

That's very peculiar -- there's nothing wrong with that SQL statement so
far as I can see, provided, of course, that it's executed from within
Access. You're not trying to run this query from some other
application, are you?

The only other thing I can think of is that you may have a broken
reference in your VBA project, such that the DateAdd function isn't
being recognized. In the VB Editor environment, click
Tools->References... and look in the list for any reference marked as
MISSING. If you find one, follow the instructions given here:

http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html