Results 1 to 4 of 4
  1. #1
    NadiaVKhan is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    25

    Make a table with fields in a form

    Hello,



    I wasnt sure how to word this right. I am NOT trying to make an Access Object table.

    I am trying to create a 5x5 table in a form similar to what I created below.

    **************

    Year 1 ______________ Year 2_________________ Year 3 Class ______________


    Year 1 Year 2 Year 3 Total
    Asian
    Hispanic
    White
    Total

    *************

    This table runs of a query I made. And the “Start Date, End Date, and Class” fields are the criteria linked to my query. I am able to get the total rows and columns without any difficult by using the method below for each class.

    =DCount("[Class]","qryUniversity","[Class] = 'Science'")

    =DCount("[Class]","qryUniversity ","[Class] = 'Math'")

    =DCount("[Class]","qryUniversity ","[Class] = 'Biology'")

    =DCount("[Class]","qryUniversity”)

    I input this code into the control source of the fields in my form.

    I am having difficulty with everything else. First of all, is there a way I could the making my Query know that when I enter “2016” under txtYearOne, I actually mean “>=1-1-16 and <=12-31-16”?

    Secondly, how can I get DCount to count values based on TWO criteria?



    Would I do something like this:

    =DCount("[Class]","qryUniversity","[Class] = 'Science' “[txtYearOne] = “2016” ")

    (Blue refers to my query and red refers to the criteria of my query)

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    That is not how you design a relational table.
    but you add as many fields to the Where Clause as you need....

    Dcount("*","query","[field1]=2016 and [field2]='" & me.txtBox & "'")

  3. #3
    NadiaVKhan is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    25
    Thanks!!!

    And if you dont mind, can you tell me how I can fix my query?

    I want to type in ONLY the years in my form. (ie: "2015, 2016, and 2017). But I only know how to query for dates using this format: >#1-1-15#

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You can utilize any one of many date format functions - e.g. Year(datefield) will return just the year. See also DateFormat

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

Similar Threads

  1. Replies: 1
    Last Post: 08-29-2016, 08:16 AM
  2. Replies: 6
    Last Post: 12-13-2014, 09:20 PM
  3. Replies: 9
    Last Post: 11-04-2014, 08:06 PM
  4. Replies: 8
    Last Post: 05-08-2014, 12:07 PM
  5. help. too many fields to make a form !!!
    By amd711 in forum Forms
    Replies: 2
    Last Post: 10-23-2012, 08:47 AM

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