What is wrong with this sql?

Dim SQL as String

SQL = "SELECT [Sales Orders].InvoiceDate FROM [Sales Orders] WHERE ((([Sales Orders].InvoiceDate)>=#12/16/2009# And ([Sales Orders].InvoiceDate)<=#12/31/2009#))"

DoCmd.RunSQL SQL

Why does the above SQL give me a 2342 error, "A RunSQL action requires an argument consisting of an SQL statement?" I copied it direct from a query using the query design tool.

Sponsored Links:

Related Topics

VBA code didin't work anymore after upsized access 2007 to SQL Server 2005
I have an Access 2007 application which has VBA code to avoid users to key in wrong data in the data entry form. After I upsized the main table to a back end SQL Server 2005, then link the main table at Access 2007 front end, the VBA code at Access 2007 didnt work any more. All other functions are still OK.
Could anyone give me some ideals? I tried to use different SQL connection string, it still doesnt work. The one of error message is [Microsoft][ODBC SQL Server Diver ][SQL Server] Incorrect syntax near !.
However, I didn't see anything wrong


Deploying Application with SQL database
Ok here my problem I have an application that uses a SQL database. I have package this up into an installer so it can be installed on another PC. The package include the SQL server and the application along with the 3.5 .net frame work.

To see if it can be installed successful on another PC I made a virtual PC (windows XP) using VMWARE and installed it but when ever I ran my application it said that the administrator could not connect to the database.

The SQL database was installed along with all required framework. So what I did was went into management console for the SQL database and gave the administrator rights to login. It still giving me the error.

I am not sure what I doing wrong since this my 1st time trying to package and deploy a software to install.


error on inserting value
anyone can tell me what's wrong with this code? it keeps on giving me error "Number of query values and destination fields are not the same". please help.

btw, cTime and ccDate are Textbox

Dim sql, in_sql, out_sql As String
sql = "INSERT INTO Transaction(TimeIn , EntryDate) VALUES ('"
sql = sql & cTime & "," & ccDate & "')"
DoCmd.RunSQL (sql


error on inserting value to the table
anyone can tell me what's wrong with this code? it keeps on giving me error "Number of query values and destination fields are not the same". please help.

btw, cTime and ccDate are Textbox

Dim sql, in_sql, out_sql As String
sql = "INSERT INTO Transaction(TimeIn , EntryDate) VALUES ('"
sql = sql & cTime & "," & ccDate & "')"
DoCmd.RunSQL (sql)


Sending Results of Querty to Report
I have a data entry form with 18 text boxes, used for entering criteria for a query.

Right now, when my query runs, it runs for all 18 boxes, even if 17 of them have zero's, which don't need to be run.

So, instead of running one query, that runs all boxes, I am using if/then and goto statements to check the text boxes for zero, and if it is a zero, to GOTO the sql query I have written further down. So I have 17 sql querys written, and depending on how many boxes have actual data, the program jumps to the appropriate sql query. I am trying to keep it simple, this is my first foray into SQL.

My question is, how to pass the data generated by the query, to a report. As I understand it, the SQL generates a record set. So, I need to get that recordset to the report. Or am I doing this all wrong


Access giving wrong result using sql query
I am using a MS access mdb file to display some record from oracle database using odbc connection. But it is giving wrong result sometimes. below is the scenario:-

I have a table (linked table) called map_detail in mdb as well as oracle with same table structure. I formed one query in mdb (sql query) select * from map_detail where batch_no="SSO15121". It is always fetching some other result, but when I am changing the query by changing the batch no "SSO15148"
it is working fine. I noticed that for cases it working.


VBA SQL using LIKE
wrote SQL statement in ACCESS 2007 using LIKE that will not work. If I create a query with the same function (user input) it works fine. please tell me what I am doing wrong or if there is a setting or something that needs to be installed to work.

sql = "SELECT tablename.fieldName FROM tablename WHERE fieldname LIKE '*" & variablename & "*'"

running the query returns the matches I have. running this sql returns no matches. I have looked on internet for help, write the code exactly as they state and no matches. I am trying to match parts of names (last name or first name). ie: I have 3 ROSS that should show, but sql gives me nothing.Also, when taking user input, I UCASE the input to the variablename.


create a report using SQL
reports, so please be patient if I'm a little indistinct or use the wrong terms.

I have some SQL which creates a recordset.

The data in the recordset will change based on criteria supplied by the User.

I'd like to create a Report which prints that recordset: the data fields will be the same, just the contents will vary.

I can't see any obvious way of defining a Report with a recordset as its input, so unless I've missed something obvious I guess I'd have to create the Report definition in the same SQL code that creates the recordset.

Can anyone point me at an example of generating and executing a report created entirely from SQL, and using a recordset as its data source


Query fields (columns) do not apear in the right order.
I have a query whose SQL is generated from VBA. There are 3 different tables involved in the query. My problem is that the query's fields do not appear in the correct order and by that I mean they in a different order than listed in the SQL. Here is the SQL: [SQL CODE]

If I rearrange the fields manually and close/save the query then open it again, they show up correctly. However once the VBA code is run, the query once again is generated with the fields being in the wrong order.

I tried creating a datasheet form based on the query, and the fields were in the right order right away. the problem is I can not use a form for this, because the fields used are dynamic.


VBA + SQL statement creation/execution.
I have a form with a lot of unbound fields. I chose this method because I need a lot of control over the form. I have completed everything I need, seemingly, except writing the information to the database.

I have declared a string variable to dynamically generate my INSERT sql statement then wish to execute it.

I am having two issues:

1) a string can only be 256 characters. what data type can I use for a much longer statement?

2) how do I execute my string (or whatever new datatype you suggest) against the database and have it return an error code so I can be sure it happened; for example, just using CurrentDB.Execute(sql) does not tell me if anything went wrong


VBA SQL Update with variables
Please could someone kindly help me with this issue. I'm sure it is something simple, but I don't know what I'm doing wrong.

All I want to do is update a table with the Windows Username of the person importing a spreadsheet.

I have imported the spreadsheet successfuly into a temporary table and now want to update it. The code just doesnt work.

Public Function UpdateDateAndBy()
Dim SQL As String
Dim WINUSERNAME As String
WINUSERNAME = Environ("UserName")
SQL = "UPDATE tblMainData SET ImportedBy=" & WINUSERNAME
DoCmd.RunSQL SQL End Function


Error 3061, SQL problem
So I'm building a query for export to excel based on user filters and so forth.

It was working dandily, until I threw in some code to replace field names with values from another table. Now rather than look for problems with the vba, I'd like to figure out what the hell is wrong with the SQL.

So here's the SQL:

[CODE]

It's all one solid mass, but I figure no one would brave it that way :P

Basically the only thing I've done to it since the working version is include the "As *" for the numPedi, notePedi, and timePedi

The error I get is the following:

Error Number: 3061
Error Description: Too few parameters. Expected 30.


Query second highest value
I am trying to get the ID of the second highest record. Unfortunately, the sql-result is always empty. What is wrong with my


Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet
Lots of questions come up in the SQL Team forums about conversions between Access and T-SQL and some of the differences between the two SQL dialects. Here's a few handy things to help you out with converting your projects. Check in now and then as this short list will eventually grow as more things come up.


Getting error 3021 when trying to retrive data from record set
Maybe I am going about this wrong but I have a record set that retrieves a field out of one of our mdb's

Set FS = db2.OpenRecordset("Select SQL FROM SCSSQL Where String_Name = 'selComponentType';")

when I create a watch on this record set I see the data I want in it.

So I am trying to copy that data into a string variable to use elsewhere. Currently I am trying the following:

FROMSQL = FS![SQL]

But I get the run time error 3021 and it says that no record exists.

SQL is the name of the field that I am pulling data from in the data base and I think that is what I am supposed to place in the [].

Is this the best way to go about what I am trying to do or is there a better way


Filter Failure
The first is the right logic, but fails. The second is the wrong logic but runs.

strSQL = "SELECT * FROM MyTable"

rstRecordsCursorType = adOpenStatic
rstRecords.CursorLocation = adUseClient
rstRecords.Open strSQL, CurrentProject.Connection

This filter is the correct logic. Only the dates on the 10th and number column can be either 10 or 12. This however throws an error "Arguments are the wrong type, out of acceptable range or in conflict with one another."

rstRecords.Filter = "DateCol = #07/10/2011# AND (NumCol1 = 10 OR NumCol2 = 12)"

By removing the parenthesis access will allow this filter but its the wrong logic, the results aren't what they need to be.

rstRecords.Filter = "DateCol = #07/10/2011# AND NumCol1 = 10 OR NumCol2 = 12"

I have tried several different ways. The first filter works fine with DAO but I am switching to an SQl server and migrating everything to ADO. This seems like a simple problem but its turning int a big headache


A2007 ADP/SQL Parent Child form
Access 2007 ADP/ SQL Server 2005 BE / Win7 x64

Most of my app will have a lot of parent/child forms. My question is what is the best preferred method of first creating the parent record and obtain the ID and pass to the child datasheet record.

Just curious to see what I need to do before I spend a lot of wasted time on a wrong method.


Access 2007 Reporting Versus Crystal Reports
Recently the topic of Crystal Reports was brought up. We are a small firm with a small IT budget.

Our data lives in SQL Server, Access 2007, Excel, and in a purchased package’s Pervasive Database which we can access nicely via ODBC.

We currently do not have Crystal Reports, or any other report writer other than the reporting features that are integrated into Access 2007.

I took a stand and said that I believe that Access 2007 will be able to handle our reporting needs. We currently have very few reports and it appears that our reporting needs are quite basic.

The bulk of our data lives in SQL Server, but we do not have SQL Server Reporting Services available.

Are there issues that I should be aware of when starting to use Access 2007 Reporting against SQL Server? So far, our initial experiments have worked nicely, but we have only started to scratch the surface.

If we are heading down the wrong path, I would like to know sooner rather than later.


dbSeeChanges
I'm getting an error : A new error has occured. Run-time error 3622

"You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column."

I've tried adding dbSeeChanges (in red below), but I then get an Invalid Argument Error.

Can anyone tell me where I'm going wrong. Thanks.

Dim rs As ADODB.Recordset
Lot = """" & Me.Lot.Value & """"
SQL = "Select * from dbo_WipDevStatus where Lot = " & Lot & ""
Set rs = CurrentDb.OpenRecordset(SQL, dbSeeChanges)


SQL search only returning 2 records...
I have some code to search a text field in my database. I want to find all records matching part of a string. For example, when searching for "ti" there should be 68 matching records. I wrote the following code, but it only returns 2 records. If I paste the SQL into a query, the 68 records are returned.

What's happening?! I've been through the code dozens of times and I can't work out what's wrong