SQL Problems....

[diesel_medic]

Hi,

I've been trying to get the sub bellow, without much suceess.

Private Sub cmdSearchDate_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim StartDate As Date
Dim EndDate As Date

If IsNothing(txtStartDate) Or IsNothing(txtEndDate) Then
MsgBox "You must input a start and end date", vbOKOnly, "Katymin's Korner Accounts Administraion System"
txtStartDate.SetFocus
Exit Sub
End If

StartDate = txtStartDate
EndDate = txtEndDate

strSQL = "SELECT Sum([tblTransactionsSummary].ExVATPrice) AS [SalesTotal]"
strSQL = strSQL + " FROM [tblTransactionsSummary]"
strSQL = strSQL + " WHERE ((([tblTransactionsSummary].SaleDate)"
strSQL = strSQL + " Between [StartDate] And [EndDate]"
strSQL = strSQL + " And (([tblTransactionsSummary].Confirmed)=True))"

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

With rst
.MoveFirst
txtTotalSales = ![SalesTotal]
End With

Set rst = Nothing

End Sub

The SQL statement works when I put it the Access Query Builder and I get the desired results, but when I try it in VBA, I keep getting a runtime error 3061. Any help would be appreciated.

 

[pdx_man]

Try ...

strSQL = strSQL + " Between #" & [StartDate] & "# And #" & [EndDate] & "#"

 

[ajetrumpet]

Private Sub cmdSearchDate_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim StartDate As Date
Dim EndDate As Date

If IsNothing([txtStartDate]) Or IsNothing([txtEndDate]) Then
MsgBox "You must input a start and end date", vbOKOnly, "Katymin's Korner Accounts Administraion System"
txtStartDate.SetFocus
Exit Sub
End If

StartDate = txtStartDate
EndDate = txtEndDate

strSQL = "SELECT Sum([tblTransactionsSummary].ExVATPrice) AS [SalesTotal]"
strSQL = strSQL + " FROM [tblTransactionsSummary]"
strSQL = strSQL + " WHERE ((([tblTransactionsSummary].SaleDate)"
strSQL = strSQL + " Between [StartDate] And [EndDate]"<---Access is probably reading these as fields, and not parameters (they are parameters, right??)
strSQL = strSQL + " And (([tblTransactionsSummary].Confirmed)=True))"

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

With rst
.MoveFirst
txtTotalSales = ![SalesTotal]
End With

Set rst = Nothing

End Sub

The SQL statement works when I put it the Access Query Builder and I get the desired results, but when I try it in VBA, I keep getting a runtime error 3061. Any help would be appreciated.

I usually get 3061 when I have used brackets incorrectly...

 

[boblarson]

...
StartDate = txtStartDate [color=red]' see these are variables and so they need to be referenced correctly[/color]
EndDate = txtEndDate

strSQL = "SELECT Sum([tblTransactionsSummary].ExVATPrice) AS [SalesTotal]"
strSQL = strSQL + " FROM [tblTransactionsSummary]"
strSQL = strSQL + " WHERE ((([tblTransactionsSummary].SaleDate)"
[color=red]strSQL = strSQL + " Between #" & StartDate & "# And #" & EndDate [/color]& "#"
strSQL = strSQL + " And (([tblTransactionsSummary].Confirmed)=True))"

A slight variation of PDX Man's code as it has to be changed as they are variables and not fields.

 

[diesel_medic]

Thanks for that guys,

I've tried the changes and I now don't get any error messages, but it won't produce any results! A bit more thought required I think!