SQL Query to VBA and display result in Form

Question

So I have a few Queries already written and my goal is to have a user input certain fields that would change the way the Query is returned, basically having the user change 2 or 3 parameters of the original Query.
First, I'm having problems getting a Query to execute in VBA:

Private Sub QResultButton_Click()
DoCmd.OpenQuery (Readings2009, acViewPreview,acReadOnly)
End Sub

[Readings2009 is a Query I created in Access] This returns a syntax error.
I have also see this done:

strSQL = "SELECT Readings2009.id, Readings2009.othercolumn, Readings2009.another 
          WHERE Readings2009.something > today() ..."
DoCmd.RunSQL (strSQL)

I can't get either to work. Any help would be greatly appreciated.

Fionnuala · Accepted Answer · 2009-03-25 22:07:14Z

0

In this example, the name of the query must be a string, that is, it must be in quotes:

DoCmd.OpenQuery "Readings2009"

See: http://msdn.microsoft.com/en-us/library/aa220295(office.11).aspx

The other example is for running action queries,not for opening queries to view them.

It would be best to create a form and to use that to display your query.

edited Mar 25, 2009 at 22:07

answered Mar 25, 2009 at 21:38

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nick Sinas Over a year ago

Either of the following still returns a syntax error. DoCmd.OpenQuery "Readings2009" or DoCmd.OpenQuery ("Readings2009", acViewPreview,acReadOnly) do I need to define this as a string first?

Nick Sinas Over a year ago

If you could post an example of how to properly run a Query in VBA that would really be all I need.

Fionnuala Over a year ago

The line I posted should work. Does the query Readings2009 work when you open it manually.

David-W-Fenton Over a year ago

Well, for one, there is no Today() function in Jet/Access. You want Date() instead.

Nick Sinas Over a year ago

Ok I got the Remou's suggestion to work but I need to display this Query in a new form. Any ideas?

|

Collectives™ on Stack Overflow

SQL Query to VBA and display result in Form

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related