Results 1 to 7 of 7
  1. #1
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118

    #ERROR in Field that I am trying to remove

    I have a field in a table with a some values = '#ERROR' its an imported table so the #ERROR is expected and nothing I can do about it. I am trying to do a query that groups and counts the number of #ERRORS but get a data mismatch error therefore I am trying to substitute #ERROR for a blank I have used criteria : IIf([FieldName]="#Error"," ",[FieldName]) and also IIf(IsError([FieldName])," ",[FieldName]) But both still show the #ERROR. How can I get rid of the #ERROR text in the field?

    Thanks

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Upload the table?
    I cannot see why the first would not work. I seriously doubt the second would as it is not an error?
    Are you sure they are in the table and not showing on a form for some reason?
    Not a lookup field is it?
    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

  3. #3
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    The Error field is the result of a calculated field based of a field named ‘text’
    Example entry in that Memo field Text would be:
    ** Section: Standard Consultation Notes ** [Question: Date of Session] 17/8/21 [Question: Notes Written on (date)] 17/8/21 [Question: Face to face or teletherapy] Face to Face [Question: If Face to Face where did the appointment happen] Home [Question: Who was present during the session] Alfie Katie moulson SALT myself [Question: Any feedback from parent/carer] The soft play room was locked today :-which meant he was unable to bolt into that room to escape therapy session. Cast still on. New one put on hopefully to be removed next week [Question: Session aims] Ch in issolation. Ch initial [Question: Activities carried out during the session and comments on the childā€™s performance] Fishing activity to settle him and encourage turn taking. Posting activity with greedy gorilla: one food item was won in return for ch attempt in isolation. Used cued artic to support production. He kept giving ch words instead of in isolation initially but soon understood the task and did well. Articulation station ch flash cards. Good attempts at ch word initial words. [Question: Homework/Strategies/Advice given] No follow up given today as mum seems to have a lot to deal with . [Question: Plan for next session] Continue ch turn taking and listening skills. [Question: Any other comments] Much better session today.

    The calculated field searches the txt looking for the answer of the question “[Question: Face to face or teletherapy] Face to Face” and is derived fist by:
    Step 1. F2FQ: InStr([Text],"[Question: Face to Face or teletherapy]") to find the question.
    Step 2. F2F: Mid([Text],[F2FQ]+40,12) to return the answer 12 characters following the question.

    This works fine with a field named F2F returning 1 of 3 possible results: Face to Face, Teletherapy or #Error, if it cant find the text in step 1.
    What I am wanting to do is count the Face to Face, Teletherapy or #Errors

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Instr() returns a 0 if the string is not found. So try 0 in your IIF()

    Text is a reserved word so bad choice for a field name.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  5. #5
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    Thanks Monke123, Ill give that a go. BTW Text is the name of the field on the CSV being imported would you suggest renaming it via a query? e.g. Text:Note_Template

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think this is what Moke123 suggested:
    IIF([F2FQ]>0,Mid([Text],[F2FQ]+40,12),"N\A")
    And to rename it the field in a query you do it the other way around: Note_Template:Text (in SQL will appear "SELECT Text As Note_Template FROM tblCSV").
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    Gicu Thanks I'll give that a go to. Re Renaming yes of course, my fault.

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

Similar Threads

  1. Remove Error Line from Report
    By AdiDoyle in forum Access
    Replies: 15
    Last Post: 08-08-2017, 02:23 PM
  2. Replies: 6
    Last Post: 04-04-2017, 11:09 AM
  3. Replies: 0
    Last Post: 06-17-2016, 08:56 AM
  4. Remove data if field value is zero
    By tombarberio in forum Queries
    Replies: 6
    Last Post: 10-28-2014, 04:45 PM
  5. Remove contents from each field
    By nancyszn in forum Access
    Replies: 7
    Last Post: 08-06-2009, 03:41 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