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?
View Replies
ADVERTISEMENT
Aug 25, 2004
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
View 2 Replies
View Related
Oct 4, 2012
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.
View 1 Replies
View Related
Jun 7, 2013
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?
View 10 Replies
View Related
Jul 6, 2005
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
Exit_Command2_Click:
Exit Sub
Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click
End Sub
------
So I get just qry in msgbox ,but not result set so what i am missing let me know,if anyone can help me.
Hope to get answer asap.
Thanks
vsap
View 3 Replies
View Related
Nov 19, 2006
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.
Thanks!!
View 1 Replies
View Related
Jun 10, 2013
how can I see the size of all elements on the DB, Tables, Queries, etc...
View 14 Replies
View Related
Nov 21, 2006
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?
View 3 Replies
View Related
Nov 1, 2007
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?
View 1 Replies
View Related
Nov 2, 2005
Hi,
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
-------------------------------------
Can anyone advise?
thanks in advance
View 4 Replies
View Related
Oct 31, 2014
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)"
View 12 Replies
View Related
Nov 11, 2014
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
Code:
Me.PeopleBx.Selected
and other possible variations but failed.
View 9 Replies
View Related
Jan 26, 2014
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.
View 2 Replies
View Related
Jun 25, 2014
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).
View 10 Replies
View Related
Aug 23, 2013
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.
Code:
DoCmd.OpenReport "tblInterval subreport", acViewDesign, , , acHidden
Reports![tblInterval subreport]!BoxInsp1.Visible = False
Reports![tblInterval subreport]!BoxInsp2.Visible = False
Reports![tblInterval subreport]!BoxInsp3.Visible = False
[code]...
View 2 Replies
View Related
Jun 14, 2006
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?
View 1 Replies
View Related
Mar 22, 2013
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"
View 1 Replies
View Related
Jan 22, 2013
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.
View 4 Replies
View Related
Jul 28, 2014
I create e field with calculation expression and I need to round the results. I insert the following expression:
Round(IIf([A]="N";0;(IIf([B]>0;([B]-[C]-[D]-[E]-[F]+[G]);([H]-[C]-[D]-[E]-[F]+[G]))));2)
I have back the following error message: Could not find field 'Round'.
I do not understand why, because Round is not a field, but a function.
View 6 Replies
View Related
Aug 21, 2014
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
[Code] .....
View 2 Replies
View Related
Dec 3, 2012
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).
View 3 Replies
View Related
Feb 27, 2014
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.
View 4 Replies
View Related
Oct 8, 2013
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.
View 6 Replies
View Related
Apr 6, 2015
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.
View 3 Replies
View Related
Jun 9, 2006
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
View 2 Replies
View Related
Mar 21, 2005
Hi,
I have a table with the following 3 fields (these are the ones i'm having the issue with but there are others).
ID, Country, City
The ID is an auto number.
The Country Field is a lookup with the following lookup properties
Display Control: Combo Box
Row Source Type: Value List
Row Source: "England";"Spain";"France" etc
:confused: Now the problem...
I also want a lookup in the City field which changes to reflect what was selected in the Country field.
E.G
If in the Country Field Spain is selected then in the City Field I should have the option of selecting Barcelona, Madrid, Valencia etc.
Is this possible in the lookup of a table???
Thanks. :)
View 4 Replies
View Related
