Results 1 to 15 of 15
  1. #1
    Drisconsult is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Jul 2010
    Posts
    13

    Removing zeros from form

    Hello

    I have written a school database for African countries that cannot afford the UK type databases, which require an Internet connection and as host of other features.

    My problem is that zeros appear when a student has not completed a subject, When the final form is shown, all the subjects not chosen appear with zeros, which teachers' find very disconcerting. My question is, how do a create a blank field when the subject has not been
    chosen. Please see the three uploaded Forms.



    Click image for larger version. 

Name:	P1.png 
Views:	30 
Size:	46.4 KB 
ID:	48761Click image for larger version. 

Name:	P2.png 
Views:	30 
Size:	50.3 KB 
ID:	48762Click image for larger version. 

Name:	P3.png 
Views:	30 
Size:	41.5 KB 
ID:	48763

    The subjects not chosen are seen as 0%. I need to show blank fields for not chosen subjects.

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,338
    You can use the format of the text box to hide the zero values, but you could also completely hide the null subjects?
    That would avoid entering values that are meaningless?

    Format route: https://support.microsoft.com/en-us/...0-15d36b7f9c3b

    Hiding the blank subjects : Use the tag property of the controls and loop through them in the current event of the form.
    If you want assistance with that post back here.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,759
    I would have to ask why your are showing blank subjects?
    One of them even has a value of 76% ?

    Even I would find that not disconcerting, but weird?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Posts
    1,173
    Are you using a default value of 0 in the table?
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,076
    There are 17 controls on a form for subjects. What happens if the count becomes 18? Really should use a datasheet or continuous form view and list the records that apply rather than show 12 empty spots.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Drisconsult is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Jul 2010
    Posts
    13
    Click image for larger version. 

Name:	P6.png 
Views:	24 
Size:	50.6 KB 
ID:	48771
    Here is my Table showing details.
    Click image for larger version. 

Name:	P5.png 
Views:	24 
Size:	47.4 KB 
ID:	48772
    Here is the Form showing data with Nz and calculating with just one data entry
    Click image for larger version. 

Name:	P4.png 
Views:	23 
Size:	25.4 KB 
ID:	48773
    Here is my Nz data for TOTART

  7. #7
    Drisconsult is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Jul 2010
    Posts
    13
    I feel I must clear up some very strange replies to my Post
    1. There are 17 subjects because that is all schools in east, central and west Africa can offer. There cannot be any more than 17 subjects.
    2. Students choose up to six subjects, that is why you see blanks. They cannot choose the whole 17
    If I try to remove the zeros in the Format Property Box, how will I set the answer to a percentage?

  8. #8
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Posts
    1,173
    If I try to remove the zeros in the Format Property Box, how will I set the answer to a percentage?
    isn't it something like YourNumber divided by 100 times 100 gives you the percentage.

    MM = iif(isnull(YourNumber),"",(YourNumber / 100)*100)

    or MM = iif(YourNumber = 0 ,"",(YourNumber / 100)*100)

    Need more info to be more precise.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  9. #9
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,338
    You can specify the format for each variation of result. Positive, Negative, Zero, Null . So something like

    ##%,##%,"",""

    However I would agree with the sentiments of the other responders, who are suggesting that you only display the subjects they have taken.
    Essentially your table design is incorrect, instead of a table holding 17 subjects you should have a table which has fields like;

    tb_StudentSubjects
    StudentSub_ID AutoNumber (PK)
    Student_ID Number Links to student id in student table
    Subject_ID Number Links to a list of subjects
    Grade Text
    Result Number
    Comments Text











    You would then use a sub form to display just the subjects they have records for.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,759
    Quote Originally Posted by Drisconsult View Post
    I feel I must clear up some very strange replies to my Post
    1. There are 17 subjects because that is all schools in east, central and west Africa can offer. There cannot be any more than 17 subjects.
    2. Students choose up to six subjects, that is why you see blanks. They cannot choose the whole 17
    If I try to remove the zeros in the Format Property Box, how will I set the answer to a percentage?
    Not strange at all. People are concerned that the structure is incorrect to begin with.

    And when the number of courses changes to 18, as it well might, the whole db needs to be redesigned?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    190
    As they have already told you the structure should be wrong so it would be advisable for you to show what you have done so far by attaching the file after having compressed it in zip format.

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,122
    I agree, the structure is wrong with Repeating Groups in the table.

  13. #13
    Drisconsult is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Jul 2010
    Posts
    13
    Gentleman
    May I thank you all for resolving the 0% problem I had with my Form. This is now resolved. Please see final result

    Click image for larger version. 

Name:	final.png 
Views:	19 
Size:	39.5 KB 
ID:	48777

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,076
    The % format looks kind of strange for a total of 298.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    190
    If you don't attach a sample file, you can't figure out what you did and so help you.

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

Similar Threads

  1. Removing form sidebar?
    By bleyden in forum Forms
    Replies: 1
    Last Post: 04-06-2016, 10:26 AM
  2. Replies: 18
    Last Post: 01-12-2016, 11:53 AM
  3. Issues with leading zeros in a form field
    By apetriella in forum Forms
    Replies: 2
    Last Post: 02-10-2015, 11:27 AM
  4. Removing AutoFormat in a Form
    By iOsiris in forum Forms
    Replies: 5
    Last Post: 05-08-2014, 01:45 PM
  5. removing record from form
    By rsearing in forum Programming
    Replies: 7
    Last Post: 05-14-2009, 10:34 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