Txtbox value to concatenate exported query name

[seb]

Hello everybody


In MS Access 2013 I have this sql that works well.

SELECT Format([dat],("yyyy""_""mm")) AS Month, tbltask.taskname AS TaskFROM tbltaskINNER JOIN tblMOD ON tbltarea.task_id = tblMOD.task_id
GROUP BY Format([dat],("yyyy""_""mm")), tbltask.taskname
HAVING (((Format([dat],("yyyy""_""mm")))=[Forms]![frm_Main]![txtmonth]));

To run querys in a Form I have a txtmonth to enter year_month for example 2017_03. I also have I command button
to export the query to excel. My code is:

Sub cmdexportmod_qry_month()
If DCount("*", "mod_qry_month") <> 0 Then
DoCmd.OutputTo acOutputQuery, "mod_qry_month", acFormatXLSX, "D:\ASSUKKAR\Cons_mod_month\mod_qry_month.xlsx ", False
MsgBox "La Consulta se exportó a Excel"
Else
MsgBox "The already exported to Excel"
DoCmd.Close acQuery, "mod_qry_month"


End If
End Sub

I need the name of the exported query be concatenated with values I enter in "txtmonth". For example If I type in "txtmonth" 2017_03 the query exported name will be: 2017_03_mod_qry_month.
if I enter 2017_02 the query name will be 2017_02_mod_qry_month and so on.


Please help

Many thanks

[andy49]

This sort of thing worked for me


Within your function, set the query up in VBA as follows:

strSQL="SELECT * FROM tblT WHERE ID =" & Forms!Form1!txtID
Set qdf=CurrentDB.CreateQueryDef("NewQuery",strSQL)
DoCmd.OpenQuery qdf.Name



"newQuery" can be altered to be your 2017_02_mod_qry_month

[seb]

Andy. Thank you for your help.
After I copied your code and typing 2017_03 in txtmonth and clicking the command button to export query to excel the following message is displayed:
Compilation Error: Variable not defined. In the code strSQL is blue coloured.


Then I typed Dim srtSQL as String an run the query again and this time this message is displayed:
Compilation Error: Variable not defined. In the code Set qdf is blue coloured.


Then I typed Dim qdf as String and running the query again this message is displayed:
Compilation Error:
Error 3075 in execution time
Sintaxis error in query expresion 'ID=2017_03


I don't how to fix this problem.
Please help

[andy49]

Ok put debug.print strsql after the strsql = etc line. Then look in the immediate window. Currently the strsql is not producing an sql which will run a query.

Copy and Paste it in here and we can help more


Sent from my iPhone using Tapatalk

[seb]

Andy49.
I typed Debug.Print strSQL = "SELECT * FROM tblMOD WHERE ID =" & Forms!frm_Main!txtmonth.
The immediate window is empty.

[andy49]

Just debug.print strsql




Sent from my iPhone using Tapatalk

[seb]

Andy49
I enclose my database (test_day) with one table where I am trying to concatenate date to query name when exported to Excel.
Take a note I have Access 2013 Spanish version.
Many thanks for your help.test_day.zip

[andy49]

test_day1.accdb

I've made a slight change to name the file according to what date was chosen.

eg if you select the file for 14/01/2017, the filename14012017

[seb]

Ajax.

Thank you for your patience.
Finally I found how to fix my big mistake.

Kindest Regards

[seb]

Andy49.
I posted a wrong message her. I am still trying to set your code in my project. I let you know results as soon possible.
Warm regards.