I have a form built from a table and in a specific cell I need an equation but can't figure it out yet. I have several different fields and I need 2 fields divided to get the result. i.e. Total_Actuals divided by SA_CDI_Money = MoneyPercentage. Any ideas how I can get this to work?
View RepliesI have three sources of data which all equate to a reconciliation of the same data e.g. the same record of a stock holding is held on three separate systems, which must all be the same.
I am trying to build a an equation in a query field, which compares each quantity:
Qty Diff: [Dealer Qty]-[Pulse Qty]-[SWFAL Qty].
If the quantities are all the same, then I want to return a Zero. If one or more of them are different, I want that difference to show. Variance of the above equation that would do it?
I'm currently working on a project that calls for a graph that is based on an equation which the users can change while in the database.
the equation is basically an annual compound interest formula: 65*(1+x)^([YearNumber]-1), where x is the rate of change as a decimal, and what the users can change.
I've tried to use a simple query with this equation as one of the expressions, where the x is displayed as [Rate as decimal], but when I do this, I get an error on the table: the database engine does not recognize [Rate as Decimal] as a valid field name or expression.
I've also tried tying the equation to an unbound text box on a form called "RateasDecimal", with a button that opens the form with the graph on it, but I get the same error - except [Rate as Decimal] is replaced with "forms!form1!RateasDecimal".
is it possible to create a graph like this in access.
I know this has to be an easy fix but I can't find it. I would like to open my form with empty fields, in the first field, ready to go. Right now it is opening with the first field of the very first record. Please help
View 2 Replies View RelatedSo I'm not new to Access but I am to 2010. It has been a bit "challenging". Here's my first question:
1.) I'm trying to search on a field by using a command button. I basically want to click the button and the following message pops up: Enter MRN.
2.) When the MRN is entered, I would like the form to filter on all records that have this MRN.
3.) In old versions of Access, I would create a Macro for this and then call the Macro in the form.
4.) I've tried the FindRecord action in the Macro but it does not work. I actually came across several actions that don't seem to be working properly (getting error messages).
5.) In my head, this should be one of the EASIEST things to do. I've done this before in several different databases. I will admit it has been a few years since I have used Access for this (ie. building forms, macros etc.). I've primarly used it to pull in a data set and then run some queries to get the data I need quickly versus using Excel.
I have 2 fields in my form.. on if for date (date picker)..
The other one is for day..
I am using this atm:
Code:
Private Sub okt_courtdate_AfterUpdate()
okt_courtday = Format(okt_courdate, "dddd")
End Sub
So.. after i pick my date.. when i click tab.. the day field in form will be filled with correct day of that date.. The problem is..
I want to change days to my countries language.. example if it's monday, i want it to be "isnin".. I need to do this for all 7 days of the week.
Hello, I have just spent ages doing searches and reading everything I can on locking. But, I have yet to find an answer as to how I can lock a specific field in a specific record.
e.g. Staff enter customer details, then at the end of the day the admin (me) checks it over and presses a big old button that stops them from locking certain fields in the current record only - they must still have access to the unlocked fields of the current record, and it must not lock any other records.
I'm guessing there's some VB code in the form of fieldname.lock = true, but then it locks the field throughout the whole table!
Can anyone tell me how to do this please?
How to do an UPDATE using VBA on a form to update a specific record on the table using an unbound field on the form to filter the update.
Every time the code runs, it tells me: Run-time error '3144': Syntax error in UPDATE statement and takes me to the "CurrentDb.Execute strSQL, dbFailOnError" line at the end of my sample below.
Here's my code:
Dim strSQL As String
Dim strCriteria As String
strSQL = ""
strSQL = strSQL & " UPDATE [tblTicket] SET"
strSQL = strSQL & " ([UpdatedBy], [AssignedTo], [Requestor], [Dept])"
strSQL = strSQL & " Values"
strSQL = strSQL & " ('" & unbEnteredBy & "','" & cmbAssignedTo & "','" & cmbRequestor & "','" & cmbDepartment & "')"
strSQL = strSQL & "Where [tblTicket]![DateTimeOpened] = #" & FORMS!frmTicketTracker.unbDateTimeOpened & "#;"
CurrentDb.Execute strSQL, dbFailOnError
I want to hyperlink from a query direct to the relevant record in a specific form. I have a hyperlink field in the form which shows up in the query. When clicked in the query, this hyperlinks to the form but I cannot make it select the correct record in the form.How do I get it to select the correct record?
View 3 Replies View RelatedI have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
I have a query drawing information from two tables. The first table has information about a product, and the second table holds all the faults found per product. So any one product can have anything from 1-14 faults. I have created a report from the query, but the Fault item that I put on it only ever shows the first fault for that product. Is there some way of fixing this at report level, or on the query?
The tables are connected through the ID field, but the primary key for the faults table is another autonumber. Ex:
Primary Key___ID___Fault
___1__________ 1____fault 1
___2__________ 1____fault 2
That's two faults for the same product. Maybe I could create new fields in the query like Fault1: [Fault] with criteria set to primary key = 1? And one for each fault? But when I do it, it sets that criteria to the entire query, so the results end up blank because the "primary key" field doesn't exist on the product table.
I guess what I'm asking is can you set a criteria for just one field of your query to follow? Or can I resolve this issue in the report design?
Many thanks,
Tom
Good day,
I need a textbox to a five digit number. How would I write code to reflect this.
Thanks.
i have this form with 6 list box that output the data of a 6 tables.
*under each list box there is a text box that sum the field of their table.
i cant figure it out..
thanks
Hi,
I wish to place a command button next to a specific field which when clicked, launches the search command ready to search records against that field.
I have already tried this and managed to place a button to search - however, this searches every field in every record - I just want to search 1 specific field.
I am a bit new to this, so please be as untechy as possible!
Thanks,
Ritchie.
Hello everyone,
Ive designed a databse - no problem. Ive done all queries I need to - no problem. Ive designed forms for entering data and reports for showing data - no problem. THE ONE PROBLEM I have is with 1 field.
The problem is that the field is a memo box, used to add data on groups attended. For example: General, customer care, mystery shopping.
Can I run a query so that a different user can search the memo box for attendence to a certain group? eg, search this databse and field for customer care.
At the moment I can only seach on the first word in the field. I dont really want to have seperate fields as each employee can have attendence to different groups ie bo can have just general while bill has general, mystery shopping, customer care etc.
Any help would be great
In my vba code i write this
If DLookup("Full_Name", "tbl_Personal_Information", "Full_Name = Forms!frm_New_Person!F_N") > 0 Then
'--------------------------
MSG = MsgBox("this client is existed and his id is {" & [P_ID] & "} ",
vbOKOnly + vbInformation + vbMsgBoxRtlReading, "client database")
Else
strsql101 = "insert into tbl_Personal_Information (Name_English, Father_English, Family_English, Mother_English,
P_ID, NName, Father, Family, Mother, Birthdate, Nationality, Record_Number, Record_Place, Address,
[Code] ....
My problem is the id in the msgbox give me the last id from tbl_personal_information
But what I want is the id of the client that i try to add...
I'm using MS Access 2010, how to flag a criteria in an age field like, Pop-up if age is under 14??
View 3 Replies View RelatedI have a table with a large text field in it, among other fields.
What I am trying to do, in a query, is to show only the characters that are between brackets "[" and "]" for that field. And, if there is more than one pair of brackets, show only what lies between the last pair of brackets.
What I've tried so far is use InStr() functions to find these brackets and then use a Mid() function to show the enclosed text. But, it doesn't work well and it gives me a very complex query! In fact, I don't think it is a good idea to even use these functions in my query. That would probably slow it down a lot.
Can someone show me a function that I could use to do what I'm looking for? I need function names that could help me make better searches for more informations.
Thank you!
Hi
I need to select from a table field of upto 50 characters: the first 15 characters and then the remaining charachters (i.e. after 15...)
I would much apreciate some help...
Thanx
hi there
I'm building a forum from the scrath. It is already working, but I want to have in each topic the number of replies it has.
there are 2 tables, one for the topics and other to the replies. in the replies table there is a field called id_post that has the id of the post witch it belongs.
I solve the problem by inserting a query inside the loop of the posts:
<%
While Not rs.EOF
%>
<%
Set rs2 = Server.CreateObject("ADODB.Recordset")
sql = "SELECT COUNT(*) as cont FROM replies where id_post= " & rs("id") & " "
rs2.Open sql,Conn,1,2
%>
post: <%=rs("post")%> replies:<%=rs2("cont")%>
<%
rs.MoveNext
wend
rs.close%>
but someone told that this would make the page slower, so I want to know if I can do the same thing without having the query inside the loop.
I don't know if I made myself clear, sorry for that.
tks for the help.
I have a ticket database where tickets are added to access with no priority scores on them. Over time, meetings and discussions, those tickets are given scores through a form on my database. What I would like to do is somehow add a timestamp that shows exactly when a ticket went from being unscored, to having a priority score assigned to it. Note, before the ticket is scored; the Priority Score field is still blank. Null?
An idea I had was to somehow put a timestamp on the "Priority Score" field in my form that will only timestamp when that field goes from being blank to when it gets a number for the first time. It will not change the timestamp when that number is changed down the road. (We edit the priority scores, but I only want to know when the ticket was actually scored for the first time. This is for reporting purposes, how many new tickets we score each month, etc)
Could I do something along the lines of assigning code to the BeforeUpdate value of my Priority field on the form? Although I'm assuming this would put up a timestamp any time the field is altered afterwords also.
I have a DB to keep track of my employees. I have one table with named EmployeeT with the basic information like name, address, phone number, etc. And another table named EmployeeHistoryT which tell me about there hisorty in the company, basically, If they were Late, did not show up, cancelled from that day. They share a relationship threw the employee ID. IN the history table, I have DateofAction:, TypeOfAction:, and Description:. In the type of action area I have it set to were you type in what they did, wether it be Late, Did not Show up, Cancelled, etc. I want to be able to get a total about of Late, Cancels, etc. I want to know that John Smith was Late 16 times, cancelled 10 times, and Did not show up 2 times. Is this possible with only one feild.
View 6 Replies View RelatedI have a column named "stuffno" that i should enter the id of my stuff.I should enter "no 111".I did this because some stuff dont have id's so if i make a calculated field. "No " & [stuffno]I will get in the field of stuffno1 "no " for the stuff without id..Ok now i want to make another column that contains just the id of the stuff..Is there anyway to take just the id numbet the contents of "stuffno" field without "no " so the "stuffno1" will be empty or it will contain just the id.
View 3 Replies View RelatedI created a template called "AD - Assembly". In this table I have fields for:
"AD_ RecNum" - This is the auto generated number field for each record, and also my Primary Key
"Document Number" - This is the number that is being assigned to each document placed in this table. (i.e. AD-0001-001)
"Description" - This is the description of each of the documents
"Originator" - This is who is assigning the document a number
"Origination Date" - This is a =CDate(Now()) field that will automatically generate the date and time the document was assigned a number.
I then created a form called "AD - Assembly". On this form I created a field called "Last Record". I want to open the "AD - Assembly" Form, and have this "Last Record" field automatically filled with the "Document Number" filed of the last created record based on the "Origination Date" field.
I created a a table with multiple columns. For example the table looks like as follows:
Table name - Observationcode
Code Value Description
CCH 1000 Heavy Crack
CCM 750 Medium Crack
CCL 500 Low Crack
I created fields on the subform - frmsubmain1
Field names are as follows:
Observation Code
Value
Description
I created combobox for a field called "observation code" so that the above mentioned table "observationcode" appear as a pull down menu to select from multiple lists. In the property section I bound the feild "Observation Code" to " column 1" to record the item in the column 1. I reapted this process for the fields "Value" but this time I bound the field to column 2 to show the value rate or value for the selected code. Again I reapeated this process for field name called "Description" but this time I bound the column to the column 3 to record the description for the corresponding code.
On the main form - frmmain
I created a field called Rate to calculate the total value in the subform "frmsubmain1" the validation rule looks like as follows:
=sum(Forms![frmsubmain1]![Value])
None of these seems like working except for the first one "Observation code". Observation code is looking into the right column and registering the right code but not the other two fields are not looking to the right columns, instead they are looking into the firts coloumn. I don't know why. Can anyone help me resolve these issues. I appreciate your help. Thanks.
Girma
have a look on the attached sample database.
How can I get the query to work in order to count specific values per day.