Set field name in export spec based on combo box

[CuriousGeorge]

Hello,

Im trying to get my export to work.

My problem is that the field name (that is displayed in the query) is chosen via a combo box so it changes from time to time.

I get compiling error when trying to run the code because i need to set the field name to something in my export spec. It works though when the name is the same in combo box as in the export spec.

How do i set the field name in the export spec if it changes when a new selection is made in the combo box?

Thanks

Private Sub cmdSearch_Click()
Dim qdf As DAO.QueryDef
Dim s As String
Dim specname As String
specname = "Export Specs"
s = "SELECT " & Me.ComboParameter & " FROM TableData WHERE (((ReturnDate([AbsTime]))>=[Forms]![frm]![ComboDate] AND ((ReturnDate([AbsTime]))<=[Forms]![frm]![ComboDateTo])) AND ((TableData.BananaField) LIKE [Forms]![frm]![ComboType]));"
 
Set qdf = CurrentDb.QueryDefs("qryAll")
qdf.Sql = s
'DoCmd.OpenQuery ("qryAll")
DoCmd.TransferText acExportFixed, specname, "qryAll", "C:\Users\SSS787\exjobb\Database\Readlog_update\exports\export.txt", True
End Sub

 

[JANR]

Private Sub cmdSearch_Click()
Dim qdf As DAO.QueryDef
Dim s As String
Dim specname As String
specname = "Export Specs"
s = "SELECT [B][COLOR=red]([/COLOR][/B]" & Me.ComboParameter & "[B][COLOR=red]) As " &  Specname & "[/COLOR][/B] FROM TableData WHERE (((ReturnDate([AbsTime]))>=[Forms]![frm]![ComboDate] AND ((ReturnDate([AbsTime]))<=[Forms]![frm]![ComboDateTo])) AND ((TableData.BananaField) LIKE [Forms]![frm]![ComboType]));"
 
Set qdf = CurrentDb.QueryDefs("qryAll")
qdf.Sql = s
'DoCmd.OpenQuery ("qryAll")
DoCmd.TransferText acExportFixed, specname, "qryAll", "C:\Users\SSS787\exjobb\Database\Readlog_update\exports\export.txt", True
End Sub

JR

 

[CuriousGeorge]

thanks for fast reply.

Got compiling error:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. (Error 3141).

"qdf.Sql = s" is highlighted in code.

Tried adding ' ' to '" & specname & "' but didnt work either.

Any idea?

Thanks

 

[JANR]

add
Debug.Print s

After
s= "SELECT......"

This prints the SQL to the immediate window and you can see if you spot any faults. It could be a missing space between Key-words.

JR

 

[JANR]

Tried adding ' ' to '" & specname & "' but didnt work either.

NO don't put single qoutes around specname

Be wary of that every keyword (SELECT, FROM, WHERE, As) have spaces around it.

JR

 

[JANR]

Just spotted your problem, it's those damned `Speces in fieldnames:
...
Dim specname As String
specname = "[Export Specs]"
....

You have to use square bracets around fieldnames if you must use a space.

JR

 

[CuriousGeorge]

The square brackets didnt work unfortunatly but tried also to use a specname without any spaces and it generates the following error:

The data being exported does not match the format described in the Schema.ini file. (Error 3438)


The format in the spec-file is fixed and it is only one column. What im not sure of though is what to set the field name as in the specfile? cant leave it empty. (schema.ini file)

cheers

-------Edit-------

Changed the field name in the Schema.ini file to same as specname i.e ExportSpecs then it works.

Is it possible to set the field name in the schema file so that the choice from the combo box is displayed?