How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask nyprogrammer Your Own Question

nyprogrammer
nyprogrammer, Engineer
Category: Microsoft Office
Satisfied Customers: 615
Experience:  Engineer with 13+ years experience in Microsoft Office, especially VBA scripting and application integration.
Type Your Microsoft Office Question Here...
nyprogrammer is online now
A new question is answered every 9 seconds

I am using the DLookup function and received an unknown error

Resolved Question:

I am using the DLookup function and received an unknown error - #Type!. My formule is as follows =DLookup("Fat %age", Raw Material Data", "[RM Input 1] =" &[Forms]![Raw Material Data]![RM Product Code]). HELP!!
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  Richard replied 2 years ago.

Professional Help :

Hi there and welcome

Professional Help :

Am I able to look at the file?

Professional Help :

you can upload it to www.wikisend.com

Professional Help :

and provide me the download link

Customer:

I have never used wikisend?? Is there another method to send it to you?

Professional Help :

thanks go it

Professional Help :

where is the function located?

Customer:

Formula Table Form - if you go to Design View you will see it.

Professional Help :

ok, looking now

Professional Help :

ok, can you tell me what you want to show there please

Customer:

Yes - I would like it to return the "Fat %age" from the Raw Material Data table based upon the RM Product Code entered in RM Input 1 - once you solve that for me - I can do RM Input 2 through 8

Professional Help :

ok, something is causing the form to crash, its not the formulars

Professional Help :

do you have code written anywhere in the forms?

Professional Help :

ok, believe I found the problem

Professional Help :

open the database, then click on the top left Office Button

Professional Help :

Choose Manage, then Compact and Repair Database

Professional Help :

once done, test please and let me know if you still have the problem

Professional Help :

how did it go?

Customer:

Now I get the error #Name!

Professional Help :

can you tell me what that field should be displaying and how?

Professional Help :

The one returning the #Name

Customer:

Yes, when you select a raw material on the Formula Table form [RM Input 1] - the field showing #Name! should be returning the value for the corresponding field [Fat %age] for that raw material selected in [RM Input 1] from the Raw Material Data Table

Customer:

For example - if I type in (or select) 80060 for RM Input 1, the Fat Percentage should be 22% - see 80060 in the Raw Material data Table.

Professional Help :

ok, test it using this lookup please

Professional Help :

=DLookUp("Fat %age","Raw Material Data","[RM Product] =" & [Forms]![Formula Table]![RM Input 1])

Customer:

No - that provides #Error! - do you still have the file??

Professional Help :

can you send it to me again pelase

Customer:

http://wikisend.com/download/574526/Database2.accdb

Professional Help :

ok

Professional Help :

i see you did RM Product Code instead of RM Product as i typed

Professional Help :

can you copy my formula and paste it exactly as provided please

Professional Help :

=DLookUp("Fat %age","Raw Material Data","[RM Product] =" & [Forms]![Formula Table]![RM Input 1])

Customer:

I did - both returned #Error!

Professional Help :

ok, I am not sure why this is not working

Professional Help :

I will opt out for another expert that may be able to resolve this for you

Customer:

OK - thank you - what do I need to do??

Expert:  nyprogrammer replied 2 years ago.
Hello, my name isXXXXX you for your patience. Here is the formula for the first one:
=DLookUp("[Fat %age]","Raw Material Data","[ID] =" & "0" & [Forms]![Formula Table]![RM Input 1])

Please click here to download an updated example with all six boxes populated.
Customer: replied 2 years ago.
Thank you so much - I am going to Accept so that you get credit - but might I be so bold to ask why it is [ID] and not [RM Product Code] and why is the "0" necessary - thanks!!!!!!!!!!!!!!!!!!!!!!!!
Expert:  nyprogrammer replied 2 years ago.
Hello, thank you for the update. Sure I will be happy to explain those two items.

It is [ID] because the actual value of RM Input 1 is the ID. The value displayed in RM Input 1 is the RM Product, but that is because the value displayed is based on a query, taking the ID and looking up the Product.

The "0" & just makes it not display #Error if nothing is selected in RM Input 1. When nothing is selected, the value is Null, which will make the DLookup return #Error. So appending a "0" on the front makes it be 0 instead of null, so that no error occurs (but also nothing is returned). When it is a valid number such as 1, then it becomes 01, which still looks up 1 in the database. It's just a trick to avoid #Error.

If I answered your question, please click the green 'accept' button my answer.
nyprogrammer, Engineer
Category: Microsoft Office
Satisfied Customers: 615
Experience: Engineer with 13+ years experience in Microsoft Office, especially VBA scripting and application integration.
nyprogrammer and 7 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.
OK - so I just started trying to populate the Formula Table via the Form and it is not working - I am assuming I need to pay another fee - but perhaps not. You see - there is a recipe for every item listed in the Minced Product Data Table made up of the Items from the Raw Material Data Table. I had assumed if I create the Formula Data Form that each time I select an Item from the Minced Product Data Table on the Formula Data Form it will create a new record in the Formula Data Table. Let me know - I can Accept and then re-post this question - but I would like ot stay with you as an expert.
Expert:  nyprogrammer replied 2 years ago.
Hello, thank you for accepting the answer. I can still assist (just don't open a new question at least not yet). Let me read what you posted above and see what we need to do...one moment please.
Customer: replied 2 years ago.
THANKS!
Expert:  nyprogrammer replied 2 years ago.
Hello, thank you for the update. I think I see what you want to do.

If that form is specifically for adding records, I could add a button at the bottom that says 'Add', and then inserts the data into the Formula Table, and could do that under this question you have already accepted without needing a new question.

If you want the form to be able to add, edit, and delete records, there would be a bit of redesign needed that would probably require a new question.
Customer: replied 2 years ago.
I do not want an edit or delete - just an Add button.
Expert:  nyprogrammer replied 2 years ago.
Hello, thank you for the update. I will take a look at adding that button. I see some values on the form that I don't see columns for in the table, should those columns be added to the table?
Customer: replied 2 years ago.
No - here was my thinking - and if I need to pay more then let me know. We have Finished goods, raw material and formulas. I did not see a need to repeat all of the information for Finished Goods and Raw Materials - just Product Codes. The Form will ultimately include some more calculated fields for total fat percentage, costs, etc. Then I was hoping if we modify anything in the formula, we could simply create a new record. But each time we pulled a record up, we would see current cost, budget, projected cost, etc. these costs will come from another Table not yet created.
Expert:  nyprogrammer replied 2 years ago.
Hello, thank you for the update. The way this type of database would normally be set up is with the Raw Materials and Finished Goods tables as they are. But the Formula table would have only the ID #'s from the Raw Materials and Finished Goods tables in it. So for example, Product ID 1 might have Raw Materials 2, 8, 12, 18 in it. The Formula table might include the quantity of the ingredient and any other formula specific information.
Customer: replied 2 years ago.
Thanks!!
Expert:  nyprogrammer replied 2 years ago.
Hello, thank you for the update. Which route did you want to pursue? I can add the button as described earlier under what you already accepted.

Or for a separate accept, I can make the required modifications to have the formula table contain just the id's from raw materials and finished goods. Then the formula could contain an unlimited number of ingredients (it wouldn't have to be limited to 8). Also if you make reports and other database enhancements later, it would be easier to work with the tables that way. If you foresee wanting quite a bit of work done on this database as it progresses, I believe this site offers a subscription plan for unlimited questions.
Customer: replied 2 years ago.
I actualy added the button myself. But yes - once our first round of design is done - we may want to get unlimited to enhance it overall.

Thak you again!!
Expert:  nyprogrammer replied 2 years ago.
Excellent, I'm glad you got the button added! If you need any other assistance with it, or after you get unlimited and need the other enhancements, please bookmark this link (CTRL+D) to request me in the programming category and put *For NyProg* in your question title. Thank you.

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional