Hi All,
I'm new on this forum and not that experienced in coding.
I am making an export to Excel and am wondering if (and how)
I can set a Fliter on output.
I have a db for keeping scores for contests, this db is used by several people in diferent locations,
so I would like them to export there own contest and me importing this in my own db. I need
to have a complete (all contests) db for competion rankings.
Thanks for replying !
Hiya,
Bit stuck here but bet it is really stupidly simple I have the following code to export a query into a spreadsheet which worked fine but after I added the path name which is in bold it no longer works.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "NME With Company Code", C:My DocumentsTDL Update(Format(Date, "mmmm")) & "'s TDL Information", True
what am i doing wrong what I am looking for it to export this to a specific folder in my documents.
Thanks in advance
:confused: :eek: :confused: :eek: :confused:
I have aproblem with the range of this thing. I think I have a wrong synthax or something.
I need to have the first 120 records of columns A and D
The first two lines aren't records but titles
So I had:
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "ExcelTEMP", mijnFile, False, "A3:A122;D3:D122"
Access tels me there is somthing wrong with the range, though it works in Excel :confused:
quickie -- i am away from my project at the moment - but does the TransferSpreadsheet overwrite existing data ??
i have the transfer set up in to a xls ( this works) but if i change the source will it over write the transfered data (I want it to do this )??
regards
Hi, I am a new user.
I need to:
Take the result of a query and export it to excel.
Manipulate the data in excel.
Re import the same data into a table
And finally put the table into an existing form
I have succesfully created the macros to
Export the data and
Re-import the data
BUT I have to mannually open the spreadshhet and save it
for the RE-Import to reflect the updated data from the ss.
Is there anyway to automate this saving process?
I am importing an Excel file of production data into a table using the TransferSpreadsheet method. Here is a snippet of the code:
DoCmd.TransferSpreadsheet acImport, , "tblMPSDATA", _
"" & stFilePath & "", True, ""
Everything works fine except the column which contains 'Material' holds both numeric and alpha-numeric values. For example these are both material numbers: 156952 and 1238707-202. The data in this column is formatted as General. The data type for Material in tblMPSDATA is Text, 18 character length. The alpha-numeric materials are all at the end of the file. When I import, an error table is created listing the alpha-numeric materials with the error 'Type Conversion Failure'. But if I have an alpha-numeric material in the first row of data then everything is imported just fine.
I have set up a nice little popup form with a file path and command button for controlling the process of bringing in this data. I really do not want to have to add special instructions about making sure the Excel data is sorted in a certain manner prior to importing. Any thoughts on why Access is not treating everything in this column as text?
Thanks,
JAB
I used the DoCmd.TransferSpreadsheet method to export from an Access 2003 table to Excel. The table had around 440 fields but only 230 (column iv) got exported.
Does anybody know whether there is a limitation on the number of fields that can be exported.
Thanks
Claude
Hi,
I am trying to auto-import data w/ a single-click from several Excel sheets. A sample line:
DoCmd.TransferSpreadsheet acImport, , "tblTS1", filename, True, Product!A1:H100
where
tblTS1 - tbl where I'm putting the data
filename - c: est.xls
Product!A1:H100 - range of data I want in sheet Product
I am having problems w/ the "range" portion of Transferspreadsheet (I know it's this portion, because it works when I pre-name the range in Excel). I have tried all sorts of variations on the syntax:
Product!A1:H100
"Product!A1:H100" or 'Product!A1:H100'
"Product" & ! & "A1" & : "H100"
"'Product'" & ! & "A1" & : "H100"
Product & "!" & A1 & ":" H100
I get the error msg:
The MS Jet database engine could not find the object '$:'. Make sure the object exists and that you spell its name and the path name correctly." or "Syntax error"
I wanted to mistake-proof the data transfer, so I did not want to:
- predefine the range in Excel (under InsertNameDefine)
- have the user enter values in text boxes
Any help w/ the syntax would be greatly appreciated!
Cheers,
dvs :confused:
I have been using a transferspreadsheet cmd for a while now in Access 2003, but when I recently split the database so that the table the import is going to is now linked rather than residing in the same mdb file, I get a numeric field overflow error. I manually made all the values in the import 0, converted it to text, but got the same error.
When I imported the table back into the file, rather than having it linked, the transfer worked fine.
Can you not run the transferspreadsheet command on a linked table or am I missing something?
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code:
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
I have a continuous form based on table "INCOMES" that shows all the payments received, which mediums can be (field "PMNT_MEDIUM"):
- check
- transfer
- taxes
- cash
Table "INCOMES" is filled using another form, but in this particular form I just want to show "check", "transfer" and "cash" (not "taxes") so that I can track all the cash incomes.
Note: taxes are loaded because they appear in my invoices and I need them there to reach the invoice total amount.So my form has a search bar which allows me to search by PMNT_MEDIUM listing all "checks", all "cash" or all "transfer". I can also search by payment number (meaning: check number). To that end I have a "search" button that applies the filter. And I have another button that "cleans" the filtering by "putting a "" in the search-bar and then calling the "on click" of the search button".
What I need is, no matter if I click over the "search" or "clean" button, it NEVER shows me the "taxes".Search button, on click code:
If IsNumeric(Me.busq_chq_med) Then
Me.Filter = "[PMNT_MEDIUM_NUMB] =" & Me.SEARCH_BAR
Else
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*'"
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*' or [INVOICE] like'" & Me.SEARCH_BAR & "*'"
End If
Me.FilterOn = True
Clean filter button, on click code:
[SEARCH_BAR] = ""
Call [Search button]_click
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*'"
Me.FilterOn = True
I have a form that is showing data from 1 table. That table has 12 different fields on it and I want to be able to filter based on selections I make in a combo box in the header of the form. The filter string must be dynamic enough to allow filtering based on 1 criteria selected, or multiple criteria selected. For example:
If I have values in filter fields 3, 5, and 9 I'd want the filter string to be created as follows:
"...WHERE field3 = field3filter.value AND field5 = field5filter.value AND field9 = field9filter.value"
If I have values in only field 7, I'd want th efilter string to be created as follows:
"...WHERE field7 = field7filter.value"
And so on and so on.
I have created some filters before but all of the different VBA syntaxes I'm using seem to come up short.
How can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.
View 3 Replies View RelatedI am using MS Acess2000 and need to make a report that will be passed around with production work. My primary key is the invoice number of the work order. Currently to open an invoice i have a macro, attached to a query with the following qualifiers; Like [Enter invoice]
This pulls up the current record fine.
for the form and flags the folloing in the property filter sectin of the form
(((([CustomerTableMasterRef].[Invoice]) Like [Enter invoice])))
PART 2
Now I am trying to use microsofts how to filter a report using a forms filter...
This picks up on the Invoice query as shown above but does not just insert the query results...
Is there better code or another way to approach this... Currently i am using:
Name:cmdOpenReport
Caption: Open Report
OnClick: [Event Procedure]
Private Sub CmdOpenReport_Click()
If Me.Filter = "" Then
MsgBox "Open an Invoice First"
Else
DoCmd.OpenReport "rptCustomers", acViewPreview, , Me.Filter
End If
End Sub
Using this code not only does my report not detect the correct fields to import data (no data is filled in) but it requerys the invoice or atleast should, which I could do with out all of that code...
Where should i go from here?
I have placed a filter button on a form as a filter and written the following on-click event procedure:
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdApplyFilterSort
Me.Filter = "ACCOUNT_DO_NOT_EMAIL = 'HS'"
Me.FilterOn = True
When I click the filter button I get a blank message box titled 'Microsoft Access" and an OK button, when closed the filter works perfectly.I have checked this procedure in other forms and it works without showing the blank message box.The only difference with this form is that its control source is a union query.
Hello Everyone,Been a while since I needed to post on this forum, but I found something rather disturbing today; either that or I have missed something obvious. I have a large query of financial data, and I filter out certain codes which I don't want to include. The filter is;<>"BL01" And <>"SS01"Which works as it should, filtering out all BL01 and SS01. However, it also filters out any NULL values, which is something I would not expect it to do. When I enter NULL as a filter, I get the records I expect. What am I doing wrong here? I am using Access 2003 SP3 on Windows XP Service Pack 2
View 4 Replies View RelatedIm Writing a Dbase to Log our Drawing documents as they come from our Customers..
Now Each month there maybe the same part number but a NEW revision,
so the part number is on a form, and the Revision is on a sub form linked so there can be more than one attached to one part number,
thats the basic idea, on my query,
I only need the latest revision to be shown, they may change from
1st MPGA-44
2nd MPGA-45
Final 25-2-333
Ive given all the revisions in the sub form an autonumber to keep them in order, so I can Have them sorted on the form Descending,
so it reads the latest Revision on the top when the form is open,
My problem is that the Query shows all the Revisions, most of the time when looking up drawing ppl only want to see the latest revision,
can anyone help me limit to the highest sort number on the revisions ?
Thanks
Kai.
My continuous form (frmThisDate) is based on a query. The table has, among others, a field called 'PODate' which is a date field.
I like to have a box on the form in which I can put a date. Then the records are being filtered by day and month only.
So, when I put in 04/29/2005, I like to get all records for 04/29 regardless of the year.
Any help will be greatly appreciated.
i have a form with three Option groups and one combo box and i am trying to filter it out to the subform that i have on the same form. here is the code, everytime i try to click on Apply Filter. its not working. my filter is not showing on the subform.
Dim strmarket As String
Dim stradvertisers As String
Dim strheading As String
Dim strudac As String
Dim strfilter As String
If IsNull(Me.txtmarket.Value) Then
strmarket = "Like '*'"
Else
Select Case Me.fraMarket.Value
Case 1
strmarket = "Like '" & Me.txtmarket.Value & "*'"
Case 2
strmarket = "Like '*" & Me.txtmarket.Value & "*'"
Case 3
strmarket = "Like '*" & Me.txtmarket.Value & "'"
Case 4
strmarket = "= '" & Me.txtmarket.Value & "'"
End Select
End If
If IsNull(Me.txtAdvertisers.Value) Then
stradvertisers = "Like '*'"
Else
Select Case Me.fraAdvertisers.Value
Case 1
stradvertisers = "Like '" & Me.txtAdvertisers.Value & "*'"
Case 2
stradvertisers = "Like '*" & Me.txtAdvertisers.Value & "*'"
Case 3
stradvertisers = "Like '*" & Me.txtAdvertisers.Value & "'"
Case 4
stradvertisers = "= '" & Me.txtAdvertisers.Value & "'"
End Select
End If
If IsNull(Me.TxtHeading.Value) Then
strheading = "Like '*'"
Else
Select Case Me.FraHeading.Value
Case 1
strheading = "like '" & Me.TxtHeading.Value & "*'"
Case 2
strheading = "Like '*" & Me.TxtHeading.Value & "*'"
Case 3
strheading = "Like '*" & Me.TxtHeading.Value & "'"
Case 4
strheading = Me.TxtHeading.Value & ""
End Select
End If
If IsNull(Me.CboUDAC.Value) Then
strudac = "like '*'"
Else
strudac = "='" & Me.CboUDAC.Value & "'"
End If
strfilter = "[market] " & strmarket & " and [advertisers] " & stradvertisers & " and [heading]" & strheading
With SubForm![frmMeterAdSubform]
.filter = strfilter
.FilterOn = True
End With
End sub
:confused:
how would i go about adding a filter through vb?
here is the code i am using now that works good, but i need to make it filter by office when it runs.
Private Sub Command7_Click()
Me.sa_subform_singlesite.Form.RecordSource = "Test_SingleSiteByCampaign_WestSouth"
Me.sb_subform_singlesite.Form.RecordSource = "Test_SingleSiteByCampaign_WestSouth"
Me.sc_subform_singlesite.Form.RecordSource = "Test_SingleSiteByCampaign_WestSouth"
Me.sd_subform_singlesite.Form.RecordSource = "Test_SingleSiteByCampaign_WestSouth"
Me.se_subform_singlesite.Form.RecordSource = "Test_SingleSiteByCampaign_WestSouth"
Me.sg_subform_singlesite.Form.RecordSource = "Test_SingleSiteByCampaign_WestSouth"
Me.sn_subform_singlesite.Form.RecordSource = "Test_SingleSiteByCampaign_WestSouth"
Me.so_subform_singlesite.Form.RecordSource = "Test_SingleSiteByCampaign_WestSouth"
Me.sp_subform_singlesite.Form.RecordSource = "Test_SingleSiteByCampaign_WestSouth"
Me.sq_subform_singlesite.Form.RecordSource = "Test_SingleSiteByCampaign_WestSouth"
Me.ss_subform_singlesite.Form.RecordSource = "Test_SingleSiteByCampaign_WestSouth"
Me.st_subform_singlesite.Form.RecordSource = "Test_SingleSiteByCampaign_WestSouth"
End Sub
so when i run this instead of it showing all the teams in each subform i need to only show one team per subform.
Thanks
chad
I was using the following code to create a finder form which worked for item that started with FilterBy:
Select Case FindBy
Case "Customer No"
Form_frmCustFinderSub.Filter = "[CustNo] like " & """" & FilterBy & "*" & """"
Form_frmCustFinderSub.FilterOn = True
Case "ShortName"
Form_frmCustFinderSub.Filter = "[ShortName] like" & """"+ FilterBy & "*" & """"
Form_frmCustFinderSub.FilterOn = True
End Select
I wanted to take it a step further and give the user the option to find records that contain FilterBY I tried the following code but got a type mismatch error
Select Case FindBy
Case "Customer No"
Form_frmCustFinderSub.Filter = "[CustNo] like *" & """" & FilterBy & "*" & """"
Form_frmCustFinderSub.FilterOn = True
Case "ShortName"
Form_frmCustFinderSub.Filter = "[ShortName] like *" & """"+ FilterBy & "*" & """"
Form_frmCustFinderSub.FilterOn = True
End Select
Has anyone seen this before?
I have a form which runs a piece of code on close.
The code:
Code:Prior to this code I populate with a recordset "rstChkConfirm"strEmail = rstChkConfirm("EmailContact")DoCmd.SendObject acSendReport, "rptConfirm", acFormatXLS, strEmail, , , "Trade Confirms from Kyte Options" & date, , No, False
I want the report "rptConfirm" to filter on open with the string "strEmail"
Code:Private Sub Report_Open(Cancel As Integer)'Filter report by the email from the previous recordsetMe.filter = "EmailContact = '" & strEmail & "'"Me.FilterOn = TrueEnd Sub
Now the filter does not work, I presume it's because the strEmail is somehow not recognised by the new VB that has opened in the new report doc. Any ideas how I can make it a global var?
Many thanks
Jon
Hi,
I have a General Info form (Form 1) which gathers the info for a specific project. On that form I have an invoice button that pops up the Invoice Form (Form 2) that gathers all the invoice info for the main file in Form 1. Now, I may have more than one entry in Form 2 for the same record in Form 1. How can I apply a filter that will only show records associated to the record specified in Form 1 in Form 2.
Example:
In Form 1, I am at record A and I press the invoice button. How can I only show the records associated to record A in Form 1 in Form 2.
Hopefully I have not confused to many!!
HELP!!
Cheers
I created a Form in a database that has text boxes relating back to a table. The form also has subform that shows the records from two other tables related to the First. There is a many to one relation back to the main table.
As I scroll through the main table the corosponding records in the Subform show up....Everythign is working great.
What I want to add is a Filter to filter the amount of records down.
I am thinking of a button that opens another form.
THis form will have a few dropdowns and text boxes which the users can input values
These values will then be used to create the filter.
Once the filter is ran I need a List box on the main form to pop up with the returned records
Finally when the user selects one of these records in the List box the record will be represented in the Main Form.
Does anyone have any examples of how to do this....
I can create the other form adn add the dropdowns.....I just need help creating the Filter and how to get it to Return the records to a list box in the main form and then connect a selection in the listbox to the main form...
PLease Help
THank you
Hi All,
Just after a code or way to filter client details via a combo box.
Basically select combox drop down menu select the sales rep then it filters by sales rep and shows only the clients for that sales rep. I can get it to filter but it does the first client of that sales rep but not the rest.
Thanks
Karl
Friends,
I want to records in a combobox of a form. I want to filter by the record of that combo (Id) when selected and display it. This is the AfterUpdate code:
Private Sub somecombo_AfterUpdate()
DoCmd.ApplyFilter , "Id = Forms!basedata_trial!somecombo"
Me!somecombo.Requery
Me!somecombo.Enabled = True
End Sub
My problem is, though i am using 'remove filter' from the menu, actually the Id is not changing, so the other fields remain unchanged.
What is the code for Remove filter?
Anshuman