DateAdd in query criteria trouble (1 Viewer)

Hi everyone,
I´m having a little trouble with the Date Add function in a query criteria and would appreciate any kind of help:

I have English Ms Access and my local time configuration in spanish (Chile). I use 31-01-2018 for January 1st, 2018.

In a query criteria when i use:

<CDate([Forms]![frm_lease]![txt_lease_renovation_date]) it has no problems and it works fine.

The thing is when in the criteria I´m adding the DateDiff function. I only need to substract one day from [txt_lease_renovation_date] so i use the following:

<DateAdd("d",-1,CDate([Forms]![frm_lease]![txt_lease_renovation_date]))

Then access returns an error : "The expression you entered contains invalid syntax. You may have entered an operand without operator.

I have vba code in other forms of this database where i´m ussing DateAdd and DateDiff and it works fine; but this error can´t figure it out.
I´ve tried single quotation, tried not to use the Cdate function, tried to use brackets.

The strange thing is with the DateAdd function in the query criteria, because the Cdate worked well.

I think this kind of basic problem but i would happy if any of you guys can look at it.

Thanks a lot!

I found the solution:

wrapped the "minus one" in double quotation marks.

<DateAdd("d","-1",CDate([Forms]![frm_contrato_arriendo_renovacion]![txt_fecha_inicial_renovacion]))

I can´t understand why it worked because de DateAdd function requieres the interval as string and the number is a numeric expression, so why to put double quotation and treat the number as a string?

Microsoft support shows this:

Required. Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).
(can´t post the link because im a newbie)


Sorry guys if this is too elemental for you, but i have been scratching my head for a little while.

Thanks anyway!

That's weird.
Are you sure the minus sign is actually a minus sign and not some strange hyphen pasted from the interwebs somewhere?

Very weird.

Day is default unit for arithmetic with dates. Don't need DateAdd function. Simply: date - 1

I wonder if the problem is caused by your regional settings.

Pat Hartman said:

I wonder if the problem is caused by your regional settings.

Click to expand...

It could be a reason, but the dates in my version of ms access work just as any other office program and considers regional configuration.

Some time ago i realized that people with the Spanish version of MS access had trouble with the iif function written in Spanish (sii), you had to write it in English and worked, but if you opened the sql view of the query it would change to spanish and caused an error.
Solution : installed access in the English version.

Have a nice day!

p.perez said:

I use 31-01-2018 for January 1st, 2018.

Click to expand...

Regardless of regional settings, this can't be right. 31-01 is Jan 1st???

MarkK said:

Regardless of regional settings, this can't be right. 31-01 is Jan 1st???

Click to expand...

Yes it is, In our regional configuration either Excel, Outlook or any other program we use day - month - year.

English speaking languages use month-day-year

Yeah, but look at the numbers: 31-01 is Jan 01??? I'm not talking about the order they appear in. There is no way that 31-01 is Jan 01. Or, if it is, how do you express Jan 31?
See what I mean?

MarkK said:

Yeah, but look at the numbers: 31-01 is Jan 01??? I'm not talking about the order they appear in. There is no way that 31-01 is Jan 01. Or, if it is, how do you express Jan 31?
See what I mean?

Click to expand...

You are right, should be 01-01-2018 for january 1st and 31-01-2018 for january 31st

Thanks anyway