[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Syntax Error SQL Query Date Function

Posted on 2010-09-22
12
Medium Priority
?
747 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 2000 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
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

650 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