• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

Access VBA query definition

I am dynamically creating field names for a query definition that is used to export data to an excel sheet. The field names are the last three months names. Everything works fine apart from the month names which are created in quotes. How should I write the query to exclude the quotes?

The query writes the three dynamically created month names as:
‘Jan Use’       ‘Feb Use’      ‘Mar Use’

I want:
Jan Use       Feb Use            Mar Use


Dim Monthname1 As String
Monthname1 = Format(DateAdd("m", -1, Date), "MMM") & " Use"
Dim Monthname2 As String
Monthname2 = Format(DateAdd("m", -2, Date), "MMM") & " Use"
Dim Monthname3 As String
Monthname3 = Format(DateAdd("m", -3, Date), "MMM") & " Use"
Dim qd As DAO.QueryDef
Dim Querytxt As String
CurrentDb.QueryDefs("qryCurrentRecords").SQL = "SELECT tblCurrentStock.[size], tblCurrentStock.ply, tblCurrentStock.[LST W/K], tblCurrentStock.[MTH/2DA] , tblCurrentStock.[1Mth] As '" & Monthname1 & "'  , tblCurrentStock.[2Mth] As '" & Monthname2 & "' , tblCurrentStock.[3Mth] As '" & Monthname3 & "' , Round(([1Mth]+[2Mth]+[3Mth])/3) AS [Avg], Round([stock]/12) AS [Mths lft], tblCurrentStock.Stock FROM tblCurrentStock"
0
JoeBo747
Asked:
JoeBo747
1 Solution
 
jimyXCommented:
Did you remove the single quotes from your query?

Dim Monthname1 As String
Monthname1 = Format(DateAdd("m", -1, Date), "MMM") & " Use"
Dim Monthname2 As String
Monthname2 = Format(DateAdd("m", -2, Date), "MMM") & " Use"
Dim Monthname3 As String
Monthname3 = Format(DateAdd("m", -3, Date), "MMM") & " Use"
Dim qd As DAO.QueryDef
Dim Querytxt As String
CurrentDb.QueryDefs("qryCurrentRecords").SQL = "SELECT tblCurrentStock.[size], tblCurrentStock.ply, tblCurrentStock.[LST W/K], tblCurrentStock.[MTH/2DA] , tblCurrentStock.[1Mth] As " & Monthname1 & "  , tblCurrentStock.[2Mth] As " & Monthname2 & " , tblCurrentStock.[3Mth] As " & Monthname3 & " , Round(([1Mth]+[2Mth]+[3Mth])/3) AS [Avg], Round([stock]/12) AS [Mths lft], tblCurrentStock.Stock FROM tblCurrentStock"
0
 
peter57rCommented:
Yu have explicitly put the quotes into your query so the obvious answer is to remove them.
0
 
Rey Obrero (Capricorn1)Commented:
test this


Dim Monthname1 As String
Monthname1 = Format(DateAdd("m", -1, Date), "MMM") & " Use"
Dim Monthname2 As String
Monthname2 = Format(DateAdd("m", -2, Date), "MMM") & " Use"
Dim Monthname3 As String
Monthname3 = Format(DateAdd("m", -3, Date), "MMM") & " Use"
Dim qd As DAO.QueryDef
Dim Querytxt As String
CurrentDb.QueryDefs("qryCurrentRecords").SQL = "SELECT tblCurrentStock.[size], tblCurrentStock.ply, tblCurrentStock.[LST W/K], tblCurrentStock.[MTH/2DA] , tblCurrentStock.[1Mth] As [" & Monthname1 & "]  , tblCurrentStock.[2Mth] As [" & Monthname2 & "] , tblCurrentStock.[3Mth] As [" & Monthname3 & "] , Round(([1Mth]+[2Mth]+[3Mth])/3) AS [Avg], Round([stock]/12) AS [Mths lft], tblCurrentStock.Stock FROM tblCurrentStock"



you need the [] brackets since your aliases have spaces
0
 
plummetCommented:
Hi,

It's because you are adding single quotes in the SQL definition. However if you left them out you'd get an error because there's a space in the name, so you need to enclose them in square brackets:

CurrentDb.QueryDefs("qryCurrentRecords").SQL = "SELECT tblCurrentStock.[size], tblCurrentStock.ply, tblCurrentStock.[LST W/K], 
tblCurrentStock.[MTH/2DA] , tblCurrentStock.[1Mth] As [" & Monthname1 & "]  , tblCurrentStock.[2Mth] As [" & Monthname2 & "] , 
tblCurrentStock.[3Mth] As [" & Monthname3 & "] , Round(([1Mth]+[2Mth]+[3Mth])/3) AS [Avg], Round([stock]/12) AS [Mths lft], 
tblCurrentStock.Stock FROM tblCurrentStock"

Open in new window


I hope that helps
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now