vba syntax - using date variables in SQL

[murfeezlaw]

Hi all, i've been working at this for what feels like forever and my head is getting sore from banging it on the desk. Can anyone see what is wrong with this syntax? Any help would be so appreciated. I know it's long. I had it broken up but was getting errors so i put it together so it might be easier to debug.

i'm trying to build a Recordset using the following SQL code and i'm getting an "Expected End of Statement" error on the ")" after dteEndDate & "#"

dteEndDate & "#").

Dim dbs As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim dteStartDate As Date
Dim dteEndDate As Date
Dim dteTowerTreatyStart As Date
Dim strSQL As String

Set dbs = CurrentDb()

dteStartDate = [Forms]![frmPoliciesInForce]![txtStartDate]
dteEndDate = [Forms]![frmPoliciesInForce]![txtEndDate]

strSQL = "SELECT dbo_Quote.CompanyID, dbo_Quote.PolicyID, dbo_Quote.Effective, dbo_Policy.StatusID, dbo_Coverage.CoverageSectionID FROM (((dbo_InvoiceHeader INNER JOIN dbo_InvoiceDetail ON dbo_InvoiceHeader.InvoiceKey_PK = dbo_InvoiceDetail.InvoiceKey_FK) INNER JOIN dbo_Coverage ON dbo_InvoiceDetail.CoverageID = dbo_Coverage.CoverageID) INNER JOIN dbo_Quote ON dbo_InvoiceHeader.QuoteID = dbo_Quote.QuoteID) INNER JOIN dbo_Policy ON dbo_InvoiceHeader.QuoteID = dbo_Policy.QuoteID GROUP BY dbo_Quote.CompanyID, dbo_Quote.PolicyID, dbo_Quote.Effective, dbo_Policy.StatusID, dbo_Coverage.CoverageSectionID HAVING (((dbo_Quote.CompanyID)='RM0028' Or (dbo_Quote.CompanyID)='RM0037' Or (dbo_Quote.CompanyID)='RM0060') AND ((dbo_Quote.Effective) Between #" & dteStartDate & "# And #" & dteEndDate & "#") AND ((dbo_Policy.StatusID)='PIF') AND ((dbo_Coverage.CoverageSectionID)='2'));"

With dbs
Set qdf = .CreateQueryDef("tmpPIF", strSQL)
DoCmd.OpenQuery "tmpPIF"
.QueryDefs.Delete "tmpPIF"
End With
dbs.Close
qdf.Close

TIA
Ted

 

[MakeItSo]

Hi Ted,

it's the simple mistakes that drive us potty:

& dteEndDate & "#[red]"[/red]) AND ((

Remove that red double quote...
;-)

Cheers,
MakeitSo

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SkipVought]

Hi,

1) Your query does not aggregate, so there is no need for a GROUP BY or HAVING clause.

2) You must be VERY CAREFUL with your PARENTHESES using OR and AND. In this case and IN statement would be better.

3) I'd sdvise using progressive concatenation of your string for clarity...

    Dim strSQL
    
    strSQL = "SELECT"
    strSQL = strSQL & "  dbo_Quote.CompanyID"
    strSQL = strSQL & ", dbo_Quote.PolicyID"
    strSQL = strSQL & ", dbo_Quote.Effective"
    strSQL = strSQL & ", dbo_Policy.StatusID"
    strSQL = strSQL & ", dbo_Coverage.CoverageSectionID"
    strSQL = strSQL & vbLf
    strSQL = strSQL & "FROM"
    strSQL = strSQL & "  (((dbo_InvoiceHeader INNER JOIN"
    strSQL = strSQL & "  dbo_InvoiceDetail"
    strSQL = strSQL & "   ON dbo_InvoiceHeader.InvoiceKey_PK = dbo_InvoiceDetail.InvoiceKey_FK)"
    strSQL = strSQL & " INNER JOIN"
    strSQL = strSQL & "  dbo_Coverage"
    strSQL = strSQL & "   ON dbo_InvoiceDetail.CoverageID = dbo_Coverage.CoverageID)"
    strSQL = strSQL & " INNER JOIN"
    strSQL = strSQL & "  dbo_Quote"
    strSQL = strSQL & "   ON dbo_InvoiceHeader.QuoteID = dbo_Quote.QuoteID)"
    strSQL = strSQL & " INNER JOIN"
    strSQL = strSQL & "  dbo_Policy"
    strSQL = strSQL & "   ON dbo_InvoiceHeader.QuoteID = dbo_Policy.QuoteID"
    strSQL = strSQL & vbLf
    strSQL = strSQL & "WHERE dbo_Quote.CompanyID IN ('RM0028','RM0037','RM0060')"
    strSQL = strSQL & "  AND dbo_Quote.Effective Between #" & dteStartDate & "# And #" & dteEndDate & "#"
    strSQL = strSQL & "  AND dbo_Policy.StatusID='PIF'"
    strSQL = strSQL & "  AND dbo_Coverage.CoverageSectionID='2';"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}