DateAdd Function in a Computed Querry (1 Viewer)

[sandros]

Hello
I am trying to use the dateadd function in an expression in a computed querry.
What i am trying to do, is to use this function, to create a collumn that increases the date of a table's field (DateEntry), for five years.
The syntax is Expr1: DateAdd('y',5,[Datentry]) but it seems that comma, after 'y', creates a problem which i cannot bypass.
Can anyone help please?

 

[JHB]

The syntax is Expr1: DateAdd('y',5,[Datentry]) but it seems that comma, after 'y', creates a problem which i cannot bypass.
Can anyone help please?

Depending of from where in the world you're - you've to use ; instead of a comma.

 

[sandros]

Depending of from where in the world you're - you've to use ; instead of a comma.

Thank you for the reply
I am from Greece.
When i try to use ; Access changes the original expression to this :
Expr1: DateAdd('\y';5;[Datentry])
which returns an error result when i run the querry

 

[Minty]

Try using double quotes around the "y" ?
DateAdd("y";5;[Datentry])

 

[sandros]

Try using double quotes around the "y" ?
DateAdd("y";5;[Datentry])

I've tried, no result, same answer

 

[Minty]

Have you tried going back to the comma with the double quotes ? DateAdd("y",5,[Datentry])

 

[sandros]

Have you tried going back to the comma with the double quotes ? DateAdd("y",5,[Datentry])

I know it sounds weird but i've tried all possible combinations

 

[Minty]

Is DateEntry definitely a date?

 

[sandros]

Is DateEntry definitely a date?

The field is definetely a date, the problem is at expression syntax where comma is not accepted. But anything else, changes the expression in a way that returns an error. :banghead::banghead:
I don't know if there are any Access settings that define the manipulation of comma or semicolon

 

[Grumm]

Maybe take a quick look at this : http://www.techonthenet.com/access/functions/date/dateadd.php

So you need to use a comma. Then I think the code returns an error because "y" isn't valid. Try it with the correct syntax and give it a try.
DateAdd("yyyy",5,[Datentry])

 

[Minty]

What delimiters do you normally use for other functions ? IIf ? or Format ?

 

[Grumm]

Does a delimiter in VBA changes from location ? If it is grats to the guys who wrote the compiler... the official help say to use "," for that specific function.
But I can be wrong

 

[Minty]

I hope not - wouldn't make for much portability! I completely missed the mal-formed "yyyy" bit...

 

[JHB]

..
I don't know if there are any Access settings that define the manipulation of comma or semicolon

No it is the location, in Denmark we use the, as decimal point/separator.
Here is a link to which countries use . or , as decimal points - half down the document.
https://en.wikipedia.org/wiki/Decimal_mark

Does a delimiter in VBA changes from location ? If it is grats to the guys who wrote the compiler... the official help say to use "," for that specific function.
But I can be wrong

Yes you're wrong - I've wasting to much time in the start using MS-Access to forget it.
Below is a picture of the error message if you use a comma.

Here with a ;, (no errors).

But the SQL-String is with a , (so you've to be very aware of where to use , or ; )

SELECT DateAdd("y",5,[Date of Loan]) AS Expr1
FROM LoanT;

 

[leanpilar]

use your immediate window to test it helps me a lot
?dateadd("yyyy",5,date())
09.04.2021.
?dateadd("y",5,date())
14.04.2016.

 

[Grumm]

I'm from Belgium and we use the , also as decimal point. I haven't ran into a similar problem yet (Maybe a good time for me to see this post before pulling my hair out)

Most of the time I use calculated fields on the form itself. And if i need SQL i write the full statement in VBA code. I don't like the macro's and wizardry things

 

[sandros]

As it is evident when i use the semicolon, Access adss a "switch \", which returns an error message. :banghead::banghead:
expr1: DateAdd("\y";5;[Datentry])

 

[JHB]

Then do it backwards:
Create a new query, choose the table.
From the menu - choose "SQL View", copy the below into it, (remember to insert your table name):
DateAdd("y",5,[Datentry])

SELECT DateAdd("y",5,[Datentry]) AS Expr1
FROM YourTable;

Then choose "Design View" from the menu - what do you get - is it with a , or a ;?
But when you want to add 5 years, then you need 4 y's, DateAdd("yyyy",5,[Datentry]).

 

[sandros]

Then do it backwards:
Create a new query, choose the table.
From the menu - choose "SQL View", copy the below into it, (remember to insert your table name):
DateAdd("y",5,[Datentry])

Then choose "Design View" from the menu - what do you get - is it with a , or a ;?
But when you want to add 5 years, then you need 4 y's, DateAdd("yyyy",5,[Datentry]).

I thing we have the solution. This tip really worked out. I want to thank you for your time, you really help me at my work!

 

[JHB]

You're welcome, good luck.