I 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...
I use the follwing code to filter a report based on the listbox selection on a form. Below is the code I use, the problem it will error if the results have an apostrophe in the string.
Private Sub FilterDesc_Click() Dim strWhere As String Dim ctl As Control Dim varItem As Variant 'make sure a selection has been made If Me.ListCarrier.ItemsSelected.Count = 0 Then MsgBox "Must select at least 1 Carrier"
I have a report containing all the fields from my "main table". I want to create buttons on a from that will filter information from the report. For instance, I have a field titled "Priority". I want to create a button that will filter the report to only show records with a "Level 1" priority. Is there anyway I can do this using VBA?
I have a bit of a problem with a database in Access 2013. On 1 of the forms, I have a number of buttons which open other forms and filter the results using an embedded macro. All has been well until a few days ago when error 2950 pop up box started appearing. After fiddling around for a while it all works OK (without actually changing anything) until next time the database is opened. I checked to make sure the location is "trusted" and all seems OK.
I have a Main form with a sub form.The main form is a customer record (form view), and the sub form is all other customers (same fields) datasheet view.I have a navigation bar to filter (using different queries) the results in the sub form
same postcode Same company (different location) everyone except Main form customer
I cannot get a different filter (Query) to show only customers whose name begins with the same letter as the customer in the main form?I have been trying Like but think I am referencing it wrong, as it prompts me to enter a value rather than using the value in the field in the main form.This is what I have in the query (of the subform)
Customer ID: <>[Forms]![NewAllCustomerListF]![CustomerID] Customer Name: Like "[Forms]![NewAllCustomerListF]![CustomerName]" & "*"
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
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.
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.
I have a form where you can select four different options: Health, Dental, Vision, and COBRA, with a button to run a census. Right now, the button runs a DoCMD.OpenReport to open a report named "Census" where there are four text boxes, "Health Coverage Type" "Dental Coverage Type" "Vision Coverage Type" and "COBRA Coverage Type". I want the user to be able to select a checkbox, then filter the report to only enable the text boxes of the corresponding names.
So if someone selects Health & Dental, I want the report to run with the "Health Coverage Type" and "Dental Coverage Type" textboxes enabled/visible, but the other two to be blank.
I have a report on this report there is a Destination City field and a Current City field, loaded from a table. I have a command button that loads a form to filter on the destination city, I have another command button that loads a form to filter on the current city location.
What I would like is, when I use the form to filter the destination city, then load the form to filter on current city, I want the listbox on the form for the current city to only list cities that are associated with that destination. Both form listboxes have the rowsource from the same table, just different fields. Is this possible?
I want to create a navigation form, or any form for that matter that has an embedded report. The report has data arranged by ClientID. I would like to create buttons down the left side of my form (why I thought a navigation form could work) where I can put the client name on the buttons and when I click them the report will filter to only show that clients data. I have client tables and the report data linked in queries, but just need to know how to get started with these buttons acting as ID sorters.
For example: ClientID: 1 = Client: ABC Corp
In my report ClientID 1's data is grouped along with all other client ID's.
In my form I'd like buttons down the left side with ABC Corp written on it (followed by buttons for the other clients) and then when I click the ABC Corp button only ABC Corp's data shows on the page from the embedded report...
When I'm applying a sort and filter in a form, Access is updating the Filter and Order By properties of the form, so that it is possible to re-use in conjunction with Filter on Load and Order By on Load properties. However, a consequence of this is that when the form is closed, it prompts the user whether they want to save the design of the form. I want to circumvent this as I don't want to re-use the sort and filter and I don't want to be prompted to save the design of the form.
Although I can circumvent this by closing the form using a method that doesn't prompt for saving, the additional complication here is that the form in question is in the Navigation subform of a Navigation Control. Hence when I click on a another Navigation button, it (not me) closes my current form and hence prompts me whether I want to save the design of the form (if I have been sorting and/or filtering). I can't see how to circumvent this and the prompting is resulting in unacceptable usability.
I have a table that has the fields Scheme no, Title, Area, Pole No, Rate No, Description, Quantity. Each Scheme No has a number of Poles attached to it, and subsequently each Pole has a number of different rates attached (eg of table shown below)
On the corresponding form I have ID No, Title and Area in the Form header as they are the same for all fields. Therefore in the detail section I want the rest of the fields. The problem I have is I want the user to be able to choose a pole no from a combo box and show up only the corresponding rates to that pole. So for instance below the user could select in the combo box Pole no YP08 and only the rates used on that pole would show up (in this case it would be just HV99 and its description).
I have a field of user inputed data. The data are dates. The standardized way we'd like dates entered is DMMDDYYYY. The first D is inconsequential, it just needs to be present, whereas M means month, the D means day, and the Y means year.
I would like to filter my results to ONLY see the data that has been inputed correctly. Can you filter data in a query based on formatting, and if yes, how so?
(The title is somewhat misleading because I don't want to filter the results necessarily, I just want the incorrectly entered data to not even show up period)
My understanding of the LIKE operator seems to be deficient for filtering a query when using the Access query pane. I have a combo box that lists the values to be filtered. One option in the combo box is "No Selection". If "No Selection" is selected, I want the filter turned off, in other words ALL records are shown.
In theory the following code would seem to be correct when placed in the criteria line. It does not work: IIf([forms]![type5frm].[combo2]=24,Like "*",[forms]![type5frm].[combo2])
If the value of COMBO2 =24 (No Selection), then I would like all records to show up, but no records are displayed. The FALSE side of the IIF statement works correctly.
As an experiment I tried LIKE by itself and it displayed all the records: LIKE "*"
Another failed try:IIf([forms]![type5frm].[combo2]=24,[agency]) Like "*",[forms]![type5frm].[combo2]) AGENCY is the fieldname.
A more exotic attempt that failed:IIf([forms]![type5frm].[combo2]=24,Val(Str([agency]) Like "*"),[forms]![type5frm].[combo2])
Not sure whether this really belongs in the Forms, or VBA or forum, picked Forms because I think it stems from that, despite probably involving VBA in the solution...
I'm getting used to using filter by form and find it quite useful for those off-the-cuff queries, however, it is possible to 'capture' the records so that the results of the filtering can be passed to a readymade report (so that they can be printed out in a more presentable manner than the table view)? (To what would the Record Source of the report be set?)
I've created a table (Films). One of the fields is linked to another table (People). The table 'People' is a list of people with a categorization next to them (e.g. actor, director etc)
Is there a way that I can create a field called 'Actors' in the table 'Films' and for the drop down only to display those people who have been categorized as an actor?
I have a query that I am using in VBA to select the number of days equipment is past due for service. I have an equipment table with a last maintenance field (tbleqDP with field DPLstMnt) and another table (tblmaintdue) that defines the maintenance interval. I have a query (qrynumdays) that finds the number of days from tblmaintdue for the equipment type based on a value in cboMaintEquipType combobox. The VBA then generates a report based on which type of equipment is selected using a select case statement. An excerpt of the VBA is below:
Select Case cboMaintEquipType.Value Case "DP" Set qdf = db.CreateQueryDef("qryoverdue", _ "Select DPTag, DPLstMnt, DateDiff(""d"",DPLstMnt,Now())-NumDays AS Overdue from tbleqDP, qrynumdays where Overdue > 0") DoCmd.OpenQuery "qryoverdue" DoCmd.OpenReport "rptDPmntovrdue", acViewPreview
I am having problems with the "where overdue > 0" statement. It is asking me for parameter value for overdue. How can I get this to automatically filter out any values that are negative - in other words, don't report on equipment that is not past its maintenance interval due date.
Also, I think there is a way around how I am naming my query in the createquerydef function because later I just delete the query after the report is run. Is there a better way to do that.
I have a query form that allows user to search by two criteria (in which, user can select "And" or "Or" clause for the two criteria). I also allow user to filter the results by date range. On the form, I have two command button, one will prompt the results in a query table, and the other will prompt a report.
Problem 1 I manage to prompt report with a date range (i.e. two unbound text boxes for start date and end date). But, I dont' manage to disable the filter if the date range is null. Below is the code for this report command button. Should I use a toggle button to make a select case?
Private Sub cmdReport_Click() Dim varItem As Variant Dim strDocName As String Dim str1MainCate As String Dim str2MainCate As String Dim str2MainCateCondition As String Dim strDate As String Dim strSQL As String Dim strFilter As String ' Build criteria string for 1st ComboBox For Each varItem In Me.fstMainCate.ItemsSelected str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'" Next varItem If Len(str1MainCate) = 0 Then str1MainCate = "Like '*'" Else str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1) str1MainCate = "IN(" & str1MainCate & ")" End If ' Build criteria string for 2nd Combo Box For Each varItem In Me.SecMainCate.ItemsSelected str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'" Next varItem If Len(str2MainCate) = 0 Then str2MainCate = "Like '*'" Else str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1) str2MainCate = "IN(" & str2MainCate & ")" End If ' Get 1toggle button condition If Me.optAnd2MainCate.Value = True Then str2MainCateCondition = " AND " Else str2MainCateCondition = " OR " End If ' Build SQL statement strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _ "WHERE NewsClips.[1CategoryMain] " & str1MainCate & _ str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & ";" ' Build criteria string for Date If Not IsNull(Me![dateTo]) Then strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#" 'Format(Me.dateTo, "mm/dd/yy") Else strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#" End If ' filter string strFilter = strDate ' Open report strDocName = "RptCateDateQry" DoCmd.OpenReport strDocName, acViewDesign, , strFilter With Reports(strDocName) .RecordSource = strSQL .Filter = strFilter .FilterOn = True End With DoCmd.Save acReport, strDocName DoCmd.OpenReport strDocName, acViewPreview Exit_cmdReport_Click: End Sub
Problem 2 I have no idea how to filter the query results by date range in the query table. Below is the code of the query table button, which do not offer the filter feature. Would really appreciate it if you can give me some advice.
Private Sub cmdOK_Click() On Error GoTo cmdOK_Click_Err Dim blnQueryExists As Boolean Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command Dim qry As ADOX.View Dim varItem As Variant Dim strDate As String Dim str1MainCate As String Dim str2MainCate As String Dim str1MainCateCondition As String Dim str2MainCateCondition As String Dim strSQL As String ' Check for the existence of the stored query blnQueryExists = False Set cat.ActiveConnection = CurrentProject.Connection For Each qry In cat.Views If qry.Name = "QryCateDateForm" Then blnQueryExists = True Exit For End If Next qry ' Create the query if it does not already exist If blnQueryExists = False Then cmd.CommandText = "SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips" cat.Views.Append "QryCateDateForm", cmd End If Application.RefreshDatabaseWindow ' Turn off screen updating DoCmd.Echo False ' Close the query if it is already open If SysCmd(acSysCmdGetObjectState, acQuery, "QryCateDateForm") = acObjStateOpen Then DoCmd.Close acQuery, "QryCateDateForm" End If ' Build criteria string for Date If Not IsNull(Me![dateTo]) Then strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#" 'Format(Me.textStartDate, "mm/dd/yy") Else strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#" End If
' Build criteria string for 1MainCate For Each varItem In Me.fstMainCate.ItemsSelected str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'" Next varItem If Len(str1MainCate) = 0 Then str1MainCate = "Like '*'" Else str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1) str1MainCate = "IN(" & str1MainCate & ")" End If ' Build criteria string for 2MainCate For Each varItem In Me.SecMainCate.ItemsSelected str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'" Next varItem If Len(str2MainCate) = 0 Then str2MainCate = "Like '*'" Else str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1) str2MainCate = "IN(" & str2MainCate & ")" End If ' Get 1MainCate condition If Me.optAnd1MainCate.Value = True Then str1MainCateCondition = " AND " Else str1MainCateCondition = " OR " End If ' Get 2MainCate condition If Me.optAnd2MainCate.Value = True Then str2MainCateCondition = " AND " Else str2MainCateCondition = " OR " End If ' Build SQL statement strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _ "WHERE NewsClips.[1CategoryMain] " & str1MainCate & _ str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & _ str1MainCateCondition & strDate & ";" ' Apply the SQL statement to the stored query cat.ActiveConnection = CurrentProject.Connection Set cmd = cat.Views("QryCateDateForm").Command cmd.CommandText = strSQL Set cat.Views("QryCateDateForm").Command = cmd Set cat = Nothing ' Open the Query DoCmd.OpenQuery "QryCateDateForm"
' If required the dialog can be closed at this point ' DoCmd.Close acForm, Me.Name ' Restore screen updating cmdOK_Click_Exit: DoCmd.Echo True Exit Sub cmdOK_Click_Err: MsgBox "An unexpected error has occurred." _ & vbCrLf & "Procedure: cmdOK_Click" _ & vbCrLf & "Error Number: " & Err.Number _ & vbCrLf & "Error Description:" & Err.Description _ , vbCritical, "Error" Resume cmdOK_Click_Exit End Sub
Sorry for posting this question again, as I thought it's better to make it a seperate posting, rather than a reply to my early post. Your advice will be greatly appreicated.
I'm creating an item look-up form with 2 criteria that I wan't to auto-filter as the user types. I'm placing my code in the Change event of the textbox, and only calling the .text for the current textbox (.value or no property at all for the other). The filter works smoothly and as intended until it returns no results in my table, and then it pops me with the error. I've tried forcing focus to the textbox in different places throughout the code, but to no avail. If I use the .value or no property for the current textbox, I'm not getting the CURRENT text, but the old text before it was changed, so the filter doesn't work properly.
Private Sub txtVendor_Change() Me.FilterOn = True Me.Filter = _ "((strSearchVendor Like '*" & Me.txtVendor.Text & "*' AND strSearchItem LIKE '*" & Me.txtPartNum & "*'))" Me.txtVendor.SelStart = Len(Me.txtVendor.Text) End Sub
It usually pops the error on the last line "Len(Me.txtVendor.Text)", but I've had it do it on the Me.Filter line as well (only when debugging and messing with next statement placement).
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 kept the names as simple as I can for the example sake to which I can substitute my names in after.
I have a split form - form1, I have a check box on this form - checkbox1 - That I would like to use to make only the checked results from the table - table1 - show and if it is not checked to show all the results. How would I go about doing this? The table field is called field1.
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.
The company I work for has branches in multiple states and we use Access to organize all their tax requirements. We print out reports for every month, and my company would like to create a pop-up window that will let us filter the report when it starts up so that it automatically shows only the dates that we wish it to. How do I create a filter that pops-up when the report starts?
I have a QBF, and when user inputs the search criteria and clicks on print button, a report prints out based on that search criteria. I would like that report to include the search criteria as well. How can I do that?
I am not so good in Access, so please be as much specific as one can get, when suggesting a solution.
I'm a new user with access and I'm trying to build a "Status" report based on criteria within the database. I've setup a form where the user would choose which "Status" they want to print on the report but in all my reading (two days worth) I can't figure out how I should accomplish this task.
I'm attaching the database and the form is "frmStatusReport". If someone could take a look at it and give me an idea of the best possible solution, it would be greatly appreciated.
Maybe someday I can pass my knowledge on to another newbie...
