|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
April 26th, 2006, 06:26 AM
|
|||
|
|||
|
DoCmd.RunSQL !!!!!!!!
Can someone please tell me why when I try to run this code:
<code>DoCmd.RunSQL "SELECT * FROM MyTable WHERE MyTable.Date BETWEEN '" & startDate & "' AND '" & endDate & "';"</code> I get the error from MS Access - 'A RunSQL action requires an argument consisting of an SQL statement' I've tried this a billion different ways, including assigning the string to a variable and referencing that, but it no work  HELP!
|
|
#2
April 26th, 2006, 06:38 AM
|
||||
|
||||
|
try this
Code:
DoCmd.RunSQL("SELECT * FROM MyTable WHERE MyTable.Date BETWEEN '" & startDate & "' AND '" & endDate & "';")
__________________
Nothing is Impossible bcoz IMPOSSIBLE itself says.. I M POSSIBLE........................ Be cool !!!!!!!!
|
|
#3
April 26th, 2006, 06:43 AM
|
||||
|
||||
|
ohhhhhh sorry u can not use select statement with this ,...only insert ,update and delete the transaction statemnt only.....
to do this u can use Dlookup()
|
|
#4
April 26th, 2006, 06:45 AM
|
||||
|
||||
|
docmd.runsql will work for update/edit/delete queries
if you want to select values, use an ADODB.Recordset here is a quick example Code:
Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset sql = "SELECT * FROM TABLE WHERE ...." rs.Open sql, CurrentProject.Connection while NOT rs.EOF . . . rs.MoveNext wend rs.Close Set rs = Nothing hope this helps
__________________
Look! Its a ShemZilla    
|
|
#5
April 26th, 2006, 07:04 AM
|
|||
|
|||
|
Ok, I understand what you have done, except I'm not sure what to stick in the while loop.
Running the code as it is, exits the while loop straight away, i.e. the End of file has been reached immediately. But no exceptions.. thanks for your help.
|
|
#7
April 26th, 2006, 07:10 AM
|
|||
|
|||
|
all i want to do is return all columns from the table where the date value is between the startDate and endDate combo boxes. That's it.
|
|
#8
April 26th, 2006, 07:14 AM
|
||||
|
||||
|
yes, but you need to do something with that columns, like
adding it to a listbox or displaying it somewhere, once its selected it needs to go somewhere right?
|
|
#9
April 26th, 2006, 07:22 AM
|
|||
|
|||
|
ok, I see what you mean.
I have added a listbox called results to my form. how would I add records retrieved to the listbox then?
|
|
#10
April 26th, 2006, 07:25 AM
|
||||
|
||||
|
you can try something like this
Code:
while NOT rs.EOF
listBox.Items.Add(rs("field1"))
rs.MoveNext
wend
hope this helps
|
|
#11
April 26th, 2006, 07:38 AM
|
|||
|
|||
|
Here's my code:
Dim results as ListBox results = [Forms]![Form1]![results] SQL = "SELECT * FROM MyTable WHERE MyTable.Date BETWEEN " & startDate & " AND " & endDate & ";" rs.Open SQL, CurrentProject.Connection While Not rs.EOF results.AddItem ((rs("field1"))) rs.MoveNext Wend rs.Close Set rs = Nothing I get variable in with block not set, I'm not sure how to link the listbox on the form to the vbcode..
|
|
#12
April 26th, 2006, 07:50 AM
|
||||
|
||||
|
ok, try this code
Code:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Me.results.RowSourceType = "Value List"
SQL = "SELECT TriStar_FirstName FROM TriStar"
rs.Open SQL, CurrentProject.Connection
While Not rs.EOF
Me.results.AddItem (rs("YourField"))
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
make sure to change YourField to a field you want to add in the list box
|
|
| Viewing: ASP Free Forums > Programming > Visual Basic Programming > DoCmd.RunSQL !!!!!!!! |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
