Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2012
    Posts
    7

    Question Unanswered: dlookup returns frist value in feild

    Hi All,
    i could use your help, I have two tables my call log tabe that has a combox with zip codes that i am using, cazip table that has my zip codes and the state codes (shown below).
    zip - statecode
    61721 - 13
    61007 - 15

    i created a form so that when i change the zip dlookup will return the state code for me in the box that labeled statecode.... Currently when i change the zip code it only returns the frist value in side the colum of state codes.

    exp

    zip=61007
    statecode=13

    so this is my current dlookup script

    Private Sub CBOZIP_AfterUpdate()
    STATECODE = DLookup("[STATECODE]", "[CAZIP]", "[CBOZIP]=" & CBOZIP)

    what do i need to do to make it pull the state code that matches the zip.

    i am very new to this as in the last week so most of my learning is hands on and that not much. any help would be great
    Thanks
    jkisner
    Last edited by jkisner; 01-20-12 at 00:10. Reason: wording backwards

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Would this be simpler for you?

    Autofill
    Paul

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Is the Field in the CAZIP Table actually named CBOZIP? If so, what is its Datatype?

    Fields composed entirely of Digits but not used for mathematical calculations are usually defined as Text, and your syntax is only correct if CBOZIP is defined as a Number.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

  4. #4
    Join Date
    Jan 2012
    Posts
    7

    reply

    Hi
    Thanks for your help it is currently has a data type as a number.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Please confirm that the Field in the Table is actually named CBOZIP? I assume that CBOZIP is a Combobox that has the Zips in it, but that's an odd name for a Field in a Table.

    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
    Jan 2012
    Posts
    7

    REply

    hi thanks for you help. The table fields are ID,STATECODE,ZIP,CITY,COUNTY the primery Key is Zip . Im not sure if that matters but thought i would let you know incase it does if you need any othere info please let me know. Thanks again for your help.

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If the Table Field is named ZIP then your code needs to be

    DLookup("[STATECODE]", "[CAZIP]", "[ZIP]=" & CBOZIP)

    Of course, since ZIP and STATECODE are in the same Table, the standard way of doing this would be to have both ZIP and STATECODE in the CBOZIP's RowSource, and then use this code to populate the STATECODE Textbox:
    Code:
    Private Sub CBOZIP_AfterUpdate()
      Me.STATECODE = Me.CBOZIP.Column(1)
    End Sub
    assuming that the STATECODE was the 2nd Field in the Combobox, reading left-to-right; the Column Index is Zero-based.

    This would alleviate the need to use DLookUp() altogether.

    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

  8. #8
    Join Date
    Jan 2012
    Posts
    7

    Reply

    Thanks, I took your second option and it worked great. Now if i want to take the value given in the STATECODE and look for othere items in a differnt table. would i need to us DLookup for that. THANKS FOR YOUR HELP

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Yes, DLookUp would be the way to go, assuming that the other Table had a Field that would match STATECODE.

    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

  10. #10
    Join Date
    Jan 2012
    Posts
    7

    Thanks

    Thanks for all your help i have it up and going and it is all looking good. now i just need to figure out mult user stuff. Which i well post a new thread if need be. Thanks again for all your help. have a good day

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    Good luck with your project!

    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

Posting Permissions

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