Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Adding field the query in VBA

Posted on 2011-02-18
16
Medium Priority
?
539 Views
Last Modified: 2013-11-27
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  
With CurrentDb.QueryDefs(strFromSource)
        
End With

Open in new window

0
Comment
Question by:jdallain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 5
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34925726
dim qd as dao.querydef, nSql as string, oSql as string

set qd=CurrentDb.QueryDefs("query1")
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

0
 

Author Comment

by:jdallain
ID: 34925822
Thanks capricorn1. I'm going to piece it together.
0
 

Author Comment

by:jdallain
ID: 34926093
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.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34926203
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.

0
 

Author Comment

by:jdallain
ID: 34926406
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.

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34926458
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
0
 

Author Comment

by:jdallain
ID: 34926505
That's were I'm at a loss :)
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 34926532
post sql of a query and the name of the field that you want to add.
0
 

Author Comment

by:jdallain
ID: 34926765
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?                      
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34926998
you already have the codes to parse the sql statement, just follow the pattern
0
 

Author Comment

by:jdallain
ID: 34943786
Using...

Set qd = CurrentDb.QueryDefs(strFromSource)
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.WeightStatus, Count(tblPat_HtWtBMI_calc4.WeightStatus) AS CountWS, tblPat_HtWtBMI_calc4.TotalPID, [CountWS]/[TotalPID] AS PercentWS
FROM tblPat_HtWtBMI_calc4
GROUP BY tblPat_HtWtBMI_calc4.WeightStatus, tblPat_HtWtBMI_calc4.TotalPID, tblPat_HtWtBMI_calc4.AdmitStatus
HAVING (((tblPat_HtWtBMI_calc4.AdmitStatus)="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.
0
 

Author Comment

by:jdallain
ID: 34943874
Maybe something along these lines...

With qd
        .CreateProperty
Or  
        .Fields
End with

0
 

Author Comment

by:jdallain
ID: 34944551
I think this could work...

with qd
        .Fields.Append  .CreateField(strName,  varType, varSize)
end with
0
 

Author Comment

by:jdallain
ID: 34944813
That's just for tables.
0
 

Author Comment

by:jdallain
ID: 34991787
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
0
 

Author Closing Comment

by:jdallain
ID: 35036404
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question