Solved

Syntax Error SQL Query Date Function

Posted on 2010-09-22
12
700 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 48

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

695 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