Nz & Dlookup problem

I keep getting the #Error in my text box.  What is wrong with this syntax?

=Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")
0
Utf
3/20/2008 9:52:01 PM
access.reports 4434 articles. 0 followers. Follow

6 Replies
996 Views

Similar Articles

[PageSpeed] 33

Check the Dlookup
1. Check if the name are correct
2. If FacID field is a text field and not numeric, add a single quote before 
and after the criteria

DLookUp("[Affiliation]","tblFaculty","[FacID] ='" & [Outside] & "'")

3. In any case, try running the dlookup without the text box using the 
Immidate windows (press Ctrl+g) and then type
?DLookUp("[Affiliation]","tblFaculty","[FacID] 
=EnterHereAValueThatWillReturnData")

Press Enter and see if any value returned or you are getting an error message






-- 
Good Luck
BS"D


"Donna" wrote:

> I keep getting the #Error in my text box.  What is wrong with this syntax?
> 
> =Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")
0
Utf
3/20/2008 11:24:00 PM
It works fine when there is data; but when the field is empty, I get #Error 
in the text box.  It just dawned on me that I "posted" this under Reports 
instead of Forms.  Would the syntax be different on a form?

"Ofer Cohen" wrote:

> Check the Dlookup
> 1. Check if the name are correct
> 2. If FacID field is a text field and not numeric, add a single quote before 
> and after the criteria
> 
> DLookUp("[Affiliation]","tblFaculty","[FacID] ='" & [Outside] & "'")
> 
> 3. In any case, try running the dlookup without the text box using the 
> Immidate windows (press Ctrl+g) and then type
> ?DLookUp("[Affiliation]","tblFaculty","[FacID] 
> =EnterHereAValueThatWillReturnData")
> 
> Press Enter and see if any value returned or you are getting an error message
> 
> 
> 
> 
> 
> 
> -- 
> Good Luck
> BS"D
> 
> 
> "Donna" wrote:
> 
> > I keep getting the #Error in my text box.  What is wrong with this syntax?
> > 
> > =Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")
0
Utf
3/20/2008 11:34:00 PM
Perhaps you could use

IIF(NZ(DLookup("[Affiliation]",TblFaculty","[FacID]=" & [Outside])) =
0,"",DLookup("[Affiliation]",TblFaculty","[FacID]=" & [Outside]))

Are FacID and Outside both number fields?

Evi

"Donna" <Donna@discussions.microsoft.com> wrote in message
news:30A224C2-88EE-4E9F-A987-973388DBCC04@microsoft.com...
> I keep getting the #Error in my text box.  What is wrong with this syntax?
>
> =Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")


0
Evi
3/20/2008 11:35:58 PM
In that case try

=DLookUp("[Affiliation]","tblFaculty","[FacID] =" & Nz([Outside],0))

Assuming that 0 return no value, so the nz will add a value to the criteria, 
other wise when there is no value the dlookup act as

=DLookUp("[Affiliation]","tblFaculty","[FacID] =")

 And that will cause an error


-- 
Good Luck
BS"D


"Donna" wrote:

> I keep getting the #Error in my text box.  What is wrong with this syntax?
> 
> =Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")
0
Utf
3/21/2008 12:14:01 AM
Donna wrote:

>I keep getting the #Error in my text box.  What is wrong with this syntax?
>
>=Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")


Nothing syntactically wrong that I can see.

If FacID is a Text field in its table, then you need to use:

	..., "[FacID] =""" & [Outside] & """")

I seriously doubt that you need to use Nz for this and it is
possible that a result of "" could cause a problem.

-- 
Marsh
MVP [MS Access]
0
Marshall
3/21/2008 12:25:26 AM
No, the syntax for a DLookup is the same whereever it appears.
What sort of data is in the fields in your dlookup? Date? Text? Yes/No tick
box?
Number? Find out by looking at the DataType column in Table Design View of
the table that has these fields.

List them eg
Affiliation - Yes/No


Evi

"Donna" <Donna@discussions.microsoft.com> wrote in message
news:C6DEEA01-A774-44D0-8A03-7913B25D7072@microsoft.com...
> It works fine when there is data; but when the field is empty, I get
#Error
> in the text box.  It just dawned on me that I "posted" this under Reports
> instead of Forms.  Would the syntax be different on a form?
>
> "Ofer Cohen" wrote:
>
> > Check the Dlookup
> > 1. Check if the name are correct
> > 2. If FacID field is a text field and not numeric, add a single quote
before
> > and after the criteria
> >
> > DLookUp("[Affiliation]","tblFaculty","[FacID] ='" & [Outside] & "'")
> >
> > 3. In any case, try running the dlookup without the text box using the
> > Immidate windows (press Ctrl+g) and then type
> > ?DLookUp("[Affiliation]","tblFaculty","[FacID]
> > =EnterHereAValueThatWillReturnData")
> >
> > Press Enter and see if any value returned or you are getting an error
message
> >
> >
> >
> >
> >
> >
> > --
> > Good Luck
> > BS"D
> >
> >
> > "Donna" wrote:
> >
> > > I keep getting the #Error in my text box.  What is wrong with this
syntax?
> > >
> > > =Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")


0
Evi
3/21/2008 9:31:53 AM
Reply:

Similar Artilces:

Recreate VLookup in MS Access 2003 Form Using DLookup.....
PLEASE!!! Don’t reply back and tell me to use DLookup() unless you also provide the coding I need as well. I have been trying for weeks to use DLookup() and cannot code the query properly and I am VERY frustrated. The MS Excel VLOOKUP query I am trying to recreate in MS Access 2003 is as follows: =IF(VLOOKUP(EG2,EH:EI,2,FALSE)=B2,"Good",VLOOKUP(EG2,EH:EI,2,FALSE)) Example "Config No": MTU0301-0010 Example "Concatenated Config No_4" data: 0020000100050001000100010001000200050001000100010003000200030001000200030001000100040004000200010001000...

Accounting Problem. Complex Copy/Paste and Index/Match too.
I have to look at debits and credits spit out by in the form of a database query. For whatever reason, the query results are basically split into quadrants like 1 & 2 on the top and 3 & 4 directly underneath. Data appears only in quadrant 2 (upper right) and 3 (lower left), on a sheet named ‘Sheet1’. For the most part all debits are perfectly offset with matching credits and all credits are perfectly offset with matching debits . . . unless the persons entering the data mistype (and people do make mistakes). There could be a couple hundred entries for instance, let’s ...

Problem (x axis?) incremental
I have values in the first colum from 1895-2008 (for x axis) and values for y axis startign at 1 up to 2500 How can I create a line graph to show this? Thanks (Sounds obv.) If the first column values are not uniformly spaced, you should use an XY chart, not a line chart. An XY chart automatically uses the first column for X values, unless (as I discovered yesterday) the X and Y values all consist of dates. To ensure that Excel correctly parses your data for any chart type, put the X values in the first column, and Y values in subsequent columns. Leave a blank cell above the X values,...

Sum a DLookup
I have a report with grouping based on RepName and it displays each reps totals in [TxtTotals]. On that report I have another text box [TxtRate] with =DLookUp("CommRate","TblCommissionRates",[TxtTotal] & " Between AmountCollectedLow And AmountCollectedHigh") This gets my CommRate for each rep from a (Unrelated table) and this works fine. But I'm trying to get the average rate in the report footer But when i use =Avg([TxtRate]) its looking for a parameter [TxtRate] How can I do this without changing my query? Thanks Arlend The aggregate function...

All kinds of problems
Using P2003, [Iused to use P2000]. I print a 16 to 22 page 4-H newsletter which contains a monthly calendar(taking up one whole page) that I like to isolate and do two things with. In the past I would hit select all for the page I wanted to isolate, and then copy and paste either into a Word document, or as a separate Publisher document. With 2000 I had no difficulty doing either. With 2003, when I try to copy the calendar onto a single Publisher page, it looses all the formats...the font does not copy over nor do the colors. The calendar is filled with various text boxes and images(it is a ...

Problem Installing Office Sounds
Group: When I try to install the Office Sounds for Office 2003, I get an dialog box that says: The feature you are trying to use is on a network resource that is unavailable. Click OK to try again, or enter an alternate path to a folder containing the installation package 'sounds.msi' in the box below. I have done a search of my computer for the sounds.msi file and it doesn't exist. I have also searched for the same file on my Office 2003 CD but could not find it either. I called Microsoft support for Microsoft Office and they could not solve the problem. They told me that it w...

Windows 7 acttivation problem
Hi I have enterprise licence for W7 and I received DVD with W7 pro. I've installed it on PC without being asked for any key. Now I can't activate because W7 searchs for KMS server but I need to use key which I have but it is for MAK, shouldn't hard to do but W7 doesn't let me change key for MAK - any ideas how to activate it or change with success windows key? any help will be very nice. Hi Redav, Is your computer hooked to a network ?maybe a KMS for Windows Server 2008 or Vista is installed, if that is the case,then the KMS in the network is now cached on y...

Problem when removing GetDlgItem
Hello, all. I've been handed an old code base, written using VC++ 1.52C and MFC. I'm trying to clean it up and one of the things I'm trying to do is to get rid of GetDlgItem as Joseph Newcomer advises. Today I ran into a problem. I added controls for two listboxes c_BaselineLB and c_TargetLB. I replaced all the listbox pointers uses in the code and got the following error only when I tried to use AddString() or SetItemData() with the controls. All other uses of the controls are fine. error C2662: 'SetItemData' : cannot convert 'this' pointer from 'const c...

Thanks, group! You fixed my Passport problem.
After a system reinstall I was having the dreaded "sign-in unsuccessful" problem with Passport. A Google search turned up several suggestions, none of which worked until I found Dick Watson's post on Feb 7th which, among other things, suggested re-registering msxml3.dll (regsvr32 msxml3.dll). This fixed me! As to what may have screwed up the registration in the first place, I can only speculate that it may have had something to do with the fact that I installed Visual Studio.NET 2003. Knowing how big VS is on XML, it may have "stolen" some things from the older msxml3....

Printing in Landscape Problems
I an running Excel 2000 on XP Pro and printing with a HP 5L. Everythin prints fine in portrait but when I switch to print in landscape thing get really whacked out. It does not print all of the header row correctly and has thin blan lines through a two of the columns and then prints the header data tha it did not print on a sheet all by itself. If I print the same sheet i portrait it prints fine. The sheet is too wide for portrait and need to be printed in landscape. The sheet I am working on know has 1 columns. When I print it prints the first 9 cells in row one blank. O he second sheet it ...

problem with publisher
I work in an estate agents office and we publish our own property list using microsoft publisher, which is about 6 x A4 pages of detail which has a thumbnail picture of a property with a breif description of text. There can be 50 properties on this list and they are all in price order. When a new property comes on for sale ie: £100,000 I may have to insert it between a property priced at £95,000 & one priced at £105,000. To insert this what I currently have to do is move every single property down approx 5 spaces to be able to insert the new details, moving 49 propert...

Outlook and problem with Norton Symantec
First of all, my apologies if I've missed any relevant posts on this... O/S is W2K. Having recently installed Symantec's Internet Security Professional 2004, I now find that when I open Outlook I get an error message (although no number is visible) to say that "This application has requested the Runtime to terminate it in an unusual way." The heading for the error message is MS Visual C++ Runtime Library. Clicking OK shuts the Outlook window and if I then open it again, no problem and it runs normally. I've tried entering various bits of the message and headin...

deleting a macro resulted in a problem
I'm a beginner with excel macro. I tried to write a macro and soon I found I couldn't finish it so I just determined to delete it to get out. Then when I open the file, a message poped up, reading that there is a macro in this file, you can cancel, start it,blah,blah. As I've deleted the macro and I am sure there is no other macros in this file, I am confused. I tried to cancel and start the macro, it said "module unfound" under both options and the file couldn't be opened. As this file contained some important info for me, can someone offer some help? Thanks, Militan...

Table Ident Problem
Hi all, I have a problem and wondered if anybody can offer me any advice. When copying and pasting entire rows from one table to another I sometimes hit an column alignmnet problem. To explain (I'll be as brief as I can) we use documents to record client info that we call a 'Running Record' (RR), these Running Records are word docs with a table in, the tables have 4 columns and can have any number of rows depending upon the case. To complicate matters we have two versions of the running record in use. both versions have 4 columns, but the widths of these colu...

IMS Problem
Hi, I have a server dialling into a network using a VPN connection. It registers it's internal IP Address 192.168.x.x and can be pinged from other Servers in the VPN but only when the Internet Mail Service is disabled on the RRAS Server. As soon as it is running, ping replies fail. Other servers are connected in the same way and IMS is working fine for them - it's just this one server. Has anyone seen this before? Thanks in advance, Allan. ...

Excel Saving problem
Hi, I am using vb.NET code to create Excel. I am using Office XP professional. .NET framework 1.1 is used. Now when I save any Excel from my .NET code, a short cut (.lnk file) is created for that particular xls file and containing folder. This is really strange problem. I have not coded for creating short cut. Please help me in this regard. Thanks and Regards, Shishir --- Message posted from http://www.ExcelForum.com/ ...

multi-user problem 05-08-07
I am encounter some problem with my access database. Currently I am using Access 2K and I have one mbd file in my shared folder which allow multiple user to access it. But sometimes when other people try to open the file, they might encounter the problem that the file has been place on admin user (pc name) and they couldn't access it. People might open the access database without closing it. Any way to solve about this problems? Or is it something wrong with the setting of shared folder? Thanks I'd reccomend using SQL Server and Access Data Projects <xiaodan86@hotmail.com> w...

Slipstreaming Problem
Hi In the past I have sucessfully slipstreamed windows xp home from the original cd to SP2 and it all works OK. However I have just slipstreamed from the original cd to SP3 and when I use it to reinstall XP it starts up as you expect, loads the usuall instaltion files and goes to reboot. Leaving the cd in the drive it just goes around and does it all again. Take the cd out at this stage it asks for the cd and continues as you would expect. Whats going on? Another question is do you have to slipstream in stages? For example from the original cd to SP2 to SP3 or original cd ...

problem with formatNumber
I�m doing a develop in asp.net, wich i update in CRM with CRMIncident.Update i have problem only in currency field. The browser client can use culture spanish with separator decimal "," in format number not equal to CRM (if it is ".") programing i can read configuration culture of browser client but not culture configured inside the CRM can I read culture inside CRM? the version is CRM 1.2 and vs.net 2003 Help me please... thanks ...

Money 2005 OFX Bank Download Problem
I've scoured this listserve and am still not finding a solution to the problem. I use First Mid-Illinois Bank & Trust and I am no longer able to download their OFX data file into Money 2005. I get some sort of a "corrupted file" error message. I've deleted Temp Internet Files, rebooted the computer, etc. and nothing is working. Is there a fix to the program, or some sequence of commands I need to do to solve this? Could Symantec Anti-Virus also be causing this? Help! ...

Continuous form problem
I have a imgPencil on a continuous form. OnCurrentEvent is set to: If Me.txtActivityType = 22 Then Me.imgPencil.Visible = True Else Me.imgPencil.Visible = False End If The above code works fine on a Single Form. Is there a way to get it work on a continuous form without affecting all the other records? Thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201005/1 No. While it may look as though you've got, say, 20 different occurrences of ...

smtp virtual server setting problem ?
dear MVP if i want to configure a SMTP virtual server to forward unresolved messages to a smart host ? how can do this ? coz my office exchane have a domain can't send a email to a customer , if use ISP smtp server is ok my exchange show some error in "QUEUES " " the connection was droppted due to an smtp protocol event sink " i check all smtp setting , haven't denny this IP & domain ,i already accept that domain to a trusted site and then antivirus also make that domain to "approved sender" pls help me thx There are this articles about your issue...

Calendar server problem
WTF is wrong with the calendar.office.microsoft.com server??? Mid December 2009 things started to go south. Repeated appearance of login dialogue boxes when the automatic updates of my published calendar occurred. Appearance of error dialogue boxes: "The update of "calendar" failed." No one seems to have an answer although many posts indicate that this problem is widespread. Why can't I find any information on this from Microsoft?? If someone from Microsoft sees this post, PLEASE post an explanation on Live ID and Microsoft Online Office webpages!!!...

Outlook signature problem
I have tested below code, and it is work allright in html file, but when i insert to outlook signature, when i click ok, it jump a pop window and require a exe file path, any one can assist me on this ? Really appreciated for any help . Because of code <> doesn't show here, so i replaceed with (). (HTML)(HEAD)(TITLE).:: Track & Trace ::.(/TITLE)(/HEAD) (BODY) (FORM target="_blank" id=Form1 name=Form1 action=http://tt.eculine.net/index.aspx method=post)(INPUT type=hidden value=dDwtMTU2NzI1MDgzNTs7Pq5dcx9lge6d9qfHr7/OUvl7P/3q name=__VIEWSTATE) (TABLE height="1...

Problems with tooltips
I've got a property sheet modal dialog, and I've added tooltips to all the controls, using the CToolTipCtrl. Everything is working great, except combo boxes! I can't get the tooltip to display for a combo box. I suppose I could give AddTool a rect structure, rather than a pointer to the control, but I'm curious why its not working the way it is. Russ Dirks ...