Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    repeat calculation (9.0/2001)

    Now I'm trying to figure out how to have one column look at another and if anything in the 2nd column is repeated then have the 1st column return a 0, else a 1. I know how to do this in excel, but how do I do this in an access querry?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: repeat calculation (9.0/2001)

    try this in a new column:<pre>IIF([field1] = [field2],0,1)</pre>

    Francois

  3. #3
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: repeat calculation (9.0/2001)

    I tried it, and it came up w/ a repeating "UNIQUE
    IIF([307G_0O]=[307G_0O],0,1)" in every cell. And the repeat I'm looking for would be in the same field, does that make any difference?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: repeat calculation (9.0/2001)

    In a query you need to put an expression something like:
    <font color=blue><font face="Georgia">
    ReturnedValue: IIF([307G_0O]=[307G_0O],0,1)
    </font face=georgia></font color=blue>
    In this case, ReturnedValue is an Alias that will show up in the column name of the query - is is made so by the : (colon) which follows it directly. Also note that your expression will always return a 0 since the field will always be equal to itselt (unless it's Null, which should return a 1). Hope this helps.
    Wendell

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: repeat calculation (9.0/2001)

    Sorry, i didn't understand that it in the same field(column)
    try this if the data are strings(text)<pre>IIf(DCount("YourField","YourTabl e","[YourField]='" & [YourField] & "'")>1,0,1)</pre>

    If the data are dates then <pre>IIf(DCount("YourField","YourTable","[YourField]=#" & [YourField] & "#")>1,0,1)</pre>

    and if the data is numeric :<pre>IIf(DCount("YourField","YourTable","[YourField]=" & [YourField])>1,0,1)</pre>

    One side note: Dcount function can be slow on large tables.
    Francois

  6. #6
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: repeat calculation (9.0/2001)

    it's coming up w/ an error msg: The expression you entered contains invalid syntax, you may have entered an operand without an operator.... could you look over what I put in? UNIQUE:IIF(DCOUNT("DWG_NO","DETAILED PLAN","[DWG_NO]="[DWG_NO]='"&[DWG_NO]&"'")>1,0,1)
    Unique is what I want to call the field, DWG_NO is the field I'm trying to see if has any TXT repeats, and detailed plan is my table name. Thank you for all your help.

  7. #7
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: repeat calculation (9.0/2001)

    How do I make it null, or what do I do so it recognizes it as a null and returns a 1?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: repeat calculation (9.0/2001)

    Try giving the field another name than UNIQUE. Since UNIQUE is a keyword in the query language that Access uses, it is not a good name for a column.
    Moreover, the third part in your DCount function is wrong. It contains [DWG_NO]= twice. If DWG_NO is a text field, it should be

    ...,"[DWG_NO]='" & [DWG_NO] & "'") ...

    and if DWG_NO is numeric, it should be

    ...,"[DWG_NO]=" & [DWG_NO]) ...

  9. #9
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: repeat calculation (9.0/2001)

    Okay, that formula seems to work on the design view side of the house. However, when I run it, (I changed unique to original), the "original" field comes up as "#error" and I get an error msg stating "Syntax error(missing operator) in query expression '[dwg_no]=117fm10012". I clicked on "help" and nothing popped up. Oh, and the dwg_no is a txt field containing both txt and #'s and symbols.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: repeat calculation (9.0/2001)

    Since your field is a text field, you must surround the value with quotes. The expression you have in your post lacks these; that causes the error. Change the third part of the DCount to<pre>...,"[DWG_NO]='" & [DWG_NO] & "'") ...</pre>

    or<pre>...,"[DWG_NO]=" & Chr(34) & [DWG_NO] & Chr(34)) ...</pre>

    If the DWG_NO field can contain both single quotes and double quotes, this will be problematic.

  11. #11
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: repeat calculation (9.0/2001)

    And it worked! Thank you- <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

Posting Permissions

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