Add a number of months to a date

  • Thread starter Thread starter Deuce Sapp
  • Start date Start date
D

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?
 
Deuce Sapp said:
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?
Click to expand...

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.
 
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.
 
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])'.
 
Deuce Sapp said:
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])'.
Click to expand...

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,

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


Dirk Goldgar said:
Deuce Sapp said:
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])'.
Click to expand...

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)
Click to expand...
 
Deuce Sapp said:
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;
Click to expand...

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
 
Back
Top