Results 1 to 13 of 13
  1. #1
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296

    how to check for a double quote in a calculated control?

    I have a calculated control using the following code:



    Code:
    Right([length],1=""")
    I tried doing both """"(4) and """""(5) but neither work. They then give me a type error.
    I am trying to get it to detect if the last character is a double quote. Ex: 62"

    full code:
    Code:
    =[Quantity] & "' X " & [Length] & IIf(Right([length],1="""),"","'")
    (basically if theres a double quote then zls and if there is no double quote at the end then add a single quote signifying it is in feet.)

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Think a double quote is chr(34)

    so try

    right(length,1)=chr(34)

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If that doesn't work, then maybe IIf(Right(length,1) = """","","'")
    You are missing 1 double quote. Do you really want the single quote after quantity e.g. 12' X
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by CJ_London View Post
    Think a double quote is chr(34)

    so try

    right(length,1)=chr(34)
    I still get a type error. Both [Quantity] and [Length] are short text in the table.

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you show us an example of the two input fields and the desired output?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It could be that the report control(s) and field name(s) is/are the same. Or perhaps 1 or more records are null, although Right(Null,1) doesn't produce an error by itself.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Right(Length,1)=chr(34) should work

    Another option:
    =[Quantity] & "' X " & [Length] & IIf(InStr([length], Chr(34)) > 0, "", "'")

    If Length is Null, the apostrophe will return. Is Null a possibility?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The last expression ignores the position of the double quote, so this would also work (if there is only one occurrence of Chr(34)):
    Replace([Quantity] & "' X " & [Length],Chr(34),"'")

    Cheers,

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Vlad, I don't think OP wants to replace quote mark. The original expression returns empty string if quote is present, apostrophe if not.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Gicu View Post
    Can you show us an example of the two input fields and the desired output?

    Cheers,
    Here are some examples of inputs and the desired output

    Quantity Length Output
    1000 50 1000' X 50'
    500 52" 500' X 52"

    Occasionally things need to be in inches so since the field is a short text I was just going to have the user indicate that by adding the double quotes.
    The name of the control is "QuantityXLength".
    The field names are "Quantity" and "Length". There are no controls named Quantity or Length, closest is a Control named Lengths.
    Without the IIF statement it is fine with nulls.

  11. #11
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by June7 View Post
    Right(Length,1)=chr(34) should work

    Another option:
    =[Quantity] & "' X " & [Length] & IIf(InStr([length], Chr(34)) > 0, "", "'")

    If Length is Null, the apostrophe will return. Is Null a possibility?
    This worked perfectly! But what did I do wrong?
    Since null is a possibility and should be a ZLS if it is.

    =[Quantity] & "' X " & [Length] & IIf(InStr([length], Chr(34)) > 0, "", IIF(Isnull([length]),"", "'"))

    This adaptation seems to work!

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If Length is Null, don't return X. Use + for concatantion of X to Length. If Length is Null this will return Null. Concatenation of Quantity with Null using & will return Quantity. This is because + as concatenator is from old BASIC but + is also arithmetic operator and arithmetic with Null returns Null.

    Or

    =[Quantity] & "' X " + [Length] & IIf(InStr(Nz([length], Chr(34)), Chr(34)) > 0, "", "'")

    Or

    =[Quantity] & "' X " + [Length] & IIf(Right(Nz([length], Chr(34)), 1) = Chr(34)), "", "'")

    or

    =[Quantity] & "' X " + [Length] + IIf(InStr([length], Chr(34)) > 0, "", "'")

    Quantity will not have following apostrophe.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by June7 View Post
    If Length is Null, don't return X. Use + for concatantion of X to Length. If Length is Null this will return Null. Concatenation of Quantity with Null using & will return Quantity. This is because + as concatenator is from old BASIC but + is also arithmetic operator and arithmetic with Null returns Null.

    Or

    =[Quantity] & "' X " + [Length] & IIf(InStr(Nz([length], Chr(34)), Chr(34)) > 0, "", "'")

    Or

    =[Quantity] & "' X " + [Length] & IIf(Right(Nz([length], Chr(34)), 1) = Chr(34)), "", "'")

    or

    =[Quantity] & "' X " + [Length] + IIf(InStr([length], Chr(34)) > 0, "", "'")

    Quantity will not have following apostrophe.
    Ah, very interesting information! I appreciate it very much!
    I was actually looking for something more like this
    Code:
    =[Quantity] & "' X "+[Length] & IIf(IsNull([Length]),"'",IIf(InStr([Length],Chr(34)),"","'"))
    Thank you! I wouldn't have been able to do it without everyone's help!

    Here is a table of results with this code
    Quantity Length Result
    100 10 100' X 10'
    100 10" 100' X 10"
    100 null 100'

    Note: I realize it is redundant to multiply 100' by 10". I prefer using the @ symbol because this is for quantity material to be at length but the person using it everyday prefers X

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

Similar Threads

  1. Replies: 8
    Last Post: 11-27-2017, 08:26 AM
  2. Replies: 3
    Last Post: 12-10-2015, 03:51 PM
  3. Replies: 2
    Last Post: 01-04-2013, 10:05 AM
  4. Using double quote as text delimiter
    By EddieN1 in forum SQL Server
    Replies: 4
    Last Post: 03-11-2012, 08:49 PM
  5. OpenReport Command with Double Check Where Statement
    By Robert M in forum Programming
    Replies: 3
    Last Post: 09-17-2009, 04:01 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