Queries :: Payment Method - Multiple Filter Criteria On Same Field
Jul 30, 2014
I am using MS Access 2007.
I would like to display a report based on the table called "expenses", filtered by:
- "from" (datefrom field) and "to" (dateto field) date on "payment date" field;
- multiple criteria on same field called "payment method" (I would like to include only payment methods "check" and "credit card", but not the other payment methods in the field, such as "cash", "transfer", etc.)
For that end I made a query based on the table "expenses", and in the "payment date", in the criteria field, I entered:
between [form]![formname]![datefrom] and [form]![formname]![dateto]
This works fine so far, however when I attempt to add multiple criteria on the "payment method" field, it does not filter accurately any longer. In the same row of the criteria field where I completed the date criteria, I enter "check". In the next row, same field, I enter "credit card".
Since it doesn't work, I tried putting both arguments in the same line as the date criteria (always in the payment method field) as: "check" and "credit card" but still does not work (now it filters the payment method correctly, but the dates filter appear as if I have never completed them).
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code: TRANSFORM IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT SELECT PT_LEVEL.INF_YEAR, PT_LEVEL.INF_MONTH, PT_LEVEL.UNIT
What I am trying to do is fairly simple i just dont have the ability to correctly code what i want to do.
I want to filter my query based on some criteria in multiple columns. But i only want the query to filter based on the specific criteria if a checkbox has been selected.
Basically i want the criteria for one of the columns criteria to read
IF a check box "Check0" is selected THEN filter the column to only records that = 1 and if "Check2" then filter all records that = 2
I have query with a calculate field to finds the next service due date but I'm having a problem getting it to only show services due dates in the next 30 days.for some reason I cant add a criteria date()-30...I have to calculate the next service it takes service intervals from maskservicemonths field then find the last service date and generates the next service due date NextService: DateAdd("m",[MaskServiceMonths],[FindLast]).
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.
This is the trimmed down part of the query that is causing the result.
Code: SELECT [tblP&E].PnE_ID, tblUsage.Measurement AS UOM, IIf([tblUsage]![Week_Ending]=#6/20/2014#,[tblUsage]![Usage],"") AS [June 20], IIf([tblUsage]![Week_Ending]=#6/27/2014#,[tblUsage]![Usage],"") AS [June 27], IIf([tblUsage]![Week_Ending]=#7/4/2014#,[tblUsage]![Usage],"") AS [July 4], IIf([tblUsage]![Week_Ending]=#7/11/2014#,[tblUsage]![Usage],"") AS [July 11] FROM tblUsage RIGHT JOIN ([tblP&E] LEFT JOIN tblCosts ON [tblP&E].[PnE_ID] = tblCosts.[PnE_ID]) ON tblUsage.PNE_ID = [tblP&E].PnE_ID;
I have two tables, Students and AttendanceRecords.
Students just has studentID and studentName
AttendanceRecords has AttRecID, studentID, presence, thedate
I'm looking to create what looks like an Excel grid, with the last 10 days as columns and the student names as rows. All the cells in middle will be filled with the values of 'presence' for that student/day (e.g., P for present, A for absent).
Here's something I'm currently considering.
-I could make 10 queries, each using LEFT JOIN to connect studentName with presence & thedate on studentID, varying the 10 queries only in that 'thedate' will have a criteria of Date() -1 , Date() -2 , etc. -If I'm understanding it correctly, I'll then have 10 tables, each containing 3 rows -- student name, presence, and the date (with each table having only 1 date repeated throughout). -I could then join those 10 queries together on studentName, theoretically resulting in 1 big table with all the student names and the corresponding presence values for the last 10 days
If I do that, I could make a form in Continuous view and have each row show the studentName and 10 text boxes closely bunched up with presence values.
That seems very inefficient? Making 10 queries separately and then manually merging them seems redundant.
Also, now that I think about it, will the final product end up being read-only, or if the user changes one of the presence cells will it update the corresponding record in AttendanceRecord?
I am trying to make a query that outputs the minimum "Need Year" AND ALSO if the need year was equal to 9999 it shows "NO DATA".
This is what I have so far for checking the minimum value:
field: Need Year: MinofList(PMS_output!pqi_ny,PMS_output!iri_ny,PMS_ output!sdi_ny,pms_output!sai_ny)
I am not sure if I should be putting it in the criteria to check whether this minimum value (need year) equals to 9999 or not and if it does, it says "NO DATA" instead of 9999.
I need to update the periodtype field in my table depending on different values in the field Formtype- I am looking to do this without having to use VBA. I have the following fields in a table - I want to update the value of the field Period_type as follows -
When Formtype is 10-Q, update Periodtype to "Quarterly"
When Formtype is 10-K, update Periodtype to "Annual"
The current value of Periodtype for both formtypes is "Semiannual"
I'm working with a table of bird survival data I am trying to summarize in a query. I've got a bit of a roundabout way to achieve my goal, but I'm curious if there is a simpler approach.
Background : In my table, each row represents a day I check a given nest and includes a [Nest ID] (not unique, multiple visits to each nest), a [visit ID] (auto numbered, so it's a unique value for each visit at each nest), the calendar day I visited a nest [Date], and [Survive] (1 or 0) depending on whether a nest survived or failed.
I'm trying to convert this detailed table into one that is more concise. Instead of each visit to a nest being a row, each nest becomes a row with 4 fields: The Nest ID, the minimum date (the day I found a nest), the last day a nest was checked (Max[Date]), and the last day a nest was checked alive (essentially max date where survival=1).
My current solution is to run 3 separate queries. The first queries the max date where survival=1, the second queries the max and min dates regardless of any other criteria, and the third brings both queries together.
I am curious if there is a way to create the same final product in a single query rather than doing multiple ones as I have done?
I have a form bound to a table and I need to display and edit records. I want to narrow down the records by applying two filters; one combobox to select the ClientID and another to select the date. What I have so far is this:
Private Sub CboClientID_AfterUpdate() Me.Detail.Visible = True CboDate.RowSource = "Select AppointmentDate " & _ "FROM tblSample " & _ "WHERE ClientID = '" & CboClientID & "' " & _ "ORDER BY AppointmentDate" End Sub
then in the cboDate afterupdate event I have:
Private Sub CboDate_AfterUpdate() Me.Filter = "ClientID = '" & Me.CboClientID & "' AND [AppointmentDate] = #" & Me.CboDate & "#" Me.FilterOn = True End Sub
This appears to work with some records and not with others and I don't know why?
I have set up a query that will pull data from table1. There are two fields in my query to which I will filter by entering certain basic criteria. In the criteria line of my query field, I have entered "800" to only return this type of data. If I run the query, it returns only those fields, which is exactly what I want. However, I also need an additional filter in another field. I have entered "4", to return only those data matches.
So, my entire query is based on returning only the data from table1 that matches the two criteria ("800" and "4"). Problem is the query will not return any data when I enter the "4". Any ideas why this would happen? There does appear to be an extra space in table 1 for the field containing "4". I have tried to set the criteria to match, but it still does not return any values.
I have a large table with information about different cars, called "Car Metadata". In this, each car has a unique identifier ("Car Code"), the make ("Manufacturer Name") and the model ("Short Model").
What I want to do is create a form with dropdown menus.I have a first combo box with all the makes in the Metadata table.
What I am having trouble with is that I want a second combo box underneath which would only show the models which correspond to the make selected in the first box.E.g. someone could select Ford in the top box and the second box would only show Fiesta, Focus etc.
Once it has done that I want it to be able to write the Car Codes for the records which match up to the make & model selected to a new table. Just to complicate things further, there can be multiple records with the same make and model but different Codes, I want all these multiple codes to be written to the final table.
I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org .gov .du .pk .dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
Below is what I have tried but in vain and it says atmost you can atmost one criteria row in sub query
SELECT tableA.WEB_ADDRESS FROM tableA WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org .gov .du .pk .dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
SELECT tableA.WEB_ADDRESS FROM tableA WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
Hello everyone. I'm a little new to the Access scene. I have a strong background in C, C++, Java, and assembly. Seems all the access books out there fail to mention the need to know vbs, heh.
Anyhow, I have a form ([WrkR]) based on a table ([ORDERS]) that has a customer's name ([Tables]![ORDERS]![name]), total payments to visa ([Tables]![ORDERS]![visa]), and total payments to cash ([Tables]![ORDERS]![cash]), and total payments to debit ([Tables]![ORDERS]![debit]). My form also has three rows, each containing a text box which holds how much a person has paid, and a combo box ([combo1], [combo2], [combo3]), that allows the user to choose what payment type was used. I have included a screen cap to demonstrate this.
I need to be able to create a function or macro, anything that will sum up the appropriate totals and store them in the corresponding columns in my table. Like in my example, there are 2 visa costs and a single cash cost that should be added up and stored in my table in the appropriate fields.
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
I was trying to filter a word using Not Like "word" in my query cirteria, some data is blank also, From this record i want to retrieve the result. But when i use Not Like Criteria, filter is working but blanks columns are also filtered.
I want to display all records (including blanks) except what i shown in the criteria.
I have two date fields in a table. I need to find the days between these dates and, if its greater than 7 days, I want to display the record in a report, so far, I have found a hand function that allows me to enter two dates and it returns a long data value representing the number of days in between the two dates. (google "I've developed the following code to count the business days between 2 dates." and its the second one that starts as SOLVED I made no mods to it as it does what i need it to do.
So, i added this to its own module within the data base for use within a query. My test query basically displays the unique ID, the start date and the end date and then displays the values returned from the function. here is the SQL:
SELECT [Main Table].[Unique ID], [Main Table].[Start date], [Main Table].[End Date], (Business_Days_Between([End Date],[Start date])) AS [Days between] FROM [Main Table] WHERE ((([Main Table].[Start date]) Is Not Null) AND (([Main Table].[End Date]) Is Not Null));
When ran, this Query works... However, when I enter a criteria like =2 or > 7, it says data type mismatch. I have even attempted the CInt() function to make sure its formated as int but i continue to get the same error.
Is it possible to do a criteria like the "Between" to pull data like a work order number?
Ex WO#: WO5551212
I would like to be prompted to enter a Work Order Number when I run a query, then have the query display all of the data for that particular work order number.
I am using a macro and the SendObject method to email a snapshot of a report to 29 different program coodinators(which is working fine). Using the macro i have 29 exact reprorts except for the different criteria in the ProgramID field for each program. I would like to get away from the macro and code the SendObject method and specify the criteria for the ProgramID field for a single report. I will most likely use a SelectCase statement for the 29 different programs. Can anyone tell me how to set the varible criteria on a single report using the SendObject method.
How can I have a user enter the WHERE criteria in the Set Rs statement below.
Set Rs = CurrentDb.OpenRecordset("SELECT [Net] FROM [tblRecap] WHERE [Yr] = 2000 and [Event] = 'lancaster'")
I would like a message box to ask the user to define the [Yr] = and [Event] = fields since they would change from time to time and then pass the values to the OpenRecordset method. Is this possible? Can someone help supply the code?
A parameter query won’t work because when you manipulate these queries in DAO through VBA you need to supply the parameter value before you open the recordset object. If you don’t DAO generates an error.
Here is my code:
Option Compare Database Option Explicit
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim ConsqWin As Long Dim ConsqLoss As Long Dim tmpWin As Long Dim tmpLoss As Long Dim Rs As DAO.Recordset Set Rs = CurrentDb.OpenRecordset("SELECT [Net] FROM [tblRecap] WHERE [Yr = 2000 and [Event] = 'lancaster'") Do While Not Rs.EOF Do While Rs!Net > 1 tmpWin = tmpWin + 1 Rs.MoveNext If Rs.EOF Then Exit Do Loop If Rs.EOF Then Exit Do If tmpWin > ConsqWin Then ConsqWin = tmpWin tmpWin = 0 Rs.MoveNext Loop
Set Rs = Nothing
End Sub
By the way the YR field is a number and not a date so the WHERE clause doesn’t need #2000#
Some students pays fee for one month, some for two months and some 3 months.
School administration requires me to print notices for every student according their payment mode after comparing with next due months and already paid months.
Some notices will show their fee for the months as
(Id= 1 Jan-14, Feb-14), (Id=2 Jan-14, Feb-14, Mar-14), (Id =3 Dec-13, Jan-14) Considering as Id 3 has not paid due month i.e. Dec-13 Fee and his payment mode is 1 month only.
I have a form where I am trying to use 4 combo boxes(nomenclature,BPN,vendor, and reference) to filter a list box containing part numbers. The way I have it set up right now is in the listbox it is searching for each field and then in the criteria section i have [Forms]![myform]![respectedFieldsCombo].
This works when selections are made from the combo boxes but when one is blank (not being used to filter) then I assume it passes null for that value and the listbox doesn't return anything. I have tried to make it so the listbox ignores null values but im still having this problem.
For example: If i only have a selection for the vendor combo box then i want the list box to show all respective part numbers for that vendor, where nomenclature or any of the other fields are irrelevant. I also want to be able to stack these filters upon every new combo box selection.
