Creating a select query using sql in VBA

I have one table called PRICING and 21 other tables that I link separately to 
this table to create a SELECT query. In other words, instead of creating 21 
SELECT queries, I want to be able to use variables within VBA to change the 
table name that links to the pricing table (fileds within all 21 other tables 
are named exactly the same).

I have been trying to create a VBA procedure to run the SQL statement to 
temporarily create the SELECT query.

I undertsnad that that I can use the DoCmd.RunSQL function, but cannot get 
the SQL statement right.

When I copy and paste the SQL statment from an exisiting query, I still get 
errors.

The SQL statement that will eventually replace with variables is as follows:

SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, pricing.Base, 
natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP %], 
([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net
FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;

When I try "clean up" the code to:
strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, 
pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP 
%], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & vbCrLf 
& _
"FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;"

I am still not successful... HELP PLEASE


0
Utf
2/24/2010 4:42:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

6 Replies
838 Views

Similar Articles

[PageSpeed] 49

Try this:

strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, 
pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP 
%], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & _
"FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy"

I got rid of the vbcrlf and replaced it by   " & _

The underscore at the end is the linebreaker for statements you want to 
break down.

hth
-- 
Maurice Ausum


"Les Gombart" wrote:

> I have one table called PRICING and 21 other tables that I link separately to 
> this table to create a SELECT query. In other words, instead of creating 21 
> SELECT queries, I want to be able to use variables within VBA to change the 
> table name that links to the pricing table (fileds within all 21 other tables 
> are named exactly the same).
> 
> I have been trying to create a VBA procedure to run the SQL statement to 
> temporarily create the SELECT query.
> 
> I undertsnad that that I can use the DoCmd.RunSQL function, but cannot get 
> the SQL statement right.
> 
> When I copy and paste the SQL statment from an exisiting query, I still get 
> errors.
> 
> The SQL statement that will eventually replace with variables is as follows:
> 
> SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, pricing.Base, 
> natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP %], 
> ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net
> FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;
> 
> When I try "clean up" the code to:
> strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, 
> pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP 
> %], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & vbCrLf 
> & _
> "FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;"
> 
> I am still not successful... HELP PLEASE
> 
> 
0
Utf
2/24/2010 6:55:02 PM
First, you can NOT use DoCmd.RunSQL to execute a SELECT query.  You can only 
use DoCmd.RunSQL to execute ACTION (and DDL) queries.  Action queries are 
queries that add records, delete records, or update records.

If the 21 other tables all have the same structure and field names, then you 
probably have a design problem.  You should have one table with the current 
structure and one additional field that identifies whatever the current reason 
is for having 21 identical tables.

A simple trick to build the SQL string would be to use a table alias.  I'll 
assume that NatCat is the table you want to vary.

Dim strSQL as String
Dim strAuxTable as String 'set strAuxTable = to the name of the changing table

strSQL = "SELECT p.Hierarchy AS base_Hierarchy" & _
", p.Material, p.Base, n.[Trade %]" & _
", (([Base]*[Trade %])) AS Net1, n.[EDLP %]" & _
", ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net" & _
" FROM pricing As P INNER JOIN [" & strAuxTable & "] As N" & _
" ON p.Hierarchy = p.hierarchy"

Your next step is to use the string as a record source for a form or report;
  or as a row source in a listbox or combobox;
  or as the SQL property of a query;
  or as the source for a recordset.  Whatever you do depends on what you are 
trying to accomplish.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Les Gombart wrote:
> I have one table called PRICING and 21 other tables that I link separately to 
> this table to create a SELECT query. In other words, instead of creating 21 
> SELECT queries, I want to be able to use variables within VBA to change the 
> table name that links to the pricing table (fileds within all 21 other tables 
> are named exactly the same).
> 
> I have been trying to create a VBA procedure to run the SQL statement to 
> temporarily create the SELECT query.
> 
> I undertsnad that that I can use the DoCmd.RunSQL function, but cannot get 
> the SQL statement right.
> 
> When I copy and paste the SQL statment from an exisiting query, I still get 
> errors.
> 
> The SQL statement that will eventually replace with variables is as follows:
> 
> SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, pricing.Base, 
> natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP %], 
> ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net
> FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;
> 
> When I try "clean up" the code to:
> strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, 
> pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP 
> %], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & vbCrLf 
> & _
> "FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;"
> 
> I am still not successful... HELP PLEASE
> 
> 
0
John
2/24/2010 7:08:03 PM
Thanks Maurice... I managed to download a whole function statement and VBA 
procedure that solved my problem.

Thanks anyway

"Maurice" wrote:

> Try this:
> 
> strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, 
> pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP 
> %], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & _
> "FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy"
> 
> I got rid of the vbcrlf and replaced it by   " & _
> 
> The underscore at the end is the linebreaker for statements you want to 
> break down.
> 
> hth
> -- 
> Maurice Ausum
> 
> 
> "Les Gombart" wrote:
> 
> > I have one table called PRICING and 21 other tables that I link separately to 
> > this table to create a SELECT query. In other words, instead of creating 21 
> > SELECT queries, I want to be able to use variables within VBA to change the 
> > table name that links to the pricing table (fileds within all 21 other tables 
> > are named exactly the same).
> > 
> > I have been trying to create a VBA procedure to run the SQL statement to 
> > temporarily create the SELECT query.
> > 
> > I undertsnad that that I can use the DoCmd.RunSQL function, but cannot get 
> > the SQL statement right.
> > 
> > When I copy and paste the SQL statment from an exisiting query, I still get 
> > errors.
> > 
> > The SQL statement that will eventually replace with variables is as follows:
> > 
> > SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, pricing.Base, 
> > natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP %], 
> > ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net
> > FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;
> > 
> > When I try "clean up" the code to:
> > strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, 
> > pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP 
> > %], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & vbCrLf 
> > & _
> > "FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;"
> > 
> > I am still not successful... HELP PLEASE
> > 
> > 
0
Utf
2/24/2010 7:54:15 PM
Thanks John... learnt the hard way about DoCmd.RunSQL

"John Spencer" wrote:

> First, you can NOT use DoCmd.RunSQL to execute a SELECT query.  You can only 
> use DoCmd.RunSQL to execute ACTION (and DDL) queries.  Action queries are 
> queries that add records, delete records, or update records.
> 
> If the 21 other tables all have the same structure and field names, then you 
> probably have a design problem.  You should have one table with the current 
> structure and one additional field that identifies whatever the current reason 
> is for having 21 identical tables.
> 
> A simple trick to build the SQL string would be to use a table alias.  I'll 
> assume that NatCat is the table you want to vary.
> 
> Dim strSQL as String
> Dim strAuxTable as String 'set strAuxTable = to the name of the changing table
> 
> strSQL = "SELECT p.Hierarchy AS base_Hierarchy" & _
> ", p.Material, p.Base, n.[Trade %]" & _
> ", (([Base]*[Trade %])) AS Net1, n.[EDLP %]" & _
> ", ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net" & _
> " FROM pricing As P INNER JOIN [" & strAuxTable & "] As N" & _
> " ON p.Hierarchy = p.hierarchy"
> 
> Your next step is to use the string as a record source for a form or report;
>   or as a row source in a listbox or combobox;
>   or as the SQL property of a query;
>   or as the source for a recordset.  Whatever you do depends on what you are 
> trying to accomplish.
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> Les Gombart wrote:
> > I have one table called PRICING and 21 other tables that I link separately to 
> > this table to create a SELECT query. In other words, instead of creating 21 
> > SELECT queries, I want to be able to use variables within VBA to change the 
> > table name that links to the pricing table (fileds within all 21 other tables 
> > are named exactly the same).
> > 
> > I have been trying to create a VBA procedure to run the SQL statement to 
> > temporarily create the SELECT query.
> > 
> > I undertsnad that that I can use the DoCmd.RunSQL function, but cannot get 
> > the SQL statement right.
> > 
> > When I copy and paste the SQL statment from an exisiting query, I still get 
> > errors.
> > 
> > The SQL statement that will eventually replace with variables is as follows:
> > 
> > SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, pricing.Base, 
> > natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP %], 
> > ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net
> > FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;
> > 
> > When I try "clean up" the code to:
> > strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, 
> > pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP 
> > %], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & vbCrLf 
> > & _
> > "FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;"
> > 
> > I am still not successful... HELP PLEASE
> > 
> > 
> .
> 
0
Utf
2/24/2010 7:58:10 PM
Hi John...'nother question.

I am not too sure about your statement about the design problem. If you 
don't mind I would like to explain my thought process and maybe you could 
suggest a better way?

The first table is a list of product types and their basic prices (table is 
called pricing and key field that links to other tables is hierarchy).

I the have a host of customers that are linked to a discount structure 
dependant on what type of customer they are.

So for example I may have a customer that is a restuarant and therefore is 
linked to the restaurant discount structures. Hence the 21 other tables(21 
different discount structures)

When I want to create a promotional document that "calculates" pricing for 
the relevant customer and then allows an additional promotional discount, the 
selection of the qualifying customer will update a variable with the 
discounting table to then allow a select query to update a form control...

Hope I make sense

"John Spencer" wrote:

> First, you can NOT use DoCmd.RunSQL to execute a SELECT query.  You can only 
> use DoCmd.RunSQL to execute ACTION (and DDL) queries.  Action queries are 
> queries that add records, delete records, or update records.
> 
> If the 21 other tables all have the same structure and field names, then you 
> probably have a design problem.  You should have one table with the current 
> structure and one additional field that identifies whatever the current reason 
> is for having 21 identical tables.
> 
> A simple trick to build the SQL string would be to use a table alias.  I'll 
> assume that NatCat is the table you want to vary.
> 
> Dim strSQL as String
> Dim strAuxTable as String 'set strAuxTable = to the name of the changing table
> 
> strSQL = "SELECT p.Hierarchy AS base_Hierarchy" & _
> ", p.Material, p.Base, n.[Trade %]" & _
> ", (([Base]*[Trade %])) AS Net1, n.[EDLP %]" & _
> ", ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net" & _
> " FROM pricing As P INNER JOIN [" & strAuxTable & "] As N" & _
> " ON p.Hierarchy = p.hierarchy"
> 
> Your next step is to use the string as a record source for a form or report;
>   or as a row source in a listbox or combobox;
>   or as the SQL property of a query;
>   or as the source for a recordset.  Whatever you do depends on what you are 
> trying to accomplish.
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> Les Gombart wrote:
> > I have one table called PRICING and 21 other tables that I link separately to 
> > this table to create a SELECT query. In other words, instead of creating 21 
> > SELECT queries, I want to be able to use variables within VBA to change the 
> > table name that links to the pricing table (fileds within all 21 other tables 
> > are named exactly the same).
> > 
> > I have been trying to create a VBA procedure to run the SQL statement to 
> > temporarily create the SELECT query.
> > 
> > I undertsnad that that I can use the DoCmd.RunSQL function, but cannot get 
> > the SQL statement right.
> > 
> > When I copy and paste the SQL statment from an exisiting query, I still get 
> > errors.
> > 
> > The SQL statement that will eventually replace with variables is as follows:
> > 
> > SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, pricing.Base, 
> > natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP %], 
> > ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net
> > FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;
> > 
> > When I try "clean up" the code to:
> > strSql = "SELECT pricing.Hierarchy AS base_Hierarchy, pricing.Material, 
> > pricing.Base, natcat.[Trade %], (([Base]*[Trade %])) AS Net1, natcat.[EDLP 
> > %], ([Base]-[net1])*[EDLP %] AS Net2, [Base]-[Net1]-[NET2] AS net " & vbCrLf 
> > & _
> > "FROM pricing INNER JOIN natcat ON pricing.Hierarchy = natcat.hierarchy;"
> > 
> > I am still not successful... HELP PLEASE
> > 
> > 
> .
> 
0
Utf
2/24/2010 8:11:01 PM
You know your data and your requirements. HOWEVER, if your 21 discount tables 
have the same structure then I would suspect that they can be combined into 
one table with a new field named BusinessType or DiscountType.

Then when you want discount information for a restaurant you filter the result 
by specifying the DiscountType = "restaurant".  A BIG advantage of this 
approach is that if you get a new type of business, you don't need to add a 
NEW table and change all your forms, reports, code, etc. to work with this new 
business type.  You just add the needed records to the table and everything 
should work.

If your discount tables vary in structure, then you probably have a valid 
argument for maintaining the different tables.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Les Gombart wrote:
> Hi John...'nother question.
> 
> I am not too sure about your statement about the design problem. If you 
> don't mind I would like to explain my thought process and maybe you could 
> suggest a better way?
> 
> The first table is a list of product types and their basic prices (table is 
> called pricing and key field that links to other tables is hierarchy).
> 
> I the have a host of customers that are linked to a discount structure 
> dependant on what type of customer they are.
> 
> So for example I may have a customer that is a restuarant and therefore is 
> linked to the restaurant discount structures. Hence the 21 other tables(21 
> different discount structures)
> 
> When I want to create a promotional document that "calculates" pricing for 
> the relevant customer and then allows an additional promotional discount, the 
> selection of the qualifying customer will update a variable with the 
> discounting table to then allow a select query to update a form control...
> 
> Hope I make sense
0
John
2/25/2010 1:58:04 PM
Reply:

Similar Artilces:

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

How do I create a sample in Excel?
I am trying to create a sample, needing every 20th record out of around 3000 records. Mary, You need a VBA procedure. Sub AAA() Dim RowNdx As Long Dim DestRng As Range Set DestRng = Worksheets("Sheet2").Range("A1") For RowNdx = 1 To 3000 Step 20 Rows(RowNdx).Copy Destination:=DestRng Set DestRng = DestRng(2, 1) Next RowNdx End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mary" <Mary@discussions.microsoft.com> wrote in message news:ADE0AD7F-B459-41AE-8B2...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

Can't create Organizational Forms Library in Exchange 2003 with SP
Hello, I cannot create an Organizational Form in EFORMS REGISTRY folder (from First Administrative Group->Folders->Public Folders->EFORMS REGISTRY in ESM). When I right-click the EFORMS REGISTRY folder and select New, there is no Organization Form. Instead, I only see Public Folder in the popup menu. Do you have any idea why Organizational Form menu does not show? My Exchange Server is Exchange 2003 with SP2. The login user is Administrator. Could you please help me? Thank you very much. Yang Is that account member of "Enterprise Admins" group? Yang Zhang wrote: &...

Using scanner in Word97
I want to scan a picture into word97 using my HP 4370 ScanJet. Could not find option of "From Scanner" under "Insert" --> "Picture". Apparently I must need some sort of Word97 Add-On. What and where is the add-on? Is it on the Office97 CD? Same applys to Excel97. Also, does microsoft sponsor a Word97 / Office97 discussion group? If so, would appreciate a link. "PSRumbagh" <PSRumbagh@discussions.microsoft.com> said this in news item news:39100DC1-A7EE-4679-881D-526BAA386620@microsoft.com... > I want to scan a picture ...

Queries and Charts
Does anyone know why the expressions in queries work fine for reports but not charts? ...

Pass parameter from FORM to QUERY
I have an append query that I trying to call from a cmd btn...but it prompts me for the parameter(ie QuoteID)... How do I tye it into my call: Dim stQueName As String stQueName = "Quote Query" DoCmd.OpenQuery stQueName, acNormal, acEdit On Apr 13, 12:56 pm, jlt...@hotmail.com wrote: > I have an append query that I trying to call from a cmd btn...but it > prompts me for the parameter(ie QuoteID)... > How do I tye it into my call: > > Dim stQueName As String > stQueName = "Quote Query" > DoCmd.OpenQuery stQueName, acNormal, acEd...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

How do I create a click on + symbol to open a root and click on -.
I'm looking to create an excel file with drop down menus. I'd like to have a category. Click on the "+" symbol and the category opens up and shows all of the subcategories. Each category can further be opened if I so choose. Each category can be have a number total associated with it. When you click the "-" symbol. The subcategories close and the sum total of all subcategories is shown in the category total. example. creating a budget. Category is utilities sub categories are: phone, cable, electric, gas, etc... Monthly utility total ...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

Global distribution List Not Appearing in Outlook Global Selections list
I have created a global list in AD in Server 2003 ( using Exchange 2003) and assigned my users. For some reason the list is not showing in the Outlook client global address list. I have compared this list to others i have created in the past and cannot spot any differences. What am I missing? 1. Have you got it hidden from the GAL? 2. Using cached Exchange mode? Therefore requiring a rebuild of the OAB to see it? Oliver On Fri, 16 Feb 2007 13:24:05 -0500, "rlm" <rmorton@execpc.com> wrote: >I have created a global list in AD in Server 2003 ( using Exchange 2003...

Select Nodes
I am using the XmlDocument Select Nodes method and it seems to work OK except that it returns embedded nodes in the collection when I only want the nodes that are not embedded. For example I have a statement as follows: nodes = doc.SelectNodes(\\Node1\\Node\\DesiredNode); It gives me back in the collection all the DesiredNode tags. If I select retrieve the nth node as follows node = nodes[n-1]; It gives me the correct node and all DesiredNodes that are embedded within the nth node. So if I ask for the first node it gives me the first node which includes about 5 embedded DesiredNode...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

Using Company Wide Mail Templates.
Hi, I do not know if i am at the right spot here, or if it is evne possible, but i got the following question. My boss would like me to make sure that every outgoing mail has the same looks. It starts by adding a signature that is the same for everyone, except with ofcourse personalized information. This was easily done by giving everyone a signature. The next question is however, to put the head of our website, also above our mail. This means that every user that sends a mail, the mail will have a nice header, underneath that header, the mail is typed, and then its ended with the si...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

creating a spredsheet and log the info into another spredsheet
hello, I have a excel sheet that is printed out and a cashier manual enters information, invoice number, invoice amount, cash amount, check amount, amex amount.... I would like to have the cashier input this information on her PC and print a copy to go along with the deposit and at the same time log the information into a google excel document I created. Any ideas how this can be done? ...

SQL Server Connection Failed
Hi everybody, this is my scenario: 1. Windows Server 2008 2. WSS 3.0 + MOSS 2007 3. SQL Server 2008. i'm having this problem: "Login failed for user 'sa'. Reason: An error occurred while evaluating the password. [CLIENT: 72.55.191.137]". in this momment i can't see any application from sharepoint. somebody knows how can i do? i don't change the sa password. i don't change anything in the sharepoint configuration. since two days ago i'm having this problem. pls, is very urgent, because i have to deliver this server the next monday to the...

Creating Exchange 2003 organization
Is it possible to have two Exchange Organizations in the same domain? I need to reproduce a problem and was hoping to install an Exchange server to an existing domain however I need the organization to be different. Is this possible? When I installed EX 2003 I wasn't prompted for information other than location of files. Thanks in advance On Mon, 28 Feb 2005 08:09:03 -0800, "RP" <RP@discussions.microsoft.com> wrote: >Is it possible to have two Exchange Organizations in the same domain? I need >to reproduce a problem and was hoping to install an Exchange server...

Request Selection
Hi, if we try to assign an activity to an open Request there are all Requests available (include closed). is there a way to show only open requests in this select-formular ? regards armin ...

Backing up databases on remote SQL
Hi, I recently followed one of the MS articles on how to move GP 9 sql database to a different server. Everything went well except when trying to run the backup option from the GP9 server. I currently have a server with GP9 installed and another server with SQL and the GP databases. When I try to run the backup from the backup option on the GP server it says " You must be on the server machine to perform this task". presume this is because the SQL is no longer on that server. The SQL server has a backup routine of its own, but I would like our finance users to have the ability...

Using Publisher 200 with Publisher 2003
How do I covert PUB2000 documents to Pub2003 documents and vice versa? Pub 2003 can open anything, no conversion necessary. Going backward is a bit trickier. File - Save As and chance the file type to a Pub 2000 file. Possible problems can arise if you've used a feature that was not available in the 2000 version and your file size will grew immensely. -- JoAnn Paules MVP Microsoft [Publisher] "nasuco" <nasuco@discussions.microsoft.com> wrote in message news:500C7A7A-4026-434C-8CC2-2DFDB69D81C4@microsoft.com... > How do I covert PUB2000 documents to Pub2003 do...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

Can't create the item #2
I use Outlook from MS Office 2003 Business Edition. Everything has worked fine until the past few days. Now, whenever I click on an E-mail address link inside a webpage, I get a message from Outlook that says "Can't create the item." One person on this group gave me the following advice: >Close Outlook, find and rename the frmscache.dat to .old I did searches on all my drives and the file named above was not found. I doubled checked the search to confirm that it included hidden files and system folders in the search. Still no luck. More thoughts? ...

VBA form
Im using a form/macro that i linked to through another thread and a tailoring it to my needs. I basically have it doing what i need but cant get a button to do what i want. When the user starts the macro i brings up the form with a message and 2 buttons (Continue/Cancel). Whe continue is pressed i want the message on the form to change to aniothe message and so on until all messages are shown. Ive attacehed what i working on and apprecaite any help offered. Thank yo +------------------------------------------------------------------- |Filename: Excel.zip ...