Results 1 to 3 of 3
  1. #1
    mbryanr is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2010
    Posts
    2

    if record exists...conditional query criteria

    Ok..here is what I am attempting to do.

    Update query based on a form text box control (txtID) and run from a command button.

    The user will input an equipment number. Since the query is actually a hierarchy tree the user may not necessarily know which column it is located.

    Based on the equipment number, the query will return all rows that include the equipment number.

    I think I can do it with a recordset or dlookup...

    If DLookup("IDValue", "tblbase", "IDValue = '" txtid "'") = 0 Then
    SELECT tblBase.IDValue, tblBase.Level1, tblBase.Level2, tblBase.Level3, tblBase.Level4, tblBase.Level5, tblBase.Level6, tblBase.Level7, tblBase.Level8, tblBase.Level9, tblBase.Level10, tblBase.Level11, tblBase.Level12
    FROM tblBase
    WHERE (((tblBase.IDValue) Like "*"));

    ELSE
    IF DLookup(...) > 0 Then
    Select
    From
    Where (((tblbase.IDValue) Like "[Forms]![frmForm]![txtid]"


    I also need to return the row even if there is null values in other fields (use nz?)
    I need help with the best method to perform this and syntax.




    Any help is appreciated. Thanks in advance.

  2. #2
    llkhoutx is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Location
    Houston, Texas USA
    Posts
    373
    Use
    Code:
    Where (((tblbase.IDValueLike [Forms]![frmForm]![txtid
    Note that "[Forms]![frmForm]![txtid]" does not reference the form control, it's a mere string.

    You're on the right track.

    Congradulations, many posters don't even try.

  3. #3
    mbryanr is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2010
    Posts
    2
    Thanks...you can tell I'm a beginner? ;-) :-D

    I'll give the coding another run and post a sample dB as soon as possible. (can't post actual as the data is proprietary.)

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

Similar Threads

  1. Help with Conditional statement in query
    By Rhues in forum Queries
    Replies: 1
    Last Post: 01-11-2010, 02:09 PM
  2. Replies: 1
    Last Post: 11-30-2009, 05:05 AM
  3. Conditional Query Question
    By Silver Rain 007 in forum Queries
    Replies: 0
    Last Post: 11-16-2009, 11:30 AM
  4. Conditional Update query ...
    By valkyry in forum Access
    Replies: 0
    Last Post: 04-25-2007, 02:04 PM
  5. Conditional Format Query
    By Schwagr in forum Queries
    Replies: 3
    Last Post: 03-20-2006, 02:39 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