Calc'd Field Won't Format?
Aug 11, 2006
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:
View Replies
ADVERTISEMENT
Feb 4, 2008
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 ?
View 6 Replies
View Related
Sep 12, 2015
I 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 Related
Feb 17, 2014
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
View 2 Replies
View Related
Nov 19, 2005
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
View 2 Replies
View Related
Jun 11, 2006
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
View 1 Replies
View Related
May 17, 2007
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
View 14 Replies
View Related
Oct 29, 2004
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
View 2 Replies
View Related
Mar 7, 2006
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 Related
Jun 7, 2007
Hello 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:
View 1 Replies
View Related
Apr 4, 2008
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
View 1 Replies
View Related
Apr 23, 2014
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 Related
Jun 26, 2015
Have a continuous form with 2 date fields in each record .
If dateField1 is empty ,Then I want a way to make DateField2 Take its Place .
I know how to make things happen with conditional formatting on one field by UsingIsNull ,I have tried sending datefield2 to back.But cant work out how to bring it to front If DateField1 IsNull.
View 3 Replies
View Related
Jan 25, 2008
Hi,
I have this code which makes all the data displayed on the form in this respective field capital letters e.g John will be displayed as JOHN and saved as so.
However, what i want to do is only make the first letter as John not JOHN. How can I change the code?
Me.surname_forename = UCase(Me.surname_forename)
Thanks
View 5 Replies
View Related
Oct 7, 2005
I have changed the inputmask of a field based on an if statement. however i put / characters in place and it wont leave them there can anyone help.
Me.txtbox = "AAAAA/AAA/AA"
View 5 Replies
View Related
Oct 11, 2006
I want my PRODID (PK, AutoNumber) field to have "PROD-" before the number, i thought it could be done by seting format to "PROD-"# but that only displays it inside MS A, when i use sql queries through php it only retreives the number. How can i avheve this?
View 3 Replies
View Related
Feb 19, 2007
I saw a thread once that explained how to format a date or phone number field after you've entered the data, but I can't find it again.You do it so that you don't have the __/__/__ or the ___-___-____ pop up in the field and don't have to worry about inadvertently starting typing the data in the middle of the field instead of the beginning.Anybody know where that thread is at, or know how to do that?
View 2 Replies
View Related
Mar 11, 2008
Is there a way, either in a form or report, to modify the format of a text string within a field?
Basically, we are gathering text data using a memo field (due to the amount of data) and would like to highlight sections of the text by making it bold. It does appear that in Access a field is all or nothing in terms of formatting.
Can one put characters that would identify the start or end of such a string in the data itself?
Thanks.
View 2 Replies
View Related
May 17, 2007
I have two tables that I import that have both a date field and a time field formated as text. date field looks like yyyymmdd and time looks like hhmmss. I am using two append queries that combine the data into one table and I need the date and time fields to be date and time format respectively. When I run the query as is I get no values in either the date or the time field due to a data tyoe mismatch. My question is how do I convert the input table date and time text type fields to date and tie format for the append query.
Thanks for the help
Tony
View 2 Replies
View Related
Oct 25, 2006
Hello. I'm triyng to change every field format in a Form each time I jump from one field to another. I want it to get grey and bold when it got the focus
I managed it with a GetFocus and Lost Focus for each field but whena form has a lot of fields it gets "a lot" of programing.
Is there a way of doing it another way, for instance On current
Thanks
View 5 Replies
View Related
Aug 28, 2013
If I'm working strictly from SQL, is there a way to format a field as currency, standard number, etc. without using the format() function? Basically the equivalent of setting the format in the property sheet in design view, without going to design view.
View 1 Replies
View Related
Sep 21, 2014
I have a table that contains information along with a field named "Date Input" and then a calculated field called "Due NLT Date". New records are appended to this table daily. The calculated "Due NLT Date" field is simply "[Date Input]+5".
In some unforeseen circumstances the "Due NLT Date" field needs to be extended to 6 or 7 days from the "Date Input". I am trying to find a way adjust the number of days using a command button/VBA getting the desired number of days either from an input box or from a txt box on a form.
I have been playing with using SQL ALTER TABLE in VBA but can't get it to work.
I'd image it would look something like this but how to write it correctly:
RunSQL ALTER TABLE "table" FIELD "field" to calculated "[Date Input]+ & input box "Enter number of days""
View 5 Replies
View Related
Jun 23, 2006
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?
View 6 Replies
View Related
Aug 24, 2007
I have a calculated field in my query that takes the difference between to incomes. I want to format this field to currency. How can this be done?
View 1 Replies
View Related
Nov 26, 2007
I have a field (date field) that when I try to imput data will tell me that that what is being imputted is not in the correct formate or to large for the field settings. It is in the right formate etc. Is the field size applicable to just that field or overall everything entered in that field in the whole database? It's a decient size data base and I'm wondering if everything in that field is over the size, but then how can that be?
Help!
View 4 Replies
View Related
Jul 5, 2005
Does anyone know how to change a field FORMAT default? I am creating lots of tables with lots of number format fields. I don't, however, want a zero default value (or any default value for that matter) for all of these fields. I can go in and delete them manually, but that seems like a backward way of doing things. Does anyone know how to elimate that default value from the format of a number field in general?
Thanks!
Carly
View 2 Replies
View Related
