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
1165 Views

Similar Articles

[PageSpeed] 57

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:

hyperlink Problem #5
I am unable to launch a PDF document in word/excel XP/2003 using the Hyperlink facility. Every other type of document launches OK, but not PDF, even from the same folder. Any ideas? Acrobat reader flashes onscreen for fraction of a second, and disappears. Everything else works fine. Opens and stays open for me (Excel 2003). Doesn't seem to be an Excel issue else it wouldn't open at all. -- Jim Rech Excel MVP "Brian Mateer" <Brian Mateer@discussions.microsoft.com> wrote in message news:6C9F7218-86EF-44AF-92A5-2C91C9733C85@microsoft.com... |I am unable to launch ...

Priv.edb Defrag\isinteg Problems
Hi We're currently testing disaster recovery for Ex5.5. I've restored a copy of the priv.edb from a backup. I've ran a defrag, and this has reduced the size of the database from 41gb - to 2.4gb. It seems unlikely to me that it is possible for it to apply that much compression - especially when I know that this DB holds gigabytes worth of attachments alone. This issue could be causing us a problem when we move on to running isinteg. This gets to 100% but then keeps on scanning folders. It had been running for 20hours and was still processing (got upto 5millions folders!). I had re...

Problem opening excel files from email
Has anyone heard of a problem opening up excel files from an email attachment? This problem started happening to me about a week ago - when I receive an excel file by email and I attempt to open it from the email message, the excel program opens but the file does not. So what I have to do is save the file to my desktop and open it from within excel (File menu - then open) Can anyone please help. Thanks Dave -- DXC001 Sometimes one of these works when a similar problem occurs with windows explorer. Tools|Options|General|Ignore other applications (uncheck it) --- or --- Close Excel a...

strange problem
Hi All, I am having a problem emailing from my outlook clients. I will describe my setup and then my problem. I have outlook 2000 clients that connect to an exchange server. These same clients also have an internet email service setup to go and get their own email from the pop3 account on the internet. Previously we would pick up our email from the ISP, but always send using the exchange server because I had setup a smarthost to redirect to. We have recently changed our ISP and we have been told we cannot use the exchange server. so I changed the outgoing mail server on the interne...

Problem using Public Folders
Hello people I'm having a problem with Public Folders in Exchange 2003. From Outlook, I can see the public folders, and from ESM I can also see all the groups on the Storage Group The problems are that I can't see the folders on ESM under the "Folders" tree, inside my Administrative Group, and neither can I see the System folders (it shows the error with an id of c1030af1 - forbidden). This way, no one can see, e.g., each other's availability on Calendar for meetings Can anyone shed a light to me? I've checked some documents on the net and Microsoft's ...

Microsoft Accelerator Eservice problem
Hi all, I don't know whether it is the right place to ask, but I have been pulling my hair for these two days :(. I use crm accelerator eservice. I have created a custom entity. There is no mandatory field in this custom entity except "name" field. I have successfully displayed the form for this custom entity using Eservice EntityEditor complete with its picklist. I just add this code on my on_load method of my aspx page: protected void Page_Load(object sender, EventArgs e) { //check id string id = Request.QueryString["id"]; if (...

Date based query problem
I have an inventory database (in Access 2007) that calculates the Beginning Inventory, # Cards Sold, # Cards Purchased, and Ending Inventory. I have a query that pulls the # Cards Sold, # Cards Purchased, and calculates the Ending Inventory based on the date parameters given to the report ("Enter Beginning Date" and "Enter Ending Date"), but I need to pull the Beginning Inventory information based on all data prior to the "Beginning Date" of the report. For instance... the date parameters are 5/1/07 (Beginning Date) and 5/31/07 (Ending Date), so the Beginn...

Problems compiling a MFC Application in VC++ 6.0
Hi! When I try to compile an MFC Application, I get this error: Compiling resources... C:\Archivos de programa\Microsoft Visual Studio\VC98\MFC\INCLUDE/l.esp\\afxres.rc(1) : fatal error RC1011: compiler limit : '%1!ws!': macro definition too big Error executing rc.exe. IPSMultiProtocol.dll - 1 error(s), 0 warning(s) The MFC Application is the one generated by the wizard, there isn't any code. I've got also installed Visual Studio 2003 and 2005. The install order was: Visual Studio 2003, 2005 and finally Visual Studio 6.0 Any ideas? Thanks! Greetings Don't know i...

UDF in this item; view problem; upgrade from 2003 to 2010
I have Office 2010 beta. I exported my Outlook 2003 to a .pst file, then imported to Outlook 2010 beta. Most everything works well. I would like to view ALL of my old "UDF in this item"s in the view mode. I have created a new view, HOWEVER, it will not show any of my OLD "UDF's in this item." I am able to add any Office pre-assigned drop down items. Is there a way to view my UDF in this item, without going to each individual contact? I was able in 2003 version to sort & view all of them. Did I incorrectly import? You never transfer Outlook da...

Problems using CArchive "<<" operator
Hi, I get this compiler error message for the code below: "error C2678: binary '<<' : no operator defined which takes a left-hand operand of type 'class CArchive' (or there is no acceptable conversion)". Whats wrong, the code is from a book about MFC? void CLogHandler::StoreLog(CLogItem logItem) { unsigned int nType =0,nMsgId=0; CFileException e; CFile fileArchiveFile; if (fileArchiveFile.Open( "C:/test3.txt", CFile::modeReadWrite | CFile::modeCreate| CFile::modeNoTruncate, &e)) { CArchi...

Problem communicating between two DLLs
Hi all, I am communicating using two DLLs, which for simplicity I name as DLL1 and DLL2. I am using gsoap for communication between these two DLLs. It is on windows XP using VC++. I access the handle of gsoap from DLL1 which is allocated in DLL2. After I am done with the handle in DLL1, I again access a method defined in DLL2 from DLL1 and deallocate the handle. I can see that in DLL1 copies of this handle are still maintained and when the program exits, there are memory leaks. I used a memory debugger to check the memory leaks which shows that there is problem with the handle which is creat...

Upgrading to CRM 3.0 Problems
Hello, We're upgrading from CRM 1.2 to 3.0 and are coming across an error we can't get past. After entering all the information it brings up the system requirements screen where it goes through everything to make sure the install will be smooth. Everything checks out except for IIS. The exact error is "The Web site's NTAuthenticationProviders must be Negotiate,NTLM" We've researched the error on the web and found the adminscripts command to make the site Negotiate,NTLM and the get command does show it to be just that but when we restart IIS and start the ins...

Autosave Problem
Hi everybody! Since some time now, everytime I open my Excel (2000) I get a message like this: "AUTOSAVE.XLA is already open. If you open it again you may loose the changes you have made. Do you want to open AUTOSAVE.XLA again?" Does anybody know how to end this? thanks! --- Message posted from http://www.ExcelForum.com/ Sounds as if Autosave is selected under Tools, Add-ins and it is also in a location, like your XLSTART folder, that Excel loads automatically from at startup. If it's in XLSTART delete it. The same goes for the Alternate startup file location specified ...

Problem with DoCmd.Open Form code
Hi part of this was already answered a while ago but I have now found a problem with it that I need to fix, im just unsure what to do. I have a main table that I wanted to be set up so that when using navigation buttons you couldnt click into a new blank record. This was fixed by turning off allow additions to the forms properties. I also needed to be able to add a new record but via a click of a button on another form and for the on click action used..... DoCmd.OpenForm "Jobs", , , , acFormAdd The only problem I have is that once I the form opens up I can save the record and g...

Smartlist Export Problem
I have one little tiny problem with Smartlist. It appears that it depends on the order that I perform the Great Plains Install. When I run a Smartlist and then export to excel I don’t get the progress bar to show me how far along the export is going. On some machines it does but on some machines it doesn’t. What happened is I installed Great Plains, installed the Service Pack, ran Great Plains Utilities, the ran Great Plains, then installed the Service Pack again, then installed the chunk file (addon for Great Plains we have) and then updated the reports.dic file. When I did it tha...

Address problem with Outlook
I switched from Outlook Express (OE) to Outlook2003. Contact address was transferred smoothly from OE to Outlook2003. I see the complete listing of addresses in the Outlook Contacts. But while writing emails under Outlook2003, email address did not show up. It prompted following error: The address list could not be displayed. The Contracts folder associated with this address list could not be opened. ............ Please help me how to handle this address problem. Satoshi Just configure your Address Book: http://support.microsoft.com/default.aspx?scid=kb;en-us;287563&Product=ol20...

Inventory Reconcile/Allocation Problem
Last week I posted a manual Inventory Adjustment to correct the on hand quantity of an item; I wrote off 196 pieces. No problem at the time, bin showed 0 qty, qty available 0, and qty allocated 0. Then I ran an Inventory reconcile on Friday night - now this item has a bin named AUTOCREATE, with a qty of 0, qty available of -196, and qty allocated of 196. Item stock inquiry indicates the same; 0 on hand, (196) available, and 196 allocated. There are no sales orders or manufacturing orders requiring this item. Any ideas why the Reconcile threw this out of balance? Don, Do you use Se...

receive mail problem
Dear All, I have a receive mail problem, please help. background: My company: ABC.com Computer A: Exchange Server 2003, SP2 Computer B: IIS SMTP Gateway Problem: Recently, my colleague reported that they cannot receive mail from A@xyz.com & B@xyz.com. But they can receive mail from them in the past. And other people can received mail from xyz.com except A@xyz.com & B@xyz.com. And A and B said they can send mail to our company without any fail report, so A & B suppose there is no problem and the mail have received to our mailbox. And I want to ask is there any way to check if...

Opening shared worksheet problem
We have a user that has a worksheet in a shared folder on her pc. She shares the folder with two other users in the office. When she opens this one particular spreadsheet, she can see her latest changes for only a couple of seconds and then the spreadsheet reverts to the original. Is there a solution or explanation for this problem? ...

Find and Replace problem
hello all... first... thanks to all who offer their expertise..... this group has been a wonderful asset. My problem.... a few weeks ago I built a spreadsheet whereby I could enter time quickly by simply typing, say, 12.45 and the spreadsheet would automatically replace the . with a : on the fly. I set that find/replace routine up so that I could use the number pad to enter the times on the fly. Now, no matter what spreadsheet I open it will replace the period with a colon. And for the life of me I can't figure out/remember how I set it up or how to make it stop..... aaarrrggghh! ...

Input Mask Problem
So, I'm creating an input mask for my date field short date 99-99-9999 works fine and dandy in try it mode, but when I go to add a test record, it comes back with this message - even though where I type the data it __-__-____ is there to be filled in. the value you entered isn't valid for this field for example, you may have entered text in a numeric field or a number that is larger than the fieldsize setting permits. WTF. thanks. -- Kindly, JH Post the input mask's string from the Input Mask property of the field in the table. -- Ken Snell <MS ACCESS ...

Cube building problem!!
hi All, i have some problem with my ProServer 2003 ProServer 2003 Sp2 SQL 2000 SP4 when i want to build cube manualy the proces start and i get two errors and two warnings in event viewer (when i start processing from analysis menager i don't have any errors but after build i don't see NEWest result in portfolio analyzer): 1. Component: PCSViews File: ViewsDrop.cls Line: -1 Description: <Description><![CDATA[Error Information: Additional Information: An error occured calling CallViewGenerator(projectserver, -1, <ProjectData> <EntProjectName...

2006 language? problem
Using Money 2006 with a stock price history (chart) displayed I click in left column for "Interactive charts on msn money". I get "this page cannot be displayed" with the address of http://de/ the 'de' leads me to believe that it is trying to get to German? sites. My Money 2006 is a boxed version (US) purchased at a Best buy. I am not multilingual (barely single-lingual) so should have never installed 'German' language setting anywhere. Help appreciated. Michael ...

computer problems
i turned my computer on it started to run when it got to the black window display it quickly went blue ,it had some wriying and numbers on but disappeared to fast for me to read it then the screen went blank then after a few seconds it come up on my screen invaid format. i have tried going into safe mode and still nothing i have also tried evrey thing i know and still nothing .can you please help me with my problem thank you very much .my operating software is windows xp On Mar 20, 6:30=A0pm, tahione <tahione.486...@no.email.invalid> wrote: > i turned my computer on it ...

money 2004 ie6 problem
when i try to install money 2004 small business edition, the installation program attempts to install internet explorer 6 which is already installed. it comes up with an error and suggests installing it manually from the cd.when i try to install it manually, it will not install because it says there is a newer version already installed. how can i get round this- can i uninstall ie6, or is there any other way? got it! Copied the cd to my hard drive and it installed no problem, no error messages. "J" <cattj@sasktel.net> wrote in message news:8bac0bf0.0308081606.6a60856@p...