Dear all,
How do i insert or place the ms calc.exe in specific place on the form
I know how to activate with command button, but how to specifically place that application say calc.exe on the fixed specific place on the form
Thanks
Hi
I'm trying to finish a query that calculates the time worked in the AM and PM, adds them together, then takes that away from the standard working day.
I've done everything else, but when I take the hours worked away from the standard day it always ends up with a positive number, regardless if they haven't worked enough hours.
I've looked through the forums and everyone is talking modules, but thats over my head.
Thanks for looking, apprieciate the help.
Alan
I have a Qry field:
TotalUsage: Val((Round(Nz([UomUsage])/DSum("Nz([UomUsage])","[tblDetail]",),4)*100))
the problem is that the DSUM needs to sum up all the like records using the same SubID. I need some extra code in the divisor part. EG
subID uomUsage TotalUsage
33 10 100
33 40 100
33 50 100
34 43 100
34 57 100
the dsum needs to use SubID in order to sum correcly EG on SubID 33 as 100 uomUsage.
SubID is a Number field, Long integer.
I am presently getting wrong info. the formula above Dsum is summing as 200 since i eroneously don't have Subid in the Qry calc.
How can i fix? Thanks
Penwood
I have a calc'd percentage field in my query. This is a multi user app and the problem is that is can (and does often) happen that the numerator information for my calc gets entered before the denominator data and therefore the calc'd field shows and as an error.
I attempted to use a simple IIF statement to input a message rater than the error code but now the field will not format to Percent. It worked, I got the message to display and it calc'd the records that had data, it just will not display in percent format. It does let me set the format to percent, it just doesn't display and does not allow the decimal places to be set.
This was the line:
CalcPercent:[Numerator]/[Denominator]
This is the new line:
NewCalcPercent:IIf([Denominator] = 0, "Message", [Numerator]/[ Denominator])
I've tried to search for an answer and I'm sure it is going to be something simple that I've overlooked.
Any thoughts?:confused:
Hi,
I've searched hours on the forum but with no luck. If there's anyone out there who can help me would be very much appreciated.
The calculation in Excel is very simple but I am unable to replicate in Access. Please don't confuse this with FIFO calculation as it is purely Weighted Average Cost method based on Buy unit prices i.e Sale unit prices are not required as it will sell at the Average Cost of the Buys upto the point of Sale.
i.e.
Buy 100 @2 = 200
Buy 100 @3 = 300
Total 200@ 2.50 =500
Sell 100 leftover will be
Total 100 @ 2.50
I am attaching an Excel S/sheet which shows the Calculation of Stock ABC and the Average Prices (which is what I am trying to achieve). I have also attached an Access Dbase with same details but without any calcs as whatever I have done I am unable to achieve my result.
Any help will be much appreciated.
Thanks
Ok, I know it's simple but...
subtracting field B from field A.
Field A has data in all records, field B only some. The 'only some' I get a result but the ones that have nothing in B I get nothing even though A has data.
Both are currency and default is 0.
How do I take nothing from something and get nothing you ask ?
This is the second edited section:
I got the update query to work properly. Is this the best way to solve my problem?
This is the original edited section:
For the problem below would it be possible to use an update query to update the table?
Criteria for the termination_date field would be "is not null"
payment_to_date Update to: field would equal DateDiff("m",[payment_start_date],Now())*[monthly_payment]
Is it possible to use other fields in the table as part of the UpdateTo: calculation? I get an error when I try this.
This is the original question:
I have a field in a table called payment_to_date that I want to always be populated with the current total of how much a customer has paid to date based on their monthly payment rate. I have a form for data entry into this table but once their account info is entered the first time their won't be any changes to the account unless they terminate the service so calculating from an event procedure on the form doesn't make sense to me (I am new at this).
I entered the following in a text box control source on the form and it works but the textbox is not bound to the table so it's not the right way to do it.
=IIf([termination_date] Is Null,(DateDiff("m",[payment_start_date],Now())*[txtpayment]),(DateDiff("m",[payment_start_date],[termination_date])*[txtpayment]))
The calculation from above is basically:
If there is no termination date then the payment to date = the number of months between todays date and the date payment was started multiplied by the monthly pay rate.
Can anyone tell me how to keep the information in the table current for the payment_to_date field?
Any help is appreciated.
Troy
Hello everyone. I am very new to databases, may have bitten off more than I can chew, but I was tried of working in 4 different spreadsheets and decided to pull them all into a database so that I could get what I wanted when I wanted. Here is what I need to be able to do: In a table I have 3 different industries entered 5 different times. They all cover a Monday to a Friday. Each day for each industry I record a reading from a meter. I make a calculation by subtracting Mondays number from Tuesadays, Tuesday from Wednesday, Wednesday from Thursday and Thursday from Friday. This calculation gives me how much water each industry used each day, over 24 hours. Doing this in a spreadsheet was simple. My table has the Industry name, date, previous day meter reading, present day reading. I want to have the calc show in my query. How do I set up this calculation. Any help would be greatly appreciated. Thank you. I attached a sample database that contains the table and query.:confused:
View 3 Replies View RelatedHello again everyone!
My head's swimming right now so the solution to this problem may be right under my nose and I'm just blind to it.
I have a table with events, represented by an ID number, and the date/time the events occured. Each event is also attatched to a particular mission number. Looks kinda like this:
Mission EventID Time
Each Mission has different events like liftoff and patient contact, and I need to find the time between events for each mission. I'm trying to calculate the Time from inital call to mission end, from dispatch to liftoff, ect.
Do I need to put these times into my mission table as columns, Time1, Time2, Time3... or can I leave them normalized and still run calculations on them? :confused:
How do I use fields in related tables to create a calc field? The wizard only shows me the current table's fields. Can I do create me desired field by manually creating the calculation and bypassing the wizard? Or is this another restriction of Access?
View 1 Replies View RelatedI want to create a calculated field that If the employment type is FT Hourly or PT Hourly it will have a pop up screen for the Hourly Wage to be entered. If those criterion are not met it nulls the Hourly Wage field.
View 1 Replies View RelatedWe are creating a simple database to maintain driver license information for faculty, staff, and students who use cars from the university’s motor pool.
To do this, I have created two tables: tblDRIVER and tblLICENSE.
The fields in tblDRIVER are:
pkfDriverIndex
strLastName
strFirstName
strInitial
strAddress1
strAddress2
strCity
strState
strZIP
datBirthDate
The fields in tblLICENSE are:
pkfUpdateIndex
fkfDriverIndex
datDateUpdated
strState
strLicNumber
datExpirationDate
ynViolations
ynActive
Information about the driver is stored in tblDRIVER and the driver’s license information is stored in tblLICENSE. Periodically, we run a report that identifies drivers whose licenses are due to expire within a certain number of days. All this works fine.
My problem is that I am trying to create a lookup form that will load from a data entry form that will permit the Motor Pool Clerk to look up a driver by name and review the licensing information (which appears as a subform).
All this sort of works- I am using a combo box (based on a query) to look up a driver’s last name (which it does) and to populate all the driver’s information on the look-up form (including license information in a subform). Currently, the combo box locates the driver (including the unique index, last, and first names), and populates the form with first and last name but the rest of the information is not displayed on the look up form. Worse still, sometimes one driver’s last name matches up with the first name of the next driver listed in the table! This seems to happen only if a look up is attempted more than once. What gives.
Thanks for the help.
If I put a calculated control on my form, I don't seem to have any way of pushing its changes to a field when those changes happen. Access can be downright infuriating at times. I've spent days just putting together a simple invoicing application.
My form is not based on a query like the northwind database, because that created such ridiculous and useless behavior with subforms that I couldn't. So now that it is based on a good old fashioned table, when I change a qty or price, the calculated "extended price" (=[qty]*[price]) field visibly changes just fine, but it never has an "after update" or "after change" event. Why would this be? How am I to save these bloody changes to an actual field in a table without using some grossly inelegant code?
I Have a table that I collect data for numerous ID's. One ID may have 5 dates that is was serviced. It is due to be serviced at set intervals which is calculated from the most recent service date. I'm calculating the next service date in a text box with the DateAdd func. =(DateAdd('m',[Cal Freq],[MaxCalDate])) with the MaxCalDate being a Max func in a different text box for selecting the most recent sevice date. I'm trying to update the [Next Due] field in the [Master Asset] table with the calculated next service date so I can do queries on items that are due service within a date range. If I do an Nex Due Expr1: in the queries to calc the next due service date then do a criteria on that Expr1: such as >=[Start Date] And <=[End Date] it states datatype mismatch? Is there a way to select a requested data range on a calculated type date/time? Or How can I update the [Next Due] filed in a different table?
View 8 Replies View RelatedI have an expression in one column of my query and It keeps returning a negative result and there are no negative numbers in the source data. Can anyone help. I just want to sum the sums the two IIf expressions, but its not working. Thanks
Other Qty: Sum(IIf([QSum]![ACCOUNT]=2 And [QSum]![ICUNIT]=95 And [QSum]![UNITS]="ITEM(S)",[QSum]![QUANT],Null)) Or Sum(IIf([QSum]![ACCOUNT]=2 And [QSum]![ICUNIT]=100 And [QSum]![UNITS]="ITEM(S)",[QSum]![QUANT],Null))
I have two tables. The first contains details of a budget holders money allocation for a given period, and the other tracks their spend on products over that period. How can I generate a query to calculate the total running spend for each user from the "budget spend table" that will be written into the users record in the "budget allocation table".
My aim is to show details of budget allocation, total spend to date and remaining budget for each user in an Order form / report.
Can anyone please advise me on how to do this or suggest another way of doing it. Any help would be greatly appreciated.
Thanks in advance.
Regards Peter
I’m a newbie to Access, I have done some simple things but I now have to do something that is out of my league.
I work in an engineering company and I'm trying to make a database that the engineers can select a specification quickly, a specification or spec is like a table. Things like materials, ratings, pipe Schedule, etc are stored in it. The spec says how strong a pipe will be.
When selecting a spec you have to look at the service (what the pipe is caring, eg high pressure steam, or low pressure water). Services also store things like Design Temperature and Design Pressure.
You are giving the Service, and you have to select a spec that will be able to handle the Design pressures. To do this you compare the Design pressure to the Maximum allowable Pipe pressure (it is calculated at the join (flange) as this is the weakest point).
One other pike of information is the Tables that have the Maximum allowable Pressure. To find the Maximum allowable Pressure, you need to know the Material, Rating and the Design Temperature. Each material has its own table.
If you have a look at the attached file then in tables, SPec has Spec ID (Spec) which is Primary Key. Flange Material (FlangeMat) and Flange Rating (FlangeRating).
The Flange Material should take you to another table, FlangeMat. This table is used to get you to the correct Maximum allowable Pressure table.
There are 2 Maximum allowable Pressure Tables, TABLE_211 and TABLE 212. Here the rest of the information from the Spec is needed (Flange Rating (these are the field names with 150, 300, 1500, etc numbers)). And also the Design Temperature is needed of the service.
Then it looks up the Maximum allowable Pressure from the Table and displays it. I was thinking a form.
Also the design Pressure needs to be displayed so the engineer can compare the two.
So for the form, there will need to be a drop down that has the Service, A drop down that has the Spec, and the two values, the Design Pressure and the Maximum allowable pressure.
All I have done is shown on the file, I started to play around with the Relationships and tried to create a Form but I could not get it to work so. I deleted it and left it with just the tables. If possible can someone please help me, maybe even get my file to work and the post the working one, I will then be able to understand it.
The company I work for uses A97 throughout. Although on my laptop I have Access 2003. I want to design my inventory control database using A2003 not 97 for obvious reasons. I know A97 users cannot open a Acess 2003 db.My Q is: If I created a A2003 db and also built some data access pages within the database and placed it on my server, could users view/add/edit records in the db via these pages using their web browsers? This would only be required across our small network of less than 10 users.Any advise welcome. Even just a YES or NO would help Many thanks.
View 4 Replies View RelatedCan anyone look at this database at let me know why it is not working
plse
Stuart
Hi All,
I need to create some conditional formatting on a field that will change the colour depending on how much money has been spent
I have MaxBilling As the Field I want to attach the condition formatting too which can be a user entered value or system updated depending on other things.
The Current worked costs total field Is Called Text119.
when a project starts The Cost will be 0 so I don't intend to assign a colour there.
when the Costs get to 50% of MaxBilling I would like to change the colour to Green
70% Chances to yellow
90% Would change to Dark Red
I can do most things but %'s and me don't mix well LOL
Any help would be much appreciated
thanks
mick
hey there kinda new to access...
how would i work out the amount of hrs worked?
e.g
time start at 8.00am, time finish at 5.00pm
how would i get to say 9hrs?
I have two database which are very similar. They both use the same back end table. I have a query that I developed in one db and it works correctly there. However, when I import it into the other db, I get the Ambiguous Outer Joins message. I've tried copying the SQL from one and pasting it into the other and that didn't help. I've checked the table used and they are all properly linked in the second db. Any ideas about what could be going on?
View 4 Replies View RelatedI have created a query thats initiated by a command button from a form and one of the criteria for the query is a 'Like' command.
On the form, various option buttons and combi-boxes determine the search criteria and put it into a hidden textbox. Then, the query is run based on the contents of that hidden textbox using 'Like'.
However, I cant seem to get the 'Like' command to work with the contents of the hidden [forms]![frmSearch].[txtSearchcriteria] field.
Do I need to edit the VB code on the form to format the text to include the Like '* prefix and *' suffix, or should my search box remain basic text and have the Like command in the query ??
I seem to have tried loads of variations incorporating the various components of the Like command (the asterisks, single quote marks and even the Like command itself) on either the Form field or query but nothing seems to work. Anyone have any ideas ??
Thanks chaps.
The on click event, for a Report, has the following:
Sub DateEntry()
Dim Start_Date As Date
Dim End_Date As Date
Start_Date = InputBox("Enter Start Date mm/dd/yyyy")
End_Date = InputBox("Enter End Date mm/dd/yyyy")
End Sub
This works fine and allows for Input. I added the sub below, but it does not work and the report opens:
Sub CheckEntry()
If Start_Date Is Null Then
MsgBox "No Date was Entered"
DoCmd.Close acReport, "Summary Action Report"
Exit Sub
End If
If End_Date Is Null Then
MsgBox "No Date was Entered"
DoCmd.Close acReport, "Summary Action Report"
End If
End Sub
I have a form with two textbox, when I type something in textbox, the second textbox is filled with database query "SELECT MAX...", this works in one PC but doesnt work in other...
Why ?
all PCs is Windows XP with Office 97 and 2000.
thx
I thought I understood Dlookup – but I am not able to make it work!
Here’s my scenario:
tbl_Details (GroupID, and other misc fields)
tbl_GroupList (AutoKey, GroupID, GroupName)
rpt_Report (GroupID, and other misc fields)
I would like my report to show the GroupName instead of GroupID (Data is entered as GroupID – hence, I created the look-up table “tbl_GroupList.”
I created a text box on my report:
=DLookup(“[GroupName]”,”[tbl_GroupList]”,”[GroupID] = Reports![rpt_Report]![GroupID]
I think my syntax is correct – I just can’t figure out why it won’t work. Do I need to link the tables?
I even tried DLookup in a query (changing the above code to fit the query fields) and can’t seem to make it work. Please help!
PS -- I don't think it matters, but I am trying to do this in a sub report.