Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Join Date
    Oct 2007
    Posts
    22

    Unanswered: Dlookup does not work

    HI,

    I have a table CoGroups, which two text fields. One field is called "Code" and the other one is called "Country".
    I have a query which has several fields. But I would like to look up the field, which is called "Company No 1" and has only numbers in it. Each number represents a country.

    Field: DLookUp("Country","CO Groups Query"," 'Company No 1 = 00002' ")

    Since every field is stored as text, I am using single quotation marks. I only get the first code in the table. It does not look up any value.
    In the code above I was just trying to look up the code with 00002, which is definetly in the query. How should I proceed??

    Many thanks for the help.

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Use this as an example

    Dim x
    x = "00002"
    x = DLookup("[Country]", "[CO Groups Query]", "[Company No 1] = " & "'" & x & "'")
    MsgBox x
    Dale Houston, TX

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Field: DLookUp("[Country]","[CO Groups Query]","[CompanyFieldName] = '00002' ")
    or
    dim SomeVariable as string
    SomeVariable = "00002"
    Field: DLookUp("[Country]","[CO Groups Query]","[CompanyFieldName] = ' " & SomeVariable & " ' ")
    or
    Field: DLookUp("[Country]","[CO Groups Query]","[CompanyFieldName] = " " " & SomeVariable & " " " ")
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Oct 2007
    Posts
    22

    hey

    Hi,

    thanks for the message. I just tried it out.
    It should work without a message box. When running the query, the new field with the dlook up function should show the corresponding country name.
    Since this database is connected to another database and retrieves new data, it can't be store otherwise.

    Any suggestions??

  5. #5
    Join Date
    Oct 2007
    Posts
    22
    Hi again,

    the 00002 was jsut an example. The code should not just look up this value.

    I will copy paste an example.

    This is the Co Groups Query


    Code Country
    00001 aa
    00001 ag
    00002 UK
    00003 IRE
    00004 FR
    00005 y
    00005 ad
    00005 ae
    00006 NL
    00007 DE
    00008 a
    00009 IT
    00010 AT
    00011 JP
    00012 VFIL
    00013 VDHBV
    00014 VDHL
    00015 GLOBAL
    00016 IIC
    00017 b
    00018 z
    00019 ODVHBV
    00020 e
    00020 af
    00021 d



    Company No 1 Country
    00002 ?
    00003
    00022
    00034
    00034
    00034
    00034
    00034
    00034
    00034
    00034
    00034
    00034
    00034
    00034
    00034
    00044
    00060
    00081
    00086
    00107


    The new field should look up the country name in the first query. How can I achieve this?? Pleaseeeeeeeeeeeee helppppppppppppppp

  6. #6
    Join Date
    Oct 2007
    Posts
    22

    Dim??

    I don't know how to use DIM. Please consider that I am not very good in sql programming.

  7. #7
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Question

    In order to lookup the correct value - dlookup needs the criteria (ie 0002) where does this information come from.?

    is the value (0002) on a form?
    Dale Houston, TX

  8. #8
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Debbie - use dim just like we gave it to you - what dim means is "I am going to create a variable for a value I do not yet know the value - but I may know the type of data it is"

    Dim myval as string

    dim x - means - I may not know what type of data it is (variant) but still return the value

    dim x

    does that help?
    Dale Houston, TX

  9. #9
    Join Date
    Oct 2007
    Posts
    22
    Each row has a different code. I tried the above solution, but I get the first country for the rest of the query. How can this be resolved?

  10. #10
    Join Date
    Oct 2007
    Posts
    22
    Quote Originally Posted by axsprog
    In order to lookup the correct value - dlookup needs the criteria (ie 0002) where does this information come from.?

    is the value (0002) on a form?
    Thanks so much for your response. 0002 was just an example to see if the criteria works.
    Each row may have a different code. I tried the solution, but then get UK, which is the country for the code 0002 for all the other rows as well. I need to look up each row on the query.

    thanks a bunch

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you're using the dlookup in the query, simply change the SomeVariable to the field name....
    Field: DLookUp("[Country]","[CO Groups Query]","[CompanyFieldName] = ' " & SomeVariable & " ' ")
    to
    Field: DLookUp("[Country]","[CO Groups Query]","[CompanyFieldName] = ' " & [myFieldNametomatchtoCompanyFieldName] & " ' ")
    if on a form...
    Field: DLookUp("[Country]","[CO Groups Query]","[CompanyFieldName] = ' " & Forms!MyFormName![myFieldNametomatchtoCompanyFieldName] & " ' ")
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Oct 2007
    Posts
    22
    Quote Originally Posted by pkstormy
    If you're using the dlookup in the query, simply change the SomeVariable to the field name....
    Field: DLookUp("[Country]","[CO Groups Query]","[CompanyFieldName] = ' " & SomeVariable & " ' ")
    to
    Field: DLookUp("[Country]","[CO Groups Query]","[CompanyFieldName] = ' " & [myFieldNametomatchtoCompanyFieldName] & " ' ")
    if on a form...
    Field: DLookUp("[Country]","[CO Groups Query]","[CompanyFieldName] = ' " & Forms!MyFormName![myFieldNametomatchtoCompanyFieldName] & " ' ")

    [myFieldNametomatchtoCompanyFieldName]

    What do I put in here???????????//

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Dim is used for declaring variables, which you use in VBA.
    Sounds like you're simply using a query, which is written in Structured Query Language, so our approach is different.

    With the data you provided above, what are you trying to achieve?
    Perhaps you should provide what result you should expect too
    George
    Home | Blog

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try this, it's a bit of a stab in the dark because I'm still not sure what your schema looks like.
    Code:
    SELECT t1.[Company No 1]
         , t2.Country
    FROM table1 As [t1]
     LEFT
      JOIN table2 As [t2]
        ON t1.[Company No 1] = t2.code
    Replace "table1" and "table2" with your relevant tables/queries. Table1 is the one that contains the field [company no 1] and table2 contains the country names against their respective code.
    George
    Home | Blog

  15. #15
    Join Date
    Oct 2007
    Posts
    22
    Code Country
    00001 aa
    00001 ag
    00002 UK
    00003 IRE
    00004 FR
    00005 y
    00005 ad
    00005 ae
    00006 NL
    00007 DE
    00008 a
    00009 IT
    00010 AT
    00011 JP
    00012 VFIL
    00013 VDHBV
    00014 VDHL
    00015 GLOBAL
    00016 IIC
    00017 b
    00018 z
    00019 ODVHBV
    00020 e
    00020 af
    00021 d



    Company No 1 Country
    00002 What is the country name?? According to the query above,
    00003 the country name should be UK
    00022
    00034
    00034
    00034
    00034
    00034
    00034
    00034
    00034
    00034
    00034
    00034
    00034
    00034
    00044
    00060
    00081
    00086
    00107

Posting Permissions

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