Over 1 million tech questions and answers.

# Solved: Access iif then formula error

Q: Solved: Access iif then formula error

HI FOLKS,

I'M HAVING A HECK OF A TIME FIGURING THE "HUEY, DEWY, AND LEWY" IN MY ACCESS FORMULA BELOW:

THE FORMULA IS BASICALLY AS FOLLOWS:
TOTAL Days on Risk: IIf([NEW EXPIRATION DATE]-[ORIGINAL EFFECTIVE DATE]<=0 THEN THE AMOUNT OF DAYS ON RISK SHOULD BE "ZERO", OR IF THE [ORIGINAL EXPIRATION DATE]<[NEW EXPIRATION DATE], THEN THE AMOUNT OF DAYS ON RISK RESULT SHOULD BE [ORIGINAL EXPIRATION]-[ORIGINAL EFFECTIVE DATE], OTHERWISE THE FORMULA SHOULD BE THE [NEW EXPIRATION DATE]-[ORIGINAL EFFECTIVE DATE])

This is based on an insurance cover I'm trying to figure out the Total Days On risk based on the if then or else above. How the heck do you put this formula into Access? Am I missing " " or something?

RELEVANCY SCORE 200
Preferred Solution: Solved: Access iif then formula error

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc. You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Access iif then formula error

Read other 16 answers
RELEVANCY SCORE 56.8

I have a database that tracks products and each product has a code. I'm trying to create a formula in a query that will multiply only 2 codes by 6% and the rest by 12%. Each code has an amount attached to it within one invoice, and this is the amount I need multiplied for a separate report.
Thank you so much for any help.

A:Solved: Help with Access Formula

Read other 16 answers
RELEVANCY SCORE 56.4

I'm running windows 7 and just created a very simple access database that requires a formula, I hope someone can help me out.

In the form I created there are 3 fields:

total Cholesterol
HDL
Choles/HDL Ratio

The Choles/HDL Ratio value is determined by dividing the total Cholesterol value by the HDL value.
I know that the formula must be written in the form design window - but I don't know the formula.

Thanks for your attention and cooperation.

Justdan

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM)2 Quad CPU Q8200 @ 2.33GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 4
RAM: 4028 Mb
Graphics Card: NVIDIA GeForce 9800 GT, 1024 Mb
Hard Drives: C: Total - 476837 MB, Free - 414290 MB; D: Total - 114439 MB, Free - 35269 MB;
Motherboard: Intel Corporation, DG43GT
Antivirus: avast! Antivirus, Updated and Enabled

A:Solved: access 2003 formula

Read other 16 answers
RELEVANCY SCORE 56.4

Hi,
I'm trying to generate a formula that will tell me the Median outcome from a query. Is there a standard formula for Median in Access?

If the results of my query (for example) are 1 through 100 rows, I want the formula to count the number of rows, divide by 2 and tell me the value of a particular field in the middle row.

Hypothetical example:

Count of [ID#]=100/2 tell me the value of [Amount] in row 50

Not sure if this makes any sense to anyone but I'd appreciate any help.

THANKS!

A:Solved: Access Formula in report

Paintergrrl, you are the first person to post on here who actually wants a Median value for real, why I don't know, because to me it is a pretty meaningless value.
Access doesn't have such a Function, although it has quite a few simple Statistical functions.
So it will have to be done as you have outlined, you can obtain the record count either in a Query or using a VBA Recordset, but to get the Median Record you have use to use a VBA recordset.

Read other 1 answers
RELEVANCY SCORE 55.6

Someone suggested this function to calculate a workday when given a beginning date and a number of days to be added on. I just dont know what to do with it to make it work. Where do I put it and is there anything init that must be changed (field names?). Please let me know

' ********* Code Start **************
'
' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.
'

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
' Add the specified number of work days to the
' specified date.

' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.

' In:
' lngDays:
' Number of work days to add to the start date.
' dtmDate:
' date on which to start looking.
' Use the current date, if none was specified.
' adtmDates (Optional):
' Array containing holiday dates. Can also be a single
' date value, if that's what you want.
' Out:
' Return Value:
' The date of the working day lngDays from the start, taking
' into account weekends and holidays.
' Example:
' dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
' returns #2/25/2000#, which is th... Read more

Read other answers
RELEVANCY SCORE 55.6

I am attempting to run an update query that compares data from another column then updates the date column. The formula I have is

IIf(([Frequency]=1),([Next Day Off]+7) Or ([Frequency]=2),([Next Day Off]+14))

I can only get one of them to work correctly. Either the first formula works or the second part of it works but not both.

What am I doing wrong?

Any suggestions?

A:Solved: Access Update Query Formula

try it this way...

Expr1: IIf([Frequency]=1,[Next Day Off]+7,IIf([Frequency]=2,[Next Day Off]+14,""))

I nested the statement, and the last result I left empty quotes because I did not know if there was a 3rd posibility? If it is only 1 or 2 then it will be fine

Read other 2 answers
RELEVANCY SCORE 55.6

Okay easy question for today.

I have a query with a field called "min". I want to create another field that takes the value of "min" and multiplies it by 1.25.

I have tried the following:

Code:
Extra time: [min]*1.25
This just returns a blank field called extra time. What is the correct expression?

Thanks
Cara

A:Solved: Access Query: creating a formula

The correct formula is
ExtraTime: [min]*1.25

I am not sure why yours is not working, other than the space between Extra & Time.

Read other 2 answers
RELEVANCY SCORE 55.6

Hi,

I have a table with a text field (call it "NbrCounter") that holds data in the following format:

Number1
Number2
.
.
Number15
.
.
Number124
.
etc...

I'm wondering if there is a way to write an update query that would strip off the numbers on the right and put them into a new number column.

In Excel I would simply write a function like =RIGHT(E6,LEN(E6)-6) which would strip off 6 characters starting from the left, leaving just the numbers. Is there anything similar I can do in Access?

Thanks

A:Solved: Access update query formula

still have those functions and MID

mid = http://www.techonthenet.com/access/functions/string/mid.php
left = http://www.techonthenet.com/access/functions/string/left.php
right = http://www.techonthenet.com/access/functions/string/right.php
length = http://www.techonthenet.com/access/functions/string/len.php

so you should be able to do something the same
something like - dont have access in front of me

=mid([nbrcounter],7,len[nbrcounter]-6
so this should start at character 7 (the 1st part of the number and then take 6 away from the length of the string and so extract the number

sorry its not the exact syntax

Read other 3 answers
RELEVANCY SCORE 55.2

I have an Access 2003 db that exports percentages for project reps, equaling 100%. For instance:

Sally 34%
Bob 23%
Ed 12%

etc...

However, some reps are involved with projects that have very small numbers, so I end up with lots of Jon 1% and Megan 2% values.

Ultimately, I shoot these values over to Excel, where they are charted. Instead of every rep with any project hours, I would like only for the top, say, 90% of project reps to show. Then, for those bottom 10%, just a value called "Other" with the percentage. This wouldn't always be 10% of course, but could be 9% or 8% depending on how large a percentage the top reps occupy. So, it would look something like:

Sally 34%
Bob 23%
Mike 22%
Ed 12%
Other 9% (which would actually be Chris 6%, Megan 2%, and Jon 1%)
There's a very roundabout way I can do this in Excel, but I'm sure there's a way to do it in Access before export as well. Any ideas on how this would work?

I would be more than willing to explain further, offer screenshots, or whatevs.

Thanks!

A:Solved: Formula help for Access 2003 query builder

Well it sounds easy when you write it, but I think you will find that it is virtually impossible to do in an Access query,
You can of course select the Top "N" number of reps in a query, but that is fixed & would not necessarally equate to 90% or anywhere near it. You could of course do it with a Top N query and VBA, the simplest way would be to use the VBA to put the (0% Reps & their percentages in to a Temporary table to export to Excel.

Read other 3 answers
RELEVANCY SCORE 55.2

Hi,

based on this...
http://forums.techguy.org/business-applications/910811-calculated-values-charts-s-store.html
​...I will be storing values calculated on a few formulas in the DB, I wonder if thers is some sort of recommended way to do it?

So far my approach would be like this:

Ctl A - input field using table field as 'data source'
Ctl B - input field using table field as 'data source'
Ctl C - unbound field with formula to calculate and show on screen the Resulting Value.
Ctl D - "print" field where Results are 'echoed' so they get stored in the bound table field (using table field as 'data source')

I am using C and D because the source of C is a formula instead of a table field and therefore it can't be stored.
Maybe there is a way to avoid this and I should do that instead?

TIA.
HQ.

A:Solved: Store formula results in Access 2003

Read other 13 answers
RELEVANCY SCORE 55.2

I am looking to round down a currency box and dropping the cents to a whole dollar amount that cannot exceed \$362.00 Dollars.

The formula in the box titled [DC1] is - =([Q1]*(0.041))+(*6)

I need to drop the cents and if the dollar amount is over \$362.00 round down to that.

In a similar box I need to round down a percent to the nearest .5 but not to exceed 26.0

The box titile is [WKENTT1] and the formula to get the percent is -
=[SBPW1]/[RDWBA2].

Also, I would like to compare the currency box [SERPW] with [WBA7]
and if [SERPW] is greater than [WBA7] enter the amount from [EMPCHG] into a box titled [SEPEMPCHG1].

Any ideas, tips, or sample formulas to help me out? Thanks.

A:Solved: Access 2000 Text Box Formula Questions

Read other 16 answers
RELEVANCY SCORE 54.4

I'm a contractor. I use 2007 MS Access on Windows 7. I have a form that gives me the price to charge per sq foot, using a mathematical expression in the Expression Bulider under properties when designing the form. Suppose it says
=[sq ft] * 1.23 It works fine, but the results are only realistic when the sq ft is between 500 and 6000. I don't want the form to give a value if the sq ft is below 500 or above 6000. Can I do that? Thanks.

A:Solved: Access - setting upper limit for formula in a form

Read other 7 answers
RELEVANCY SCORE 54.4

I got two questions if anyone can offer a help:

Q1: in DVD List 1 for D0112 i can't show name under Star 1 due to my formulas

Star1: IIf(IsNull([Starring]),"",IIf([CommaPos]<>"",Left([Starring],[CommaPos]-1),""))

Star2: IIf(IsNull([Starring]),"",IIf([Comma],Right([Starring],Len([Starring])-
[CommaPos]-1),""))

my formula assumes there are two names separated by a comma. Example:
Starring
Tom Cruise, Meg Ryan

so i can break up the text and it becomes

Star1
Tom Cruise

Star2
Meg Ryan

(i need to keep IIf(IsNull([Starring]),"" otherwise it will display #error# by the way.)

However when there is a single name nothing shows (because formula can't locate a comma so shows nothing as it is designed). Is there anyway i can play with formula to show a single name?

Q2: in DVD List 1 for D0114 i can't show brief description under Star1 and Star2

Sometimes we have documentaries so instead of movie star names we type brief description of the movie. Example: 30 years after Vietnam war.

Due to my formula nothing being displayed. Is there anyway i can play with formula so that when there is no comma in the sentence formula should display whole thing?

thanks all

A:Solved: Access 2003 how to modify text break up formula

Read other 13 answers
RELEVANCY SCORE 54

Hello. I am having trouble with a workbook I am creating. I have five columns with the potential to contain data (it is a gradebook for my class). The problem is that not all columns currently contain scores (I haven't graded a writing assignment, for example). Because those columns not used return the #DIV/0! error, it also screws up my other cells where I calculate GPA and Letter Grades. How do I get it to skip a columns sum if no data has been entered for that column?

Thanks?

I have the spreadsheet if anyone feels they need to "see" it to help.

Jeff See

A:#DIV/0! Error How do I get formula to skip a cell where the formula returns a sum 0?

Read other 8 answers
RELEVANCY SCORE 52.4

=IF((TODAY()-R5)>183, "Overdue", "Current"), IF(R5="","N/A")

I added the condition in bold letters so as not to return the OVERDUE value if the cell is blank but it's not working. the first set of conditions work just fine. thanks for any help.

A:Solved: If, then formula error - Excel

the format of the IF condition is

TEST , True, False

what you have is an extra bit in bold that IF will not work with

You need to re structure the formula to have an imbedded IF

= IF( R5="", "N/A" , IF( (TODAY()-R5)>183), "Overdue", "Current") )

SO you test for a blank cell in R5 first - if thats true then "n/a"
BUT if its false
then you do a 2nd IF statement for the condition

I have not put into excel - so i hope the syntax is OK - brackets maybe wrong

Read other 3 answers
RELEVANCY SCORE 52.4

Hi,

This may be simple for some and as of now it's not for me.
I'm trying to use the following code, but receive an "Application" error 1004.
Would some one be so kind and point out my confusion.
Code:

Sub test_formula()
Range("A4").Formula = "=If(D4<>0,D4,"")"'''From this
''' To
Range("A4").Formula = "=IF(RC<>0,RC,"""")"
End Sub

Solved my problem. I used the R1C1 format

Read other answers
RELEVANCY SCORE 52.4

I am running a sales spreadsheet that requires sales entered each day. I use a simple formula to run a continuous total starting at Monday and ending Sunday (1st cell for sales input is B4, second D4, third F4 and so on. 1st Cell for accumulated sales is C4, second E4 and third G4 and so on.
I use B4+D4 to caluculate the running total in E4 or example,and to eliminate that total appearing in G4 where the next formula is B4+D4+F4 I precede that with an IF formula IF(E4=0,'', ). This way I only show the current Week to date totals, not the upcoming days in the rest of the week. My problem lies in Holidays. If for instance D4, the Tuesday was a holiday and I enter either "holiday" or just leave it blank, the rest of the weeks formula will not work. I get a Value message. How can I achieve my goal of getting a daily week to date total without having it show in the upcoming days AND have a holiday in there too??? Any suggestions would be appreciated. Thank you John

A:Solved: incorporating IF formula with daily add formula and Holidays included

Read other 7 answers
RELEVANCY SCORE 52

Hi,

I want to check a value in a string using indexof. If I check one value it works fine, but when I try to check two values in a string it fails and it shows ### as the result value.

This code works, checks for YK value:

=IF(Data!\$B\$3={1},IF(Data!\$FL\$3<>"",IF(IndexOf("YK",Data!\$FL\$3,1)>0,"X",""),""),IF(OR(Data!\$B\$4={2,5}),IF(Data!\$FL\$4<>"",IF(IndexOf("YK",Data!\$FL\$4,1)>0,"X",""),""),IF(Data!\$B\$5={4},IF(Data!\$FL\$5<>"",IF(IndexOf("YK",Data!\$FL\$5,1)>0,"X",""),""),IF(OR(Data!\$B\$6={7,9}),IF(Data!\$FL\$6<>"",IF(IndexOf("YK",Data!\$FL\$6,1)>0,"X",""),""),""))))

This code fails when I check for YT and YK, it doesn't show me an error, can I do this way?

=IF(Data!\$B\$3={1},IF(Data!\$FL\$3<>"",IF(IndexOf("YK", "YT",Data!\$FL\$3,1)>0,"X",""),""),IF(OR(Data!\$B\$4={2,5}),IF(Data!\$FL\$4<>"",IF(IndexOf("YK","YT",Data!\$FL\$4,1)>0,"X",""),""),IF(Data!\$B\$5={4},IF(Data!\$FL\$5<>"",IF(IndexOf("YK","YT",Data!\$FL\$5,1)>0,"X",""),""),IF(OR(Data!\$B\$6={7,9}),IF(Data!\$FL\$6<>"",IF(IndexOf("YK","YT",Data!\$FL\$6,1)>0,"X&q... Read more

A:Solved: Error Excel Formula - indexof

Nevermind.. FIxed

Read other 1 answers
RELEVANCY SCORE 52

Hi,

I use Excel 2000. I would like to know if I can put a formula in a cell and then copy the result to paste elsewhere.

For example, let's say that in column A I make a list that starts with 4 in cell A1. Then A2 contains the formula =A1+2. A3 contains A3+2, and so on through A10. So I end up with the list 4, 6, 8, 10, 12, 14, 16, 18, 20, 22 in cells A1-A10.

But now, if I want to copy the result in A8 (18) and paste it elsewhere, can I do it? I don't want to copy & paste the formula because the formula will not give the correct result if the cell is not in the original sequence. I want to copy & paste 18 to a new cell, not the formula =A7+2. Can it be done? Thanks.

A:Solved: Is There A Way To Copy & Paste The Results of A Formula, But Not The Formula?

2 ways-

1) Copy the item and then 'paste special' (right click the cell you want to paste the number(s) in; select paste special; select values only.

or

2) Save the sheet as a .csv and all the formulas are removed.

Only two ways I've found my friend, good luck.

Read other 3 answers
RELEVANCY SCORE 50.4

Hey,,

Here's the deal

I am linking through excel 2007 to an access 2007 DB Query (with columns A & B).
Column A & B show correctly in excel I have added a third column C, to calculate the running sum of A&B
Thus
C1 = Running Sum
C2 = (A2+B2)
C3 = (A3+B3)+C2
C4 = (A4+B4)+C3
C5 = (A5+B5)+C4
C6 = (A6+B6)+C5
...and so on and so forth...

Now I hit the refresh external data button and ......\$%##!!!
Suddenly Excel in all his wisdom decided that he feels the need to edit my formula...
Now it reads as follow:
C1 = Running Sum
C2 = (A2+B2)+C1 //the "+C1" is automatically inserted by Excel & results in a Error
C3 = (A3+B3)+C2
C4 = (A4+B4)+C3
C5 = (A5+B5)+C4
C6 = (A6+B6)+C5
...and so on and so forth...

How do I kill this bug?

A:Solved: Excel 2007 - Formula Error After External Data Update

The problem is that when you refersh you must have chosen to clear the table so your formula is lost.
What I usually do is refresh and the then have it run a amcro afterwards that reinserts my column with my needs.

Read other 2 answers
RELEVANCY SCORE 49.2

Hi Guys,
I am new i got one error that "access run time error 3061 too few parameters expected 1"

i write one query which is run in query build in access, i copied that query and paste in to VBA code but i got above error i dont know whats wrong

my query is

strgrQuery = "SELECT Count(Vendor_Resources.onsite_offshore) AS CountOfonsite_offshore FROM Vendor_Resources WHERE (((Vendor_Resources.[Vendor_Name]) = 'Sujeet') And ((Vendor_Resources.[Onsite_Offshore]) = 'Offshore')) GROUP BY Vendor_Resources.onsite_offshore;"
Set rs = db.OpenRecordset(strgrQuery, dbOpenSnapshot)
If (rs Is Nothing) Then
MsgBox "Sorry rs is Empty"
End If

A:Solved: Access Error access run time error 3061 too few parameters expected 1

Read other 7 answers
RELEVANCY SCORE 46.8

I'm trying to do a formula and it won't take. What am I doing wrong?

I want to divide the profit by the customer cost plus the shipping cost. Here is what I had:

= [Profit] / [Customer Cost + Our Shipping Cost]

Thanks,

Kammmie

A:Access Formula

Read other 8 answers
RELEVANCY SCORE 46.8

I have a table that has the fields ROOM and LASTNAME. What I want to do is to create a report that will show the persons last name if their room number is listed in the Field ROOM and to display "Not Occupied" is their room number is not in ROOM.
Any help would be greatly appreciated

Thanks,

Jnar

A:Need help with an Access formula

Read other 6 answers
RELEVANCY SCORE 46.8

Hi,
I am working on General Journal on access, and I am trying to add the sum function (for all debit and credit separately) on the footer. For some reason, I am unable to do so. Can any one suggest the easiest way to accomplish this?

A:MS Access formula

I you are referring to the page footer then you can't calculate there. You should use the grouping option, in there you will find the option to add a goup header and footer.

Place your text controls in the header and your sum calculation in the footer. If you just want the over all total then leave your controls in the detail section and put the Sum in the REPORT footer.

Depending on the level of detail you will have to play around with it. Move them around and rerun the report to see how it changes. Grouping will give you the option of keeping records together by for example: Customer, Account, year, Month etc.. You can repeat the header section if it over runs a page.

=Sum([YourfieldName])

Read other 1 answers
RELEVANCY SCORE 46.8

I am new to this forum, so please excuse me if there is already a thread about this.

I need to know how to do a formula in Access. Basically i have a query that has an inventory total and a used total.

I need a formula that will subtract the used total from the inventory total.

The end result will be as below

Inventory Total Used total Remaining
T-shirts 10 5 5

i.e. inventory total - used total = remaining

Thanks

A:Formula's in MS Access

Hello diablo, in the next Blank Column enter the following in the Header where the Field name normally goes

Remaining:[Inventory Total] - [Used Total]

this assumes that "Inventory Total" and "Used Total" are the names of your 2 fields, if not put your real field names in the Brackets instead.

Read other 2 answers
RELEVANCY SCORE 46.8

I designed a Invoice database. I need to be able to have it calculate the following: if the invoice is over \$5,000 then tax at 7%. If the invoice is under \$5,000 tax at 6% plus \$50. If the invoice is a negative amount then it needs to deduct the same tax as was originally charged. Expr1: (IIf([Amount]<-5000,[Amount]*0.07,IIf([Amount]>-5000,[Amount]*0.06+50,[Amount]*0.07)) this is the formula I came up with, but it does not do the negative amounts correctly. Specifically if the negative amount is like -5020.

A:Access Formula

Read other 10 answers
RELEVANCY SCORE 46.8

I have two tables joined with projid. Table A has budget information that is coming from another program. Table B is a table to make adjustments to that budget. Not all projects have an adjustment. When I run the query to join the tables and have the budget from A less the adjustments calculated i run into a problem. if I don't have a matching projid in B, it does not put anything in the adjustment field and then does not put anything in the final amount field. if there is not an adjustment i want it to know that and the budget and the final amount would be the same. (clear as mud?)

A:is there a formula in Access

Why isn't there a matching projid in table b?

Read other 3 answers
RELEVANCY SCORE 46.8

Okay, so I'm a complete beginner to Access and am trying to get it to do something that resembles an if formula.
I have it set so that it's looking for an account number that appears in both tables, but I want it to list it only if the following columns do not equal 402 or 404 or 423:
Code1
Code2
Code3
Code4
I can get it to do it for the 1st column only, because there is always data in that column. If I copy that formula over to each criteria then it will only work if there is data in that column. Any help???

A:If Formula in Access

Read other 10 answers
RELEVANCY SCORE 46.8

I want to create a report in Access that gives me a total for a column. we have the fields reg hrs, OT hrs, reg pay, OT rate. i can create a column that takes the (reg hrs X reg pay) + (OT hrs x OT rate). now i want to total that column in the footer, now what? i need some help with creating that part.

Thanks so much

A:Formula in Access

Read other 8 answers
RELEVANCY SCORE 46.8

Alright, maybe this is simple for some but I haven't gotten into this part of Access or VB. I have a table that has a field( I'll call it field 3) that sometimes has a value and sometimes doesn't, I was using this field to do a small calculation in a report. The problem comes in when there is no data in the field it returns a zero. The expression for the report text box is "=(field1*field2)*field3", which works great as long as the value in field3 is 1 or more. How do I tell Access to use "1" if field3 is <=0 ?? My wife seems to recall that she has done this in a VB class she took and it certainly seems possible, I just don't know where to start. Any help appreciated as always.

A:Need Access formula

Read other 16 answers
RELEVANCY SCORE 46.4

I am creating a new database in Access and was wondering if there was a formula (similar to the Countif in Excel) that I can put in a column to count the previous number of times a persons name has been entered into the database?? Please help!!!

A:Can you use the Countif formula to use in Access?

Read other 16 answers
RELEVANCY SCORE 46.4

Hi there

I'm starting to learn Access. I have created a table and some queries based on my table. One of the colums in the table called "bus" is called "BusAge". I have been calculating the age of the bus in the "BusAge" column on an excel spread sheet. In other words I take today's date and subract it from a previous date and divide by 365 (=(now()-A1)/365). I then manually input these numbers into my Access table in the column called "BusAge". Then I do my queries.

I know that I can import the excel table into my Access data base. I also know that I can create a link to my Excel spread sheet. I want to avoid doing these kinds of steps if possible.

Is there a way that Access can do this for me automatically in the talble using a formula or a macro? I can do the formula in the query but that is of no use to me. I need the formula in the table. Is this possible or not?

Mario

A:Access Table formula

Read other 10 answers
RELEVANCY SCORE 46.4

I have a field where I can enter a date that someone worked and it will be overtime. If they work on Saturday, they will get paid time + 1/2. If they work on Sunday they will get paid double their normal pay. That being said, I want to enter the date (ie: 4/15/16) and then it can determine what day of the week that is. I have that part figured out. Now when it is a Saturday, I want to multiply the number of hours worked times 1.5 or if it is a Sunday I want to multiply the number of hours worked times 2. What would my formula be?

Read other answers
RELEVANCY SCORE 46.4

This should be simple - why can't I get it?

I've built a report based on a query that takes sales data from several stores, for different date ranges and calculates an average weekly sale. The report summarizes the average weekly sale on one line for each department. So, the query takes total sales devided by number of weeks. Then the report takes total weekly sales devided by number of stores to get an average weekly sale for the group - broken down by department (grocery, produce, meat, etc.).

Now, I just need a simple total of these numbers. The name of the text box with the numbers I want to add is "Weekly Sales". In the report footer, why can't I make a text box with the formula =sum([Weekly Sales])? If I try to do another calculation like adding up all the "Sales" and deviding by weeks and then deviding by stores - it just doesn't come out the same as adding up all the numbers that display in the report in the [Weekly Sales] text box.

I hope I'm making sense. I know it's simple.

By the way, my report has:

Report Header - Report title
Page Header - column headers
DeptNo Header - Nothing
Detail - Nothing
DeptNo Footer - Summary Avg Sales by dept. (Sales/Weeks/#Stores)
Page Footer - just page #'s, etc.
Report Footer - Where I'm trying to add all the amounts in DeptNo Footer

Thanks for any help available.
Judy

A:Access Formula Problem

Read other 7 answers
RELEVANCY SCORE 46.4

I hope I can explain this semi-intellegently.

I have an Access 2000 database with a report that has a text box showing TotalNumberToBeCompleted (no problem here).

I would like to have a text box showing TotalNumberActuallyCompleted (this is where the problem is). If I do a simple Count function, I get the Total Number To Be Completed for the month For Each Location. I would like to just count the checkboxes that have a check mark, thus counting the Total Number Actually Completed For the Month regardless of the Location.

I've added the Count function to the Location Footer, Location Header, Topic Footer, Topic Header, you name it. It just doesn't work for me.

Greatly appreciate any help!

A:Access -I Have Another Formula Problem

Read other 7 answers
RELEVANCY SCORE 46

I have a form in an access database. This form tells us when our employees have taken their year tests and when the next one is due. This testing is yearly. I have a form that will show:
1) the name of the employee
2) when the test was last taken
3) when the next test should be taken
4) if their testing status is "expired" due to missing a test date

the problem is that 4th colum is not caculating exipired correctly. We have employees that have taken their test this year and colum 3 does state the correct next test for next year yet, they are showing as "expired" as if they had missed this years test. Here is the formula for the "status" colum:

=IIf([next test]<Now(),"EXPIRED!",Null)
Any advice or help would be appriciated....

A:form formula in Access 2003

Read other 12 answers
RELEVANCY SCORE 46

Hi,

I have a field that consists of cases, and another field that consists of number of days (refer below). Does anyone know how I can count the total number of cases with number of days that are LESS THAN 90 days?

Case ..... Number of Days
Case 1 ...32
Case 2 ...45
Case 4 ...95
Case 5 ...26
Case 6 ...80
Case 7 ...55
Case 8 ...100

Total number of cases: 8
Total number of cases < 90 days: 5 (This is the answer I want)

A:MS Access 2003: Count Formula

Create a query, click the Totals button at the top, add both columns and add the number of days twice. Set the second one to count and put your <90 days in as a criteria.

Read other 1 answers
RELEVANCY SCORE 46

I am fairly new to Access 2007 and am trying to creat a formula in a Query using DLookup. I am very familar with Excel and the VLookup formula, unfortunately Access does not have VLookup.

What I need Access to do is compare a field from table A and table B, and where those two fields match give me the result that in the MilageRate column of table B.

Fields being compared
Vehicle ID from Vehicle Logs table (text field)
Tag No from Mileage Rate table (text field)

If they match, output
MileageRate from Mileage Rate table (number field)

This is the formula that I have been playing with. I have put the " and ' in different ways and continue to get a 'Type mismate in Express' error. Can someone help?

MRate: DLookUp("[MileageRate]","Milage Rate","[Vehicle ID]=[Tag No]")

Thanks
Astor64

A:Access 2007 DLookup Formula Help

Read other 9 answers
RELEVANCY SCORE 46

Having not had much activity with the MS Access databases I've designed, I'm getting "rusty". Have a stacked "IIf" formula that isn't working. The formula is Phone: IIf([Homephone],[Homephone],IIf([Cell],[Cell],"?______________")). It works if the Homephone is blank (produces the "? ____"), but for records that have no Homephone, but DO have a Cell, it does not display the Cell.

Why? I know I'll feel stupid when someone tells me, but have to ask.

A:Access query formula not working

Oops! Never mind. There must have been a space or some other invisible character in the formula that was causing it to ignore the Cell field. I copied the formula to the Clipboard before trying some changes that didn't work [being careful to not copy beyond the last ')']. When I dropped it back and ran the query again, it worked . The formula looks identical to what was there when it didn't work, but some foreign element must have been there.

Read other 1 answers
RELEVANCY SCORE 46

Hi I am very new at this and out of my element.
I have a standard monthly report that generates list to update client data every 6 months. The formula needs to be changed to fit our new needs. I do not know how to access the standard formula to replace it.

the old formula looks from current day out 30 days for needed for 6 month updates. What I would like is one that will generate a list for just the reports in that month as all due dates are now the first of the following month (no matter what the day is all 6 month updates are due from the month not the day anymore ie all June are due in Dec 1).
Bruce

A:Change Access report formula

Read other 8 answers
RELEVANCY SCORE 46

Hi,I'm a newbie from MS Access and just starting to read some articles about it. But I know how to work on MS Excel. I'm dealing with a mdb file which I believe an MS Access file. Query 1: I just want to know how to edit its formula which is embedded on it. and Query 2: How to enter the data automatically which has a unique code I made on every item (for example you are going to enter the price of more than 2000 items). I'm pretty sure there is a way to enter it just like VLOOKUP in excel but I dont how in MS Access or visual vasic.Thanks(Moderator edit: post moved to more appropriate forum. jgw)

Read other answers
RELEVANCY SCORE 45.2

Hi People,

I need some assistance in making a formula for my Access Database.

Supposedly I have the following values in my database:

ID Name No_HR_Asia No_HR_Euro No_HR_USA
1 John 20 10 10
2 Eddie 15 5 22
3 Kimberly 5 25 10

Now in a query I want to find out the Max Number of hours that each person have worked between those 3 fields, so the output in the query should be something like this:

ID Name MAX_HR
1 John 20
2 Eddie 22
3 Kimberly 25

Coul anybody tell me the SQL statement I should use to get the result?

Thx beforehand, any help is greatly appreciated.

A:Microsoft Access 2000 - Formula Question

Read other 8 answers
RELEVANCY SCORE 43.2

For some reason I cannot get Excel 2000 to display the results of a formula. Only the formula text displays. I've gone to Tools-Options-View-Windows Options and checked and unchecked the Formulas checkbox. It doesn't make any difference.

Help!

Thanks,

Thomas

A:Excel 2000 Formula Text Displays instead of Formula Results

Format the cell as a number, it prolly is fmt'd as text

Read other 3 answers
RELEVANCY SCORE 43.2

This occurs in Vista on a new Viao laptop when trying to run a proggy written for windows. Apparently Vista lacks csrastl.ocx which I have copied from this XP home machine and registered it in Vista windows/system32. The install proggy was deliberately modified to take account for this but the situation has now changed and that peeps is no longer available to help.

Is there a quick and easy answer?
cheers
Lil

A:Solved: Error 75 in RS Home, Path/File access error

I had a query up on another forum where this has been answered.

The software had been written for earlier versions of windows so Vista did not have an appropriate file in its registry. Copying, pasting and registering this file in Windows system32 with the AUC switched off and a modified proggy downloaded and installed has done the trick. This problem and another has kept me busy all the weekend.
Cheers everybody.
Lil

Read other 1 answers
RELEVANCY SCORE 42.8

Hi,

i am trying to use this formaula..

=(\$AN\$12+(3*(SQRT(((\$AN\$12)*(100-\$AN\$12))/(\$AN\$14)))))

but i keep getting the #NUM! error. can anyone help me sort out why as i cant see anything wrong

A:my formula gets #NUM! error

whats in the cells / format of an12 and an14

Read other 3 answers
RELEVANCY SCORE 42.8

Hi ~

I'm working on a financial formula, trying to calculate a growth rate for a row of cells. The problem is that not all rows have complete data sets, so the formula picks up blank cells and returns a #DIV/0! error.

The existing formula works fine for those rows that have non-zero entries in all cells, and I *can* manually adjust the formula for the starting / ending cells in each row--but what a pain(!). As this spreadsheet grows larger, or the data sets change over time, it means I have to constantly keep adjusting it for every row of data involved, which is extremely time-consuming.

Here's an short example of what I might have in any given row:

A1 (blank), B1(blank), C1(blank), D1(.10), E1(.25), F1(.72), G1(1.0), H1(.80), I1(.60), J1(.75)--then in K1 is the growth formula, e.g. =((J1/A1)^(1/10)-1).

Is there ANY way to create a formula that will dynamically adjust for the blank cells that might exist across a row of data and let the formula do the calculation?

I'm new to advanced Excel topics, but any ideas would be MOST appreciated(!).

Thanks -

CP

A:Excel Formula / How Can I Ignore Blank Cells In Row Formula?

Read other 14 answers