Reply:

SQL in Excel data
Hi all, Is there a possibility/way to run an SQL query in an excel data sheet? I have quite some data like the sample below, now i would like to have the sum of spending for each person. Like it is possible in Access. A1 B1 Field1 Field2 Chuck 12,89 Mike 23,09 Jean 9,34 Chuck 30,00 Mike 3,80 Chuck 22,00 Mike 7,23 Jean 10,55 Jean 10,75 Jean 31,45 Chuck 19,99 Result Field1 SumOfField2 Chuck 84,88 Jean 62,09 Mike 34,12 Advice would be appriciated. Cheers, Ludovic Hi You could use a formula like this ...

Form for table
Here's the problem: Access 2003, XP Pro- I have three tables that are related by primary keys. I have a table[Table QC] that has the field [EmployeeID], the data type is number--that field is linked to the autonumber primary key of the Employees Table. So far so good. I am trying to make a form that has a combo box that looks to the Employee Table to get me the drop down of the employees (Last Name, First Name). The SQL statement builds out okay, and I get the correct name config., but because the data type is a number it won't accept the data. I looked at the Northwind example and i...

How to get XML data out of an XML file
I am trying to retrieve the Parameters first or second (0, 1 ,2) node from the following XML file: <?xml version="1.0" encoding="utf-8" ?> <Robot xmlns="http://tempuri.org/RobotDefaults.xsd"> <Parameters> <Name>Decker</Name> </Parameters> <Parameters> <Name>A</Name> </Parameters> <Parameters> <Name>B</Name> </Parameters> </Robot> There will be more data than just a name for each Parameters node. Here is my code: Dim node As XmlNode = xmlDocument.SelectSingleNode("/P...

Build Dynamic Query from Form
I am trying to build a dynamic Query from a Form. I keep getting an error that reads ‘Object qryFilter already exists’ I suspect it has something to do with the string of dates being passed to the Query; strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom] And [Forms]![SearchForm]![cboTo])" I am trying to add a means for a user to Query by Customer and Trader AND all records between two dates. This was working fine for Customer and Trader; when I added in the code to filter by dates I started having problems. I know the SQL will be li...

Collecting and exporting data
I've tried postings this same question 3 hours ago, and I have yet to see it in the list of posts.....so I'm doing it again. Working in Excel 2002/XP, I have one file (Tasks) with multiple worksheets (Areas of Responsibility) with all the same column headings (Name, What, Start, Deadline). I want to be able to pull/extract the complete row of information from all worksheets for a specific Deadline date, and have it show up in a new worksheet. Can this be done? How? Thanks -- Diane A possible simple solutin is to use Data > Filter on each of the source worksheets t...

No Form Assistant in Quote Product Screen
While entering data in the QuoteProduct screen, no Form Assistant is available. Is it the usual feature or I need to configure something to view the Form Assistant? Also is it possible to see contact hierarchy in an account in the form of a Organisational Chart? Is their any add-on available in relation to this? ...

Exchange 2003 on a Domain Controller.
Hi All, I have a WinNT/Ex5.5 to AD 2000/03 and Ex2k3 migration to do. The client is a small company with only about 15 users. The are a network solutions company that needs flexibility so SBS is out of the question. My initial thought was to have 2 Win2k domain controllers/file servers and one Win2k3 member server running Ex2k3. They only have 2 Win2k server licenses and 1 Win2k3 server license. The problem is that they want to be able to use RPC over HTTPs. I understand that requires a 2003 Global Catalogue server. So, my plan of the 2000 DCs doesn't work. My question is:...

Sum in Form Footer
Hi Folks, I've made a the following calculation in form Detail section into text box and I've called my text box "Chaussures" =Sum([Chaussures])+([aldo])+([Feet-First])+([Transit])+([Globo]), this sum calculate the sum of Units which are into these text boxes "aldo", "Feet-First", "Transit" and "Globo" and this for each line record. Now in the Form Footwear, How can I make the total of units "Chaussures" I've tried a lot of things and nothing seems to work. Your help will be much appreciated, Thankign you in advance,...

Error saving a Shared Workbook
I have a user using a shared workbook and gets an error everytime she does this ONE specific change. She deletes a line and then saves and gets a message that says, "EXCEL.exe has generated errors and will be shutdown by Windows... blah blah blah". If she makes anyother type of changes it saves just fine. I also had another user do the SAME change and that person also got that error message. When the file is changed to "Not Shared" then the line is deleted it saves fine also. The problem is obviously with the file itself and that is shared out. can anybody ...

Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my classes.(Alphabetized names down left vertical column. Dates across top of horizontal row.) I added a different color to all cells in every other row to make for easier reading of each student's name and absences. Every other row stays with a white background. My question: I wondered if it was possible to lock row colors while allowing data to change on top of them. If a new student is added to my class in alphabetical order, the alternating color pattern is often lost. It is a pain to rechange row and cell colors. Any shortcut ...

How refresh imported data automatically?
In Excel, I've imported data into a worksheet. I know I can click Data.. Refresh Data to requery the source, but I want to be able to do it in a more automated way. Is there some sort of macro I can write when opened or something? Thanks, Ron Ron, Select the cell the data starts in, select Data/Import External Data/Data Range Properties Check the box "Refresh on file open" and anything else that needs checking off... Other than that, you could record a new Macro that selects your cell, refreshes data, and assign the macro to a keystroke, or even an icon. Beege ...

Appending in a sub form
I am creating a venue ticket tracking DB in Access '07. tblVenueEvent has info about Venue, date, time, event with a VenueEventID (key field) which is linked to tblTicketInfo which has section, seat, row, etc. I set up an entry form completing VenueEvent info with a sub form for ticket info. Some of the venues have a standard set of seats that I want to "auto populate" into the sub fom. I have set up a Venue table with the standard seats for the venue. I can set up an Append Query on the sub form, which does append the tblTicketInfo, but does not capture the VenueEven...

Question About Missing Data
So, this is probably really easy, but I just want to ask and see if I may be missing something here. Some data on vendor numbers changed. Let’s say IBM used to have a vendor number 12345 and now it’s vendor number is 56789. I can identify IBM as IBM, but I really want to use the number, not the name. Should I set up a table that ties the numbers together, so that Access knows 12345 = 56789? Or, should I do some kind of Update Query and change all incidences of 12345 to be 56789? Or, is there some other, method, like a ‘best practices for missing data’? Thanks! Ryan--- ...

Outlook Data Files #4
I've done some reading but I'm still confused about the use of Office Outlook Personal Folders File (.pst) versus Outlook 97-2002 Personal Folders File (.pst). I understand the basic "Office Outlook Personal Folders File (.pst) to create a new Outlook 2003 .pst file. Click Outlook 97-2002 Personal Folders File (.pst) to create a new Outlook .pst file that is compatible with earlier versions of Outlook." When we have upgraded to Outlook 2003 or set up a new PC with Outlook 2003, when adding PST's you can chosse either of the two file type options and browse to chos...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

Is there a way to convert OE 03 form templates?
I have a user that has upgraded from outlook 2003 to outlook 2007 and has a form template from 2003 that is in a FDM extension. Outlook 2007 is unable to read this form. Is there a way to convert this form, or will the user just have to create a new form with their new version of outlook? Any information appreciated. Thanks, -AllenK What happens when you try to install the form? Any error message? -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.ms...

Multiple Item Forms
Hi. I am new to your form and am struggling with the Multiple Item Forms. I want a combo box on the top that will select an id number from a table called 'SurveyA'. The multiple item form should then only display the records in a table called 'ActTravDiary' with that id number. As the ID number is changed a new group of records should be displayed in the form. I know how to initiate a change in one combo box based on another combo box (i.e. Row Source), but I cannot find a similar property for the multiple item forms. Please help. On Fri, 12 Feb 2010 21:00:2...

Set Enabled On All Controls in a Form?
I am trying to set all controls visible, unlocked, and enabled, as the baseline for disabling or locking down some fields based on other criteria. Here's what I tried, which gives me an "Invalid or unqualified reference" error: Function SetEnabledFieldsBase() Dim ctl As Control For Each ctl In Me.Controls .Enabled = True .Visible = True .Locked = False Next ctl End Function I based this on several examples that were intended to do something to all text boxes on a form. (I have never done this before, so sorry if this i...

Find what control is using a data item
I built a form, then deleted 2 columns from the source table and now I get a popup asking for the value of those 2 columns. The problem is, I don't use those columns so I need to find what on the form is refferencing the deleted columns. I have looked at the control drop down on the properties window and the tab order window and can't find a control with either one of the missing column names. How do I determine what is trying to refference the deleted columns? I found the problem, the column was still referenced in the underlying query that fed the form. "MeSteve"...

Is there a way to test if a control exists on a userform?
I have a sub (ClearUserform) that is called by several userforms. Sometimes the control may not be on the userform passed to ClearUserform, thus I get an error. So I added the On Error Resume Next statement to quickly work around the error. Is there a way to test if the control exists on the userform passed to the sub. Sub MySub() Call ClearUserform(Userform1) End Sub Sub ClearUserform(MyForm As UserForm) On Error Resume Next MyForm.TextBox1 = "" ' i have other controls list here On Error GoTo 0 End Sub -- Cheers,...

merging data
Hi all:) I'm new to this forum and to to Excel (2000) and not sure that I'm asking this question correctly but here is what I'm trying to do. The data on my work sheet is pasted from another sorce and includes information: name, ref #, time, date. I use this information looking up the status of an order and enter a "status note" in 5th (last) column. As the day progresses, the source that the data comes from is copied and pasted under the current list I already have going and much of the data on the lower list is a duplicate. How can I merge the to list so that I hav...

Drawing static/edit control in different colors
Hi All, This should be a simple question, but it is possible to, control the color of text written to static text or an edit box? I need to do this at during InitDialog and while dialog is 'up' (DoModal). As an aside, can change the color of static text just based on the resource ID? i.e. not creating a variable for the control in the dialog class. Thanks in Advance! Corey. Corey Wirun wrote: > Hi All, > > This should be a simple question, but it is possible to, control the color > of text written to static text or an edit box? I need to do this at during > In...

html control
I would like to create a custom entity, that has a description field that can display 'rich' formatting, e.g. an using an IE control. Is this possible, and if so can anyone point me in the right direction as to how to do this? Thanks You can only use the controls as offered by CRM. There is no such rich text editor as control. You can add an IFrame to your form. Then let this IFrame open a page which shows a rich text editor. You can then let the user work with a rich text editor. When saving the form then add some jscript code to do something with the data in that page and s...

filter records in a form
I've created a form with its subform to enter tasks of employees. however I need filter records only for active employees The form has as a source, the table CARD_EMP (employee card). It has a field ST_EMP for the employee status (Active , Pasive) In the Event Form_Current I wrote DoCmd.ApplyFilter ST_EMP='A' but it doesn't work Thanks for advance Carlos -- Message posted via http://www.accessmonster.com Hi mamumi Your advice was the solution Thanks a lot Carlos -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscodin...

GENERATE a TEXT FILE Using INPUT from a Form ??
I need to generate a Text file using (partly) information entered by the user on a form. I already have the TEMPLATE for the text file......I need to generate a COPY of the file with the word entered by the user into the form INSERTED into the text file. The text file is actually code for a web page (will have an .htm extension). The file is simply an exact copy of an existing template with only ONE ITEM of the text changed to a word that is entered into a form by a user. Example: One line within the template file is: "We have several of these items available in BLUE." I have...