Microsoft Access Help.

Discussion in 'OT Technology' started by Funky Munky, Apr 14, 2004.

  1. Funky Munky

    Funky Munky Chronic Masturbator OT Supporter

    Joined:
    Oct 5, 2003
    Messages:
    14,551
    Likes Received:
    0
    Location:
    OT
    Alright I am doing a small assignment in college with MS Access and I am not farmiliar with the formulas and I need a "count" formula to work to total up a line in my database so...

    I am making a report in design view and I know to click "Text Box" and when you place it is says "Unbound". I need to find out what to type in to use a count formula (like in Excel its "=count *range*". I need to use it to count a column in my report. Whats do I type into the text box to get it to count one of the columns?

    HELP!
     
  2. Nocera

    Nocera ...

    Joined:
    Aug 9, 2000
    Messages:
    1,307
    Likes Received:
    0
    Location:
    Long Island, NY
    Try =count([nameofcolumn])
     
  3. Funky Munky

    Funky Munky Chronic Masturbator OT Supporter

    Joined:
    Oct 5, 2003
    Messages:
    14,551
    Likes Received:
    0
    Location:
    OT
    Nope.

    It says "The expression you entered has an invalid syntax"

    And also "You may have entered an operand without an operator".
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    That should work - what's the name of the column you are trying to count?
     
  5. astriom

    astriom Guest

    Are you trying to count the number of records with a certain value of a certain field; like there are 12 records in the database with the number 789 in the "number of sales" column of the table? If this is the case you could do an on open event proceedure on the form and insert something like the following in the visual basic window.

    private sub on_open()

    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("name of table or query", dbOpenDynaset)
    dim ct as string

    ct=0

    With rs
    .MoveFirst
    Do While Not .EOF

    if rs![name of the field] = value then ct = ct + 1

    .movenext
    loop
    end with

    me!text0 = ct

    end sub

    That will count all records with a specified value in a specified table or query and put it in the "text0" (thats the #0) field of the currently open form or report. Be sure to leave all quotation marks in the formula or it won't work.

    Of course the value can be anything you want it to be. a number, a calculation, a date, a value taken from a different field in the form or report, even a text string (as long as the text is inside quotation marks).

    Access isn't Excell, even though some functions look identical, they don't always do the same things. Access is more of an eye candy database where Excell is all raw data. Access offers more versatility, but it costs you more in initial development times.

    Hope this helps...;)
     
  6. astriom

    astriom Guest

    I forgot...

    If you just want the total number of records (rows) there are by counting down a certain cloumn, just leave out the if/then statement and put:

    ct = ct + 1

    You can always use this command to count all records that have any value that field and ignore those that don't:

    if isnull([field name]) then
    ct = ct
    else
    ct = ct+1
    end if
     

Share This Page