VBA query trouble

I think I'm going blind.   I have programmed SQL statements many times in the 
past, and for some reason I simply cannot get anything to work right now...  
I've reduced my code down to the most basic statement in order to get some 
kind of error code I can comprehend.  My code, at the moment, is:

docmd.runsql "Select * from MLS Sales"

Can't get much simpler than that.   However, when I try to run that command 
I get an error that says "A RunSQL action requires and argument consisting of 
an SQL Statement."

Originally I did not use the "*" and had all my field identified.   When I 
did this I got the error that says "the Select statement includes a reserved 
word or an argument name that is misspelled or missing or the punctuation is 
incorrect."

This used to be a very simple thing to do.  Can anyone give some thoughts on 
this?


0
Utf
3/8/2010 5:53:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

4 Replies
465 Views

Similar Articles

[PageSpeed] 54

"dave_b" <daveb@discussions.microsoft.com> wrote in message 
news:E2C1CAEA-8638-4FE2-ABD8-A270A0A12050@microsoft.com...
>I think I'm going blind.   I have programmed SQL statements many times in 
>the
> past, and for some reason I simply cannot get anything to work right 
> now...
> I've reduced my code down to the most basic statement in order to get some
> kind of error code I can comprehend.  My code, at the moment, is:
>
> docmd.runsql "Select * from MLS Sales"
>
> Can't get much simpler than that.   However, when I try to run that 
> command
> I get an error that says "A RunSQL action requires and argument consisting 
> of
> an SQL Statement."
>
> Originally I did not use the "*" and had all my field identified.   When I
> did this I got the error that says "the Select statement includes a 
> reserved
> word or an argument name that is misspelled or missing or the punctuation 
> is
> incorrect."
>
> This used to be a very simple thing to do.  Can anyone give some thoughts 
> on
> this?


You cannot "RunSQL" a SELECT query;  only an action query.  So this wouldn't 
work even if your SQL statement were correct.  As it happens, if your table 
is named "MLS Sales", your SQL statement is incorrect:  you need to enclose 
that name in square brackets ([]):

    SELECT * FROM [MLS Sales]

That correction won't make your RunSQL work, though, because it's a SELECT 
query.  What do you intend to do?  Unfortunately, you can't open a SQL 
statement as a datasheet;  you would have to use a stored query for that, 
and use DoCmd.OpenQuery.  On the other hand, if you want to get a value from 
this query and manipulate it in code, you could open a recordset on the SQL 
statement.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

0
Dirk
3/8/2010 6:20:42 PM
dave_b wrote:

>I think I'm going blind.   I have programmed SQL statements many times in the 
>past, and for some reason I simply cannot get anything to work right now...  
>I've reduced my code down to the most basic statement in order to get some 
>kind of error code I can comprehend.  My code, at the moment, is:
>
>docmd.runsql "Select * from MLS Sales"
>
>Can't get much simpler than that.   However, when I try to run that command 
>I get an error that says "A RunSQL action requires and argument consisting of 
>an SQL Statement."
>
>Originally I did not use the "*" and had all my field identified.   When I 
>did this I got the error that says "the Select statement includes a reserved 
>word or an argument name that is misspelled or missing or the punctuation is 
>incorrect."
>
>This used to be a very simple thing to do.
>

That's not supposed to work, RunSQL inly operates on action
type queries (Insert Into, Update, Delete, etc), not Select
queries.  You never said what you are trying to accomplish
so I can't guess what you should be using.

-- 
Marsh
MVP [MS Access]
0
Marshall
3/8/2010 6:38:54 PM
Try "Select [MLS Sales].* from [MLS Sales]"

"dave_b" wrote:

> I think I'm going blind.   I have programmed SQL statements many times in the 
> past, and for some reason I simply cannot get anything to work right now...  
> I've reduced my code down to the most basic statement in order to get some 
> kind of error code I can comprehend.  My code, at the moment, is:
> 
> docmd.runsql "Select * from MLS Sales"
> 
> Can't get much simpler than that.   However, when I try to run that command 
> I get an error that says "A RunSQL action requires and argument consisting of 
> an SQL Statement."
> 
> Originally I did not use the "*" and had all my field identified.   When I 
> did this I got the error that says "the Select statement includes a reserved 
> word or an argument name that is misspelled or missing or the punctuation is 
> incorrect."
> 
> This used to be a very simple thing to do.  Can anyone give some thoughts on 
> this?
> 
> 
0
Utf
3/8/2010 6:54:01 PM
On 08/03/2010 17:53:05, dave_b wrote:
> I think I'm going blind. I have programmed SQL statements many times in
> the past, and for some reason I simply cannot get anything to work right
> now... I've reduced my code down to the most basic statement in order to
> get some kind of error code I can comprehend. My code, at the moment, is:
> 
> docmd.runsql "Select * from MLS Sales"
> 
> Can't get much simpler than that. However, when I try to run that command
> I get an error that says "A RunSQL action requires and argument consisting
> of an SQL Statement."
> 
> Originally I did not use the "*" and had all my field identified. When I
> did this I got the error that says "the Select statement includes a
> reserved word or an argument name that is misspelled or missing or the
> punctuation is incorrect."
> 
> This used to be a very simple thing to do. Can anyone give some thoughts
> on this?
> 
> 
> 

Bad practice to have a space in a table (or field name IMO. Have to add
square brackets round everything as the space between Sheet and 1 could be
interpreted to lead onto other sthings like SELECT * FROM Sheet WHERE .......

Try      "Select * from [MLS Sales]"
0
Phil
3/8/2010 11:25:22 PM
Reply:

Similar Artilces:

Random List from Query
For accrediation purposes we have to audit a random 10% of our cases each quarter I was wondering if there is a way to run a query or report that would let me show a random 10% of cases for a specific provider. ...

CRM customization query
Hi, I have a customization query, I want to see the number of records available before I am getting the data for reports. I need this because, I want to generate a report by applying some filter criteria but at the same time I want the resultant record number below a specific limit. On the advance find, after I am applying the filters we get hte resultant data by clicking on the Find button. This takes us to the page where we can see the data. Now I want to add a new button on the Advance find screen on click of which I can get the record count of the result set for the filter i have ...

Trouble with Filters on a large file
Have a huge file, 29000 rows with 6 columns. Ran a auto-filter but the filter stops at the "1001" row. Thought that I was quite familiar with auto-filters until this. Can anyone please help tell me what's worng or what I should do?? Thanks in Advance Michael Think 1000 is the limit for items to display in the autofilter droplist Try Debra Dalgleish's coverage on this at her page: http://www.contextures.com/xlautofilter02.html#Limits for a possible workaround (Look under "Limits to Dropdown Lists") -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xd...

Downloading trouble from TD Waterhouse into Money '04
Is anybody else having trouble downloading transactions from TD Waterhouse into Money 2004? I've been doing it for years and it stopped working on Friday when I was also having trouble running Money updates. Those now work but Waterhouse downloads will not. It tells me there is a problem with the data downloaded, contact the bank... I've been working with Waterhouse since Friday and so far it still doesn't work :( ...

Copy/Paste VBA
Ich will eine ganze Visio Seite kopieren mit allen Verbindungen zwischen den Shape und deren Verklebungen (glue Ich habs versucht mit select all, group , copy paste und ungroup auch mit der Option bei copy/paste das es an der orignalposition positioniert werden sol abe 1) die Positionen stimmten nicht überei 2) die Verbindungen waren eingetragen aber nicht verglue Was nun einer eine Version die funktioniert und einfach is thx, mfg Daniel H. You might try Copy / Paste without doing the Group / Ungroup. Are you working with a specific set of shapes? Some Visio shapes have protections t...

Trouble with time formats
Any help appreciated! I need to work out the average speed it would take to cover a certain distance. EG. If you covered 10 miles in 28 minutes what would teh average speed be... Column A: 10 miles Column B: 28 minutes Column C: Would return 21.43mph. I then want to to add time increments of say 15 seconds in rows so that I can see that if it took 28 minutes, 45 seconds, the average speed column would return 20.87mph. I can work it out by entering 28.75 for the time (or 70.50 for 1h,10m,30s), but I want to format it as time, i.e: 00:28:45 (or 01:10:30) Is this possible? Thanks...

VBA to count periods in a single cell
I am looking to count the number of periods within a single cell using vba. For instance if I have 4 cells that contain the following: 5M09-0000700.01.10 5M09-0000700.01.10.10 5M09-0000700.01.10.10.10 5M09-0000700.01.10.10.20 Thus the output of the code for the first cell should be '2', the second cell output would be '3', and the third/fourht cells would be '4' because that many period characters were encountered. Data within these cells will contain letters, numbers, periods, a few spaces and the "-" dash symbol. Any help is greatly appre...

How to get looping VBA code to respond to events on worksheet
Hello, I have some code of the form do while not g_Paused bunch of calls ... loop where I'd like the variable g_Paused to be toggled by a button on the worksheet. The only way I can seem to get the code to respond to the button click is to use do while not g_Paused bunch of calls ... ThisWorkbook.Worksheets("Name").Activate DoEvents loop But this is pretty slow and causes the sheet to flicker...is there a better method? PS I'm using buttons from the Control Toolbox Thank you. ...

New to RMS: Having trouble setting up 2nd terminal.....HELP
We have an existing customer who has 1 server and 1 POS terminal. I have recently installed a 2nd terminal and am trying to match the settings and templates of the register 1 to the new register 2. The problem is with the on-screen keyboard. I've chosen the option to use the touchscreen keyboard in manager for register 2 and picked the template for the pre-configured keyboard layout; however, register 2 just refuses to display the template like register 1 does. I know ive got to be missing something. By the way, i forgot to mention that yes, i have enabled the touchscreen keyboard ...

a count field in a query?
Hello, I have a query in which I would like to create a field which increments by 1 for each record selected, so if there are 10 records selected by the query, this column would show numbers 1 to 10. Ideally I would like to have some text in front of each number, say invoice1, invoice2 etc. From a previous posting I realise that this is frowned upon but it would be the simplest solution for my problem! How can I do this? Thanks Geoff On Mon, 03 Dec 2007 08:54:00 +0000, Geoff Cox <<>> wrote: >Hello, > >I have a query in which I would like to create a field which >...

Please help Password Trouble
I am using outlook 2000 on a DSL line on XP Home. I have chosen to not save my password, and the problem that I am having is that everytime I go to check my mail it makes me type in my password. There must be a way (like in express) that allows you to just type in your password the first time you log into outlook and keeps you logged in until you decide to log out?? Thanks for your help!!!! -- Virtualliance, Inc. Mark Needham 7 Kimball Lane Bldg A Lynnfield, Ma 01940 T 001-781-224-4700 F 001-781-224-2414 C 001-617-799-4597 www.virtuallianceinc.com mneedham@virtuallianceinc.com im: vaincmar...

Parameter query in Access 2003 report
I want to create a parameter query that asks the user to select one, many, or all values from a field when opening a report. I would like the selection to be available from a list, radio box, or pull down menu to minimize spelling errors, etc. Unfortunately the list is of names which number about 100. Can this be done? If so, could someone please point me to the help pages that describe this process? My solution seems clunky but it is the only way I can see how to do it. Any suggestions as to how else might I best approach this? Thanks in advance Debbie See my sample database that shows ...

Generate selective query results
Who can give me advice? In a table I am storing the information of apartments and their total surface area in square meters within a building. When I select an apartment within a building in a form, I would like to print a report of maximum 10 equivalent apartments of approximately the same size (surface area ± 2 m2). Of these apartments I would like to see the apartment number and the size of the surface area. I tried the Top 10 option but that does not give me the correct result. Key of the table is the building number (CPX_NUMBER). The fieldname of the apartment is VHE_NUMBER ...

Query by Form Problem 06-19-07
I'm using QBF with about six different combo boxes using: [forms]! [formmain]! [combo1] or [forms]![formmain1]![combo1] -like in a VBA book. This is so users can select criteria on a form with the combo boxes, and when they are done, they hit the search button, and it opens up another form based on the query just performed by the combo selection. This worked for about three combo boxes, but when I added another one, it freezes up and opens up a blank page. It's supposed to open the new form based on the query. Is there a better way to do this? I've looked exhaustive...

Trouble opening zip attachments
My Outlook 07 email has suddenly started trying to open every zip attachment with Internet Explorer, which doesn't work. I assume I must have somehow inadvertently changed the default setting for what Outlook uses to open this kind of attachment, but I can't find a way of re-setting that default. Any advice? Outlook doesn't choose what to open a file with, Windows does. Set the appropriate application to open a Zip file with in Windows Explorer. Tools > Folder Options > File Types. "Steve" <Steve@discussions.microsoft.com> wrote in message ne...

Form Query
I am trying to run a query for a form, I am using the following criteria [form]![frmSchoolsQry]![txtschool] . The form name and the text box from the form. When I click the command button the launch the query a parameter dialog pop up. The dialog ask for the text that is in the text box. I use forms to query dates, they work great, why don’t this work? Any suggestions? You're missing an s in Form: [Forms]![frmSchoolsQry]![txtschool] -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "rap43" <rap43@discussions.microsoft.com>...

Trouble with Forms based authentication in OWA
I am having some problems in setting up OWA with forms based authentication. From my understanding it should work like this: I go to my usual address http://<myserverdomain>/Exchange and I will be directed to the Forms Based Authentication login screen. Once I am there I can choose basic or premium function in OWA and once I authenticate I should be able to access OWA without any other login prompts. Well none of that is happening. Here is what I have done so far. 1. Enabled Forms based authentication in Exchange System manager. 2. Verified that that Authenticated Users group has Read...

How to update a specific field from a Find Duplicates querie
I have to update 1 table that has multiple duplications. If i search for duplicate on Field1, Field2, and Field3 all matching, i receive many duplicates where Field4 and Field 5 are different and/or Null. What do i need to do to the query so that Field4 and Field5 will be automatically updated when the duplicate is found? Current: Field1 Field2 Field3 Field4 Field5 Mike Smith 1234 KEEP Mike Smith 1234 DELETE 9012 Desire result: Field1 Field2 Field3 Field4 Field5 Mike Smith 1234 KEEP 9012 (...

Save query result in a variable
hi! hope you can help me AGAIN! i have a form that let's the user pick a name, job, between dates, tax(with or without) and percentage. this is for calculating the comision each employee has earned. i did this by a query that gets the info from the form adn then opens a report and in the report i make the calculations. but it turns out that several employees have 2 or more "jobs" and for each one diferent % of comission. the form and the reports handdle it fine, i'm looking for a way to add all of this reports for each person. i was thinking of saving the sum of each que...

Help with SQL Query 06-30-10
We have a distinct list of email addresses in alpha order and we need to transform it from a single column into a grid of three columns maintaining the alpha order. The list is contained in a temp table inside of our query. We then use the temp table list and perform case statement with a mod on the row_number in a select statement to columnze the data. However, the columnar data contains a null value in two of the three columns and we are needing to remove the nulls and have the actual values on each row in the output. Here is our current sql: CREATE TABLE #tmpTable ( Email_Add...

Union Query with division
Hello, it would like to join two tables, Table1 and Table2, however when joining table2 the items listed in that table need to be divided by 2. I normally do this division in a query then join that query to the union query with table1. I wonder if I can accomplish the same thing directly from the union query only. Also, is there a way to rename a customer ID with the same query? (See Customer 9 as example, changing to Customer 15) Example: Table1: [Customer] [Hours] Customer 1 10 Customer 3 5 Table2: [Customer] [Hours] Customer 9 ...

Query to count between list of number (Predicting Start/End that may occur in data range)
Hi, I have a below list of numbers. 566667 566668 566669 566665 566666 566671 566672 566680 I want a query that would return a count between start and end of range. Like Start End Quantity 566665 566669 5 566671 566672 2 566680 566680 1 Thank you. On 2 apr, 07:17, Angela <ims...@gmail.com> wrote: > Hi, > > I have a below list of numbers. > > 566667 > 566668 > 566669 > 566665 > 566666 > 566671 > 566672 > 566680 > > I want a query that would return a coun...

Solver VBA
Currently I have a simple solver VBA script in my excel workbook. The script sets the value of cell "I1" to 1 by changing cell "H1", as shown below: Sub repeatsolve() ' SolverOk SetCell:="$I$1", MaxMinVal:=3, ValueOf:="1", ByChange:="$H$1" SolverSolve End Sub What I would like to do is set the value of all cells in the "I" column, of a specific range, to 1 by changing its respective cell in column "H". Does anyone have any suggestions? Thank in advance. -- kwrohde --------------------------------------------------...

Database degrag query
Hi , I am using exchange 2003 with SP1. I have calculated the total size of mailboxes , it is total 30 GB. But the size if edb file is 50 GB and stm file is 25 GB . I have defraged the database offline so that it will purge the white space if there is any. but after defrag no difference in size of the database. it is still 50gb edb and 25 gb stm file. can anuone explain if i have only 30gb total mailbox size then why it is taking so much space. there is no rules and othere things define. Waiting for your help. Regards, Abhi. On 25 Oct 2004 20:45:43 -0700, pandeyabhishek2000@yahoo.com (abhi...

Query-based distribution group anomaly
When I do a TOOLS ~ OPTIONS ~ DELEGATES in Outlook 2003 and try to set a query-based distribution group as a delegate it seems to accept the group and whatever permissions I assign. But as soon as I click OK and then come back in to inspect it, the query-based distribution group has no permissions at all. This happens every time. However, in the same situation, whenever I assign a Global Security Group as a delegate and assign permissions, they stick. Should my query-based distribution group be accepted or is this kind of group not permitted as a delegate? But then if it's not perm...