DCount Syntax for Numbers-Creating Duplicates Warning

[analystntrg]

Hello,

I am creating a form that displays invoice entries that contractors submit for payment. At the top of my Access form I have a control that should display a duplicates warning if the record has a duplicate order number [OrderNo] or project id [ProjectID] that is already in the tblInvoiceSubmissions. The contractors can only be paid once per order/project.

I am currently using an expression with the DCount function but it occurred to me that unlike the sample I based this expression off, my fields are data type-number and not text.

I currently have...

=IIf(DCount("*","[tblInvoiceSubmissions]","[ID]<>" & Nz([ID],0) & " And [OrderNo] = '" & Replace(Nz([OrderNo]),"'","''") & "'")>0,"Possible Duplicate Order","")

Can anyone tell me how to re-write this being that[OrderNo] is a number. And how to add that the [ProjectID] field should be counted as well.
I'm trying to say...
If number of occurrences in which the same [OrderNo] or [ProjectID] within the table "tblInvoiceSubmissions" is greater than 0 display "Possible duplicate order".

I tried just removing the quotes but I wasn't sure which ones. It has been impossible to find examples of DCount syntax for numbers only text. Any help that can be offered would be a Godsend. I have a tight deadline and this is the last function to be added. Thanks for your help in advance!

[twinnyfo]

analystntrg,

Here is a possible solution:

=IIf(DCount("*", "tblInvoiceSubmissions", _
    "[OrderNo] = " & Nz([OrderNo], 0) & _
    " Or [ProjectID] = " & Nz([ProjectID], 0)) > 0, _
    "Possible Duplicate Order","")
 

This assumes that both your Order Number and Project ID are true numerical values and not strings. I changed the logic to solve for positive results and eliminated the possibility of Null values as you had.

Keep in mind that using the DCount Function, although funcitonal, will slow down tremendously once you start adding lots of records. There may be better ways to do this through VBA coding behind your Form, but, based on your question, this may serve as an initial solution. (I hope I got the syntaxt correct)