I am trying to set up a number of queries to return completed jobs with a specified time frame, usually monthly. So rather than have to type the same dates in arround 20 times I tried to use the data from a table in the Criteria. This did not work any suggestion would be much appreciated.
Attached is a screen shot showing a fragment of the query and the dilog box I get when it runs.
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
I've two tables, QA (Quality Assurance) and Instruments. I'm using form to add data to QA.
There's a field in QA, named InstrumentUsed. The criteria is that InstrmentUsed should only accept value when 'status' field in Instruments table shows 'working'. If status is 'faulty' or 'need repair', it should pop up the related error message and cursor stays on the same field.
I've not starting learning VBA yet, so I'm trying to make use of Macro's.
How to use data from another table as a validation criteria for a field in form?
Pops up a MessageBox and stays on the same field, on the form, unless error is resolved?
Cannot seem to find an answer to this, but please point me in the correct way if you know of one!
Quite simple i think, but blank mind at moment!
How would i use the values in a table/query as the criteria for another query? I believed i could type in [qryOne]![classification] in the criteria box, but this does not seem to work.
I was wondering when I am filtering out data from a table, is there a way for me to filter out specific lines (as in, if I have a 100 line datasheet, can i choose to filter out lines 5, 10 and 20 out of the query)?
For instance:
Line Name Address Phone Number 1 A1 fjlafs 453453454 3 A2 fsdfsd 343534534 4 A3 gsdgsdg 354543534 5 A4 gsdgsdgsd 345345345
I want to make it so that lines 2 and 4 are NOT included in the query when I click "RUN".
ID (Type Text) Title (Type Text) Remarks(Type Text) Formatted: FormatTitle([title],[Remarks]) Expr1: InStrRev([Formatted], "~")
public functionFormatTitle(ByVal sTitle as String, ByVal sRemarks as String) as String 'do process code here very complicated an long, but works find in the end 'creates a Multi-String delimited by | (pipe) end function
The above works, and Expr1 does give an accurate value for the position of a "~" (tilde) in the string Created by the FormatTitle() function.
However, If I put a Criteria >0 on Expr1 it asks for the value of the [Formatted] field as if it was a parameter. If I put a criteria for Formatted: Like "*~*" I get a Data Type Mismatch in Query Criteria
This Query Also produces the Data Type Mismatch in Query Criteria pardon me, but WTF? If it isn't a STring, than InStrRev() should produce an error, not an accurate response, and if InStrRev() produces a number why can't i compare it to 0 (zero)? This is indubitably messed up that I'm getting this error. There is no data type mismatch, on either of these tests, one is a string and I criteria-limit it by a string operation, the other is a number and I criteria limit it by a number, WHAT IS GOING ON!!!
Thanks Jaeden "Sifo Dyas" al'Raec Ruiner - The Frustratedly Confused
There is data in the [Year] field and the [Season] field.
I placed a button on the subform to create a report based on the data of the current "Sub Record" .
Basically, I want to use the data in the subform to create a small "Lookup" report.
OK:
I have the Management Form I have the Management History SubForm I Placed a Button Called PRICE LIST The button kicks off a Macro that previews my PRICE LIST REPORT A Query is the RECORD SOURCE for my PRICE LIST REPORT.
In the QUERY, I have two fields that I specify criteria "hopefully" based on the sub form record I am viewing.
I does not work on the SubForm. However if I Open the SUBFORM directly, the report pops up correctly.
What syntax in my QUERY is required to GET the data right frm the current SUB-FORM??
I tried the following:
Like [Forms]![Management]![Management_History].[Year]
Like [Forms]![Management]![Management_History].[Season]
i have created a query to do the calculation of how much stock came in went out and is still on hand .the problem im having is that it shows me the 0 total for product with no stock in it when i put my criteria as >0 in my goods on hand field it show me no data in the query.
my calculation fields is as follows GoodsOnHand:[GoodsIn]-[GoodsOut] Goods in gets calculated as follows GoodsIn:Nz([SumOfGoodsIn],0) Goods out get calculated same way as goods in.
The Reason i have a sum of goods in is because i have to calculate the quantity in another query because it is spread over lots of locations.
I am trying to pull the sum of document count from the SumTotalPerf query where the datereceived in the query matches the date on the form and the BookedInID in the query matches the BookedInID on the form, at the moment Text27 just displays as blank with no error messages displayed so I am lost as to what im doing wrong, Ive double checked all the spelling for my column names etc and all is correct.
I have a form where varying layers of information can be entered. In some cases, a user may know all information or only part. If i tie the query to the form, each field has to be populated for it to work. how do i set the query up to effectively use the information available and not require all information?
I have tried setting VBA code as follows:
If Me.Combo1 = Null Then Me.Combo1 = "*"
However, when i do this, it updates the form field with an asterick/wild card but does not include in query. yet, when i update the form fields with data (not asterisk/wild card) the query runs.
I have a query criteria where the data in the query field are numbers.
The following works as long as I have a value in the textbox otherwise I get an error message stating, this expression is typed incorrectly.
Code: Like [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Also tried
Code: [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Yet I use the following script for another column which has identical data and it works fine either way and I can use it in the other column and it works with or without data in the textbox
Code: Like [Forms]![FONMain]![txtFact1] Or [Forms]![FONMain]![txtFact2] Or [Forms]![FONMain]![txtFact3] Or [Forms]![FONMain]![txtFact4] Or [Forms]![FONMain]![txtFact5] Or [Forms]![FONMain]![txtFact6] Or [Forms]![FONMain]![txtFact7] Or [Forms]![FONMain]![txtFact8]
Im really stumped.. and I really need some help, Here is my problem,
I have a field with a whole bunch of regular peoples names in it (user_names). Home Developer names are also in this field, it has to be this way to show if the home is still owned by the developer or moved into. What im trying to do is build a query that takes out all of the developer names (by takes out i mean does not change the table at all, just temp so i can perform some calculations on the data).. Im having a problem with this. I have a developer_name table that looks like this
Autonumber Developer 1 Walts 2 Peets 3 Donalds
When i set up my query under field i put user_names and under criteria i have used all variations i can think of pertaining the following code
<>[DeveloperInfo]![DevName]
from what I understand it should return all values that are not developer homes, it doesnt work though, it leaves some in there. if i change the code from <> to = it takes out all the user names and leaves just the Developer names as it should, unfortunatly i need it to take out the developer names not leave them in.... Any ideas?
I am attempting my first access database which tracks blood components in our medical facility. When a component is issued to a patient there is a button to push which links to a report that runs a query. The user must enter the unique key for the component at a promt, which ensures the report generated will be for that component only. My question is if there is a way to cpture and use that key automatically when pressing the button. If I need to clarify something, please let me know--I may not have the Access developer lingo down yet...
I have a query in my database called "Open Date" which is a date formatted field when imported as DD/MM/YYYY. The criteria is set to:
>=#06/04/2014# And <=#05/04/2015#
The query works just fine. My customer has come back and asked for the way the date is displayed to be in the YYYY-MM-DD format (i know it's not ideal but thats what they want). When i've done that using the following
if there's a way to apply a list of items as criteria in the order listed? Say I've got a list of sales reps and I want a macro to run a query repeatedly, populating one rep from the list at a time in a field called Sales Rep.
having used a query to generate data from a set of records where i can select the criteria (they are two letter abrv and are in a single field in the table but there may be multiple criteria). As I would like others to do it without using the query my first thought was a form with a series of check boxes tick for inclusion in the query or not. As i have built the query am I looking at changing that or taking it all into VB and using some code to pull the relavant 'ticks' across............ and how the hell would i do that......
having used a query to generate data from a set of records where i can select the criteria (they are two letter abrv and are in a single field in the table but there may be multiple criteria). As I would like others to do it without using the query my first thought was a form with a series of check boxes tick for inclusion in the query or not. As i have built the query am I looking at changing that or taking it all into VB and using some code to pull the relevant 'ticks' across............ and how the hell would i do that......
My query is coming along nicely, but as always once one problem is solved you find another :rolleyes: !
My problem is that I have thus far specified criteria for the field OrdDeliveryCountry, but this field is not filled in unless the delivery address is different from the default address for the customer, therefore it is frequently blank and so the query wasn't finding all records, only those where the Delivery Address was specific to the order.
I want to use the IIf function to make an expression to say (in linguistic terms): If OrdDeliveryCountry is blank, then use the country in the Customers table.
Sounds simple enough, but the criteria currently is: WHERE (((ORDERS.ORDDELIVERYCOUNTRY) = "Austria" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "France" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden") AND ((PRODUCTS.PRODUCTNAME) NOT LIKE "*Upgrade" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Repair" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Rpr" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Commission") AND ((ORDERS.[DEMO/SALEID]) = 2))
So how do I combine the IIf(expr,truepart,falsepart) with "Is Not x Or x Or x"?I.E. I need to get it to exclude records where OrdDeliveryCountry does not equal one in the list, and if that is blank then the Country field in the Customers table does not equal one in the list?
My attempt is this, but I think I'm way off the mark
SELECT ORDERS.SHIPDATE, PRODUCTS.[STANDARD TARRIFF NUMBER], [ORDER DETAILS].[QUANTITY] * [ORDER DETAILS].[UNITPRICE] * (1 - [DISCOUNT]) * (1 - [SPECIAL DISCOUNT]) AS LINETOTAL, [ORDER DETAILS].QUANTITY, ORDERS.ORDDELIVERYCOUNTRY, ORDERS.ORDERID, [ORDER DETAILS].PRODUCTID FROM CUSTOMERS RIGHT JOIN (PRODUCTS RIGHT JOIN (ORDERS LEFT JOIN [ORDER DETAILS] ON ORDERS.ORDERID = [ORDER DETAILS].ORDERID) ON PRODUCTS.PRODUCTID = [ORDER DETAILS].PRODUCTID) ON CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID WHERE (((ORDERS.ORDDELIVERYCOUNTRY) = IIF(ISNULL([ORDERS]![ORDDELIVERYCOUNTRY]),([CUSTOMERS]![COUNTRY] NOT LIKE "Austria" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "France" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden"), (([ORDERS]![ORDDELIVERYCOUNTRY]) NOT LIKE "Austria" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "France" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain" OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden"))) AND ((PRODUCTS.PRODUCTNAME) NOT LIKE "*Upgrade" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Repair" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Rpr" AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Commission") AND ((ORDERS.[DEMO/SALEID]) = 2)) ORDER BY ORDERS.SHIPDATE DESC;
My thoughts:
Maybe I need to re-structure the WHERE clause? Would it work if the IIf expresssion was in the SELECT part not the WHERE part?
I would really appreciate some help with this: I'm not sufficiently familiar with structuring statements as complex as this and I don't know all the syntax rules etc.
I am trying set up some criteria for a create table query. I am using multiple fields from different tables, I am trying to delete certain feilds if they meet the criteria. I am using iif([letter c_last]=[letter a_last],null,[letter c_last]) in the criteria for letter c last, but when I run it a box appears to enter criteria for the last name field? I want it to look at the field not for data to be entered manually. I hope this makes sense. Any help would be greatly appreciated.
Need some help trying to filter out some data so it can be deleted from the main table so updated data can be pasted into it that table.
Table name is "dbo_VG_PropertyValues"
AppID - Number DNIS - Text (This is the column i need filtered) PropertyName - Text ProperetyValue - Text
I had a query where i would just type in the 3-4 DNIS numbers that i needed to search for by using "11111" or "22222" or... etc... in the simple query view under criteria..
I'm now getting lists of 50 + numbers i need to do searches for, delete and replace with updated data. I can't run a string of "or" statements on 50+ numbers, it won't allow it. I created a table called 'DNISList" with the 50+ numbers i need to search for out of the "dbo_VG_PropertyValues" table. In the simple query view i linked on the DNIS number from both tables and set the join property to show all the records that matched. Hey it looks good, so far, i got the results i wanted but can't delete anything.
Is there anyway in the criteria field under the column DNIS from "dbo_VG_PropertyValues" where i can so a search on that table without linking the tables? I hope I'm making sense. You know something like:
I have a form that run a query to select all the students taught by a given faculty member. Once the records are loaded, the faculty enters attendance data, selects a date from a combo box and hits a save button.I change my relationship and now need to be able to store the key for the field in the combobox and not the text.
How do I set the field, table and criteria in the update query to do this. The UD query uses the FacultyStudents query as its record source. Currently the FacultyStudents querry does not have the Key or the date fields since the date is selected by the faculty once their records are loaded. As I see it there is now way to make a join, so I think I either need VBA or SQL, which I am not very good (really bad) at wriitng. Can the necessary Sql be written in the query design view?