Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2019
    Posts
    2

    DCount issue using LIKE operator in the criteria

    I have an unbound report named CustomerData that I am adding text boxes into to calculate certain numbers.



    Within this report, I have a text box named [Text150] that I am using a DCount function in. I need it to show the number of customers within the table named "Customers" that have ordered from me during certain months. I keep that information in a field named [MonthsOrdered] within the same "Customers" table. Since there is possibly more than one month within the [MonthsOrdered] field per customer, I attempted to use a wildcard within the DCount criteria, as well as the LIKE operator, but I cannot find what I'm doing wrong.

    Here is my current function for finding the number of customers who have ordered something in July:

    =Nz(DCount("[CustomerID]","Customers","[Customers]![MonthsOrdered] LIKE '*July*'"),0)

    I think my issue is within the LIKE '*July*' portion, but I've tried different ways of stating it and have not found a solution anywhere. I have also tried changing "[CustomerID]" to simply "*" to no avail.

    I am not new to Access, but I'm entirely self-taught and not a professional Access programmer. Any and all help would be appreciated. This is a mystery I cannot seem to solve on my own.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,517
    Try =Nz(DCount("[CustomerID]","Customers",Instr([Customers]![MonthsOrdered],"July'")>0),0)

    Cheers,
    Vlad

  3. #3
    Join Date
    Jun 2019
    Posts
    2
    I've figured it out! Since I've used a combo box in a form to populate the [MonthsOrdered] field with the different months, the solution was to change the criteria from:

    "[Customers]![MonthsOrdered] LIKE '*July*'"

    To:

    "[Customers]![MonthsOrdered].[Value]='July'"

    Thank you for the fast reply, Vlad!

  4. #4
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,399
    What field type is your monthsordered field? Name implies it should be numeric?

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,191
    if .Value works it is probably a lookup field. That will likely continue to raise issues but what seems odd to me is that you cannot get the month from the orders table? Is there no customer order date that you can simply use Month function on rather than keeping "July" in a customer field? Your design doesn't seem quite right to me.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

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

Similar Threads

  1. Criteria issue with DCount
    By JimO in forum Access
    Replies: 10
    Last Post: 08-29-2017, 11:56 AM
  2. Need a fresh set of eyes please (Dcount issue)
    By Gina Maylone in forum Access
    Replies: 3
    Last Post: 02-23-2017, 03:12 PM
  3. Replies: 2
    Last Post: 02-26-2014, 05:06 PM
  4. DCount issue
    By RobWulf in forum Queries
    Replies: 4
    Last Post: 05-21-2013, 04:07 PM
  5. Replies: 1
    Last Post: 05-18-2012, 11:59 AM

Tags for this Thread

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 - Senior Forums