Topics Search

What is wrong with this sql?

What is wrong with this sql?
Views: 12
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:

Similar posts...

 

VBA code didin't work anymore after upsized access 2007 to SQL Server 2005

VBA code didin't work anymore after upsized access 2007 to SQL Server 2005 Icon
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

Deploying Application with SQL database Icon
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

error on inserting value Icon
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

error on inserting value to the table Icon
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)
 

VBA SQL using LIKE

VBA SQL using LIKE Icon
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.
 

Access giving wrong result using sql query

Access giving wrong result using sql query Icon
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.
 

create a report using SQL

create a report using SQL Icon
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.

Query fields (columns) do not apear in the right order. Icon
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.
 

Five Common Pitfalls When Upgrading Access to SQL Server

Five Common Pitfalls When Upgrading Access to SQL Server Icon
Access works great with SQL Server but upgrading your data is not a slam dunk. There are all kinds of things that can go wrong if you’re not careful, avoid these pitfalls when you do your first or next Access migration.
 

VBA + SQL statement creation/execution.

VBA + SQL statement creation/execution. Icon
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
 
Sponsored Links: