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:

More topics

 

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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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.

Preview
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

Preview
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.