DateAdd in query

[rigby]

Hi
This may seem a silly question, but i am still struggling with it,
nevertheless. I have created a few DateAdd expressions in my forms which work
just fine. But, i need to use the exact same expressions in a report.
=DateAdd(“yyyyâ€,3,[SSET])
But it keeps giving me an error and says that it doesnt recognise "yyyy" or
when i try using "m" i get the same result. it also puts it into [], like
["m"] and asks for a parameter value "m" before opening the report.
Why wont it work properly? I tried setting the report's field's control
source with the same expression via the same method as i did in the form.
Still get an error!

Any advice?
Thank you
Rigby

 

[[MVP] S.Clark]

How are you using it in the report? What property of the control? etc...

 

[Wayne Morgan]

It is asking for the parameter because of the brackets. The expression
should work in the report just as it works in the form. Sometimes reports do
get a little picky about some things though. Occasionally, you have to place
a textbox on the report for a field you want to use in an expression. To do
this, it's not something you want displayed, place the textbox in the same
section as the expression (unless you have a reason to do otherwise) and set
its Visible property to No. Make sure the textbox has a different name than
the field it is bound to. For example, if the field is SSET then name the
textbox txtSSET.

You have this in the Queries newsgroup. Are you doing this in a textbox on
the report or in the query feeding the report? If you're doing this in the
query, then you need to make a calculated field in the query and bind a
textbox on the report to that field. In the query design grid, a calculated
field would look like this in the Field row of the grid:

MyCalcFieldateAdd("yyyy", 3, [TableName].[SSET])

Then in the report, you would have a textbox bound to the field MyCalcField.

Also, the quotes around the yyyy in the DateAdd statement you posted are
"curly quotes" not the straight "text quotes" that I would expect to see in
code. These quotes have a different Chr() value. If they are what you have
in the code, that may also cause a problem.

 

[John Nurick]

Maybe it's the difference between
“yyyy”
and
"yyyy"

Hi
This may seem a silly question, but i am still struggling with it,
nevertheless. I have created a few DateAdd expressions in my forms which work
just fine. But, i need to use the exact same expressions in a report.
=DateAdd(“yyyy”,3,[SSET])
But it keeps giving me an error and says that it doesnt recognise "yyyy" or
when i try using "m" i get the same result. it also puts it into [], like
["m"] and asks for a parameter value "m" before opening the report.
Why wont it work properly? I tried setting the report's field's control
source with the same expression via the same method as i did in the form.
Still get an error!

Any advice?
Thank you
Rigby

 

[rigby]

Thank you very much Wayne
I have now been able to sort it out. It must have been the culry quotes
problem, i think, becuase the rest of the expression works fine.

I have now approached another issue. As my expression calculates a date for
5 years time as the expiry date, i have been informed that it needs to be -1
day of that final date. so, for instance, if the SSET completion date is 01
Jan '05 and the expiry date must then be 31 Dec '09. Make sense?? how can i
add to my current expression so it will subtract one day from the answer?

Again, thank you for you help, everyone.
Kind Regards
Rigby

 

[Wayne Morgan]

One option would be to use DateSerial(). You could break up the date into
year, month, and date then add 5 to the year and subtract one from the date.

Example:
=DateSerial(Year([SSET]) + 5, Month([SSET]), Day([SSET]) -1)

Also, with the way VBA handles dates, if you are just adding and subtracting
a number of whole days, you can simply do just that. Doing so would change
your current expression to:

=DateAdd("yyyy",5,[SSET]) -1