SQL in access,

[gussi]

Hi all,

I want to create a module that exports data to excel. I found some help on the internet but I can't get it to work.


This is my code:

strExcelFile = "H:\TEMP\test.xls"
strWorksheet = "test"
strDB = "H:\TEMP\testdatabas.mdb"
strTable = "tabel"
strQuery = "query"
strString = "Name"

MySql = "SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & "].[" & strWorksheet & "] FROM [" & strQuery & "] WHERE [" & strString & " ] = Namn"

Set objDB = CurrentDb()

'If excel file already exists, you can delete it here
If Dir(strExcelFile) <> "" Then Kill strExcelFile

objDB.Execute MySql
objDB.Close


This does not work.
If I change MySql to

MySql = "SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & "].[" & strWorksheet & "] FROM [" & strQuery & "]"

Everything works fine.
So, what is wrong with
WHERE [" & strString & " ] = Namn"
I am no stranger to SQL it is the VBA syntax that gives me headache

 

[gussi]

Hi Pat,

Namn is not a string. It is the name of a column in the query. I can use it like that in the following case:

MySql = "SELECT Namn INTO [Excel 8.0;DATABASE=" & strExcelFile & "].[" & strWorksheet & "] FROM [" & strQuery & "]"

Then offshore I only select one of the columns. But it works.

Thanks anyway.

 

[gussi]

I solved it.
It should have been:
WHERE '[" & strString & " ]' = Namn
little ' around the string
/G