Need your help

[24sharon]

Hello,

I want to export to excel a dynamic SQL.
I try to ask in other forums and didn't have any answer.
So I try here Hope that anyone know how can I do.

If I export to EXCEL the SQL:
SQL=Select…. Where category=”,1,2,3”
It’s good.

But if I export
SQL=Select…. Where category like”*,1,*”
It doesn’t work well.

I attach the example file (here you can also check the SQL semantic
To work you need make in c:/ file “111.xlt”

Thank so much for any help or idea

 

[The_Doc_Man]

Sorry, my home site's policy disallows a download from an overseas site like this one.

The way to do this correctly depends on what you are trying to do.

The way your syntax APPEARS to read is that your category can contain commas (because the quoted string that you say works ok ALSO contains commas.) So the question is why doesn't the other syntax work better?

Does it happen that you don't have a record for which the category has a 1 in the middle position?

Now, the REAL question: What did you want to do. Never mind enclosing a sample DB. Tell us what you wanted. A "dynamic SQL" doesn't really tell us anything.

Are you trying to find all items (in the working case) where category = 1 or 2 or 3? Or does the category field really contain commas and your category field therefore contains many digits separated by those commas?

I know that English does not appear to be your native language, but giving us a sample DB doesn't tell us what you wanted to do anyway, particularly if - as you suggest - it isn't working right at the moment.

 

[24sharon]

Sorry,

First of all, I understand if you don’t want to read this, because 2 things: a. to long b.My English

It's hard for me write in English than I attach file “picture better then 1000 words”

I don’t understand why don’t you can download the file?

Now to my question:

I have 2 tables: name, category.
Each name can be in many categories, But I don’t want the name be more than one
So what I did is:
Table name:
ID (auto number)
Name (text)
Category (text) here the category and “,” (don’t know the word)
For example:
1 |24sharon |,1,2,
2 |doc man |,3,5,7,
That mean that 24sharon in category 1 and 2 and the doc man in category 3 , 5 and 7.
(ho…)
After I have another form to filter the
For example I want all the name in category 3 etc,
I did that

SELECT NAME, category FROM NAME WHERE (((NAME) Is Not Null) And ((category) Like "*,3,*"))

And it’s good
But and here is my point:
I want to export to excel the results
My code:

Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")
ExcelSheet.Application.Workbooks.Open ("c:\111.xlt")
ExcelSheet.Application.Visible = True
ExcelSheet.Application.Sheets(1).Select
Set cnn = CurrentProject.Connection
Set rs = cnn.Execute(strSQL)
If Not rs.EOF Then
    ExcelSheet.Application.cells(2, 1).CopyFromRecordset rs
End If
ExcelSheet.Application.[Save]
'ExcelSheet.Application.[Close]
'ExcelSheet.Application.[Quit]
Set ExcelSheet = Nothing

My big problem is that this code open the excel but the recordset is empty, and if I open query I show record
What can be the problem and how can I fix it.

(If you read till here I Take my hat down…)