Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005
    Posts
    7

    Unanswered: Find record with certain criteria

    Access 2003

    I have a flat database that can contain multiple entries for an individual. It is a sign in sheet for company visitors. The first time they visit they must watch a Powerpoint Presentation then print a visitor badge with name, date, time, etc. The next time they visit they do NOT need to watch the presentation, just print the badge (as long as the presentation hasn't changed). I have most of this worked out with the exception of determining if they have saw the presentation before. I'm keying on FullName (which is FirstName concatenated with LastName). I also have a field called SlideShow that is initially "No" then changed to "Yes" after the pres. is watched.

    My problem is if after the first visit when they input their First and last names and click on Sign In, I am generating FullName variable but I need to search the "Contacts" table for "FullName" and see if that records "Slideshow" field has been set to "Yes". If so, I will skip the pres and print a badge.

    I have tried various forms of the DLookup.
    Currently it is this:

    dim varx as variant
    strFullName as String
    varx = DLookup("Slideshow]", "Contacts", [FullName] = StrFullName)

    Any ideas....thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by kbrown73
    Access 2003

    I have a flat database that can contain multiple entries for an individual.
    Ummmm... what does the rest of your back end look like. If it's what I envision, there are suggestions that could be made to make your life easier... just checking

    dim varx as variant
    strFullName as String
    varx = DLookup("Slideshow]", "Contacts", [FullName] = StrFullName)

    Any ideas....thanks.
    Your problem here most likely resides with quotes. You have to enclose your parameter in quotes or Access doesn't know it's supposed to be a string:

    varx = DLookup("[Slideshow]", "Contacts", "[FullName] = '" & StrFullName & "'")

    The "criteria" argument of DLookup has the same rules applied as an SQL "WHERE" clause. Strings need to be enclosed in quotes or Access thinks it's supposed to find a function or constant with the value specified.

    Here's an example in reverse:

    Say we want to find all records with a date matching today...

    DLookup("[myDate]", "myTable", "[myDate] = 'Date()'")

    This will fail. Because the quotes are present, [myDate] is trying to literally match the word 'Date()'. Now take this example:

    DLookup("[myDate]", "myTable", "[myDate] = Date()")

    Since there are no quotes, Access looks for a function called "Date()". It finds the function and returns the value (12/7/2005 in this case) and substitutes it in the criteria.

    Make sense?
    Last edited by Teddy; 12-07-05 at 14:47.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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