DateAdd Function in an SQL statement 1

[Trudye]

I cannot get my SQL statement to work. I can't seem to get the DateAdd function right. I've tried 'd', "d", dd, d and even '" & dd & "', nothing seems to work.
I am on my last few strand of hair please help.

Thanx
Trudye

strSQL = "SELECT Clients.Active, Deals.To, Deals.[Contract Status], " _
& "Deals.From, Deals.DealID, Deals.ClientId, Deals.Player, " _
& "Deals.Company, Deals.[Contract Status] " _
& "FROM Clients INNER JOIN Deals ON Clients.ID = Deals.ClientId " _
& "WHERE (Clients.Active = -1) " _
& "And ((Deals.[Contract Status]) <> '" & Declined & "' " _
& "And (Deals.[Contract Status]) <> '" & Can & "' " _
& "And (Deals.[Contract Status]) <> '" & CD & "') " _
& "And ((Deals.To <= DateAdd("d", 60, Date))" _
& "ORDER BY Deals.To DESC

 

[JonFer]

You are missing parentheses for the Date function:

Deals.To<=DateAdd("d", 60, Date() )


John

http://www.interautomateconsulting.com

 

[r937]

DateAdd("d", 60, Date)

is Date here supposed to be the DATE() function, or is it a column in the table?

if the former, try DateAdd("d", 60, Date())

if the former, try DateAdd("d", 60, [Date])


rudy

http://r937.com/

SQL Consulting

 

[Trudye]

Hi Jonfer and r937 thanks for responding on a Sunday no less. But I tried it with the date(). But I keep getting the error (which I should have stated the first time), Expecting End of Statement and it highlights the "d".


Trudye

 

[PHV]

Replace this:
& "And ((Deals.To <= DateAdd("d", 60, Date))" _
By this:
& "And ((Deals.To <= DateAdd('d', 60, Date()))" _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

You need 3 closing parentheses after Date() and a space before "ORDER BY" so:

"And ((Deals.To <= DateAdd("d", 60, Date()))) "

Or use this with fewer parenthese pairs:

"And Deals.To <= DateAdd("d", 60, Date()) "


You are using "Declined", "Can", and "CD" as string variables in your module, right?

John

http://www.interautomateconsulting.com

 

[PHV]

Another try if my previous don't work:
Replace this:
& "WHERE (Clients.Active = -1) " _
& "And ((Deals.[Contract Status]) <> '" & Declined & "' " _
& "And (Deals.[Contract Status]) <> '" & Can & "' " _
& "And (Deals.[Contract Status]) <> '" & CD & "') " _
& "And ((Deals.To <= DateAdd("d", 60, Date))" _
& "ORDER BY Deals.To DESC"
By this:
& "WHERE Clients.Active = -1" _
& " And Deals.[Contract Status] <> '" & Declined & "'" _
& " And Deals.[Contract Status] <> '" & Can & "'" _
& " And Deals.[Contract Status] <> '" & CD & "'" _
& " And Deals.To<=Format(DateAdd('d',60,Date()),'m/dd/yyyy')" _
& " ORDER BY Deals.To DESC"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Trudye]

Hi Guys ok here's what happened. I tried it the first two ways and got errors like

"Run Time Error 3075; Syntax Error Missing Operator in Query expression"

But I'll be doggoned if PHV's last attempt didn't work. I almost didn't try it after the first two failed. I couldn't understand how the format function was a factor.

Ok Guys what's the rule here I have two more Queries to write and if I have a rule of thumb it will go a lot smoother. Not that I'll get it right but it will go smoother.

Thank both of you so much for hanging in there with me. I thank you and my hairdresser thanks you.

Be well,
Trudye

OBTW, if you will indulge me yet again. I know that how to assign this result set to a form (i.e. forms![form-name].recordset= strSQL. Can I do the same with a report, even if this code is on a Form_open event?

 

[r937]

the rule is to watch out for quote nesting

rudy

http://r937.com/

SQL Consulting