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.
View RepliesIm 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?
Thanks
Greeny
Hi
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.
Thanks Marcus
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......
many thanks in anticipation......
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......
many thanks in anticipation......
Hi everyone,
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.
Thanks in advance!!
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.
View 1 Replies View RelatedUsing MS Access 2002
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:
IN ([DNISList]![TFN])
I want a query selecting records from table1 based upon a value in table2 -
table1 contains thousands of records - fields include VendorNo and VendorName
table2 contains a single record - fields include VendorSearchName (which is a "short name")
I am trying to create a query which lists all VendorNo and VendorName entries where VendorName contains VendorSearchName
eg. VendorSearchName = "Fred" will list VendorNames including "Fred Flinstone", "Big Fred" etc
Table 1: contains sales summed by salesrep by week_date for 5 years + current
Table 2: contains sales rep, start_date and end_date
Query: Sum sales by rep where (start_date >= week_date and end_date <=week_date)
Each rep has different start and end dates.
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?
Is it possible to create a table link that is read only. I realize that there a a number of work-arounds, but I'm curious about this functionality specifically.
View 3 Replies View RelatedIs there a way to link MS Access tables as "read only" in Access2000?
Thanks,
SKK
Any way in MS Access to read the TOC data from an Audio CD? It is I believe in the lead-in part. I have a music database, and would like to be able to read the track data directly from the CD (Title, artist, timing), without having to type it all in again.
I know this can be done, because my CD labelling software extract the data automatically - but can Access do it?
How do I use a value from a table as criteria in a query? I created a MakeTable query and converted it to SQL. Then I copied the code multiple times to create the multiple tables - see below. I have a table with the vendor names. How can I get the vendor names from the vendor table so I can put this into a loop for all vendors in the table?
DoCmd.RunSQL "SELECT table_data.SKU, table_data.Price, table.Vendor INTO MT__1 " & vbCrLf & "FROM table_data " & vbCrLf & "WHERE (((table_data.Vendor)=""MyVendor1""));"
DoCmd.RunSQL "SELECT table_data.SKU, table_data.Price, table.Vendor INTO MT__2 " & vbCrLf & "FROM table_data " & vbCrLf & "WHERE (((table_data.Vendor)=""MyVendor2""));"
I use an Excel interface to retrieve data from an Access file that has approximately 1 million records. I use an MS Query from Excel to run an Access Query to retrieve the data.
I have 16 "Criteria" fields (columns) in Excel that I change with drop-downs. Each criteria column has 2 to 9 values set; a total of 75 values. I pass these criteria fields to Access via a table link in Access.
The criteria of the fields in the query that is run in Access to pull the data based on the table link is updated by using the Values In A List (IN) option.
When I refresh this query in Excel, it pulls the data fine, but may take 10 minutes to run. (I am using MS Office 2003, 1Gig memory). I have limited VBA knowledge. Is there a better/faster way to pull this data?
I want to run a query on a table that holds all speed information for our trucks imported from a third party. Some of the speed alerts in that table are not correct so we set up a second table managed by the users to enter a speed exception. So if we know that Main St in Dallas TX generates false alerts for speeding we know not to call the driver, the third party db speed limit is not up to date.
So I want my query to pull all the speed data from tblSpeedData, except leave out the records where the street and zip are listed in the tblSpeedExceptions.
I've created a farm database, with a form (frmSearch) that will allow user to filter data. The form comprises of combo box and list box etc... for the user to input their own criteria. The subform below has a datasource. The data source is based on a query (qContractionSearch) which is basically a parameter query with 3 tables. The problem is, however, that it won't work with 3 tables... but will work if data source comprises just one table.
See the farm database attachment...and go to frmSearch...then go to Contraction tab. (The Cattle tab filter works fine-it only has a single-table datasource).i have a requery macro which runs whenever the user clicks 'search'.
Hello,
I wasn't sure which area to post this question in...since it involves queries, tables, and forms :)
There is a read-only table in my company's data warehouse that stores a lot of records. I wish to set up a form that will allow users to add or relate data (fields) to each of those records.
Updating the data warehouse table structure is not an option.
How can I best do this...
1. set up my own table somewhere, that will automatically append any new data warehouse records every night, so basically I'll always have a copy of the DW table, but with additional fields that can be updated?
2. set up another table/query that are linked to the DW table via unique identifier...but if I do this, how do I display the DW records alongside the new fields in a way that makes sense? another query? will I be able to write in data in this fashion?
3. ...some other way?
Any assistance will be very much appreciated!
I want to be able to open a Table as Read Only, so that a user can browse etc., but neither alter nor enter data - I want them to use the Forms for that. How then, using VBA code, do you open a Table as Read Only?
View 14 Replies View RelatedHello all,
I have a temporary table with Yes/No fields (F101,F102,F103,....etc).
In another table i have a field called ProductCode with values (101,102,103,....etc)
Is it possible to append(or update) the records of the temp table with criteria on "F" &ProductCode field? (For example if ProductCode=101 then F101 sets to yes)
Thank you in advance.
Hello all
I am using Access as a front end (GUI) with a SQL 2005 database and have several linked tables. An user encountered an odd error that I cannot figure out.
He created a query that had criteria on a date. When he tries to run it the query will not do anything. If he closes out of the query and does any other operation he gets the "This Action will reset the current code in break mode." error and will have to close out access before he can do anything else.
I'm found some strange work-arounds but want to fix the problem the correct way. Attached are two examples - the first one causes the error the second one is the work-around that returns the desired results.
Any ideas?
I have a table that has one field and I want to use this field as a query criteria . when i click on CRITERIA & BUILD, Access lets me select the table field and shows [tblBillRun]![Bill_Run] for the criteria but when I click RUN, it prompts me to ENTER PARAMETER VALUE. What am i doing wrong?
View 3 Replies View RelatedHi,
I have a query which uses manual input on two fields. I would like to automate this query by pulling the input values from another table.
How do I read the input values from the other table instead of having the users type them in ?
Al
I am interested, is it possible to read currently selected (highlighted) row in table?
I know that this is possible to move cursor on concrete field in table via VBA but is it possible to read record number when we move cursor manually (via arrows)?
I have created a form that searches a table to show certain criteria. I am having trouble with a part where the table uses a checkbox and I am trying to use a combo box on the search form to return the results based on is it checked? Yes/No or both. When I test it I keep getting Runtime error 3075 missing operator.
The sql I am using is below and when I try and see where it is going wrong it highlights the line.
Code : Me.Filter = strWhere
Code:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "#dd/mm/yyyy#"
[Code] .....