I have a large database with over a million entries. I am trying to filter certain years (04-12) and certain states (western US). After the two filters are on my entries are down to 328,000. Now, I want to save the 328,000 to a new database.
I am trying to e-mail from a form that displays filtered result, but having trouble. Could someone please guide me?
I am using DoCmd.SendObject (a routine that I found in a newsgroup). This routine works fine when I am sending it to all the recipients, but not to filtered recipients. The form shows filtered result, but I am not being able to use the command to only send to filtered recipients. Instead, when I press the button to e-mail, it populates the cc: field with all the e-mail addresses rather than the filtered addresses. Any help would be greatly appreciated. Thank you.
Here is the code:
Private Sub cmdGenerateList_Click() 'Create Email list from the appropriate query and open Outlook with those addresses in the cc: field
Dim db As Database, rs As Recordset, sql As String, emailTo As String, txtEmailList As String
On Error GoTo Err_cmdGenerateList_Click
Set db = CurrentDb()
emailTo = "" sql = "select EmailName from qInd_info " Set rs = db.OpenRecordset(sql)
Do Until rs.EOF If Not IsNull(rs!EmailName) Then 'build up email addresses separated by a semicolon emailTo = emailTo & rs!EmailName & "; " End If rs.MoveNext Loop
'Remove the last semicolon If Right(emailTo, 2) = "; " Then emailTo = Left(emailTo, Len(emailTo) - 2) End If
' Me.txtEmailList = emailTo
'Create the message with the recipients in the BCC: field ' DoCmd.SendObject acSendNoObject, , , , , emailTo DoCmd.SendObject acSendNoObject, , , , emailTo
Exit_cmdGenerateList_Click: Exit Sub
Err_cmdGenerateList_Click:
Select Case Err.Number Case 2501 Resume Next Case Else MsgBox Err.Description Resume Exit_cmdGenerateList_Click
I am trying to create a saved export on a table i have. However i want to filter so when i do the saved export it exports the table with the filtered result only.However when i filter my table and export it and save the export. The first export comes out with the filtered results but when i go to my list of saved exports and export it again, it doesnt come filtered and its just an export of the whole table.
Hello, I'm creating a database to enter Tests that have been done. I am recording the date that the test was performed and now I need Access to tell me when the next test is due (i.e. 5 years from the latest test date). I used the expression dateadd("yyyy",5,[NextTestDue]). But when I put this under Control Source, it only displays it on the form but does not save it in the table. How do I get it to save the info to the table?
I need the NextTestDue field to automatically populate when I enter a test date (The date the test was performed). Please help.
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I have two tables, say customers and purchase orders. I am trying to write query that returns a list of customers based on a wild card purchase order search.(FYI, the query is being used in a VB.NET application)
Code: SELECT Customers.WarehouseID AS Customers_WarehouseID, Customers.Name, PurchaseOrders.PurchaseOrder, PurchaseOrders.WarehouseID AS OBRs_WarehouseID FROM Customers INNER JOIN OBRs ON Customers.[WarehouseID] = OBRs.[WarehouseID] WHERE (((OBRs.OBR) Like '" & strPOFilter & "*'));
This query almost works how I'd like it to, except in cases where a single customer has multiple purchase orders that begin the same way. So, for example, if customer "mike" has purchase orders "00554" and "00553", if you search for "00", mike will be returned twice. How can I modify the query to only return the customer once?Is it possible, or should I filter out repeated offenders in my code?
I have a query that is filtered on a form (Forms!qRosterReport!SessionID) that I need to export to XML. I can export the query unfiltered but get an error with the filtered export. I am ok with Macros but very limited with VBA.
I filter my table with a query - where the Invoiced Date is NULL. In the vba ADD RECORDS code I may INSERT new records which should then also appear - but are not showing up. The records ARE added to the table, but don't show up unless I close the form and reload it and then the query filter picks up the added records.
I was having a wee look around the Export Threads and came across the following VBA Code I thought would be handy to export a Query to a new Excel workbook, and although it did export to excel, it didn't filter the query to show only the things I was wanting. Now I've managed to have it not work at all!
Code: Private Sub cmdOK_Click() 'Step 1: Declare your variables Dim MyDatabase As DAO.Database Dim MyQueryDef As DAO.QueryDef Dim MyRecordset As DAO.Recordset Dim strSQL As String Dim i As Integer
I've created a form with two textboxes and a listbox. What I want to do is to show in the listbox the registers filtered by the 2 textboxes. But I would like that the listbox refreshes while typping in the textboxes.It was no problem to do filtering with only one textbox. The rowsource of the listbox is a query in which I set in the criteria Like [Forms]![Form1]![Textbox1].[Text] & "*"Then I write the code for the change event of the Textbox1:
Private Sub Textbox1_Change() Listbox1.Requery End Sub
With this I have no problem. The problem is when using the 2 textboxes. I write the same in the criteria, Like [Forms]![Form1]![Textbox2].[Text] & "*", on another field but it doesn't work. I also write the code for the second textbox:
Private Sub Textbox2_Change() Listbox1.Requery End Sub
The listbox doesn't filter with both textboxes. When you type in one of them, the listbox filters from it, but when you type in the other textbox, the listbox shows the whole table filtered with the characters of the second textbox.
I am currently working on a form that exports a query that changes after a user set filter is applied, and am able to get the filter to apply and the query to export. The problem I am having is that the worksheet that the query is copied into retains all previous data, and if the earlier query export included more records, they remain as they were, is there any way of getting them to be blank.
I want to export my query onto a worksheet that has current data, need to delete current data or delete worksheet so that only the selected data is shown.
My code currently is:
Private Sub Command67_Click() Dim strWhere As String Dim strFile As String Const strcStub = "SELECT NomT.shkFirstName, NomT.shkSurName, NomT.shkCompanyName, NomT.shkAdd1, NomT.shkAdd2, NomT.shkPostCode, NomT.shkRegion, NomT.shkCountry, NomT.shkAdd3" & " FROM NomT" & vbCrLf With Me.FilterSub.Form
Oper sys is XP. Office 2000 version of Access. Extensive experience with Excel but very little with Access.
I'm creating an astronomical database that includes a function that calculates the distance to galaxies in millions of light years. These databases are massive. Total database has about 1.6 million galaxies (100 Meg). My query concerns about 500,000 of these.
Have the original database and done the query on the objects of interest. It includes the cacluations. All perfect.
Now, it is possible to save just the query so that I can copy it onto a CD and take to the observatory for field reference. Query has about 500,000 lines, one for each galaxy, but is only six columns wide. But at 500k lines it cannot be exported into Excel. Or at least I don't know how to do that.
I have spent a few days coding a database search page consisting of various comboboxes and textboxes. the user can select or enter data in these and when he/she clicks the search button an SQL select statement is generated on the fly and updates an existing query and this is used to query the database. I want to be able to give the user the option to save the query he/she generates and reuse it. How can I prompt him for a name and turn the SQL string strSQL that he has generated into a query and save it for later use?
I've got an access file which was probably created with access 97 or 2000, and inside a query that I know for a fact works with SQL Server 7
I was upgrading to SQL Server 8 and I needed to modify that query because it didn't work with the new version. Now, when I tried to save the query, it gave me a certain error about invalid JOIN statements. The query I wrote runs perfectly when I try to execute it manually on the SQL server, but MS Access just won't allow me to save it!
Any ideas how I can make it save my new query without it trying to verify my SQL statement?
I've got an access file which was probably created with access 97 or 2000, and inside a query that I know for a fact works with SQL Server 7
I was upgrading to SQL Server 8 and I needed to modify that query because it didn't work with the new version. Now, when I tried to save the query, it gave me a certain error about invalid JOIN statements. The query I wrote runs perfectly when I try to execute it manually on the SQL server, but MS Access just won't allow me to save it!
Any ideas how I can make it save my new query without it trying to verify my SQL statement?
I am trying to create a VB script to automate a mailing based on several query result sets from access. I have gotten to the stage that the output is correct but have a problem with the 5th and 6th record set query as they only return one record (When in fact there should be at least two for each).
I don't really understand why this is happeneing as the SQL is exactly the same as in the 2nd record set - which works perfectly. Also I've tested the SQL directly in an access query & there are no errors in the formatting that I can see... correct number of records returned.
Code: Public emailaddress, ccaddress, Subject, body1 As String Public baserow, toprow, countnumberofrows, emails As Integer Public tempdir, projectlistdir, WBPATH As String Option Compare Database Option Explicit
Ive created a query that asks the user to enter data (month and year) before it can be processed. This is causing a problem for me as i need to produce a chart from this data and access isnt letting me (access doesnt like me asking the user to enter data). So i thought if the data could be stored in a table then the chart could be drawn from the table.is this possible.Below is a screengrab of the design view fro the query.
SELECT Shift.Shift_ID, Shift.Shift_Type, Sum(Main.Shift) AS SumOfShift, Format([Date],"mmmm") AS Expr3, Year([Date]) AS Expr4 FROM Shift INNER JOIN Main ON Shift.Shift_ID = Main.Shift WHERE (((Month([date]))=[Month]) AND ((Year([date]))=[Year])) GROUP BY Shift.Shift_ID, Shift.Shift_Type, Format([Date],"mmmm"), Year([Date]);
The uploaded database has two main forms. The first is used to enter customer data, the second runs from a query and shows all those records that have been completed in the first form (frmCustInfo - AgentLog = Yes) but not completed in the second form (frmControlsSource - AnalystLog = No)
Then more data is input into one of the subforms (frmControlChecks). Now in order for me to see the results of the queries connected to two other subforms I have to move off the record and back to it (for the record to be saved and then the queries run)
After moving back to the record, the query results on the right (Information Only) are manually added to the last subform 'Input Two', then the user would move on to the next record and do the same. ------------------------------------------------------------------------------------------------------------- What I need it to do then is this: When the user tabs off the last field in the subform (frmControlChecks), the queries to the right (two subforms) would run, showing the results and also populating the last subform (frmAnalystInput)
The user would then click AnalystLog and move to the next record.
I hope this is possible without redoing the forms/queries because it's taken ages to get this far
I've also added info in this sample database and some working data if you want to take a look...thanks for any help
I have an simple Access 2001 database with one table in which I want to search multiple fields for entries which match upto four keywords using subqueries.I have entered the following sql code:
SELECT Components.[Component-type], Components.Value FROM (SELECT Components.[Component-type], Components.Value FROM (SELECT Components.[Component-type], Components.Value FROM (SELECT Components.[Component-type], Components.Value FROM Components
[code]....
and then refuses to save it giving the error: "Invalid bracketing of name 'SELECT Components.[Component-type"If I remove the changes it will save ok. This is only a test query as I will want to add many more fields and it is my first use of sql code.