Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Brian62 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    147

    =Count in SQL Report

    In the Report Footer I had this script in my Access 2010. I am now converting the entire database to SQL with Access as the front-end. In the Control Source in the Report the conversion added the "A" Alike in it. I have no idea how to convert it to work with SQL and pull the word Consent from the Consents column.

    Any idea? Thanks!



    =Count(IIf([Consents] ALike "*Consent*",1))

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try this type of thing:

    =Sum(IIf([Consents] Like "*Consent*",1,0))
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Brian62 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    147
    Quote Originally Posted by pbaldy View Post
    Try this type of thing:

    =Sum(IIf([Consents] Like "*Consent*",1,0))
    Still puts the A with Like = ALike and is giving me a invalid error.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you post the db here? I tested that and it worked as expected.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    I can't since it is a SQL BE with Access 2010 FE.
    It does work with just using Access but not with SQL.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Just tested on a form bound to a linked SQL Server table and it still worked as expected. What exactly is your formula?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    I put the formaula exactly as you wrote it in the control source in the report footer text box. The way you wrote it should, work but keeps adding in the A with Like (ALike). I don't understand why... This is what it does to it: =Sum(IIf([Consents] ALike "*Consent*",1,0))

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    See if this is relevant:

    http://www.access-programmers.co.uk/...27&postcount=5

    I've met Leigh; very smart guy.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    If I insert it in the stored procedure it will filter for consent (works in that case but not what I need). I need it to count the number of consent, regulatory, and N/A in the report in the control source textbox in the reports footer.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you try unchecking that box or changing the wildcard character to the one Alike wants? Another more dynamic option would be a subform/subreport based on a totals query that counted your items.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    The column is not a check box but a drop down from a form with Consent, Regulatory and N/A. I tried everything with the ALike that I could think of and can not get it to count the quanities I need. I can't find anything so far online to get what I need. The script you gave me was much like the one I had before I started to convert the database to SQL FE.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you read the link I posted, regarding it being an ANSI issue? I'm talking about the checkbox mentioned in the link. You could also try the other wildcard mentioned.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    I do not have the two check boxes.

    http://office.microsoft.com/en-us/ac...010341870.aspx

  14. #14
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    I do not have the two check boxes.
    I noticed that the ability to change the ANSI is it cannot be connected to SQL. Once connected, you loose the two check marks.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you try

    =Sum(IIf([Consents] Like "%Consent%",1,0))
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Count formula in Report
    By Amber in forum Reports
    Replies: 2
    Last Post: 01-30-2012, 08:22 PM
  2. count & sum in report
    By ali zaib in forum Access
    Replies: 1
    Last Post: 01-16-2012, 02:35 PM
  3. How To Count Dates on Report?
    By netchie in forum Access
    Replies: 18
    Last Post: 08-31-2011, 12:10 PM
  4. Record Count within a Report
    By BLD21 in forum Reports
    Replies: 1
    Last Post: 04-06-2011, 10:11 AM
  5. Count and Sum in Report
    By Brian62 in forum Reports
    Replies: 3
    Last Post: 02-19-2010, 04:10 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