Results 1 to 3 of 3

Thread: Query Criteria

  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Exclamation Query Criteria

    Hello -

    I have what should be a simple adjustment that I need to make.

    Here is the query that I am running:

    SELECT
    ContactMessage.InvoiceNumber,
    ContactMessage.AIdent,
    ContactMessage.OrderID,
    ContactMessage.CustName,
    ContactMessage.Message,
    ContactMessage.Date,
    ContactMessage.ContName,
    ContactMessage.RepName,
    ContactMessage.EmpName,
    ContactMessage.CustomerID,
    ContactMessage.Action
    FROM contact
    INNER JOIN
    ContactMessage ON contact.ID = ContactMessage.ID
    WHERE
    (((ContactMessage.Date) Between getstartdate() And getenddate()) AND ((ContactMessage.RepName) Like [Please enter RepName or enter "*" for all]));

    the problem is when ContactMessage.ContName is empty it doesn't return the row?

    I'm coming from a MySQL background but I don't see why it is doing this. Can anyone shine some light on this?

    Thanks in advance.

  2. #2
    Join Date
    Mar 2006
    Location
    Oklahoma City, OK
    Posts
    184
    Code:
    WHERE
    (((ContactMessage.Date) Between getstartdate()  And  getenddate())
     AND
     ((ContactMessage.RepName) Like [Please enter RepName or enter "*" for all]));

    Since ContactMessage.ContName is not used in the Where clause, did you really mean the ContactMessage.RepName

    If you rally men ContactMessage.RepName then try using the BZ () function with the field:

    Nz(ContactMessage.RepName,"")

    Like this:
    Code:
    WHERE
    (((ContactMessage.Date) Between getstartdate() And getenddate()) 
    AND 
    ((Nz(ContactMessage.RepName,"")) Like [Please enter RepName or enter "*" for all]));
    Last edited by HiTechCoach; 09-29-2009 at 04:46 PM.
    Boyd Trimmell aka HiTech Coach
    Microsoft MVP - Access Expert
    [SIGPIC][/SIGPIC]
    Office Programming 25+ years as a Software Developer specializing in:
    Business Process Management
    Accounting/Inventory Control
    Customer Relations Management (CRM)
    Electronic Data Interchange (EDI)

  3. #3
    Join Date
    Sep 2009
    Posts
    2
    Thanks for your reply.

    I did actually mean the ContName that is what makes the results weird for the query.

    Any ideas?

Posting Permissions

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