ACCESS FEED: [MS_AccessPros] Re: Need Syntax help please.

Kamis, 20 Agustus 2015

[MS_AccessPros] Re: Need Syntax help please.

Doyce

When you use variables in an SQL statement you have to contatenate them to the string. You can't just include them.

"Between #" & StartDate & "# And #" & EndDate & "# AND "

capeesh?

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

Microsoft Office Access MVP

https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@roadsysinc.com> wrote :

Friends, I can't seem to get the syntax right on this SQL statement. I've tried various combinations of #, & and " around the where line but can't get it to run. I'm trying to insert records into a table using a start date and end date parameters from a form. Also I get nothing in the immediate window when I type ?startdate or ?enddate or ?strSQLCredit.

Dim strSQLCredit As String
    Dim StartDate As Date
    Dim EndDate As Date

    StartDate = Me.Start_Date
    EndDate = Me.End_Date

strSQLCredit = "INSERT INTO tblInterCoTemp ( Account, Credit, Line_Descripton, Invoice ) " & vbCrLf & _
                   "SELECT Manual_Invoice_Detail.Account, ([Qty]*[Price]) AS Credit, [Manual_Invoice_Detail]![Invoice] & "" "" & [Manual_Invoice_Detail]![Description] & "" ("" & [Qty] & "")"" AS [Line Description], Manual_Invoice_Detail.Invoice " & vbCrLf & _
                   "FROM Manual_Invoice_Header INNER JOIN Manual_Invoice_Detail ON Manual_Invoice_Header.Invoice = Manual_Invoice_Detail.Invoice " & vbCrLf & _
                   "WHERE (((Manual_Invoice_Detail.[Account])<>0) AND ((Manual_Invoice_Header.[Invoice Date]) Between #StartDate# And #EndDate# AND ((CustomerGroup([Cust]))=""CNF""));"

Doyce

__._,_.___

Posted by: wrmosca@comcast.net

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)