Tables :: Like Function Elements Residing In A Table
Sep 10, 2013
I am trying to find a way execute various "like / is not like" commands on a field within a table. Rather than build 30-40 different "like / is not like" commands to execute on this specific field, I would prefer to have one "like" comand and have it's elements listed in a reference table that I can call into the "like" commend or function.
In this way I can change the "Like" statement filtering criteria by simply adding or deleting filter elements from the reference table rather than have to modify the "like" command's hard coding.How to do something similar, perhaps using a different function or macro?
Hello to all. The problem that I am faced with is that I have a master list with all the current users and an updated list with all the current users and new users. What I have been asked to do is to compare the information in these two tables and have been given the following steps. 1. If name (surname, first name) exists in both lists do nothing.
2. If name exists in Update list but not in Master list then insert a blank row in the Master list and add the following Update list fields to this row: ¨ 3. If name does not exist in the Update list then remove the entire row that contains that name from the Master list. 4. If 2. condition ("name exists in Update list but not in Master list") below applies, then insert the blank row with some kind of marker (e.g. "*") in a column on the far left. This will allow us to distinguish between:
So that is what I have to do can someone please tell me what a quick to do this in access would be. Thanks
I'm creating a catalog that shows all the equipment we use in our company. The problem is that most of these equipment don't have the same properties, or fields. For example, all of them have a code, model, supplier and price.
However, most of them have different properties like size, orientation, or material.My first attempt was using one-to-one tables.
My database is formed by a main table (catalog), a type table (which lists all the types of equipments the table has, e.g. control valves, tanks, field instruments, etc), and smaller tables for each type of equipment (the table for control valves has the size and material field; the table for tanks has connections and orientation field; and the table for field instruments has the fields of dial size and connections).
All of these small tables are connected to the main table using a one-to-one relationship.My question here, any better way to store and access these type of data.
I have a .mdb file (access 2003) which has ballooned in size to 1.2GB and I'm not sure why. I tried deleting some older tables (which were copies and had about 38,000 rows each) but it hasn't made a dent. Is there a way to show all the database elements tables, forms, macros, modules etc and list their sizes so I can see what has caused the size to increase?
There are 3 tables linked via ODBC which have tens of thousands of rows but as these are linked I wouldn't have thought they would have increased the size at all?
Hello guys, Its a tricky question,hope to get answer ,well my question is ,i have a table name : refer_lgl_mgr it has col name : qrynm which holds my query select atab,btab from s_prod where atab like 'Asr%' how can I execute the qrynm column. if it can be executed I need to insert the value of atab,btab into table call refer_cv with same col atab and btab.
One thing is that this should happen when user presses the button.the code is shown below which i tried: ---- Private Sub Command2_Click() On Error GoTo Err_Command2_Click
'Dim conn As New ADODB.Connection Set conn = New ADODB.Connection Set rs = New ADODB.Recordset Dim Rqry As String
conn.Open "conn", "meuser", "jul"
' Fetch qry.
Set rs = conn.Execute("SELECT QRYNM FROM REFER_LGL_MGR")
Dim strsql If Not rs.EOF Then rs.MoveFirst Do While Not rs.EOF Rqry = rs.Fields(0).Value
MsgBox Rqry
strsql = "Insert into refer_cv " & Rqry conn.Execute strsql rs.MoveNext Loop End If rs.Close
Hello can anyone help me with a code or application example of how to hide all db elements. I have a database and i want only a main form to appear for users and tables, code, etc to be hidden.
I was hoping someone could let me know how I can convert my MS ACCESS database file (.mdb) so that when user(s) run/open the database, they cannot see, nor have access to the tables (which are linked from a separate DB with just the tables), forms, queries, etc... (the elements). Right now, you can open the file, the switchboard runs, but you can still see and navigate to the tables/forms/queries seen in the background.
Is there a quick and simple way to turn this into an executable-type file where you cannot see the elements in the background?
I wonder wether it is possible to copy elements from one database to another. I have two copies of the same database. One for development and one for user access. If i for instance would like to copy a form from the development database to the other, how can this be done?
I have a form with a couple of sub forms, one of the sub forms only has a check box and a txt field and both are required before moving on to another record.
I have this following code that works partly, but it only works if one of the fields have info entered. It doesn't however stop the user from tabing through the sub form without entering 'any' data in either one.
---------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer) If Trim(Me!txtContactAgentName & "") = "" Then 'Validate Name Field MsgBox "Gotta Enter Your Name!" Me!txtContactAgentName.SetFocus Cancel = True ElseIf Me!ynCCContactLogLeft <> True Then 'Validate CheckBox MsgBox "Gotta Have a checkmark in Logged!" Cancel = True End If End Sub --------------------------------------------- Also the below throws up an error when I know it's got all the correct information. ------------------------------------ Private Sub txtContactAgentName_LostFocus() Forms.frmAllCustInfo.SetFocus Forms.frmAllCustInfo![AddNewCustomerRecord].SetFocus End Sub
Once again I am stuck. Basically I have a query as followed:Current: Sum(IIf([Run_date]-[dbo_GD-AgedDebtors]![BillDate]<=30,[dbo_GD-AgedDebtors]![Outstanding],0))I'm trying to show outstanding bills if the bill date is less than or equal to my date parameter. However, it keeps coming up "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)"
I have an unbound listbox with 3 columns. Call it PeopleBx. How can I address the item in the first row and first column. How can I address the first column in the selected row and how can I address the item in row 3 and column 2. I tried with
I have a table that is populated everyday, with following columns:
1. ward (linked to the wards table) 2. date 3. number of patients
We have a total of 18 wards, wherein the daily number of patient in each ward should be recorded. The problem we face is that we find it counter-productive if the data encoder selects a specific ward (dropdown list), then puts the number of patients, and then moves to another field repeating the process. (the date is pre-selected using a combo-box and this will fill the date fields, thus the encoder selects the date only once).
I was wondering if there is a way where we can just automatically show all the wards, so that the data encoder would just proceed on putting the figures.
I am currently exploring the possibility of adding another element to our Database.
Each of our courses has a scheme of work associated with it, and this is completed on a daily basis by the tutor for each student. These are currently just Word documents we print out.
Example : I already have provided provision for recording attendance in the database, see below, (it may look a bit strange, but our training is ad-hoc so slightly different requirements than a college for example).
I am trying to improve my code by making it more readible. The following code works, but it is certainly not the most efficient way. I`m trying to write a loop to make certain elements in an Access report visible/invisible, but I can`t address the visibility property of these items while iterating over i.
I have a large query that basically collects all informations for a given record from various tables. The query is then used as a controlsource for a report. It works fine. However, one of the tables referenced is a one-on-one relationship to the master table and there some records that do not have any corresponding records in that child table. I've entered in Nz() function for all fields from the child function to prevent the records without child records removed from the query's recordset, but it doesn't work. That is, query returns all records that has a child record, but not those without.I've already checked the Join properties to ensure that all parent records are displayed and only child records where there is a parent records.What should I do?
I have 3 table table; Invoice table, Product table and Sale_product table. Sale product table records all sale from the product table
Invoice table has these fields ID TOTAL CASH_TENDERED CHANGE
Product table has
ID CODE QUANTITY NAME PRICE
and Sale_Product table has these ID PRODUCT_CODE QUANTITY PRODUCT_NAME PRICE SUBTOTAL INVOICE
I did main form from Invoice table and sub form from Sale_product table. I want to use DLOOKUP function to load the name and price, quantity and calculate subtotal automatically from the product table based on the product code entered. i have being trying hard and i keep on getting "Name? error"
Is there anyway to turn off the rounding function in calculated fields - I need to divide a currency amount by a whole number to calculate the number of full coin bags that will be needed (bags contain different totals according to the coin value) - a whole number.
Then the result is used to calculate how much loose coin will remain.
But regardless of the field type and/or the format, the number rounds when I reduce the decimal places to zero. I need the result to be the whole number and to calculate as the whole number.
I have a code module in my interface with 3 functions that run via separate forms with user inputs. Each function is similar in design, exporting data from a generated query to excel for input into external software.
The database is split into an interface FE (Access 2007) with linked tables BE (Access 2000). I have successfully tested each of the 3 functions in my code module with a set of tables containing fake test data. However, I have discovered that if I link to a different copy of the .mdb tables, 2 of the export functions no longer work and the third one still works fine.
What is causing the problem but the part that is not working correctly is the loop.
Here is my code module:
Option Compare Database Option Explicit Public Function CreateQCStaticChartsforReports() As Boolean 'Define variables for Static Chart creation Dim qdf As DAO.QueryDef Dim strSQLStatic As String
Is there a copy and replace existing records function in access?
I.e. I would want to copy records from one table to another (with same structure) and replace similar records with in original table in the new records (which have minor ammendments made).
how to query data in my database based on a number of different criteria.I have reached a stage where I can get all the data I need from one query, however I can't figure out how to further query this data to return records from a table with the most recent date only. I have searched the forum, googled and experimented myself but I am running into "Aggregate Function" errors.In this scenario there are 3 tables. tblJobs, tblEquipment and tblInspectionLog. Each tblJobs record can have multiple tblEquipment records attached to it, and each tblEquipment record can have multiple tblInspectionLog records attached to them.
I would like to query the database for what tblEquipment records have been assigned to a tblJob ID and also return only the tblInspectionLog record with the latest Inspection_Date field.At the moment I am able to see tblEquipment records attached to tblJobs, however duplicate records appear due to multiple InspectionLog records associated with the equipment.
I have tried to filter records from tblInspectionLog using the "Max" criteria under Inspection_Date field in my query. This however returns an "Aggregate Function" error.
I upgraded to Access-2010 and the Text File Import function will not recognize dates in the format YYYY-MM-DD. The import dialogue sees enough to recognize the field as a date, but then every date encountered is written to the Import-Errors table. This is true whether the file has a .txt or .csv extension. The actual file format is .csv.
Is it possible on the Find Record button added to a form to adjust the search function so it defaults to a specific box on the form? I have a form for tracking employee's and on my Find Record button I would like it to default to the Last name instead of the Record number.
I have a form (called DeliverableDescription ) that has the following buttons: close, and save the main from has a sub form (called DeliverableDescriptionsubform) that is viewed as a datasheet also I have a table called DeliverableDescription that has three columns (outline number, name, and group number)
the DeliverableDescriptionsubformshows data from DeliverableDescription (only the outline number and name) this DeliverableDescriptionsubform is used to update the DeliverableDescription table (through copy and paste from another source)
what I need to do is populate the group number column when the DeliverableDescriptionsubform is updated the group number is just the first part of the outline number (i.e. if the outline number is 20.45.35.1.4.9 then the group number is 20)
I want to create a command in the save button that takes the string value of each item in outline number column and do some simple string manipulation to it then populate the group number
my approach is to have a select query that gives me all the outline number then through a loop iterate through each one, performing the string manipulation and update to the table.
my question is how to access elements in a sub form, since the save button is in the main DeliverableDescription form also is there a better way to do this?
thanks for any help you might be able to offer.
Code:Dim ws As WorkspaceDim db As DatabaseDim rs As RecordsetDim OutlineNum As StringDim queryResult As StringDim returnResult As StringDim SearchChar As StringDim Pos1 As IntegerDim TotalLen As Integer OutlineNum = "SELECT DeliverableDescription.OutlineNumber " & _ "FROM DeliverableDescription " Set ws = DBEngine.Workspaces(0) Set db = ws.OpenDatabase("N:statusReport_Test.mdb") Set rs = db.OpenRecordset(OutlineNum) Do While Not rs.EOF queryResult = rs.Fields(0) TotalLen = Len(queryResult) SearchChar = "." Pos1 = InStr(queryResult, SearchChar) returnResult = Left(queryResult, Pos1 - 1) DoCmd.RunSQL ("Update DeliverableDescription SET DeliverableDescription.TeamLeadNumber = returnResult " & _ "WHERE DeliverableDescription.OutlineNumber = OutlineNum ;") queryResult = "" SearchChar = "" Pos1 = 0 rs.MoveNext If rs.EOF Then Exit Do End If Loop DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
