Calculating Text In Tables
Dec 12, 2007
I need help. I want a field in my table that generates an Log number.
I already have fields:
I.D. number (sequential number)
Project Name (Text field)
Milestone (Number field)
The Log number should be made up of the first two letters from the Project Name, then the Milestone, then the ID number. The three parts of the Log number should be separated by hyphens.
So if:
I.D. number = 01
Project Name = Nice Light
Milestone = 03
Then Log number would = NI_03_01
On my form I have managed to get this to happen using an unbound calculated text box, but I need the log number displayed in the table, and this seems impossible.
Any help? Important things to note are I am not very clever when it comes to computers, or programming.
Cheers
View Replies
ADVERTISEMENT
Apr 23, 2013
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
View 2 Replies
View Related
Nov 8, 2012
I have just started using Access 2010. I need to calculate student's ages from their date of birth to today's date. I have a column set up for the date of birth but can't figure out how to get this figure.
View 2 Replies
View Related
Feb 6, 2006
hi all,
i need your help on how to calculate non-value item, in this case a textbox fills with course names & description. please refer to attachment for more information.
thanks
View 2 Replies
View Related
Feb 6, 2006
hi all,
i need your help on how to calculate non-value item, in this case a textbox fills with course names & description. please refer to attachment for more information.
thanks
View 1 Replies
View Related
Nov 24, 2005
Hi everyone
i have been struggling on this database that i am developing for a while and i haven't found a solution yet.
i have a Two tables
Table 1
"Attendance" with fields
Attendance ID,
Hours,
Group ID.
table 2
"Days" fields,
Day ID
Week beginning
Monday
Tuesday
Wednesday
Thursday
Friday
Hours
now i have created a form from these two tables, and what i wanted was each day of the week i will input a value under the day and i wanted it to be added up and then the result outputted to the "Hours" Box.
My dilema is that although i have figured out how to add up the fields in the form view by changing the Control source for the Hours box to something like
=[Monday]+[Tuesday]....[Friday]
this will only display the value in form view, the table will not update. i guess this is because the control source is not relating to te table anymore.
so if someone has some other ideas i would appreciate it.
regards
Yusef
View 3 Replies
View Related
May 27, 2014
I am trying to create an expression that determined by text fields. The field is a PASS for FAIL field. The result needs to =PASS if 70% of the total column is PASS.Can this even be done using an expression?
View 4 Replies
View Related
Mar 17, 2008
Hi,
I think this should be simple, but I am struggling to work it out
In a relational database I have a stock table containing stock and quantities, there is a customer table with an order table used as the link table also containing quantities.
What I am trying to do is place an order using an order form linked to the order table, automatically reducing the stock table by the amount ordered.
I have tried an update query with no success, also tried using the builder to make a query and linked it to the stock table with no success.
I would be grateful for any ideas, I am sure it is relatively simple
View 3 Replies
View Related
Oct 19, 2005
Hey Guys,
It’s been a while but I have been visiting from time to time. Just been busy with a load of web design.
Anyhoo, I have been asked to create a report on total revenue generation for projects and have taken it as far as I can, and now need your help.
I have two queries “qryCommTrialsRevenue_PatientEvents” and “qryCommTrialsRevenue_StudyCharges” that select income via two cost codes and between two dates.
Two further separate queries calculate the total for each project. That’s as far as I can take it at the moment and now I cannot see the wood for the trees…I need to calculate the total for each project ACROSS the tables. Can this be done successfully? Every time I try it the numbers don’t add up.
Cheers,
Rusty
:D
View 2 Replies
View Related
Aug 4, 2014
I have a data entry form [Resources] and I would like to display some information about holidays in the form footer. Once the user has picked a combination of Trainer_Name and Start_Date I would like the 'On Change' or 'On lost Focus' event (not sure which would be the best) to perform a datediff calculation.
The datediff calculation would compare the difference between the start date entered on the form and the most recent past Start_Date on a query called [Hours Holiday_P1].I could adapt the same code to also look for the difference between the End_Date on the form and the next Start_Date on the Query.The idea is that when resourcing trainers I know how long it is since and how long it is till their next holiday.The sql for the query is
Code:
SELECT Resourcing.Start_Date, Resourcing.Trainer_Name, Resourcing.Duration, Time.Hours, [Hours]/7.4 AS Days
FROM (Resourcing INNER JOIN Employees ON Resourcing.Trainer_Name = Employees.Trainer_Name) INNER JOIN [Time] ON Resourcing.Duration = Time.Time
WHERE (((Resourcing.Start_Date) Between DateSerial(Year(Date())-IIf(Month(Date())<4,1,0),4,1) And DateSerial(Year(Date())+IIf(Month(Date())>3,1,0),3,31)) AND ((Resourcing.Activity) Like "Holiday*"))
ORDER BY Resourcing.Start_Date;
View 14 Replies
View Related
Jul 18, 2015
I have a personal expenditure data base indicating daily expenditure on various heads ( File Attached). I want table to reflect expenditure on various heads on daily basis in COLTOT column. Any subsequent change in any of the field to be dynamically updated.Presently I am exporting file to excel and calculating the total there and then manually updating the COLTOT field. Other alternative I tried was to create a form and make a text field and sum the fields using formula like =nz([milk])+nz([vegetables]). I feel it may be unwieldy for such large number of expenditure heads.
View 14 Replies
View Related
Jul 29, 2015
Currently I have a form with these variables
- Assets
- Shifts
- Machine Offline Date
- Machine Offline Time
- Machine Online Date
- Machine Online Time
In the Asset Table I have recorded on each asset the number of shifts it operates for as well as starting / finishing time of those shift. e.g. 2 shift asset will start at 7:00AM finish at 11:00PM
or 3 shifts asset will start at 7:00AM finish at 6:59AM
Now I have 82 assets in the factory and 20% of those machines run 3 shifts. Each shift is 8 hrs.
What I have already done is allocate shifts per asset e.g.
when I pick Asset (a) in the Asset combo box, in the shift box it will automatically generate 2 or 3 dependant on what I have set.
If an asset runs for 2 shifts, it would mean that, that asset is operational/running from 0700 - 2300 or 7:00am - 11:PM
also if an asset runs for 3 shifts it would mean that, that asset is operational/running from 0700 - 0700 or 7:00AM - 7:00AM
These are the scenarios that is entered through a "Form"
Scenario A: Machine (a) breaks down at 1700/5:00PM on the 10/7/15 and was back online at 12:30 on 11/7/15, This machine runs for 3 shifts which would mean in the "Breakdown Downtime" the result should be 19.5 hrs
Scenario B: Machine (b) breaks down at 1900/7:00PM on the 10/7/15 and was back online at 10:00AM on 12/7/15, this machine runs for 2 shifts which would mean in the "Breakdown Downtime" the result would be 23 hrs.
I would like to make this an automatic calculation, Is this possible?
Please note I am not after a "Query", I need this calculation in the main Database table named "Tbl_MaintenanceDATA".
I understand this is not the norm and many have suggested against this, however for the purpose of what I need, it needs to be in the main table with all the other data.
Here are some snip it
snipit2.png
snipit.png
View 5 Replies
View Related
Apr 28, 2014
Let's say I have a table sort of like this one: [URL] .... (Table 1)
What I want to do is make another table that references the first table: [URL] ... (Table 2)
I want the cells in the Average field in Table 2 to calculate an average of all the values for records in Table 1 with Color fields that correspond to the Color field in Table 2 (this makes a little more sense if you look at the pictures). I could do this in Excel, but then problems would arise whenever I would add a new entry to the database, or re-alphabetized the data, since Excel math is depends entirely on the positions of cells, and I want these averages to be continually calculated correctly and to change whenever I add related records to the database.
View 1 Replies
View Related
Jul 31, 2013
What is the difference between "short text" and "long text"? Both seem to have 255 characters limit. I have Access 2013, and I found a solution, that you can use Memo instead of Short text. But the problem is, that there is no "memo" data type in Access 2013. So that won't work for me.
View 8 Replies
View Related
Feb 24, 2015
I know this is an Access 101 question, but what is the best way to view text rather than numbers in my tables. In an example which I have attached, an Orders table shows the Customer field as a number. I would like to view the actual customer name when I am looking at the table.
View 3 Replies
View Related
Sep 3, 2004
hi
i am trying to build some basic static html files.
i have a 'header' / 'middle' / 'footer'
these tables are linked in the following way.
each header has an id - and this is the link to the middle
same with the footer.
i want to output the files as below:
Sub runOuttoHTML()
Dim hFile As Integer
hFile = FreeFile
for each TITLE in tblMiddle
Open TITLE & ".html" For Output As #hFile
Print #hFile, "<html><head>"
Print #hFile, tblHeader.field1
Print #hFile, "</head><body " & tblMiddle.bodydescription
Print #hFile, tbleMiddle.FreeText1
Print #hFile, tbleMiddle.FreeText2
Print #hFile, tbleMiddle.FreeText3
Print #hFile, tbleMiddle.FreeText4
Print #hFile, tbleMiddle.FreeText5
Print #hFile, tbleMiddle.FreeText6
Print #hFile, tbleMiddle.FreeText7
Print #hFile, tbleMiddle.FreeText8
Print #hFile, tbleMiddle.FreeText9
Print #hFile, tbleMiddle.FreeText10
Print #hFile, tblFooter.field1
Print #hFile, "</body></html>"
Print #hFile,
Close #hFile
and do it again
End Sub
but i cant get my head around it - any help - as always greatfully received.
View 3 Replies
View Related
Jul 20, 2007
Hi,
I'm trying to compare partial text records across two tables.
The first table simply has a description, such as:
DescriptionHammer
Bone Saw
Power Saw
The second table is a list of terms and a category, such as:
Term, Category
Hammer, 1
Saw, 2
I'm looking to build a query will compare the two and assign each description a category based on the partial match. It seems like this should be fairly easy to do, but I'm struggling to find anything to point down that path.
Any ideas?
View 5 Replies
View Related
Jan 10, 2006
I am trying to link the textboxes on a form so that their values are stored in 2 seperate tables.
Setting the Record Source of the Form to TableA and the various Text boxes etc are set to the Fields in that table works fine, however I want some other text boxes to be linked to another set of table fields, so that when I enter values into those text boxes the data ends up in a different table.
Has anyone any ideas on how text boxes on one form can be linked to different tables.
dave
View 3 Replies
View Related
Feb 9, 2006
Hi All,
How do update two different tables (fields) from one text box from a form?
Thanks
View 2 Replies
View Related
Sep 17, 2006
I have a number of text boxes which user can change values, however on change of value the bound table does not update until the form is exited.
How do i ensure the table updates regardless of exiting form or not?
View 2 Replies
View Related
Jan 7, 2013
I randomly read on another website that your field type in a table should be 'text' unless you're doing math on the number'. Is this true?
View 14 Replies
View Related
Aug 30, 2013
I came into a database where the forms have checkboxes connected to table fields that are in text data type. I see that when the checkbox is checked, the value in the field is still -1 in the table. Is there a reason to change these fields to Yes/No data type or just keep them as they are? Are there limitations or problems to having Yes/No values (-1) in a text field?
View 2 Replies
View Related
Apr 24, 2013
I have a table of nursery school children, names, addresses etc etc. One field is Under 2? Is there a validation rule (or something like) that will automatically put Yes (or No) depending on today's date and the child's date of birth?
View 6 Replies
View Related
Jun 5, 2013
Well, I have two tables in an access file one table having customer data base along with the customer name and another table only having the customer name. Now the table having only customer name need not be having the exact customer name in terms of spelling or spaces but both of them will same for e.g. table 1 - Apple and in table 2 - app let. Therefore the system should match this and throw out the results into a different new table as matching results.
View 2 Replies
View Related
May 1, 2014
I am importing a field from and old DOS based data base where dates and text are mixed in the same field. The field can have either one of more dates or text. That is the possible entries in the field are:
(1) 12/12/2013
(2) 12/12/2013, 12/1/2013 or some other delimiter other than a comma
(3) 6 CDs
When I tried to import the field, I am finding that (1) results in a string of numbers which represents the date. The others are, of course, OK.What I am trying to figure out is if the first type occurs and I have 41640 it will appear as 01/01/2014. So, can you have mixed field types in the same field by using some custom formatting with an if (of Iif) statement and what would that be? Something like IIF(IsDate([FieldA], format([FieldA], short date), @) I just cant seem to get the right nomenclature or number of correct arguments.
View 5 Replies
View Related
Nov 14, 2013
I have a Table Named "Combined List 05/06/03" Within this table there is a Field named "Scheduled Date" which has dates but the field was created as a Text Field. How do I go about changing this field to a date field. I have approx 95,000 records in this table. Is their a way to change this field to a date field or can I create another date field and move that info over. Im using MS Access 2010.
View 9 Replies
View Related
