damianb123
asked on
Problem with sql query and variable in access
Hi,
I have written some code which basically takes the value of a query from a dropdown, as well as values from 2 listboxes which contain the start range and end range for an ID..... This is the code I have:
Set rs = CurrentDb.OpenRecordset("S ELECT * FROM " & QueryName & " WHERE ID BETWEEN " & firstidnum & " AND " & secondidnum & "")
It keeps giving me a "Syntax error in FROM clause"
Basically the value QueryName is from a dropdown listbox which contains all the queries, I then want to do a BETWEEN statement, so only the ID's selected via firstidnum and secondidnum are output...
Can anyone help?
Cheers
Damian
I have written some code which basically takes the value of a query from a dropdown, as well as values from 2 listboxes which contain the start range and end range for an ID..... This is the code I have:
Dim rs As DAO.Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim Counter As Long
Dim QueryName As String
Dim SaveToPath As String
Dim TemplatePath As String
Dim firstidnum As String
Dim secondidnum As String
firstidnum = firstid.Value
secondidnum = secondid.Value
QueryName = template_dropdown.Value
SaveToPath = "\\server\share\Database\Generated Reports\" & QueryName & "_"
If QueryName = "DCC" Then
TemplatePath = "\\server\share\Database\comments-template-modified.xlsx"
ElseIf QueryName = "DCC Adult" Then
TemplatePath = "\\server\share\Database\comments-template-modified.xlsx"
ElseIf QueryName = "Healthcare Report" Then
TemplatePath = "\\server\share\Database\comments-template-modified.xlsx"
Else
TemplatePath = "\\server\share\Database\comments-template.xlsx"
End If
Set rs = CurrentDb.OpenRecordset("SELECT * FROM " & QueryName & " WHERE ID BETWEEN " & firstidnum & " AND " & secondidnum & "")
Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
Set xlWb = xlApp.Workbooks.Add(TemplatePath)
Set xlWs = xlWb.ActiveSheet
With xlWs
For Counter = 0 To rs.Fields.Count - 1
.Cells(2, 1).CopyFromRecordset rs
Next
' .Cells(2, 1).CopyFromRecordset rs
End With
xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xlsx"
xlWb.Close False
xlApp.DisplayAlerts = True
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
rs.Close
Set rs = Nothing
MsgBox "The select report has been generated..."
The problem is with this line here:Set rs = CurrentDb.OpenRecordset("S
It keeps giving me a "Syntax error in FROM clause"
Basically the value QueryName is from a dropdown listbox which contains all the queries, I then want to do a BETWEEN statement, so only the ID's selected via firstidnum and secondidnum are output...
Can anyone help?
Cheers
Damian
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
WELL DONE! Perfect, just what I needed my friend.
Damian
Damian
ASKER
Damian