Date Query (1 Viewer)

[Dumferling]

I cannot for the life of me see what I am doing wrong here. I am using Access 2016 and filtering records for 3 months from todays date.
I put in =DateAdd("m", 3, Date()) in the criteria of a query (date field) and no matter what I do it tells me that I have invalid syntax. Every site I can find tells me this is correct but Access doesn't agree. What am I doing wrong?

 

[Minty]

That will only return records that are exactly 3 months away, and if the date field has a time value as well they won't match either.
< =DateAdd("m", 3, Date()) would get you everything up to 3 months away.

 

[bob fitz]

DateAdd() will return a date value 3 months from today.

I think you need to use for your criteria something like:

Between Date() AND DateAdd("m", 3, Date())

 

[Dumferling]

Yes, I know that but my issue is not the dates returned - its that I can't get the filter to work. It tell me Invalid syntax - operand without an operator. I am doing something really dim, I am sure. I just can't see what it is

 

[Dumferling]

Same result. It highlights the Date() part as in the screenshot above. I have replaced this with hard dates and a variety of other date information and I get the same result every time.

 

[bob fitz]

View attachment 81489
Same result. It highlights the Date() part as in the screenshot above. I have replaced this with hard dates and a variety of other date information and I get the same result every time.

If you are using the expression as the criteria in a query, then perhaps you could post a copy of the SQL statement.

 

[Dumferling]

Access won't allow me to get to a SQL view. I can't get the cursor out of that expression. It just tells me invalid syntax

 

[Dumferling]

SELECT tblContracts.ContractName, tblContracts.EndDate, tblContracts.EndDateCheck, tblContracts.BusinessUnit, tblContracts.BusinessOwner
FROM tblContracts
WHERE (((tblContracts.EndDate)=DateAdd("m", 3, Date())) AND ((tblContracts.EndDateCheck)="End Date") AND ((tblContracts.BusinessUnit)=[Forms]![frmrptMancoCriteria]![ctrlBusinessUnit])) OR (((tblContracts.EndDateCheck)="End Date And Extension"))
ORDER BY tblContracts.EndDate;

I had to put the expression between brackets and rewrite the SQL query by changing the syntax directly

 

[Minty]

If you can't replace that expression with a hardcoded date then something else is wrong.
You datefield isn't called Date is it?

Edit : Obviously not

 

[Dumferling]

EndDate is the date field I am calculating from

 

[Dumferling]

The grid looks like this

 

[Minty]

That all looks okay to me.

Take a back up of your database then try a compact and repair.
Copy the SQL you wrote earlier and paste it into a new blank query - see if that works.

 

[HalloweenWeed]

Try making a new column in your query:

Expr1: DateAdd("m", 3, Date())

Then in the conditional of your [FieldName]:

Between Date() AND Expr1

 

[bob fitz]

Dumferling

Did you try my suggestion in post #3

 

[Dumferling]

Dumferling

Did you try my suggestion in post #3

Yes, I did. It gives exactly the same error. I am beginning to think I have a corrupted database. I have tried Compact and Repair as well. No difference.
Date Add seems to be problematic because I tried it as s default in a table this morning (split database so this was in the BE) and got the same result:

Now running a general repair on MS Office to see if that makes any difference

 

[Minty]

It sounds like a corruption issue.

Create a new blank database - in the immediate window (Ctrl+G in the VBA editor) try
? DateAdd("m",3,Date()))

You should get

So yes your syntax is correct as you surmised.

 

[namliam]

Highly possible that you have a module someplace that is not compilable.
I.e. it has some coding problem.... try compiling your modules to see if this is the problem.

 

[arnelgp]

did you put the Date Criteria manually? of course you did.
some non-English version of MSO uses ; as delimiter character (not comma ,).

 

[Dumferling]

It sounds like a corruption issue.

Create a new blank database - in the immediate window (Ctrl+G in the VBA editor) try
? DateAdd("m",3,Date()))

You should get
View attachment 81527

So yes your syntax is correct as you surmised.

In a blank database this worked fine. Never used the immediate window before.

 

[arnelgp]

if it is working, then maybe exporting all object to this New DB will solve it?