I have a Make-Table Query that has five expressions. I have changed the properties of these columns so that, when you view the query, it shows the column names I have chosen. So, instead of Expr1, I get "Haggis", and instead of Expr2, I get "Cold Toast".
BUT, when this query creates a table, the column headings revert to Expr1 and Expr2. Is there any way to make my custom column headings stick in the new table?
I should add that I frequenty run this query and overwrite the table. Thus, even if I go into the table and change the field headings, as soon as it is overwritten these changes revert.
I'm currently working with a form, which is in datasheet view. I have many rows which are combo boxes (yes/no), and the name is rather long. So each line (each row) spreads on to 2-3 pages to the right.What I would like to do is make the namebar, on top of every column, a little bit higher, so the name would be split into two lines, or three. Allowing me to make the width allot smaller.Here is an example of my problem:http://213.213.137.96/~terminal/columns.jpgSo my question is, can I change the height of the column name? Or is there some trick I can use?regardsFrímann Kjerúlf
I have a table (tblrecords) with fields, entered by, recieved by and date and time,entered by and recieved by are both referenced in tblemployee.i want a query that will retrieve
fname, lname (of entered by from tblemployee), fname, lname (of recieved by from tbl employee) and the date and time from tblrecords)however i cant think how to do it with both names coming frm the same table?
I've been developing an Access database to keep track of my company's ongoing projects. There's also a form to browse the history of users actions within the program. It's based on table tbHistory that stores actions and parameters as numbers (for example eventId = 1 is "logged in" and eventId = 2 is "logged out"). I've been using a query to translate those numbers to text with a syntax like:
Code: IIf([tbEvents].[EventId]=1 Or [tbEvents].[EventId]=2;[tbEvents].[EventDesc];IIf([tbEvents].[EventId]=5 Or [tbEvents].[EventId]=6;...
It worked fine but eventually the expression within expression builder has grown to the level that exceeded allowed limit and I couldn't develop my statement any more.. I decided to develop a vba function that would take eventId as a parameter and would retrieve a string, here it is:
Code: Public Function translateHistory(eventId As Long) As String Select Case eventId Case 1 To 2 translateHistory = ""[tbEvents].[EventDesc]"" Case 6 translateHistory = "[tbEvents].[EventDesc] & "" <b>"" & [tbFormDesc].[FormName] & ""</b>"""
[Code] ....
And in my query I replaced that extremely long expression with just translateHistory([tbHistory].[eventId]). It seems to work, but the result it brings is a pure string with table names and fields - in other words, the query doesn't recognize it should be replaced with appropriate value. Here's the output I get:
Of course I'd like "[tbEvents].[EventDesc]" to be replaced with appropriate value of field "EventDesc" from table "tbEvents" as it used to be.
I wonder how can I list all the column names for a given table using a single query? I know how to do it in SQL using system tables, but no idea how to do that in Access.
I use the following code to delete from a table all records except those meeting the WHERE criteria:
Code: DELETE tblABC.*, tblABC.SubjectID FROM tblABC WHERE (((tblABC.SubjectID)<>99 And (tblABC.SubjectID)<>432));
I'd like to run this exact same query, but on many other tables, all of which are stored in tblTablesToClean (TableID, TableName).
Any good way to have Access loop through the list of tables in tblTablesToClean, each time passing the name of the table into the DELETE code and running the code, until all tables have been processed?
I'm a bit in over my head. Unfamiliar with Access macros, I need to write a bunch of them for work, and soon. Unixen I can deal with, largely undocumented convoluted Access macros are something else....
My current problem is: I have a table. The first column has a value in it for almost every row. However, there are six other columns after it. I need to write a macro to automatically delete all of the rows that don't have data in the last six columns. Microsoft Help is, as always, of zero use. The FindRecord feature allows me to use expressions to search, but of course, the help fails to tell me what syntax Access uses.
Oh, and it asks me to select a table from the drop-down list. Can I use wildcards here? Is there a way to get it to automatically open the newest table, or will we have to change the macro accordingly each month?
Most tutorials/guides I'm Googling rather brilliantly repeat the same things the help does, AKA, are useless. (Why do they bother writing them if you're not giving new information...?)
I'd ask about the other various access questions I have, but I can pick them up as I go, this is the most pressing question.
Hi, I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
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 wanted to know if it is possible to change the name of the Table which is going to be created using a Make-Table Query via code (VBA).
For example if my Make-Table query currently creates a table with the name "Table1" I want to change it to name "Table2" and then change it Back to "Table1" or "Table3" etc.... depending on the users selection.
:confused: I am using a Make Table Query to filter a Linked Excel Table. Is there a way to cut/drop the first 8 characters of the text out of one of the fields as it creates the new table?
Field NameExcel DataFinal Data Model_NameLATITUDE D600D600
Also, the final table has two Relationships with two other tables. When I run the Make Table Query once a week, I have to break the relationship to get it to run. Is there an easier way to dial with this?
Is there a way in a Make-Table query to tell the table to open when it's created? I would assume there is a function or SQL code that would do this, but I know very little SQL. Can anyone point me in the right direction? Thanks!
I have a database named NewUpdate.mdb. I have another database named MainDatabase.mdb. Almost all the tables in NewUpdate.mdb are linked tables that are in the MainDatabase.mdb file.
I've written a simple make-table query in NewUpdate.mdb that makes a backup of a table that is located in the MainDatabase.mdb file. (See code below.)
SELECT tblProviderRate.* INTO tblProviderRateSave FROM tblProviderRate;
The only problem is that this new table is created in the NewUpdate.mdb file. I need the query to be stored in the NewUpdate.mdb file and the "new" table to be created/stored in the MainDatabase.mdb file.
This sound simple enough, but I'm drawing a blank as to what I need to do in order for this to occur. Can someone tell me what I'm leaving out??
How to make-table query to make a certain field a memo field instead of a text field? The reason I cannot use the text field is the limitation is 255 characters while I require 2000 characters?
I need your help desperatly. In my db, I am making a "make table query" to match some text.
Help me build this please.
First a form which has text box, where I will put in some words.. Then a make table query, which will have =[form]![name of form]![control name] A report on same table.
Means when some one puts some words in text field of form, on pressing hit button, a like statement is automatically created in one field of query which matches those words and a table is generated from them. Result is report.
Hope this is clear, could you please help me build this?
I've got a problem with a make-table query that creates a new table which is then populated with new values that relate back to the original table the trouble is the make-table won't run with that relationship in place.
Is there a way to delete the relationship then reinstate it after the query? or any other way round that someone can think of.
I have come across a snag in my access project I am currently undertaking and need some advise on what should be done next. . .
In a nutshell, this project's primary purpose is to perform a search on a table of information based on many fields and generate a report based on the records which match the search. I have the search form working (similar to this one here) http://allenbrowne.com/ser-62.html
It displays the results from the table based on the search criteria. The form is great for viewing the results which match search criteria; however, I am having trouble doing anything else with the data (perform operations on the data, send the results to the table, etc. . .). How should I go about moving this data to a table?:confused:
I have used/am using the "INTO tbl_temp_copy" to create a temp table from a query called tbl_temp_comp. Everytime I run my query it deletes and then creates a new table with the same name - and this is what I have been wanting.
However now I would like to create a table with the date in the table name so:- "INTO tbl_temp_copy_Date()" but obviously this does not work and I do not know if it is even possible?
Anyone able to do this or know that it cannot be done?
I am having trouble with a make-table query. I run a query that populates my website with data daily. I have a bunch of fields in the db that I wanted to be displayed as one field on the website, so in my query I concatenated them. My website is able to distinguish which fields are text and which are memos so I can display multiple lines. Everytime I run the query, it creates the concatenated field as a text field. Is there a way I can default this to a memo type?
