Complex Expressions Or VBA Functions As Criteria In A Query
Aug 4, 2005
Hi there,
I have a list (listA) which has a record source of a query object (queryA).
The form (formA) which has listA in it, also has a text box for some of the fields requested in queryA. The goal is to provide a flexible filter for the records in the list, so the user can just type in 'K' in text box filter_name, and they will only see records matching the entry.
N0w, the scene is set! I can easily go into the query object, and in criteria type something like this:
Like "*" & [Forms]![formA]![filter_name] & "*"
This works! It flexible filters out the records matching the text box value..
But, if the record has NULL for the name, then it won't even match "**", which is the minimum criteria. So therefore I get exclusion of records, which I dont want.
So I thought perhaps there might be some control structure I can use in expressions... some if, else, end if statement? Does anyone know how to do this?
Alternatively I thought, I could write a function like this..
Function criteria(ByVal val As Variant) As Variant
If (IsNull(val) Or IsEmpty(val) Or val = "") Then
'do nothing
Else
criteria = "Like *" & val & "*"
End If
End Function
And then in the queryA object for criteria you just use:
=criteria(Forms!formA!filter_name)
But this doesn't seem to work.. and I dont know how to debug this...
Any ideas? I thought the function was a neat solution, but it seems not!
Alright, I've got all the pieces to this puzzle, I just can't fit them together properly. I have two tables, tblTickets and tblTicketNotes. They are in a one-to-many relationship, there can be multiple Notes attached to a single ticket. I am trying to set up a query that will pull the first (earliest by date) note for each individual Ticket. Here are the fields from each table that would be of concern here:
tbTickets TicketID - Autonumber - Primary Key Issue - Text - I will be using this later as a criteria to limit with, but not neccessary
tblTicketNotes NoteID - Autonumber - Primary Key TicketID - Long Integer - This is the foreign key of the relationship DateStamp - Date/Time - This is the Note Date, I only want the first (earliest date) one Content - Memo -This is the note information I want
So all I want is the Content of the first/earliest Note for each individual Ticket. I know this should be fairly easy, but I am at a loss.
i'm trying to use a switch function in a query but i'm unfamiliar with the format a bit. here is what i'm trying to do...
switch([Radios]![Warranty] and [Radios]![HousingWarranty]=true,[Radios]![Change front housing]*(25/60*28), [[Radios]![Warranty] And [Radios]![PortableHousingWarranty]=true], [Radios]![MaterialsUsedPrice1])
basically first statement is condition second is execution. I need to add more but i'm getting an error from this much...if I was doing this in vb.net I would just do if, ifelse, ifelse, ifelse...but...
I've got two tables in my Access 2010 database - both are identical in structure, the difference being one (Dove) contains old data and the other (Dove Data File) contains updated data. The primary key for these tables is "TowerBase".
I want to use an update query to update only the changes from the Dove Data File table into the Dove table - but for certain records only.
I've managed so far to get a query which displays only records that have changed in a particular field (Bells). Up to here I think is all good...
SELECT [Dove Data File].* FROM [Dove Data File] INNER JOIN Dove ON [Dove Data File].TowerBase = Dove.TowerBase WHERE (((Dove.Inactive)<>True) AND ((Exists (SELECT NULL FROM Dove WHERE [Dove Data File].TowerBase = Dove.TowerBase AND [Dove Data File].Bells <> Dove.Bells))=True));
But now I want to add in criteria and this is the bit I'm struggling with. I need this query to now display only records where there is no associated record in my Visit Dates table. In other words, the Visit Dates table has the "TowerBase" ID along with a visit date. These records I do not want to see in the query, as I don't want to update any changes for these from the Dove Data File table to the Dove table.
Is there anyway to use an if statemet to define the criteria for a query?
as in
if forms!RunReport!cboname <> "" then query all names that equal forms!RunReport!cboname end if
also is there any way to use a dlookup function (or another similar function) to define several criteria for my query
ex. i have a table, "modules", with a list of modules, each classified as "digital" or "analog" and i want to view all records containing analog modules from a different table, "info"
I have a query which i would want to show records between January 2004 and September 2004, assuming the [Current Month] is October 2005.
The idea is to have a listing of sold policies between the first month of previous year and the month (of previous year) prior to the current month/year.
Here's the criteria i used: Between DateSerial(Year([Enter: Current Month])-1,2,0) And DateSerial(Year([Enter: Current Month])-1,Month([Enter: Current Month]),0)
The criteria only shows records between July 2004 and September 2004.
Any way to use multiple count functions in a query with their own individual filters without affect the others?
For example:
SELECT [E&I Table].System, [E&I Table].DeleteRecord, Count([E&I Table].[Status#1]) AS [CountOfStatus#1], Count([E&I Table].[Status#2]) AS [CountOfStatus#2] FROM [E&I Table] WHERE ((([E&I Table].[Status#1]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#1]) Like "Completed" Or ([E&I Table].[Status#1]) Like "Ready for T/O" Or ([E&I Table].[Status#1]) Like "Reviewed by JVV") AND (([E&I Table].[Status#2]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#2]) Like "Completed" Or ([E&I Table].[Status#2]) Like "Ready for T/O" Or ([E&I Table].[Status#2]) Like "Reviewed by JVV")) GROUP BY [E&I Table].System, [E&I Table].DeleteRecord;
I am trying to count in each column of the E&I table with criteria using WHERE but the problem is when you have more than 1 WHERE it affects the other columns as well... tried a bunch of different ways and I am now having to create seperate Queries than combine them using another Query...
I'm new to this forum and could use some help on a simple Access app. I have attached the relations for the db. I need to collect some data to a subform as: tblFunctionCodes.FunctionCode, tblAccounts.AccountNumber, Monday (hours), Tuesday (hours), Wednesday (Hours), Thursday (hours), Friday (hours), Saturday (hours), Sunday (hours) Where I have the tblEmployees.EmployeeID, tblDates.DateWeek and tblDates.DateYear as variables. The weekdays need to be an expression with my current design, which seems to work ok from Monday to Saturday, but when I throw in Sunday Access says the query is too complicated.
I have this query to get the data, but with no expressions (can't get it to work):
SELECT tblFunctionCodes.FunctionCode, tblAccounts.AccountNumber, tblRegistrations.Hours, tblDates.DateWeekDay FROM tblFunctionCodes INNER JOIN (tblEmployees INNER JOIN (tblDates INNER JOIN (tblAccounts INNER JOIN tblRegistrations ON tblAccounts.AccountID=tblRegistrations.AccountID) ON tblDates.DateID=tblRegistrations.Date) ON tblEmployees.EmployeeID=tblRegistrations.EmployeeI D) ON tblFunctionCodes.FunctionCodeID=tblRegistrations.F unctionCodeID WHERE (((tblRegistrations.EmployeeID)=[intEmployeeID]) AND ((tblDates.DateWeek)=[intWeek]) AND ((tblDates.DateYear)=[intYear]));
I've been trying to use that as a foundation for other queries but I just can't get it to work. Does any of your guys have any ideas? Maybe an INTERSECT query??
Anyway I'm out of ideas and very pressed to makes this work. Any ideas are welcome!
Is there a way or what is the expression used if I want to do a running total in a query under a field called "Amount Donated". In addition, I would like to filter by selection using the DonorID as the primary key to show the total.
I'm trying to create a query which will retrieve holidays for a specific week, so it would allows me to enter WeekDesc (e.g. week 1) and Line (e.g. Line 1) and then retrieve some information relating to holidays. I have gotten this far already.
However, I've just realised however that for example, if I enter Week 1 and Line 1, it will only retrieve those records for which (in the Holidays Table) have Week Description as Week 1 (this other fields in the table are HolidayID (pk), PersonID (pk), StartDate (pk), EndDate and Approved (checkbox). However if the holiday runs for over a week and runs into Week 2, if i run the query for Week 2 and Line 1, it will not retrieve that holiday.
I want to be able to update stock levels by clicking a button on a form. The button executes code to run the updatequery. Details of Items are listed on a sales form. The fields include item code and quantity sold. Details of the items are stored in a table called 'Items' - fields include item code and quantity in stock. I want the clicking of the button to compare the item code in the form with the item codes in the Items table, and then reduce the quantity in stock by the quantity sold in the form.
I am trying, and failing quite spectacularly to create a single query that will contain multiple expressions on the same field.
Please see attached jpg for example.
SQL: SELECT Count(StockData.status) AS FaultyPCs FROM StockData HAVING (([StockData].[Status] In ("Waiting Repair") And [StockData].[DeviceType] In ("Thick PC")));
What I want to be able to do is then add additional expressions to filter and count in the same way for "Thin PC" "Thin Laptops" "Thick Laptops" and so on.
But when I add one of these expressions to the next column in the builder, it seems to apply itself to the expression to it's left, causing that one to now give a result of 0.
Is there a way of separating one from another or do I have to create a separate query for each expression and then use another query to pull them all together (as I have done in another situations - but this will involve creating probably 20 separate queries.
I want to have Access run a query, open a report, and print the report based on the ID number of the form that I have open. Is this possible?
The situation would be, I have a form that uses the the primary key as a record number. After I enter the data into the form, with the form open I want to add a button to open and print a report of the current record number.
Hello , I had incountered with a huge problem in my project and I need your help guys. I have a huge table contains alot of data about many people- I wanted the data will be checked and sent to a query. Here is an example for a little project beacuse I couldn't have the big one. (This example works fine) My big project is pretty the same but after I try to get the query out I get an error that the query is too complex....(It's really too big when you have 20texts to be checked with 9000 fields) If you check the query fields you may see how the OR is getting separted and because every text has OR statement everything is hanged by everything. Someone told me that I can't use query options and mannge it and I should use VB SELECT option - but I can't make it work too. So I can't show you the real example at all beacsue I can't have it to my computer and It will take alot of time to have 9000 different cells ; SO if someone knows what I am talking about and ever encoutered it , I would be really thankful ! (Also - You may see some problems with the OR ("") but I mannged it to work with the BIG project so it doesn't matter) I don't want you to focus the conditions but just the problem itself ...." the query is too complex"...
This one is a pain in the but. I tried everything, but why would it just won't work. It always says the query is to complex. Have included the database, anyone a solution?
I need to perform a query on a database (not designed by me) that is not normalized. In fact it is only one table with numerous fields (many of them Date/Time). I need to query the table based on the date fields only.
Basically the table is used to track when specific functions are completed to determine the time required to perform the individual tasks (accuracy to one day is acceptable) and find the ones which are falling behind. Since several individuals are required to complete each project each step has an average value (based on historical data.)
The following is a short narrative:
1. Step 1 is scheduled for 1/5/05 and the task is not started untill 1/7/05. This is not acceptable. There is a 1 day window. I need to flag this record (via report) if the start date exceeds the schedule date by more than 1 day. If a start date has been entered this record does not need flagged (regardless of the alloted time).
2. Step 2 is based on the amount of time alloted to complete the task once started. If the task takes more than 2 days the project needs to be included in the query for the report. As above, if the date is entered the record is not needed since there is no need to try to determine the when the project will start.
3. Step 3 measures the number of days from the project completion untill the product is sent to the central office. If the time exceeds 2 days the record needs to be included in the dataset. Again, once a date is entered in the received field there is no need to include the record.
There are a couple more steps but they follow the same criteria as the first three.
I have not been able to figure out how to get these requirements entered into the query design view.
The query I'm trying to get is: Who owes me money for the service and How much.
Each car had several treatments in the past and some have missed a payment or I didn't notice and just issued a new order.
I need a query that runs through the entire database, does a summary of all the amount owed to me per Car, and then deducts the total payments made per Car. If the balance is > than Zero,. Show me that car and the bottom line amount.
Hi guys, long time surfer, first time poster here :)
We use a prehistoric Process Plant design software package, and it stores all its information in Oracle 8i.
A report I need to pull takes data from a stack of tables, each with a unique number.
PDTABLE_113 contains a list of models, each model having a unique PARTITION_NO.
Every PARTITION_NO entry has a matching table called PDTABLE_21_XXXX where XXXX is the PARTITION_NO of the model. Each PDTABLE_21_XXXX has a row for every piece of equipment in the relevant model.
PDTABLE_21, for argument sake, has the columns EQUIPMENT_ID, EQUIPMENT_DESCRIPTION, and EQUIPMENT_STATUS.
Is there some way to query the database so I get something that looks kinda like this:
this shows that I can have multiple entries in a day for a particular serial number.
I need to select ONLY ONE serial number and corresponding data for each day (or week).
I tried to format the date to remove the time and then select the distinct date (so 1 record a day for each serial was displayed), this worked. BUT I could not link it successfully to other tables because I had to format the corresponding match date to avoid a type mismatch thus invalidating what i was doing by selecting the distinct record.
Here is the original query i was using that selected a range of dates (which included multiple dates in a single day with a single serial).
SELECT TBLRemoteUnitInfo.TankSize, TBLRemoteUnitInfo.TankName, * FROM TBLRemoteUnitRequests, TBLRemoteUnitInfo WHERE (TBLRemoteUnitRequests.RemoteFeildUnitID = TBLRemoteUnitInfo.RemoteFeildUnitID) AND (TBLRemoteUnitRequests.SerialNumber=:SerialNumber) AND (TBLRemoteUnitRequests.DateReceived BETWEEN :Datestart AND :Dateend) ORDER BY TBLRemoteUnitRequests.UnitRequestID';
how can i take a sample of the range of dates (ie 1 a day/week or month), i assume i need to create a filtered table via query first then query that table.
Its killing me!
if i am being unclear please let me know and ill try to clarify
I have come to a dead end with my query. Any help would be really appreciated.
I have a query which i have written:
SELECT TblBurnleyWwTw.MetricID FROM TblBurnleyWwTw WHERE (((TblBurnleyWwTw.[Data Source])="OMS")) OR (((TblBurnleyWwTw.TAGFunction)="CHP")) GROUP BY TblBurnleyWwTw.MetricID, TblBurnleyWwTw.[Metric Required], TblBurnleyWwTw.CALCULATIONS, TblBurnleyWwTw.PIPointSource, TblBurnleyWwTw.PILoc1, TblBurnleyWwTw.[High Level KPI] HAVING (((TblBurnleyWwTw.MetricID) Like "130*") AND ((TblBurnleyWwTw.[Metric Required])="-1") AND ((TblBurnleyWwTw.PIPointSource)<>"L"));
This retreives all codes relating to what i want. The problem is, I need this data to try and match within another column of calculations E.g. Data retreived from query I have may be 13001, 13002, 13003. What I would like to do with this data is to lookup in a calculations column if any of the above data matches to bring back the metric ID which is realted to it?
Hope i have stated this clearly enough for anyone to understand???
As i say any help or recommednations for a solution would be really appreciated
SELECT [UK Table].[Business Name], [UK Table].[Business Type], [UK Table].Address, [UK Table].City, [UK Table].Country, [UK Table].[Telephone Number], [UK Table].[Website Address], [UK Table].[Email Address] FROM [UK Table] WHERE ((([UK Table].[Business Name]) Like [Forms]![frmNz]![txtBusinessName] & '*' Or [Forms]![frmNz]![txtBusinessName] Is Null) AND (([UK Table].[Business Type]) Like [Forms]![frmNz]![txtBusinessType] & '*' Or [Forms]![frmNz]![txtBusinessType] Is Null) AND (([UK Table].Address) Like [Forms]![frmNz]![txtAddress] & '*' Or [Forms]![frmNz]![txtAddress] Is Null) AND (([UK Table].City) Like [Forms]![frmNz]![txtCity] & '*' Or [Forms]![frmNz]![txtCity] Is Null) AND (([UK Table].Country) Like [Forms]![frmNz]![txtCountry] & '*' Or [Forms]![frmNz]![txtCountry] Is Null) AND (([UK Table].[Telephone Number]) Like [Forms]![frmNz]![txtTelephoneNumber] & '*' Or [Forms]![frmNz]![txtTelephoneNumber] Is Null) AND (([UK Table].[Website Address]) Like [Forms]![frmNz]![txtWebsiteAddress] & '*' Or [Forms]![frmNz]![txtWebsiteAddress] Is Null) AND (([UK Table].[Email Address]) Like [Forms]![frmNz]![txtEmailAddress] & '*' Or [Forms]![frmNz]![txtEmailAddress] Is Null));
What is wrong? I have attached the database. The form frmNz is what I want to work. I should be able to enter company information into at least one field, allowing me to retrieve the specific company data from the UK Table in the subform.
I have three tables called, Clients basic details, Episode and Modality. There are some 56 fields in these tables. Once a month I run a query on these tables. The data is then uploaded to another site via the internet. I have now been asked to collect data into another table called TOPS which has some 23 new fields in it. The problem I have is, they want none of the TOPS data to be on the same line as the Modality Data. For instance, there are 79 fields in the query which must conform to certain parameters before the data can successfully uploaded, the query must show lines of data for all the 79 fields, but if any line has TOPS information the line cannot have Modality information in it and vice versa. I think some of the data that would be collected will be duplicate, i.e. from the episode and clients basic details tables. The query must show all 79 field headings. Any line of Data in the query result that has data from the Modality table and data from the TOPS table can only show the returned data from one of these tables, the values from the other table must be left blank and vice versa. For Instance say From the clients details table we show the clients name, from the episode table we show the number of children he has, from the Modality table we show he has structured intervention, we then must show blank records from the TOPS table. Then on another line the query must pick up the data from the TOPS table, episode table and clients basic details table and leave the Modality table fields blank. A lot of the info from the Clients and episode table will be duplicated but on the different lines.
I am trying to create ranges in data in order to create a graph. I have the following IIf statement that access says is too complex and also Im getting an error saying that it is too long to edit so does anyone have any ideas how to do this with out these problems. I need to get to $25000 $30000 in increments of $1000.
IIf([Actual$/Mile]<1000,"$0 to $1000",IIf([Actual$/Mile]>=1000 AND [Actual$/Mile]<2000,"$1000 to $2000",IIf([Actual$/Mile]>=2000 AND [Actual$/Mile]<3000,"$2000 to $3000",IIf([Actual$/Mile]>=3000 AND [Actual$/Mile]<4000,"$3000 to $4000",IIf([Actual$/Mile]>=4000 AND [Actual$/Mile]<5000,"$4000 to $5000",IIf([Actual$/Mile]>=5000 AND [Actual$/Mile]<6000,"$5000 to $6000",IIf([Actual$/Mile]>=6000 AND [Actual$/Mile]<7000,"$6000 to $7000",IIf([Actual$/Mile]>=7000 AND [Actual$/Mile]<8000,"$7000 to $8000",IIf([Actual$/Mile]>=8000 AND [Actual$/Mile]<9000,"$8000 to $9000",IIf([Actual$/Mile]>=9000 AND [Actual$/Mile]<10000,"$9000 to $10000",IIf([Actual$/Mile]>=11000 AND [Actual$/Mile]<12000,"$11000 to $12000",IIf([Actual$/Mile]>=12000 AND [Actual$/Mile]<13000,"$12000 to $13000",IIf([Actual$/Mile]>=12000 AND [Actual$/Mile]<13000,"$12000 to $13000",IIf([Actual$/Mile]>=13000 AND [Actual$/Mile]<14000,"$13000 to $14000",0 ))))))))))))))
Hello everyone! i hope someone can help me with either: 1. writing a query to get the results needed (will explain below) 2. write VBA to be used in query to get the results needed 3. or change table(s) to be able to obtain the data in all areas to get the results needed.
The result needed: How can I get the result of ONLY when there's an exact match of the combinations and not all combinations of the matches?
I have a GuitarOptionDetails and it's linked (LEFT OUT JOIN) to a ProgramCodes. so the basic result here is displaying correctly. it's showing everything in my GuitarOptionDetails and only where there's matches from the ProgramCodes. Linked on Guitar and Option as these fields are in both sides. The ProgramCodes has the "Code" for the guitar and guitar / option combinations.
Issue, and why I'm seeking help ... i need to somehow change that so it will only show the set of results where the combinations matches.
example here's the "raw" data from ProgramCodes table
GuitarOptionDetails is showing the Codes for ComboID's 2, 3, and 4 because an invoice for AE185 has BOTH 38 and BB. Again, the basic LEFT OUTER JOIN query is showing all the Codes for AE185 where there's 38 or BB.
But instead, i need the result to show only the ComboID 4 Codes and not to repeat the same Codes for the ComboID 4 Codes.
I truly hope I made sense and there's a solution for this. I have no idea how to look it up to see if there's any previous posting for this or something like this, etc ...
This is my query:- SELECT DISTINCTROW Customer.custName, Sum(Invoice.invAmount) AS SumOfinvAmount, Sum(Payment.payAmount) AS SumOfpayAmount FROM (Customer LEFT JOIN Invoice ON Customer.custID = Invoice.invCustID) LEFT JOIN Payment ON Customer.custID = Payment.payCustID GROUP BY Customer.custName, Customer.custID;
Result:- custName SumOfinvAmount SumOfpayAmount blake $20.00 julie $50.00 kate $375.00 $290.00 steven $200.00
The resulting SQL query returns incorrect invoice and payment totals for Kate. The Sum() function has duplicated. Can anyone tell me how I would change my query to fix this problem?
I have a query that when I run it normally (just click on it) then it runs fine. (It is a union query, getting it's data from 8 other queries (who has their dependancies)
But when I want to run a report from it, Access gives me an error saying "query is too complex".
I am flattered, but I would prefer access to work than say I write stuff that is too complex for it. :cool:
Any ideas?
I am confused by the fact that it runs when I double click the query, but the report bugs it out.