jdallain
asked on
Adding field the query in VBA
Experts,
I'm trying to add fields to different queries in a loop. I would think that you could open the querydefs and do it, but I can't figure it out.
Thank you,
James
I'm trying to add fields to different queries in a loop. I would think that you could open the querydefs and do it, but I can't figure it out.
Thank you,
James
With CurrentDb.QueryDefs(strFromSource)
End With
ASKER
Thanks capricorn1. I'm going to piece it together.
ASKER
nsql=select f1,f2,f3 from tablex" ?
With this, I would have to manually set the sql?
I'm trying to open queries that are already made and add fields to them. So the sql would be different.
With this, I would have to manually set the sql?
I'm trying to open queries that are already made and add fields to them. So the sql would be different.
i just showed you the way to alter the sql statement of a saved query
the oSql string will give you the original sql statement of the query.
the oSql string will give you the original sql statement of the query.
ASKER
If I add a field to oSql, the SELECT change. I'd be changing the sql with nSql. But the way nSql is set up I'd need to define the entire sql in the code. I'd like to just add to the SELECT.
the nSql i posted is just to show you that you can entirely changed the sql statement of the query.
<I'd like to just add to the SELECT. >
then just grab the oSql statement into nSql and add the fields that you want
<I'd like to just add to the SELECT. >
then just grab the oSql statement into nSql and add the fields that you want
ASKER
That's were I'm at a loss :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I know how do it like this...
strReport = strReport & strNewValue
strSelect = strSelect & strReport & " AS " & rst_ReportName!ReportName & ","
strSelect = " SELECT " & Left(strSelect, Len(strSelect) - 1)
strFrom = " FROM " & strFromSource
strJoin=""
strSQL_NewQuery = strSelect & " " & strFrom & " " & strJoin
This is part of the code I'm using in a loop to make a new query based off an existing query. However, I'd just like to add strReport to the existing query. Since the user can select any query, the sql will change every time. So if I could isolate the SELECT, I wouldn't have to make a new query. I could just add the field strReport to the existing query.
Do you want to see all the code?
strReport = strReport & strNewValue
strSelect = strSelect & strReport & " AS " & rst_ReportName!ReportName & ","
strSelect = " SELECT " & Left(strSelect, Len(strSelect) - 1)
strFrom = " FROM " & strFromSource
strJoin=""
strSQL_NewQuery = strSelect & " " & strFrom & " " & strJoin
This is part of the code I'm using in a loop to make a new query based off an existing query. However, I'd just like to add strReport to the existing query. Since the user can select any query, the sql will change every time. So if I could isolate the SELECT, I wouldn't have to make a new query. I could just add the field strReport to the existing query.
Do you want to see all the code?
you already have the codes to parse the sql statement, just follow the pattern
ASKER
Using...
Set qd = CurrentDb.QueryDefs(strFro mSource)
Debug.Print = qd.SQL
(strFromSource is selected from a combo box, which contains a list of all queries that are available in the database)
Intermediate Window display...
SELECT tblPat_HtWtBMI_calc4.Weigh tStatus, Count(tblPat_HtWtBMI_calc4 .WeightSta tus) AS CountWS, tblPat_HtWtBMI_calc4.Total PID, [CountWS]/[TotalPID] AS PercentWS
FROM tblPat_HtWtBMI_calc4
GROUP BY tblPat_HtWtBMI_calc4.Weigh tStatus, tblPat_HtWtBMI_calc4.Total PID, tblPat_HtWtBMI_calc4.Admit Status
HAVING (((tblPat_HtWtBMI_calc4.Ad mitStatus) ="O"));
Is there anyway to grab the first row and add to the SELECT statement? Or maybe another way to add a field? Since this can happen to any query, I can't really parse them all like the one in my last comment.
Set qd = CurrentDb.QueryDefs(strFro
Debug.Print = qd.SQL
(strFromSource is selected from a combo box, which contains a list of all queries that are available in the database)
Intermediate Window display...
SELECT tblPat_HtWtBMI_calc4.Weigh
FROM tblPat_HtWtBMI_calc4
GROUP BY tblPat_HtWtBMI_calc4.Weigh
HAVING (((tblPat_HtWtBMI_calc4.Ad
Is there anyway to grab the first row and add to the SELECT statement? Or maybe another way to add a field? Since this can happen to any query, I can't really parse them all like the one in my last comment.
ASKER
Maybe something along these lines...
With qd
.CreateProperty
Or
.Fields
End with
With qd
.CreateProperty
Or
.Fields
End with
ASKER
I think this could work...
with qd
.Fields.Append .CreateField(strName, varType, varSize)
end with
with qd
.Fields.Append .CreateField(strName, varType, varSize)
end with
ASKER
That's just for tables.
ASKER
Hey Cap. Sorry about the delay on over the weekend I initial asked this question. Some personal stuff came up. Do you think I should close this one and ask a better question? If it's not possible, then I can award you the points and close it. Thanks for your help.
James
James
ASKER
I don't think this is possible. Parsing works, but I can't use it for what I'm doing. Thanks for your help. James
set qd=CurrentDb.QueryDefs("qu
oSql=qd.sql
nsql=select f1,f2,f3 from tablex"
'assign nSql as the sql statement of the query
qd.sql=nsql
docmd.openquery "query1"
' restore the original sql statement of the query
qd.sql=oSql