Solved

Syntax Error SQL Query Date Function

Posted on 2010-09-22
12
676 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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 119

Accepted Solution

by:
Rey Obrero 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now