Missing Expressions

[manifold1978]

Hello, complete novice looking for a leg up here please:

Using a calcualted field I need to add x number of weeks to a date I already have in another field.
Google tells me the way to do this is with the DateAdd expression.
However in expression builder, DateAdd doesn't appear.
If I type it in directly using the syntax Google tells me to, Access says it's invalid when I try and save the table.

Driving me nuts, any help hugely appreciated.

Thanks,


A

[ranman256]

=DateAdd("d",42,[dateFld])

d = days, it may allow weeks, I didn't check.

[Bob Fitz]

I think you need to give us a bit more to work with. We know the syntax, as you you now that googled it, so no point in us posting that.
Show us what you've tried that give you errors.

[manifold1978]

Hi, thanks for the replies.
I guess the "first" issue is that I can't see DateAdd in expression builder:


If I type "date" in the expression builder window at the top, the only function that pops up as availble is "DateSerial".

That aside, if I just hand type the following in the expression builder:


I get this when I hit ok:


Hope that makes sense?
Sure I'm missing something obvious...

Thanks,
A

[Minty]

Can you clarify - Calculated Field - Is this in the Table design or a Query?
If it's a Table (as I suspect it is from your OP) I would advise against it, and simply do the calculation in a query when you need it.

Is [Timeline - Phase 0 complete] or [Timeline - Phase 1 complete] already a calculated field?

I note you have both from your screen shots, is one a typo, and also please remove the spaces and special characters from your field names, they will cause you grief and heaps of extra typing during development.

[manifold1978]

Thanks,
It's in a table deisgn, but I guess eitehr way DateAdd should at least appear in expression builder, no?
Yes, both of the fields are already calcluated. Neither is a typo, they both exist - I just used a different one in each exmaple by mistake.

Thanks for the tip on spaces.

Presumably there's still something up whether I use a query or not?

[Minty]

DateAdd isn't available as a calculated field expression. They are quite restricted on what you can and can't do with them, hence often easier to avoid.
I'm pretty sure they also can't be indexed so their potential use to save processing in a query is negated by them being slow in searches(in large data sets).

if you really want to pursue it you can simply add a number of days [MyDate] + 14 would add two weeks.

[manifold1978]

Juts to add, I tried the same expression with a non-calcualted field (just a field formatted Medium Date), I still got the same error as above.

[manifold1978]

DateAdd isn't available as a calculated field expression. They are quite restricted on what you can and can't do with them, hence often easier to avoid.
I'm pretty sure they also can't be indexed so their potential use to save processing in a query is negated by them being slow in searches(in large data sets).

if you really want to pursue it you can simply add a number of days [MyDate] + 14 would add two weeks.

Got it thanks - posted the above before I saw this reply.
Thank you!

[Minty]

Got it thanks - posted the above before I saw this reply.
Thank you!

You are welcome - good luck with the rest of your project.