Need To Calculate Costs Only If Not Empty,dont Know How
Aug 14, 2007
I have a variable cost that is a calculated field (as in the colum only exists in a query not as a colum in a table) that is variable hours * variable rate....the problem is ppl at my company sometimes dont know how the costs are goign to be broken down so they only put a Variable Cost....
and since they dont know the rate and hours my calculated field returns nothing...
how can i accomplish this, VBA might seem the way to go but i don't know how to use it for Access.....
so basically i need to calcualte the variable cost only if they enter something in var hours and var rate...other wise the var cost should just be what the users type ( so i i guess i have to make Var Costs a column in the table to give users the option of entering it)
View Replies
ADVERTISEMENT
Aug 9, 2007
I have a query that calculates Total Costs as (total fixed + total variable). total fixed is ( fixed hours * fixed rate) and total variable is (var hours * var rate) as expected....
But one of my records only has Var hours and a Var rate,therefore calculates total variable fine, but since this certain product doesnt have any fixed cost my TOTAL COSTS record for that product is left BLANK??
If there is no fixed cost than the total cost should just equal total variable....
When you enter a fixed cost and rate it works as expected though...any idead please i am on a deadline, im pretty sure VBA is the easiest way to go but i dont know how to go about it, this query is the heart of the whole Application, it is used primarily as the source for a few reports so this throws off my totals.
View 5 Replies
View Related
Nov 24, 2007
I need to audit the freight bills charged by freight company which has over 20,000 consignment per week. The basic charge is base on distance and weight, something like this:
SYD-MEL <1kg $6
SYD-MEL<3kg $6.7
SYD-MEL<5kg $7
SYD-MEL<10kg$10
SYD-MEL>=10kg $12, $4 per kg rate
SYD-PER<1kg $10
.....
Based on the freight rate, how can I use Access to calculate the freight charge for each of the consignment ? Is there anyway to build some query or something to retrieve the freight rate and calculate the freight cost of each consignment?
Thanks
View 10 Replies
View Related
Jul 27, 2005
Hello Friends,
Thank you for all the help i have gotten over the past couple days, as you can tell i am new and seek somebodys experience and wisdom to solve my issue.
I have a simple Form, That allows the users to enter contact information.
At the very top I have "Name" and "Age" and below it i have other fields such as Address, city, state, etc...
How can I make it so the user does not have access to the other information until they type in BOTH fields "Name" and "age" first.
Can somebody please show me how the Code might look like.
Thank you for your time.
View 2 Replies
View Related
Dec 12, 2007
Sorry the title dosen't make much sense, can't come up with a good title. Here's the problem, I work in a hospital and I have a program that store patient's admit date and discharge date. My concern is, I need to calcuate patient days (the amount of days the patient stay in the hospital). Let's say a patient was admitted on Jan 15th and discharged on March 20th. So I need to build a query that capture the months, let's say I do it on Feb, how can I tell that this patient was here on Feb? since I have admit date and discharge date. Thanks
eg. we have 4 patients John Smith, Ortiz Prerez, Jim Johnson and Nancy Cruz and we are running the query for Feb.
ID Name AdmitDate Discharge Date "The result should be"
1 John Smith 1/2/07 1/26/07 24 patient days
2 Ortiz Perez 1/27/07 3/4/07 28 patient days (because this user was in the unit)
3 Jim Johnson 12/25/06 Null 28 patient days because of Feb month
4 Nancy Cruz 2/14/07 4/1/07 14 patient days
View 10 Replies
View Related
May 6, 2014
I have a query that has 11 dates on it, I am trying with iif statements to get it down to one column reflecting the most recent date out of the 11, for example 5/2/14 and 4/9/14, I would want to have 5/2/14. The problem with this is that most of the cells are empty and and I can't calculate empty date cells. my thought was to create a statement that goes something like this iif([Date1]="", "1", [date1]), obviously this does not work but I would like for it to show something other then nothing so that I can calculate on it.
View 6 Replies
View Related
Dec 21, 2005
I have a table with products and their costs per unit of measure.
some products are costed by weight and others by piece.
table looks something like this:
product
pcs
weight
unit of measure (EA or KGS)
cost
Query should look like this:
product
pcs
weight
Total Cost
Is it possible to write an expression that, depending on the unit of measure uses the pcs or weight field to calculate the total cost ??????
View 5 Replies
View Related
Mar 26, 2006
I have set up a query for an invoice to work out the sum of the field etc, but another one of the criteria is to search between 2 dates, how can i have the dates set as variables which i already have managed but be able to make the sum of the prices just be set between these variable dates?
View 1 Replies
View Related
Aug 8, 2006
Hello,
I'm setting up a delivery cost and location form. What i need this form to do is say i select Guildford for delivery i need the delivery cost text box below to say £12.50 without manually looking up the costs for that area and if it was Farnham then £24.00. What is the easiest way of doing it?
Thanks
View 3 Replies
View Related
Mar 7, 2006
heres one that just don't add up
from the beginning,
i've got a form that puts data into a table from a text box named txtcomments, the table allows 255 chars to be stored the comments are show later using a copy of the input form, all this works great. now i'm taking this data and inserting it into another table using sql inset in the vb editor so i can play with it and leave the original in tact everything works fine on my test machine but when i transfere the database to the server i am only permitted to enter 127 chars in the text box i've narrowed it down to the sql statement and know that where the error is occuring ive tried deleting things and reinstalling im just going round in circles and when i transfere the database back to the test machine everything works fine the test machine uses access 2003 but the server uses 2000 this is the only differance
any help would be god like
john
ps sql statement as follows looks fine to me,
DoCmd.RunSQL "INSERT INTO tblHistory VALUES([txtcomments]);"
View 3 Replies
View Related
Apr 28, 2006
Been using simple access for a while now, and this problem is new on me...
Ive got two tables. One has data listed by OLD MATERIAL number (material) and the other has the new Material Number to convert to...
I wanted to simply add a column onto the table to show the new material number (just like a vlookup in excel)...
the odd thing is, i've done this loads of times before and tis worked... Anyone able to help?? :confused: :confused: :confused:
SELECT [2005 DATA].*, MatConversion.[WDB Material], MatConversion.[wdb name], MatConversion.Category, MatConversion.Conversion
FROM [2005 DATA] LEFT JOIN MatConversion ON [2005 DATA].Mat = MatConversion.[Celtic material No];
View 1 Replies
View Related
Nov 18, 2005
I have a problem that seems like it could be very similar to this post from a few years ago
http://www.access-programmers.co.uk/forums/showthread.php?t=46645&highlight=vba332.dll
My problem is I dont know how to use Access other then open a program. This is for a customer and they dont know how to do it either as someone else devoloped their database.
Basically it looks like the database is corrupt but I can not open it using a newer version of access because it has some permissions and I dont know how to go about making my access "look" like the other computer.
Any help is appreciated
Thanks
James
View 1 Replies
View Related
Dec 10, 2005
Hi all,
I created a database for my company and it works very well but, I need it or something to track the way I pay comisions. Its a type of multi-level company and pay rates vary. Also, once someone hits the top level they now join in profit sharing. The profit sharing part is the tough part becase say person A is at the top he is a team leader and he gets money not only from his sales, but also from the people in his group below him. It must also be able to pay the correct person if someone quits, say Person D, the system knows that it all off person D's people under him go up to person C now. I hope this isnt to confussing, but im starting to confuss myself as im trying to set this up.
Is access able to keep track of an organization like this or should I try to use something else?
Thanks.
Scott
View 1 Replies
View Related
Feb 6, 2007
What I want to do, is have an unbound form, user enters data in to it, then clicks on a 'save' button which then writes the data to a new record within a table
I have got no idea what it is called so find it hard to do the search.
View 14 Replies
View Related
Jun 19, 2007
Im trying to make a simple database in access , so far all is going well but im stuck with the inventory.
I have "products" table with Id [autonum],name[text],price[money], provider[number], stock[number]
Providers table with Id[autonum] ,name [Text],tel[number],
Employes table with Id[autonum], name[text],tel [number]
and i have Sales table with Id[autonum],date[date],employer[number],product[number],quantity[number],total price[money]
I have relationships between table.provider and provider.name then sales.employer with employer.name and sales.product witht products.name.
Now what i want to do is that every time that i make a sale it modify my stock number on the products table. I think the proper way is just to calculate stock every time i need it but i dont know how to do that either :D
Ill upload my db but i have to tell you that is in spanish so if you haveany question im here to answer.Thanks a lot to all
View 4 Replies
View Related
Sep 13, 2005
Hello,
I ve performed a query on a table and now have a set of results which is fine for example £1.56, £1.98, £87.90 (ive set the values in to a currency format), now i want to calculate the total so i have run another query on the last and used 'sum' in the 'total' this calculates the value but rounds it to the nearest integer for example 91!
the thing is i have done this many times before and havent had this problem! i cant understand it, its seriously doing my head in. Ive tried allsorts including the 'round' function which doesnt alter it.
any help would be appreciated
thanks
iso
View 1 Replies
View Related
Jun 10, 2005
I have created a database in which I have three Fields.
1. Date Joined
2. Renewal Date
3. Member/Renewal
When I type in the date a person joins our club, I have made the renewal date come up displaying a year ahead, when renewal of membershiip is due.
I would like to be able to put in the date joined date and have the field 3 show Member and then when the renewal date is reached, to have in automatically change to Renewal.
It is probaly a simply formula but I can not get it right.
Please Help.
Foxy
View 6 Replies
View Related
Aug 16, 2005
I have installed on my PC, WindowsXP sp.1, MSaccess versions 97 and XP.
After I have tested some databases, MSaccess 97 don't works successfully more, but XP version works good. I can't modify files mdb. Access works like in run-time mode: bars and commands reduced, standard icon of access absent, database window absent, ecc.
If macro autoexec or a form that play at the start of mdb are present, mdb works but it is impossibile to modify it. Otherwise mdb dont works, I can see access window with only menu files and window.
I have removed access and after yet installed it, but it don't work successfully.
maybe other application leaved files (or modification in file registry) that install procedure of access97 cant rewrite? And that dispose access 97 to work in run-time mode (or like)?
I am hopeless. Can You help me? Can you give me a list of files to remove, or list of modification to do in registry?
Danit :
View 4 Replies
View Related
Aug 17, 2006
Hi guys,
Could anyone give me a solution to protect a table and all the records in there so they can never be deleted when being viewed.
Many thanks, Ben
View 3 Replies
View Related
Mar 9, 2006
please can some1 help??
i created a form using wizard to display the information in three tables but wen the form is in form view theres nothing displayed yet in design view all the information is present. please please some1 help:(
View 1 Replies
View Related
Oct 30, 2004
1.) What is the File Extension associated with Microsoft Access database files?.........MAF or MAD
2.) A Field Name may contain a maximum of ________ characters.
3.) A ____________________ determines the kind of values that users can store in a field.
4.) A Field Description is required for all data fields......T/F
5.) Use of Field Descriptions dramatically increases the size of the database and, in general, should be avoided.....T/F
6.) FirstName, PhoneNumber, and ZipCode are fields which typically include an Input Mask.......T/F
7.) A Field Name is required, but a Field Caption is optional.....T/F
8.) A 5 digit Zip Code should use this Data Type......
9.) An INDEX speeds up updates, but may slow searches and sorting on a field.......T/F
10.) A Default Value is a value that is automatically entered in a field for new records......T/F
View 5 Replies
View Related
Sep 16, 2005
I have a qry I need for picking the info for sending a series of letters out. I finally got it to work and now Im wondering if there is a better more efficient way. The qry I call to generate my report calls on information from 10 other queries and 7 tables. Some of the other queries are needed for other sub-forms that rely on the data. Is this something I should change? or just be happy it works???
-Dan :D
View 1 Replies
View Related
May 28, 2006
Hello, Really hope someone can help me.
I have 2 lots of info in 1 table - nameley customer address & posting address
I need to print a form that always has the posting address on the same side. but in some cases if the postal address is the same as the customer address then it is not filled in. hope this makes sense:
Please Im Really stuck
View 2 Replies
View Related
Sep 15, 2006
I'm trying to save a change made to a sql statement in an access query but everytime I save it and close the database, when i open it up back again, the query returns back to the old unchange sql statement like it never got saved. I checked the user permissions and they are all checked (no restrictions). Any help would be greatly appreaciated.
View 1 Replies
View Related
Dec 6, 2006
ok.. here is a pickle..my table layout is like so..http://www.londonheathrowcars.com/hoo.jpgthe job table stores the main job details.. every job will be in this tableSOME jobs will have incoming flight details... some wontSOME jobs will have outgoing flight details.. some wontSOME jobs will have incoming AND outgoing flight details.. some wont have anyalso SOME jobs (credit card jobs) will have credit card details.. some wontthis is the general problem i am facing..there are 2 records in my job table..jobref no. 020jobref no. 021jobref no. 020 has incoming flight details.. outgoing flight details.. and credit card details.. which means... the job ref no. 020 is present in all 4 tables..jobref no. 021 has incoming flight details.. it does not have outgoing flight details or credit card details.. which means the job ref no. 021 is present in the job table and the incoming flight table onlyi want to create a form that displays every field in the database..my idea is to make a query using all four tables.. and then the form will display the results..HOWEVER!!!!!!!!the form will only display the job ref no 020.. because job 021 is not present in all the tables...why.. is there a way around this.. cant it just display both records and if job 020 does not have card details.. just leave those fields blank.. then on this form.. if i happen to add card details to job 021.. it will just create the record in the card table using the same ref no..is that clear.. someone please enlighten me
View 2 Replies
View Related
Mar 8, 2007
http://i73.photobucket.com/albums/i236/antmoran/databas.jpg
here is a screenshot of 2 of the tables in my database. i would like a query that would count up the amount of times the clientno appears in clientmeeting table. e.g c001 appears twice in the clientmeeting table and i would like to show this in my query. But also i need it to be general so that it will work for the amount of time c002 c003......appear. does anyone understand what i mean and know how to help?
View 8 Replies
View Related
