Solved

Syntax Error SQL Query Date Function

Posted on 2010-09-22
12
697 Views
Last Modified: 2012-05-10
I am getting a syntax error in this line

SQL = SQL & "WHERE((Table1.SalesDate) Between Date() And DateAdd("m"-1,Date()));"

Any help would be appreciated!

Private Sub cmdProcess_Click()
'Date_DateFellDuringLast30Or31Days
Dim SQL
SQL = "DELETE Table1.SalesDate FROM Table1 "
SQL = SQL & "WHERE((Table1.SalesDate) Between Date() And DateAdd("m"-1,Date()));"
CurrentDb.Execute SQL, dbFailOnError

MsgBox "Complete", vbOKOnly, "Process Complete"
End Sub

Open in new window

0
Comment
Question by:ca1358
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 3

Expert Comment

by:ddancer99
ID: 33736634
Try this syntax

SQL = "DELETE Table1.SalesDate FROM Table1 "
SQL = SQL & "WHERE((Table1.SalesDate) Between '" & Date() & "' And '" & DateAdd("m"-1,Date()) & "');"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33736650
try

SQL = "DELETE  FROM Table1 "
SQL = SQL & "WHERE((Table1.SalesDate) Between Date() And DateAdd('m'-1,Date()));"


or


SQL = "DELETE  FROM Table1 "
SQL = SQL & "WHERE((Table1.SalesDate) Between Date() And DateAdd(""m""-1,Date()));"
0
 

Author Comment

by:ca1358
ID: 33736776
ddancer99: on yours I received Compile error: Argument not optional

capricorn1: on your first one I received Run time error 3075 Wrong number of arguments used with function in query expression '((Table1.SalesDate) Between Date() And DateAdd('m'-1,Date())'

Second one
Run time error 3075 Wrong number of arguments used with function in query expression '((Table1.SalesDate) Between Date() And DateAdd("m"-1,Date())'
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33736861
where are you running the codes? in access or ?


try

SQL = "DELETE  * FROM Table1 "
SQL = SQL & "WHERE((Table1.SalesDate) Between Date() And DateAdd('m'-1,Date()));"


or


SQL = "DELETE * FROM Table1 "
SQL = SQL & "WHERE((Table1.SalesDate) Between Date() And DateAdd(""m""-1,Date()));"
0
 

Author Comment

by:ca1358
ID: 33736940
Access Form Connected to a command button
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33736978
you need a space after Where



try

SQL = "DELETE  * FROM Table1 "
SQL = SQL & "WHERE ((Table1.SalesDate) Between Date() And DateAdd('m'-1,Date()));"


or


SQL = "DELETE * FROM Table1 "
SQL = SQL & "WHERE ((Table1.SalesDate) Between Date() And DateAdd(""m""-1,Date()));"
0
 
LVL 3

Expert Comment

by:ddancer99
ID: 33737140
Sorry, forgot that in access you must surround dates with #,  you can try this syntax

SQL = "DELETE * FROM Table1 "
SQL = SQL & WHERE  Table1.SalesDate Between #" & Date() & "# And #" & DateAdd(""m""-1,Date()) & "#"
0
 

Author Comment

by:ca1358
ID: 33737506
It does not like DateAdd
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 33737543
try this

SQL = "DELETE  * FROM Table1 "
SQL = SQL & "WHERE Table1.SalesDate Between dateserial(year(Date()),month(date()),day(date())) And dateserial(year(Date()),month(date()) -1,day(date()))"

0
 

Author Comment

by:ca1358
ID: 33737595
Thanks, it worked.

0
 

Author Closing Comment

by:ca1358
ID: 33737671
Thanks again!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33738070
Actually, I think the problem was that everyone seemed to be missing the fact that the first comma was missing from the DateAdd function, in addition to a couple of unnecessary sets of parenthesis.

Change:

SQL = SQL & "WHERE((Table1.SalesDate) Between Date() And DateAdd("m"-1,Date()));"

to:

SQL = SQL & "WHERE Table1.SalesDate Between Date() And DateAdd(""m"", -1,Date());"
or possibly:
SQL = SQL & "WHERE Table1.SalesDate Between Date() And DateAdd('m', -1,Date());"
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question