Results 1 to 11 of 11
  1. #1
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80

    Using dlookup with field value

    So here are 2 tests I have tried:



    CODE
    MsgBox DLookup("[Clinic Name]", "tblConsultAlias", "[Service Name] = '"& Forms![frmAddRecord]!ConsultPullTitle & "'")
    MsgBox DLookup("[Clinic Name]", "tblConsultAlias", "[Service Name] = 'COMMUNITY CARE-IC EMG/NCV-636A8'")


    The first line does not work and returns an error 'Invalid use of Null'.
    The second works fine and returns 'EMG/NCV' like it should. The verbiage 'COMMUNITY CARE-IC EMG/NCV-636A8' is located in the ConsultPullTitle field of the frmAddRecord form. So something is wrong with how I am using Dlookup with either a variable or referring to the field for input.

    Thoughts?

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    There is a space missing before the & Forms..

  3. #3
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80
    Same error either way

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Add a line before it - MsgBox "x" & Forms![frmAddRecord]!ConsultPullTitle & "y" - and see what is displayed, maybe the field itself is null.

  5. #5
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80
    Displays
    xCOMMUNITY CARE-IC EMG/NCV-636A8
    y

    Is the line feed/return causing a problem since there seems to be one there?

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I don't know, quite frankly. And how would one get rid of it? I assume it is coming directly from a table so that is how it is stored. Try it in a query, in the criteria line put Forms![frmAddRecord]!ConsultPullTitle, and see if it still gives an error.

  7. #7
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80
    Did a little messing and think I found the problem, not a solution yet
    Since the data is being entered on to the form through VBA it doesn't seem to be recognizing it as 'there'. If I delete COMMUNITY CARE-IC GASTROENTEROLOGY(COLONOSCOPY)-636A8 from the field and manually type it in and then run the code it works fine. So it looks like I need to figure out how to get the data to appear like it's there after VBA enters it.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    OK, I would need to know a bit more about the process, maybe post the VBA.

  9. #9
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80
    The script looks at a large imported data file and pulls bits out. For this particular field it uses:
    Code:
    strStart = InStr(Forms!frmAddRecord.Form!ConsultInfo, "Orderable Item:") + 23
    strEnd = InStr(strStart, Forms!frmAddRecord.Form!ConsultInfo, Chr(10))
    Forms!frmAddRecord.Form!ConsultPullTitle = Mid(Forms!frmAddRecord.Form!ConsultInfo, strStart, strEnd - strStart)
    Then the above code attempts to move this over (requires a second button to be hit after data is verified) .

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You might want to subtract 1 from strEnd to get rid of the Chr(10).

  11. #11
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80
    Hallelujah, I knew it would be something silly.... works like a charm now, thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 01-20-2018, 03:40 PM
  2. dlookup for autonumber field
    By Rizvi in forum Access
    Replies: 5
    Last Post: 11-28-2017, 12:52 PM
  3. DLookup on a DateTime field
    By rjackson in forum Queries
    Replies: 1
    Last Post: 08-22-2016, 07:39 PM
  4. Using DLookup to find a value in a field.
    By julianholworth in forum Access
    Replies: 6
    Last Post: 05-13-2016, 06:35 PM
  5. DLookup for Yes/No field
    By Lockrin in forum Access
    Replies: 3
    Last Post: 05-27-2010, 12:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums