Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2013
    Posts
    22

    Question Unanswered: Need access equivalent to vlookup

    Hi all,

    I am trying to build a data base for the employees in my company to fill in the production hours. Basically they will be using the access sheet just to fill in the data and I will be using excel to pull up the bulk report. I want the employees to be able to just fill in the employee no: and the employee name and the section they work in should automatically appear in the access table. It is pretty easy in excel using a Vlookup function and I heard Dlookup is the access equivalent.However for some reason I cant get my head around it.I am a beginner in MS Access.Please help

    To summarize, Type the emp id , emp name and section should appear automatically in the table.

    Please help

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have you tried?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2013
    Posts
    22

    Need access equivalent to vlookup-this is wat i did

    This is what I have..I have a list of employee ids with the corresponding employee names in 1 table called "EMPLOYEELIST" with Columns "Emp no" and "Name" and the other tables(eg:Bending) with the columns "EmpID", "Name","Section"
    I created a form from the table(Bending) and selected properties of EmpID ,then under 'Events' tab selected 'Event procedure' and wrote the VBA code
    Name=Dlookup("Name","EMPLOYEELIST","Emp no=" & [EmpID]).

    I am pretty sure its all wrong,No idea what im doing

  4. #4
    Join Date
    Dec 2013
    Posts
    22

    This is what I have tried

    I have one table named "EMPLOYEE LIST" with columns "Emp ID","Name", which has all the employees and their IDs. Another table which says "Assembling" which has the following columns: "Date","Emp id","Name","Hours".what I have tried is , I have created a form from the table "Assembling" then in the Events tab I have filled in the following event procedure.
    =dlookup("Name","EMPLOYEELIST","[Emp ID=" & "Emp ID")

    I am pretty sure the criteria is incorrect.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Couple of things; if there really is a space in the Field name

    "Emp no=" & [EmpID]

    needs to be

    "[Emp no]=" & [EmpID]

    I say 'if,' because in one example you have a space and in another you don't.

    Also,

    "[Emp no]=" & Me.[EmpID]

    is only correct syntax if [Emp no] is defined as a Number. If it is defined as Text, it needs to be

    "[Emp no]='" & Me.[EmpID] & "'"

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    same with your table name, if you have used a space then delimit that with square brackets
    [EMPLOYEE LIST].[Emp No]

    AS a heads up the word 'name' is a reserved word, you will probably be OK but in future it will make your life easier NOT to use a reserved word.

    Also computers are pedantic, how you name your tables and columns doesn't really matter that much, but you do have to be consistent EMPLOYEE LIST is not the same as EMPLOYEELIST. fortunately Access ignores capitalisation, not all SQL engines do. Some db engines do not work well with spaces in columns names so I'd suggest you use either
    all lower case with words separated by an underscore
    employee_list
    OR
    CamelCase
    EmployeeList

    theres as many conventions of naming of tables and columns as there are political ones come election time, what ever you do do be consistent. if you are interested do a google on such naming conventions, but if you do make certain its a db naming convention AND not a programmers convention such as reverse polish
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •