SQL Select possible using Code?

  • Thread starter Thread starter Tim Loeffelholz
  • Start date Start date
T

Tim Loeffelholz

How do I run the following SELECT? The txtMyDate is
formatted as a Short Date (same as PunchDate). If it's
not possible to run a simple SELECT from the DoCmd.RunSQL
command, how do you run a simple SELECT? I can run SELECT
INTO with no problem, but with this I get the error under
the code.

I've tried setting the SELECT up in a DIM sqlReport as
String, having sqlReport = the SELECT statement, and then
doing a DoCmd.RunSQL sqlReport, but that doesn't work
either. Is it not possible to run a SELECT within VBA?
I'm not aware of how to pass a parameter (txtMyDate) to an
SQL statement that's built in the query window. I knw I
can pass the parameters using ADO in VB, so maybe this is
just beyond VBA. Any ideas?


Private Sub txtMyDate_AfterUpdate()

DoCmd.RunSQL "SELECT [Punch].[ID], [Punch].[Punch],
[Punch].[PunchDate] FROM Punch WHERE ((([Punch].
[PunchDate])= Forms!frmJavaDreams!txtMyDate))"

End Sub

I get a Run-Time error '2342;A RunSQL action requires an
argument consisting of an SQL statement. I get this when
I run a "SELECT * from Punch" as well!
 
How do I run the following SELECT? The txtMyDate is
formatted as a Short Date (same as PunchDate). If it's
not possible to run a simple SELECT from the DoCmd.RunSQL
command, how do you run a simple SELECT? I can run SELECT
INTO with no problem, but with this I get the error under
the code.

I've tried setting the SELECT up in a DIM sqlReport as
String, having sqlReport = the SELECT statement, and then
doing a DoCmd.RunSQL sqlReport, but that doesn't work
either. Is it not possible to run a SELECT within VBA?
I'm not aware of how to pass a parameter (txtMyDate) to an
SQL statement that's built in the query window. I knw I
can pass the parameters using ADO in VB, so maybe this is
just beyond VBA. Any ideas?


Private Sub txtMyDate_AfterUpdate()

DoCmd.RunSQL "SELECT [Punch].[ID], [Punch].[Punch],
[Punch].[PunchDate] FROM Punch WHERE ((([Punch].
[PunchDate])= Forms!frmJavaDreams!txtMyDate))"

End Sub

I get a Run-Time error '2342;A RunSQL action requires an
argument consisting of an SQL statement. I get this when
I run a "SELECT * from Punch" as well!
Click to expand...

Did you try using DoCmd.RunSQL like this?

strQuery = "SELECT " & [Punch].[ID] & "," & [Punch].[Punch] & "," & _
[Punch].[PunchDate] & "FROM Punch WHERE " & _
[Punch].[PunchDate] & "=" & Forms!frmJavaDreams!txtMyDate

DoCmd.RunSQL (strQuery)
 
RunCommand Method requires an ACTION SQL statement (e.g. an UPDATE SQL
String), not a SELECT statement.

If you are trying to open the DatasheetView of a Query, you need to use the
OpenQuery Method.
 
My guess is you will also have to reformat the query so that the date
parameter is wrapped in the # delimiter

strSQL = "SELECT [Punch].[ID], " _
& ", [Punch].[Punch] " _
& ", [Punch].[PunchDate] " _
& "FROM Punch "
& "WHERE [Punch].[PunchDate]=#" &
Forms!frmJavaDreams!txtMyDate & "#"

--
HTH

Dale Fye


message How do I run the following SELECT? The txtMyDate is
formatted as a Short Date (same as PunchDate). If it's
not possible to run a simple SELECT from the DoCmd.RunSQL
command, how do you run a simple SELECT? I can run SELECT
INTO with no problem, but with this I get the error under
the code.

I've tried setting the SELECT up in a DIM sqlReport as
String, having sqlReport = the SELECT statement, and then
doing a DoCmd.RunSQL sqlReport, but that doesn't work
either. Is it not possible to run a SELECT within VBA?
I'm not aware of how to pass a parameter (txtMyDate) to an
SQL statement that's built in the query window. I knw I
can pass the parameters using ADO in VB, so maybe this is
just beyond VBA. Any ideas?


Private Sub txtMyDate_AfterUpdate()

DoCmd.RunSQL "SELECT [Punch].[ID], [Punch].[Punch],
[Punch].[PunchDate] FROM Punch WHERE ((([Punch].
[PunchDate])= Forms!frmJavaDreams!txtMyDate))"

End Sub

I get a Run-Time error '2342;A RunSQL action requires an
argument consisting of an SQL statement. I get this when
I run a "SELECT * from Punch" as well!
 
Back
Top