1. ## 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. ## Re: repeat calculation (9.0/2001)

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

3. ## 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. ## 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.

5. ## 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.

6. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•