possible conflict in coding

I am having trouble with a query which involves multiple parts. It is tied up
with asset management. In a nutshell there are three components CRC(Current
Replacement Cost), ACC_Dep (Accumulated depreciation),WDCC (Written down
current cost). Other commponents include difference between construction date
and current date, design life of asset.
When the date difference is equal to the design life the answer is 0 the e.g
CRC= $64960, WDCC= $4256 (it should read the same as CRC/Design life (in this
case 15)) and the ACC_dep is $64960 and it should read CRC-WDCC. As a result
the totalsof all the road assets is out and this only occurs when the date
difference is equal to the design life.
This occurs in three differnet areas Pavement (as above) Formation and
surface coding is the same in all three instances axcept Pav,Frm and Surf are
used. Provided coding for pavement

ACC_DepPav: IIf(([qry Areas]![Pavement Value]*((100/[qry Areas]!
[Pavement_DesignLife])/100))*(DateDiff("yyyy",[BLOCK_DATA]![YEAR],Date()))>
[qry Areas]![Pavement Area]*[qry Areas]![Pavement_Cost],(([qry Areas]!
[Pavement Area]*[qry Areas]![Pavement_Cost])-([qry Areas]![Pavement_Salvage]*
[qry Areas]![Pavement Area])),([qry Areas]![Pavement Value]*((100/[qry Areas]!
[Pavement_DesignLife])/100))*(DateDiff("yyyy",[BLOCK_DATA]![YEAR],Date())))

WDCCPav: IIf([qry Areas]![Pavement Value]-([qry Areas]![Pavement Value]*(
(100/[qry Areas]![Pavement_DesignLife])/100))*(DateDiff("yyyy",[BLOCK_DATA]!
[YEAR],Date()))<=0,([qry Areas]![Pavement_Salvage]*[qry Areas]![Pavement Area]
),[qry Areas]![Pavement Value]-([qry Areas]![Pavement Value]*((100/[qry Areas]
![Pavement_DesignLife])/100))*(DateDiff("yyyy",[BLOCK_DATA]![YEAR],Date())))

CRCpav: ([qry Areas]![Pavement Area]*[qry Areas]!Pavement_Cost)

0
alrae1
2/9/2008 12:08:23 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
703 Views

Similar Articles

[PageSpeed] 15

I don't want to begin to try figure out the logic of this complex expression. 
I have one recommendation: create a small user defined function that accepts 
several fields as arguments and returns the correct value. There is no way 
that I would create or maintain a complex business rule like this in a query 
expression. Move it to a user-defined  function in a module of business rules.

-- 
Duane Hookom
Microsoft Access MVP


"alrae1" wrote:

> I am having trouble with a query which involves multiple parts. It is tied up
> with asset management. In a nutshell there are three components CRC(Current
> Replacement Cost), ACC_Dep (Accumulated depreciation),WDCC (Written down
> current cost). Other commponents include difference between construction date
> and current date, design life of asset.
> When the date difference is equal to the design life the answer is 0 the e.g
> CRC= $64960, WDCC= $4256 (it should read the same as CRC/Design life (in this
> case 15)) and the ACC_dep is $64960 and it should read CRC-WDCC. As a result
> the totalsof all the road assets is out and this only occurs when the date
> difference is equal to the design life.
> This occurs in three differnet areas Pavement (as above) Formation and
> surface coding is the same in all three instances axcept Pav,Frm and Surf are
> used. Provided coding for pavement
> 
> ACC_DepPav: IIf(([qry Areas]![Pavement Value]*((100/[qry Areas]!
> [Pavement_DesignLife])/100))*(DateDiff("yyyy",[BLOCK_DATA]![YEAR],Date()))>
> [qry Areas]![Pavement Area]*[qry Areas]![Pavement_Cost],(([qry Areas]!
> [Pavement Area]*[qry Areas]![Pavement_Cost])-([qry Areas]![Pavement_Salvage]*
> [qry Areas]![Pavement Area])),([qry Areas]![Pavement Value]*((100/[qry Areas]!
> [Pavement_DesignLife])/100))*(DateDiff("yyyy",[BLOCK_DATA]![YEAR],Date())))
> 
> WDCCPav: IIf([qry Areas]![Pavement Value]-([qry Areas]![Pavement Value]*(
> (100/[qry Areas]![Pavement_DesignLife])/100))*(DateDiff("yyyy",[BLOCK_DATA]!
> [YEAR],Date()))<=0,([qry Areas]![Pavement_Salvage]*[qry Areas]![Pavement Area]
> ),[qry Areas]![Pavement Value]-([qry Areas]![Pavement Value]*((100/[qry Areas]
> ![Pavement_DesignLife])/100))*(DateDiff("yyyy",[BLOCK_DATA]![YEAR],Date())))
> 
> CRCpav: ([qry Areas]![Pavement Area]*[qry Areas]!Pavement_Cost)
> 
> 
0
Utf
2/9/2008 4:40:01 AM
Reply:

Similar Artilces:

Is it possible
I would like to know is it possible to make million dollar in the internet, if you have any suggestions, write on my forumhttp://isitpossible.truemillion.comMark...

How do l get the sheet name from the sheet code name?
I am sure this should be pretty simple but so far the answer has eluded me. I am doing a project which involves retrieving data about the vbe. Thanks to Chip Pearson's excellent code examples so far it has gone fine. However l am having trouble retrieving the worksheet name that appears on the worksheet tabs. So using Chip's code l can retrieve the sheet code names, Sheet1, Sheet2 etc and list them as required. Lets say the the tab name that appears to the users is "A" for Sheet1 and "B" for Sheet2 So lets say that l now have Sheet1 in Cell(1,1) of a sh...

Personal Folders in OWA
Hello, I have just enabled OWA on our Exchange 2003 SP2 through ISA Server 2004. Is there any way to access personal folders in OWA? Thanks in advance. If by personal folders you mean .pst files, the answer is NO! Sorry. C C wrote: > Hello, > > I have just enabled OWA on our Exchange 2003 SP2 > through ISA Server 2004. Is there any way to > access personal folders in OWA? > > Thanks in advance. > > "C C" <someone@atsbcglobal.net> wrote: >I have just enabled OWA on our Exchange 2003 SP2 >through ISA Server 2004. Is there any way...

Totally Lost Key Code
Hi. I had my computer fixed because it was acting up and I need to re-installed Publisher 2000. Problem is I can't find the key code. Is there any way to obtain a new one????? Thanks Nanci wrote: > Hi. I had my computer fixed because it was acting up and I need to > re-installed Publisher 2000. Problem is I can't find the key code. > Is there any way to obtain a new one????? > > Thanks Sorry, but no. Publisher is an Office 2000 family product and, as such, all support for it ended nearly a year ago. Please see http://support.microsoft.com/gp/lifeprodp for mor...

Custom Entities and the State/Status Code Values
We created a custom entity called "Service Order" that operates similar to a Case/Incident in that it has several "states": New, In-Process, Completed and Canceled. Each Service Order state has a number of sub-status values. I tried to use the OOTB CRM fields "statecode" and "statuscode" for managing the settings for each one (similar to the way you handle Cases) and I was surprised to find that I could not define any state codes for custom entities. Without a state code, I am left with Active and Inactive. When a Service Order has been complete...

Downloading Statements Directly Not Possible
I have Money 2002 installed on an Win XP Pro SP1 machine. Something happened and I probably did it, that causes the statement downloads not to go directly to the account. Rather they want to be saved in a file and then opened into Money. I then have to delete the download. I have tried several things in order to fix this: 1. I verified the file associations of OFC, OFX were correct. They were. 2. Lowered all my security settings in IE. Even put banks into Trusted site zone. No go. 3. Made new accounts. Didn't help. 4. My IE cache is large (500MB) and regularly cleaned out. 5. I made s...

Code Error
The following is apparently a known problem in access 2003 - an apostrophe in the combo box item causes an error - how is it corrected? Sandy Private Sub Combo32_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Title] = '" & Me![Combo32] & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub You can use a pair of double-quote characters as your string delimiter: rs.FindFirst "[Title] = """ & Me![Combo32] & """...

CListCtrl Setting the Focus to a Row through Code
Hi i am taken a dialog based application. on the dialog i placed CListCtrl.i added 3 cols and 10 row to the list control. the first column is Checkbox. On the dialog box i placed two Buttons with captions "Next" and "Previous". when i run my application, when i Press "Next" Button the selection mark in the CListCtrl should move to the next row.and when i press "Previous" the selection mark in the CListCtrl should move to the previous row. i am using nItem=5; m_ListCtrl.SetItemState( nItem, LVIS_SELECTED|LVIS_FOCUSED, LVIS_SELECTED|LVIS_FOCUSED ); it...

is this possible? reload a form that is based on a query with new query that is defined in the form.
I have a basic form. The form is based on a query, so when I open the form, a pop comes up where I enter in what I want to query. So instead of 30,0000 records, I get 1-10 records. on the form, I have a text box. I want to be able to enter in what I want to query next and afterupdate have the recordset/form reload with that query instead of my original qeury. I figure this has to be done with VB. I am just starting to learn some VB but I am lost. Any info would be great, thank you. It does. You need to apply the new query or table name, or the sql string to the forms recordsource, then...

Possible to detect changes to an XmlDocument object?
If I instantiate an XmlDocument object, the load a file through the Load method, is there a way, later on in execution, to easily determine if the XmlDocument object has changed from the original version loaded? I'd like to be able to check a condition to see if the document in memory has been editted (i.e., nodes added, attributes changed, etc.) so that my user can decide whether or not to commit changes to the disk file before moving to another file. Will I have to resort to re-loading the file, and comparing OuterXml properties? Gabe Gabe Covert wrote: > I'd like t...

Name Conflict Dialog Box???
Excel 2000 ... I have a WorkBook that when I try to copy one of the TabSheets (any of the 3 TabSheets) I get a Pop- up containing the following (very annoying) message. ********************************************************** "A formula or sheet you want to move or copy contains the name 'My_Name' which already exsist on the destination worksheet. Do you want to use this version of the name? To use the name as defined in the destination sheet, Click Yes. To rename the range referred to in the formula or worksheet, Click No, and enter a new name in the Name Conflict Di...

Is it possible to compare a string and a field name
I have the following tables: Membership, Subscriptions, OfficeUseOnly. The Office Table contains a Year, Year To and From [financial year] and the other fields are the 5 membership types (regular, student etc) and these 5 fields contain the different amount each type of member has to pay. I want to create a form whereby on entering the financial year and the membership type the correct amount is automatically inserted into the 'Amount' field: FinancialYear - in current form = OfficeUseOnly SubsFinYear and then if MemberType = 'Student' find Student field ...

Help with listing files using Allen Browne's (modified) code
Hello All i am trying to make a list of the files in a specified directory that match a piece of search critieria. for example i have a type of file, *.pdf, that appears in a bunch of folders but i only want ones that have the word "compressor" in the name of the file. so i modified Allen's code like this: Option Compare Database Option Explicit 'list files to tables 'http://allenbrowne.com/ser-59alt.html Dim gCount As Long ' added by Crystal 'crystal modified parameter specification for strFileSpec by adding default value Public Function ...

pictures in various shapes
Is it possible to insert a picture and make it into a circle or an oval or various other shapes than just squares and rectangles? Thank you ahead of time. with a graphics editor -- Rob Giordano Microsoft MVP - FrontPage "Stephanie" <Stephanie@discussions.microsoft.com> wrote in message news:014D0D06-15F9-4D92-8D93-64FBD3A9CA32@microsoft.com... | Is it possible to insert a picture and make it into a circle or an oval or | various other shapes than just squares and rectangles? Thank you ahead of | time. | | Stephanie wrote: > Is it possible to insert a pict...

Bar code solution
Does anyone have experience with a bar coding solution that would allow warehouse personnel to among other common tasks, process inventory receipts and charge inventory out to job numbers created in a field service package that integrates with GP. Preferrably the solution would work with Small Business Financials and be upgradeable to GP standard or GP professional in the future. You could try talking to Semens. They have a wide range of devices which they or even your IT can prepare an application using VB. "Aftherthot" wrote: > Does anyone have experience with a bar codi...

copy code into all open wkbks
How do I copy a code that I want to use and store it in "all open workbooks', instead of on the open worksheet only You can put it in another workbook and then just make sure that this other workbook is open when you need the code. Lots of people who need this functionality, put their code into a workbook named personal.xls and then save that personal.xls into their XLStart folder. By placing the personal.xls workbook there, it's opened each time you start excel. nkeiru wrote: > > How do I copy a code that I want to use and store it > in "all open workbooks&...

Is this possible
I want to make a list of the files in several different folders. Is it possible to somehow "Copy and paste" just the filenames into a list, in either Word or Excel or Wordperfect. I am just trying to sort the files in several different folders to weed out duplicates. There has got to be an easier way then typing them all or screen printing the lists and manually weeding them out. Any suggestions. Frustrated... Hans, Here is some code that will start in a directory and list all files in that and any sub directories. It uses recursion to go through all the subs. Just ch...

Is this Possible ?
Hi Have a newsletter document with text boxes that have no outlines but internal gradient colour fills. Is it possible that a macro could remove all colour fills in one hit just leaving the text in place ? many thanks Dim oShape As Shape For Each oShape In ActiveDocument.Shapes If oShape.Type = msoTextBox Then If oShape.Line.Visible = msoFalse Then oShape.Fill.Visible = msoFalse End If End If Next oShape should clear the fill from text boxes that are filled and have no border. -- <>>< ><<> ><&l...

Secret Source code ...
Is the left side view of explorer a secret source code or what ? I just would like to integrate the folder tree of the explorer in one of my app. and i couldnt find any class or function to integrate it. I found on the web some samples and some class that use to be 'fakes' and implement their own method to browse folders, like that, impossible to use drag&ndrop methods, right mouse button properties, rename ... Do I really need to implement all those functions ? isnt there an existing class or template ? If all must be rewrite again, why did i bought visual stuio ? ;-) ...

Help with Code #3
A little help, please. I keep getting a "Type Mismatch" error. Column I is formatted as Date. Private Sub Workbook_Open() Dim cell As Range Application.Worksheets(5).Activate Set rngLate = Range("I2:I65535") For Each cell In rngLate If cell.Value <> "" And (cell.Value - Date) <= 2 And cell.Offset(, -1).Value = "" Then MsgBox "Order Due" & " " & cell.Offset(, -7).Value & Chr(10) & cell.Value End If Next cell Application.Worksheets(4).Activate End Sub Make sure that all cel...

Is Batch Marking Possible?
Hello, I am doing a mass mailing from Outlook. I will print 50 address labels per day. Is there a way for me to batch-mark the contacts for which I print a label? Thanks, Talal Itani ...

Is it possible to make cmd window to flash?
Greetings, I have an existing VC2003 windows console program. Now I want to add a alarm feature to it, the simpliest way is to make the cmd window to flash. Can this be implemented? Thanks, Evan >I have an existing VC2003 windows console program. Now I want to add a alarm >feature to it, the simpliest way is to make the cmd window to flash. Can >this be implemented? Evan, If the console is running in a window, I presume you can use the FlashWindow(Ex) API. In newer OS's you can find the console window handle using the GetConsoleWindow API. For older OS's here's...

possible R-NDR attack, not sure, please help
Thanks to all who help and hope to help others with same issue. Since yesterday a user on domain sent a mail out to 10 or so people. Ever since then everyone who was being sent the message including the sender is getting a message like this. Your message did not reach some or all of the intended recipients. Subject: Donald Permenter Sent: 10/21/2005 5:54 AM The following recipient(s) could not be reached: username@aol.com. on 10/21/2005 5:55 AM The format of the e-mail address is incorrect. Check the address, look up the recipient in the Address Book,...

How is possible?
I have performed the regular procedure in order to unistall outlook express. In the windows installed programs look not present but outlook express is still there and working. I cannot understand how this is possible. Would you anybody explain me? Thanks and regards Hello Salicilico, you wrote on Thu, 25 Aug 2005 20:21:32 GMT: > I have performed the regular procedure in order to unistall outlook express. > In the windows installed programs look not present but outlook express is > still there and working. > I cannot understand how this is possible. > Would you anybody expl...

Is it possible to enter Credit Notes in CRM?
Hi, I'm having a problem entering Credit Notes in MS CRM. I'm migrating Credit Notes and Invoices into CRM from our accounts system for Sales History reference. Invoices and Invoice Details are migrating fine, Credits aren't. Invoice Detail lines with a negative quantity are not calculating the Extended Value (or feeding through to the Invoice total value). When I open the Invoice Product screen for these lines an "Invalid Quantity" message appears at the top of the screen, although it does display negative values like '-1'. I've modified the Invoice Produ...