SQL help

[Peter Bellamy]

Access 2k, DAO
I have some VBA code that works ok using a DoCmd.OpenQuery.

I thought rather than write lots of queries to complete this action on different tables it would be better to write it in SQL and just substitute the table and field names as required.

As I know little about SQL I took the SQL version of my query and used that as the starting point. But I cannot get it to work, can anyone help please!!

The SQL from the query I have used is:

strSQL = "SELECT [Andrews HWST].andrews_product_type, [Andrews HWST].andrews_product_type, Product.product_weight, [Andrews HWST].andrews_product_type, [Andrews HWST].andrews_serialno, [Andrews HWST].andrews_serialno, Product.product_lablecode INTO [Data lable data1]" & _
"FROM [Andrews HWST] LEFT JOIN Product ON [Andrews HWST].andrews_product_type = Product.product_name" & _
"WHERE ((([Andrews HWST].andrews_date_datalable) Is Null));"

Thanks

Peter
PS I am sure there is better way to post the code but I could see how to do it, sorry!

 

[Rabbie]

Can you be a little more specific about what is happening. Are you getting an error message when you run this SQL. Have you checked the spelling of your SQL. This field - [Andrews HWST].andrews_serialno - has been duplicated in your SQL.

Have you tried pasting this into the SQL view of Query designer to see if it works there.

 

[Peter Bellamy]

Thanks for your reply.
Yes there is a duplication, missed that! The actual query is more complicated and so as to make it easier to post I simplified it, badly!

Deleting the duplicate it errors with
"Error 3129. Invalid SQL statement, expected DELETE INSERT UPDATE etc"

It actually came from the Query designer, SQL view !

Cheers

Peter

 

[Rabbie]

I would put in debug.print strSQL before you fire off the query so you can check that the string is being generated correctly. I have checked out what you have posted and I am a little confused because there is no link between the two tables. I think you may have lost something important while simplifying the query.

WHen I generated an Apend query it looked like

INSERT INTO AgentSessions ( Agent, TotalSessions, [Date] )
SELECT KBData.Agents AS Expr1, KBData.TotalSessions AS Expr2, KBData.Date AS Expr3
FROM KBData
WHERE (((KBData.TotalSessions)>0));

So it might help to move your INSERT INTO to the start of your query.

 

[Bungle]

When you wrap the string always leave a blank at the beginning or end of the line as below, otherwise the string runs the 2 lines together.
So 'Product.product_name" & _
"WHERE'
Will parse to: 'Product.product_nameWHERE' and fail
Add spaces like this:
Product.product_name " & _
" WHERE

 

[Peter Bellamy]

Thanks again.

I have checked the " & _ all all correct.

I have altered the string to:
strSQL = "INSERT INTO [Data lable data1]" & _
"SELECT [Andrews HWST].andrews_product_type, [Andrews HWST].andrews_product_type, Product.product_weight, [Andrews HWST].andrews_product_type, [Andrews HWST].andrews_serialno, Product.product_lablecode" & _
"FROM [Andrews HWST] LEFT JOIN Product ON [Andrews HWST].andrews_product_type = Product.product_name" & _
"WHERE ((([Andrews HWST].andrews_date_datalable) Is Null));"

And now it has the error
"Error 3075.Syntax error (missing operator) in query expression Product.product_lablecodeFROM [Andrews HWST] LEFT JOIN Product ON [Andrews HWST].andrews_product_type = Product.product_nameWHERE ((([Andrews HWST].andrews_date_datalable) Is Null));"

Cheers

Peter

 

[Bungle]

Product.product_lablecodeFROM =no space
Product.product_nameWHERE =no space
hence missing operator

 

[Peter Bellamy]

Thanks for that.
I did type spaces, they were in the code but they were not showing with the debug print.
I then typed 2 spaces and all was well !?

With that success I have converted another query to SQL in the same routine.
It works perfectly in DoCmd.OpenQuery, but not as SQL:

UPDATE [Andrews HWST] SET [Andrews HWST].andrews_date_datalable = Now() & _
WHERE ((([Andrews HWST].andrews_date_datalable) Is Null));

There is no error, it just does not update the the fields to Now().

I thought I was getting the hang of it!
Cheers
Peter

 

[Rabbie]

Any query that works on its own should work when run from code provided you have copied the SQL correctly.

I assume you are using docmd.runsql for this.

Double check that the SQL string is being passed correctly

 

[Bungle]

Now() is a variable so you have to lift it out of the string or Access reads it literally. It should look like this
"UPDATE [Andrews HWST] SET [Andrews HWST].andrews_date_datalable = '" & Now() & "' WHERE ((([Andrews HWST].andrews_date_datalable) Is Null));
Note the single quotes insode the double quotes, which could be replaced with # to use your local date, but you must have one or the other or it will fail.
Always remember Access is going to parse this text into a single string and try to run it exactly as you've typed it, so you need to add '' round non-numeric data and ensure you have spaces between every word.
Good idea to put a temporary unbound text box on your form and populate it with the parsed string so you can see exactly what you're trying to run. (myTextBox=strSQL)
If you can paste that text into a query and run it then it's fine.

 

[Rabbie]

I have checked the SQL generated in a similar update query of my own and there were no single quotes in that string. The only time I have seen quotes is when I have been adding a literal text string.

 

[Peter Bellamy]

Thanks for your help.
I have altered the SQL and it now debug prints as:
UPDATE [Andrews HWST] SET [Andrews HWST].andrews_date_datalable = 16/08/2007 16:01:29 WHERE ((([Andrews HWST].andrews_date_datalable) Is Null ));

So it is dealing with Now() ok, but it is not updating the records with it, they remain blank
Is it having a problem with <Is Null> ?

Cheers

 

[Rabbie]

There should be # characters round the date/time literal ie #16/08/2007 16:01:29#
I can't see anything else wrong

 

[Bungle]

Paste:
SELECT * FROM [Andrews HWST] WHERE [Andrews HWST].andrews_date_datalable Is Null;
into a query to make sure you have some records that qualify. If no records are returned for Null try
SELECT * FROM [Andrews HWST] WHERE [Andrews HWST].andrews_date_datalable =''; in case you have empty strings instead of nulls.
Null is not the same as an empty string.
Your string is still wrong though, you must have # or ' something round the date so fix that first in case that's the problem.

 

[Peter Bellamy]

Thanks again.

I have checked that the tables have nulls and as I said in my first post, running the query that the SQL was based on works fine.

I have tried all sorts of combinations of ' and # around Now() and it errors on them all. Without them it it parsed ok and found the date, as shown in the debug.print.

I am very puzzled!!
I still think it is the way itttryies to handle <Is Null> !

Peter

 

[Rich]

Have you tried a standard Update Query first?

 

[Peter Bellamy]

Hi Rich,
East of Eden, that sounds exotic!

By standard do mean run as a query as in DoCmd.OpenQuery? If so yes, it runs ok.
It was the SQL from that query I used in my code.

 

[Rich]

You have to use the American for date/time formats in vba, I suspect the problem could be there

 

[Peter Bellamy]

Repeating myself but the Now() function appears to be resolved correctly, to judge by the debug.print.
I think it has problem with the Is Null test.

Still unsolved !! Any SQL masters out there?

Cheers

 

[Bungle]

It may have created you a valid text string but that doesn't mean it's a valid piece of SQL! It definitely will not run with a 'naked' date/time string in there. Try replacing Now() with .....#" & format(Now,'dd/mm/yyyy') & "#.....