select statment

[Julie Chan]

all
How do I assign a select statement to a button in Access VBA?
I don't want to use the wizard and choose a query.
Many thanks
Julie

 

[Dan Artuso]

Hi Julie,
Your terminology is a bit confusing. You can't 'assign' a sql statement to a button.
Do you mean you want to open a query with a click of the button?
Remember, a Select statement *is a query*.

 

[Julie Chan]

This is what I am trying to do. I have made a button on a form and with an
'on click' event assigned the code below.
However the SELECT statement doesn't work. A MAKE TABLE staetment does
though.
Does DoCmd not support the SELECT statement?

Dim SQLString As String
SQLString = "SELECT * from table1" 'or other select statements. I have
also tried using a semi colon at the end.
DoCmd.RunSQL SQLString

 

[M.L. Sco Scofield]

The help file is pretty clear that DoCmd.RunSQL is for action queries. This is why "MAKE TABLE" works and "SELECT" doesn't.

If you just want to open a select query and display a datasheet on the screen, use:

DoCmd.OpenQuery "qselYourQueryName"

If you are building the SQL on-the-fly, you'll either need to save it as a query or use it as the RecordSource for a form displayed
as a datasheet.

If you gives us a little more info about what you are trying to do, we might be able to make some other suggestions.

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, Access MVP, A+
Useful Metric Conversion #16 of 19: 2 monograms = 1 diagram
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[Julie Chan]

Thanks for your help.
This is what I am trying to do:
I have a table with a column of dates and wish to extract all rows with
dates before or after a date I specify.
I was hoping to pop up an input box and assign the answer to a variable
which was referenced in the query.
I guess I will have to do an 'on the fly' query then.
Any advice gratefully accepted.
Julie

The help file is pretty clear that DoCmd.RunSQL is for action queries.

This is why "MAKE TABLE" works and "SELECT" doesn't.

If you just want to open a select query and display a datasheet on the screen, use:

DoCmd.OpenQuery "qselYourQueryName"

If you are building the SQL on-the-fly, you'll either need to save it as a

query or use it as the RecordSource for a form displayed

as a datasheet.

If you gives us a little more info about what you are trying to do, we

might be able to make some other suggestions.

 

[Dan Artuso]

Hi,
You can use parameters in your query. For example, you could put
<[Enter Date] as criteria in your date field to return all dates prior to the one
entered. If you want to assign the results to a 'variable' you should read up on Recordsets
in help. Also, look up Parameter Queries. Both these topics will give you a basic understanding
of what you're asking for.

 

[Julie Chan]

Thanks Dan
This is exactly the type of answer, I am looking for.
I reckon I will be alright now.
Julie

Hi,
You can use parameters in your query. For example, you could put
<[Enter Date] as criteria in your date field to return all dates prior to the one
entered. If you want to assign the results to a 'variable' you should read up on Recordsets
in help. Also, look up Parameter Queries. Both these topics will give you a basic understanding
of what you're asking for.

--
HTH
Dan Artuso, Access MVP

Thanks for your help.
This is what I am trying to do:
I have a table with a column of dates and wish to extract all rows with
dates before or after a date I specify.
I was hoping to pop up an input box and assign the answer to a variable
which was referenced in the query.
I guess I will have to do an 'on the fly' query then.
Any advice gratefully accepted.
Julie


This is why "MAKE TABLE" works and "SELECT" doesn't. as a
query or use it as the RecordSource for a form displayed
might be able to make some other suggestions. with
an