Results 1 to 13 of 13
  1. #1
    Rilly24 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2021
    Posts
    8

    Convert [#name?] error to zero in a form

    Hi Friends,
    Is a great pleasure been here.
    I created a table below
    ID RegName Status Remark
    1 A ACTIVE
    2 A ACTIVE
    3 A IN ACTIVE
    4 B NOT AVAILABLE
    5 B IN ACTIVE
    6 C NOT AVAILABLE
    7 B ACTIVE


    8 A AVAILABLE
    9 B IN ACTIVE
    10 B NOT AVAILABLE
    11 A NOT AVAILABLE
    12 D ACTIVE
    13 B NOT AVAILABLE
    14 C ACTIVE
    15 D NOT AVAILABLE

    what i want is create a summary form that will display RegName on Row while Status on column, therefore i created a crosstab query which gives me what I want, I also use Nz function to convert blank field to zero as you can see below;

    TRANSFORM Nz(Count(tblStatus.[ID]),0) AS CountOfID
    SELECT tblStatus.RegName, Count(tblStatus.ID) AS [Total Of ID]
    FROM tblStatus
    GROUP BY tblStatus.RegName
    PIVOT tblStatus.Status;

    Also i design my form as below;


    Now the problem is that each time one of the record is missing e.g Available is not in the table I get this error message #NAME? from the form as you can see;



    The question is that how can I convert this field or any other field that is not in the table 0 because it can be added anytime by the user. Or is there any other way to create the summary form apart from using crosstab query to generate the summary?

    Thank you and i look forward to hearing from you.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    you post a copy of the db
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Rilly24 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2021
    Posts
    8
    Ok attached is the copy of the db.
    Thank you.
    Attached Files Attached Files

  4. #4
    Rilly24 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2021
    Posts
    8
    Hi friends
    Could someone help me on this please?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Did you look at the control in design view? There is an error flag. When you click on it you'll see that Access is telling you that there is no such field as Available in the record source. Yet currently in the xtab query there is such a field**. I would never think to base a form on a xtab query because one time, a field is in the query, the next time it's not. So I can only imagine that if it was ever there, the link has been lost.

    xtab queries are sometimes used for reports, but you have to declare all the fields as parameters in order to avoid the volatility of the xtab query. You'd need to explain why you think you need to base a form on a xtab query. Better yet, maybe explain what the goal is without saying what doesn't work.

    EDIT
    **Sorry, mis-spoke. In the sample db, there is no such field but I was presuming it was there before and that you were able to select that field from the property sheet at some point. Just for fun I tried declaring the fields in the query property sheet (Column Headings) and it seems to work. Not something that I ever envision doing for myself though.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Rilly24 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2021
    Posts
    8
    Thank you for your quick response. Definitely is understood that, the record is not there but I want is that the field to remain there and show zero instead of #NAME so that when next the user input the record it will then count the record. I tried the same thing using report but it gives me same error. But if there is a better way to go about this instead of Xtab query I will appreciate it.
    Thank you.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So you tried this?
    Just for fun I tried declaring the fields in the query property sheet (Column Headings) and it seems to work.
    Click image for larger version. 

Name:	test2.jpg 
Views:	11 
Size:	19.3 KB 
ID:	44132

    Click image for larger version. 

Name:	Test1.jpg 
Views:	11 
Size:	18.8 KB 
ID:	44131
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Rilly24 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2021
    Posts
    8
    Quote Originally Posted by Micron View Post
    So you tried this?


    Click image for larger version. 

Name:	test2.jpg 
Views:	11 
Size:	19.3 KB 
ID:	44132

    Click image for larger version. 

Name:	Test1.jpg 
Views:	11 
Size:	18.8 KB 
ID:	44131
    Wow this is excellent it works like magic. I'm more than grateful. I noticed that if i add for example Available +Not available 2+0 instead of 2, but i got 20. Also =sum([Available]) i got [#Error]. as you can see below.
    Click image for larger version. 

Name:	Capture12.PNG 
Views:	8 
Size:	18.0 KB 
ID:	44133

    Thank you a lot I really appreciate it.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry, no idea how to add only specific columns in a cross tab query. If you can figure out how to total only 2 values then you might be able to use a union query to meld the two.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Rilly24 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2021
    Posts
    8
    Thanks a lot I really appreciate your efforts. I will try.

  11. #11
    Rilly24 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2021
    Posts
    8
    I have gotten the solution. What i did was instead of =[AVAILABLE]+[NOT AVAILABLE] in the control source I use =Val([AVAILABLE])+Val([NOT AVAILABLE]) and i got it correctly.
    Click image for larger version. 

Name:	Capture13.PNG 
Views:	8 
Size:	7.2 KB 
ID:	44135

    Thank you.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Thanks for posting a solution. I presume you mean like =Val([AVAILABLE])+Val([NOT AVAILABLE])
    I think it is a fluke that it works given what the Val function is supposed to do. A type conversion function might be better/safer. If you find that it fails try
    =CLng([AVAILABLE])+CLng([NOT AVAILABLE]) to convert to a Long data type. I'm surprised that a xtab query presents the count values as text (they are left justified). Can't say I ever noticed that before. It explains why 1+1 = 11
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Rilly24 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2021
    Posts
    8
    OK thanks I will that and feed you back with the result.
    Thank you once more.

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

Similar Threads

  1. Convert multiple form to pdf
    By onlylonely in forum Forms
    Replies: 9
    Last Post: 10-02-2017, 02:06 AM
  2. Convert form into a summary
    By krisht in forum Forms
    Replies: 1
    Last Post: 07-04-2014, 10:30 AM
  3. Replies: 3
    Last Post: 06-11-2014, 08:06 AM
  4. Replies: 11
    Last Post: 04-09-2014, 01:08 PM
  5. Convert form to a report
    By Desstro in forum Reports
    Replies: 5
    Last Post: 09-16-2010, 12:18 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