Ms Access Query Dlookup Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more dlookup #error about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges access dlookup examples Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each dlookup multiple criteria other. Join them; it only takes a minute: Sign up Ms access dlookup function error up vote 0 down vote favorite A small question but this is irritating me from 3 hours i tried to use the Dlookup elookup Function in ms access. I have two subforms in main form in and i tried to get some values from the 1st sub form to the second sub form This gave me a #error but i gave a correct syntax. I just checked it with a small input and what ever i gave in the form whether it may be a small dlookup command also its giving me the same error. Confused about whats happening in the forms
Dlookup Error Handling
and please look at the sample dllok up syntax i gave in my forms. =DLookUp("Fertigungsmenge";"Porduction Table";"Fertigungsmenge=200000") This small command is also giving me a #error i want to know the exact syntax we use for forms in ms access. thanks in advance. ms-access ms-access-2010 ms-access-2013 share|improve this question asked Oct 20 '14 at 11:51 user3379433 1711 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote Table names, Field names that have special characters - yes that includes Spaces; should be enclosed in square brackets. So try. = DLookUp("Fertigungsmenge";"[Porduction Table]";"Fertigungsmenge = 200000") Also, the DLookup will fail if there is no value found for the criteria. It will return a Null instead. So it would be best you wrap the DLookup with a Nz() function. Finally, the separator normally is a comma (',') a very few countries/regions have the semi-colon (';') as the separator. So give that one final check. PS: To refer Forms and SubForm controls refer to this matrix : http://access.mvps.org/access/forms/frm0031.htm share|improve this answer answered Oct 20 '14 at 12:09 PaulFrancis 4,8511827 Thank you for your quick response paul as i satay in germany the semi-colon syntax is correct but i just want to say one thing iam not searching for a unique value the value i want to lookup is a sum and group by value does that make any diff
be down. Please try the request again. Your cache administrator is webmaster. Generated Thu, 20 Oct 2016 18:56:34 GMT by s_wx1085 (squid/3.5.20)
companies, you will have a Company table that contains all the company's details including a CompanyID field, and a Contract table that stores just the CompanyID http://allenbrowne.com/casu-07.html to look up those details. Sometimes you can base your form or report on a query that contains all the additional tables. Other times, DLookup() will be a life-saver. DLookup() expects you to give it three things inside the brackets. Think of them as: Look up the _____ field, from the _____ table, where the record is _____ Each of these must go in quotes, dlookup error separated by commas. You must also use square brackets around the table or field names if the names contain odd characters (spaces, #, etc) or start with a number. This is probably easiest to follow with some examples: you have a CompanyID such as 874, and want to print the company name on a report; you have Category such as "C", and need to show what ms access query this category means. you have StudentID such as "JoneFr", and need the student?s full name on a form. Example 1: Look up the CompanyName field from table Company, where CompanyID = 874. This translates to: =DLookup("CompanyName", "Company", "CompanyID = 874") You don't want Company 874 printed for every record! Use an ampersand (&) to concatenate the current value in the CompanyID field of your report to the "Company = " criteria: =DLookup("CompanyName", "Company", "CompanyID = " & [CompanyID]) If the CompanyID is null (as it might be at a new record), the 3rd agumenent will be incomplete, so the entire expression yields #Error. To avoid that use Nz() to supply a value for when the field is null: =DLookup("CompanyName", "Company", "CompanyID = " & Nz([CompanyID],0)) Example 2: The example above is correct if CompanyID is a number. But if the field is text, Access expects quote marks around it. In our second example, we look up the CategoryName field in table Cat, where Category = 'C'. This means the DLookup becomes: =DLookup("CategoryName", "Cat", "Category = 'C'") Single quotes within the double quotes is one way to do quotes within quotes. But again, we don't want