Conditional null value

Wondering if anyone can suggest a way to simplify the following code, which
I’m using in the OnFormat event of a report.

        If Not IsNull(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me!
[PtAcct#])) Then
        Me![Field192] = Replace(DLookup("[ICD2]", "EntryICDs", "[EntryNum] =
" & Me![PtAcct#]), ".", "  ")
        End If
        If Not IsNull(DLookup("[ICD3]", "EntryICDs", "[EntryNum] = " & Me!
[PtAcct#])) Then
        Me![Field194] = Replace(DLookup("[ICD3]", "EntryICDs", "[EntryNum] =
" & Me![PtAcct#]), ".", "  ")
        End If
        If Not IsNull(DLookup("[ICD4]", "EntryICDs", "[EntryNum] = " & Me!
[PtAcct#])) Then
        Me![Field196] = Replace(DLookup("[ICD4]", "EntryICDs", "[EntryNum] =
" & Me![PtAcct#]), ".", "  ")
        End If


If I use the following code line alone:
        Me![Field192] = Replace(DLookup("[ICD2]", "EntryICDs", "[EntryNum] =
" & Me![PtAcct#]), ".", "  ")
I the Replace function yields an errrmsg if the DLookup function is null
Is there any way to use an 'if isnull' function or an IIf function to
accomplish the If---Then clauses that I have included, so that the coding is
neater?

TIA, Richard

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200706/1

0
atlantis43
6/25/2007 9:23:08 PM
access.reports 4434 articles. 0 followers. Follow

1 Replies
1024 Views

Similar Articles

[PageSpeed] 51

atlantis43 via AccessMonster.com wrote:

>Wondering if anyone can suggest a way to simplify the following code, which
>I�m using in the OnFormat event of a report.
>
>        If Not IsNull(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me!
>[PtAcct#])) Then
>        Me![Field192] = Replace(DLookup("[ICD2]", "EntryICDs", "[EntryNum] =
>" & Me![PtAcct#]), ".", "  ")
>        End If
>        If Not IsNull(DLookup("[ICD3]", "EntryICDs", "[EntryNum] = " & Me!
>[PtAcct#])) Then
>        Me![Field194] = Replace(DLookup("[ICD3]", "EntryICDs", "[EntryNum] =
>" & Me![PtAcct#]), ".", "  ")
>        End If
>        If Not IsNull(DLookup("[ICD4]", "EntryICDs", "[EntryNum] = " & Me!
>[PtAcct#])) Then
>        Me![Field196] = Replace(DLookup("[ICD4]", "EntryICDs", "[EntryNum] =
>" & Me![PtAcct#]), ".", "  ")
>        End If
>
>
>If I use the following code line alone:
>        Me![Field192] = Replace(DLookup("[ICD2]", "EntryICDs", "[EntryNum] =
>" & Me![PtAcct#]), ".", "  ")
>I the Replace function yields an errrmsg if the DLookup function is null
>Is there any way to use an 'if isnull' function or an IIf function to
>accomplish the If---Then clauses that I have included, so that the coding is
>neater?

I think you can avoid the whole thing if you chabge the
report's record source to a query the Joins the EntryICDs
table and includes the EntryICDs fields ICD2, ICD3 and ICD4.

The text boxes can then do the replace in the control source
expression:

	=Replace(ICD2, ".", "  ")

-- 
Marsh
MVP [MS Access]
0
Marshall
6/25/2007 9:52:08 PM
Reply:

Similar Artilces:

Formula to total items from a drop list with values in several col
I am trying to create a formula for the following: Have spreadsheet locate all columns matching "name" and total those columns total and provide grand total, please see example listed below Column A Column D Column G Column H Dog 10 35 Cat 15 10 Mouse 19 48 Cat 10 2 7 Cat 15 1 19 (All other colomns have various other information listed) Grand Totals: Dog = ? (answer: 45) Cat = ? (answer: 79) Mouse = ? (answer: ...

why the content is NULL that I read file??
to ALL: void GetOfforLenth(CFile &file, const CString *str, int index,LONG &offset,LONG &lenth) { int nstart,nend; CString pfile; file.Read(&pfile,file.GetLength()); ///why the pfile is null offset=nstart=pfile.Find(str[index]); nend=pfile.Find(str[index+1]); pfile=pfile.Mid(nstart,nend-nstart); lenth=pfile.GetLength(); } it is appreciated for anyone reply hi, Shldnt the code be something like this ? CString pFile = *str; You never seemed to set the value to the pFile. hth gangadhar terrcy wrote: > to ALL: > void GetOfforLenth(CFile &file, const CStrin...

How do I get scalar value of a child stored procedure?
Hi, I'm trying to execute multiple stored procedures from a parent storedproc. One of the child stored procedures returns a value which I need for the next child stored procedure I need to execute in the parent stored procedure. I used the following syntax but looks like it's not the correct one. Could someone help me with the correct syntax? -- Execute child sp and get UserID DECLARE @UserID uniqueidentifier SET @UserID = EXEC spNewUserEntry @FirstName = 'John', @LastName = 'Smith' I appreciate your help with this. -- Thanks, Sam "Sam&qu...

Today Conditional Format
I have a column which calculated a date based on 3 working days from a date input into a diff column. What I want to do I have the calculated date appear bold if that date has passed based on the current date. For example if I input 01/08/11 the next column calculates 04/08/11 (3 working days). I want the caluclated date to appear bold when I open the spreadsheet on the 05/08/11 ro show the calculated date has passed. I can use conditional formatting but can't get the syntax right Regards Andy Win XP Pro Office 2010 Andy Roberts presented the following explanation : > I ha...

Two Condition Vlookup?
I am currently trying to create a function that searches through an array for two exact column values which allows me to retrieve a third column value. For example: Type of Fruit Date Packaged Amount of Fruit Apples June 100 Oranges June 50 Apples July 75 Grapes July 50 In this example, I would be looking to retrieve the Amount of Fruit (Apples) that was packaged in July. I would be putt...

Getting value from edit control?
How do I get the value from an edit control into its value CString variable? Also, how do I convert from CString to char* ? CString has an overload for LPCTSTR (which depends on whether you are compiled for Unicode or MBCS) so you can pass it pretty easily. If you need to access the buffer directly CString::GetBuffer() returns the pointer to the buffer. Call ReleaseBuffer() when you are done using that point (if you modify it). To assign and retrieve values with the edit control you should create a variable for the control and use UpdateData(). Open the dialog and right click on ...

Query condition by date range
I'm trying to create a query to base a report from. I need it to return records within a date range that needs to be specified, as in specifying the beginning date and having the current date as the end date. A field is included in the query that has listed dates in the format mm/dd/yyyy. How would I write the criteria? I have part of it ready - [Please enter starting date:] . I know that's how you get the little question window. On Tue, 4 Dec 2007 14:51:00 -0800, silva wrote: > I'm trying to create a query to base a report from. I need it to return > records within a...

Need help with conditional formula (Excel 97)
Can anyone give me the correct syntax for the following formula: if A1 is blank, and B1 is more than zero, then display B1, otherwise display nothing (blank cell) Thank you JD =IF(AND(A1="",B1>0),B1,"") Success! -- met vriendelijke groetjes "Jake D" <JakeD@djhtend.com> schreef in bericht news:crkto4lv30u7uujv27ek8okr0us5t9oj10@4ax.com... > Can anyone give me the correct syntax for the following formula: > > if A1 is blank, and B1 is more than zero, then display B1, otherwise > display nothing (blank cell) > > Thank you > > ...

Conditional formatting if condition of an if statement is true and a string value is displayed
Dear Experts: I got a nested 'IF'-Formula in an excel cell that returns nothing if the condition is false. I would like to conditionally format this cell with grey shading if the condition is true and a string value is displayed. How can this be achieved using excel conditional formating functionality ? Help is much appreciated. Thank you very much in advance. Regards, Andreas Let's say your doing this conditional formatting in cell K17. In conditional formatting (xl2003) choose the FormulaIs: option and enter: =$K$17<>"" choose your formatt...

Convert text to time value
I have a series of time values in a 'General' format. They are of the type: 184525 Which is 18:45:25 or 6:45:25 pm. A time which is am would be of the type: 12345 Which is 1:23:45 am. Is there a way to convert those 'General' values to an Excel serial so that I can figure out the difference between two times? I've seen a bunch of examples on the net, but none of those that I have found deal with this format that I can tell. Thanks. Hi try =--TEXT(A1,"00:00:00") -- Regards Frank Kabel Frankfurt, Germany "Andy" <amelton@gmail.com> schrieb...

Conditional Field in Queries
Is there a way to make a field in a query conditional so that it only displays the value if the value is equal to something specifically set? Thanks. Nick Nick.Korynski@gmail.com wrote: > Is there a way to make a field in a query conditional so that it only > displays the value if the value is equal to something specifically > set? Thanks. > > Nick Explain "the value is equal to something specifically set". -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ...

Relative, absolute and mixed values
How do I set a default for relative, absolute or mixed values so that I don't have to toggle through the F4 function each time? ...

Combobox value populate cell selection
I am looking for button code to have a selected cell range merged and populated with value chosen from combobox. This value is centered in the merged cell selection. The cells range is defined manually with mouse. Bart Have a look at this event code which you can refine. No error-checking for data in the selected range...........assumes the mergerange is empty when selected. Runs when a value is selected from Combobox1 Private Sub ComboBox1_Change() Set srng = Application.InputBox(prompt:= _ "Select A Range", Type:=8) With srng .HorizontalAlignment = xlCen...

How do i use Conditional formatting for 3+ conditions ?
I have a text box in a report which highlights in colour current members in a list of past and present member names. I have six types of current members that I want to highlight, but with conditional formatting I can only use 3 conditions in the members [Type] field. I have tried [Type]="Full member" Or "Life member" but the "Or" doesn't work ! Is there a way to do this ? thanks .. Roger CF is limited to 3 contitions, but you can use OR in those expressions. If you set Condition1 to Expression, you can use: ([Type] = "Full member") Or (...

javascript + onchange + delete value from lookup
Hello, I would like to know if it is possible to delete a value in a lookup through javascript. Following scenario: On the contact form I have a picklist with several values and a lookupfield for the accounts. Now I want, when a certain value is selected in that picklist, that the value in the lookup of the account is deleted. Is this possilbe to do with javascript in an onchange event? thank you in advance. In the onchange event, when your picklistvalue is selected, simply put the following line of code : crmForm.all.parentcustomerid.DataValue = null; This will delete the value f...

Help with Conditional formatting with Dates
Hi there, I have a spreadsheet in which I have to monitor various dates which are in different columns (i.e. (1) Date Tender Released, (2) Date Company Selected, (3) Date Draft Contract Forwarded, (4) Date Contract Signed etc...). Would if be possible to Conditional Format and have diffirent ROWs highlighted for each individual Contracts (I have 300 running contracts running) every time a date is filled in these different columns: Initial Step (1) = Yellow (Call for Tender released) Step (2) = Blue (Company Selected) Step (3) = Brown (Contract under Process) Step (4) = Green (Contract Si...

Conditional null value
Wondering if anyone can suggest a way to simplify the following code, which I’m using in the OnFormat event of a report. If Not IsNull(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me! [PtAcct#])) Then Me![Field192] = Replace(DLookup("[ICD2]", "EntryICDs", "[EntryNum] = " & Me![PtAcct#]), ".", " ") End If If Not IsNull(DLookup("[ICD3]", "EntryICDs", "[EntryNum] = " & Me! [PtAcct#])) Then Me![Field194] = Replace(DLookup("[ICD...

Help
Forgive me if "Conditional Sums" is not the accurate word for what I am trying to accomplish... I'm at a loss for what to call this situation. What I have is a spreadsheet totaling attendance figures for classes taught by two people (Dave and Cary). Column A lists their name, and next to it in Column B is the number of people who attended that class. What I need is a formula that will look at the sheet and report in a given cell the total for attendance for each person. I can work basic formulas like SUM, but that will give the total for the entire column, when what I n...

outputting values to a range from one formula
I wish to generate a table automatically by means of a single formula that applies an iteration on a starting given value with a given step, and the computed values are posted/entered automtically into cells from a given cell onwards, say below it, until the computed value reaches a certain given limt. This is somehow the inverse of INDIRECT or of OFFSET. These can pull values from a variable addresses of cells. What I need is to push values into a variable addresses of cells. Can anyone help me on that?? Thanks. :confused -- Shafe ---------------------------------------------------------...

vlook on different tabs based on value
good day, i have the following so far in my macro: .... supcode = Range("B4").Value .... If supcode = "CLI" Then ActiveCell.Offset(0, 4) = "=VLOOKUP(RC[-4], '[Order Generator.xlsm]CLI'!C2:C4,2,FALSE)" .... right now i have this code repeated several times for each supcode that may exist and i am usingn an if statement for each possible "supcode". i would like to replace all the if statements, and change the "CLI" to use the "supcode" value. how do i get the "supcode" value int...

Macro deleting rows including some valu/ewpression
Hey guys In some of the cells in column A - Range("A2:A") - there are expressions including the frace/part: "\old\". I want to remove all rows including this frace and keep the remaining ones. The nearest I can get is something like this, but (of cause) my attempt was not very successful. (Yeah - I know I stole/borrowed it from someone else and tryed to ajust it ...forgive me) Sub Delete_OLD_rows() Dim cell As Range Dim delRange As Range For Each cell In Range("A2:B" & Range("A" & Rows.Count).End (xl...

XML Receipt and Conditions
I wanted to make a small adjustment to the receipt when a certain condition is met. If a particular word (eg. family) is typed into the comment on an item I wanted something slightly different to happen to the receipt I've just about got it, just stuck on the condition tag <IF> <CONDITION>len(Entry.Comment)</CONDITION> <THEN> I can get it using len but this makes it happen for any word, is there anyway I can make it so that it is more specific ie Entry.Comment contains '%family%' try <IF> <CONDITION>Entry.Comment like '%family%'&...

conditionally restricting values in excel
Hello, I want to restrict values in a particular column based on the value in other column. For eg. if column A has value int then column B should be restricted to values 1,2,3,4,5 if column A has value bool then column B should be restricted to values true and false. if column A has value string I dont want any validation to be done and the list box should not appear. Is this possible..... How do I achieve this ? Can I use macros to do this? How? Thanks Hi as a starting point: http://www.contextures.com/xlDataVal02.html >-----Original Message----- >Hello, >I want to restrict ...

Copy values from Sheet1 to Sheet2
I am no guru when it comes to Excel, so I was wondering if someone could please help. I need a formula that will copy values from sheet1 into sheet2, but then I want to clear the values in sheet1 & still keep the values in sheet2. Sheet1 has my weekly figures on it and sheet 2 will calculate those figures to give me a monthly figure. You can just link them, copy them, select where you want them on the other sheet and do edit>paste special and select paste link, then while still selected copy the pasted values and do edit>paste special as values, that will keep the values but remo...

how do i get the value of all my inventory at cost and sell price
how do i see what is my cost on all my inventory. thanks Ali, SO Manager | Reports | Items | Item Value List | first line - Extended cost column -- = "ali" <ali@discussions.microsoft.com> wrote in message news:DD0E79EF-5AEA-4881-816D-21FF78E97744@microsoft.com... > how do i see what is my cost on all my inventory. > thanks ...