Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    5

    Unanswered: Nested string function!

    Within a query, I need to show postcode characters to the left of the space, bearing in mind that there can be three or four characters, i.e.
    HR57 5FG or
    HR5 5FG
    and no spaces must be outputted. Any ideas?

    Thanks in advance to anyone who can help me.

    Janie

  2. #2
    Join Date
    Oct 2003
    Posts
    21
    Use a combination of the Left function to return X number of characters from the left part of the string and the Instr function to find the first occurence of the space. Subtract 1 from the result of Instr and you have the X.

    For example....

    PostalPrefix: Left([Table1]![PostalCode],InStr([Table1]![PostalCode]," ")-1)

  3. #3
    Join Date
    May 2004
    Posts
    5
    Thanks for that, but I am getting the message 'Data type mismatch in criteria expression. I replaced your Table1 with my table name and PostalCode with my postcode field name. Was that the right thing to do?

  4. #4
    Join Date
    Oct 2003
    Posts
    21
    Not real sure why you would get that error. I would need to look at the database to determine that.

    Replacing the table and field names with yours was correct.

    Gary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •