Solved

Syntax Error SQL Query Date Function

Posted on 2010-09-22
12
688 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

839 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