Help with a DCount

[BukHix]

Can some one tell me what I am doing wrong in this Dcount?

    If DCount("[Jobnumber]", "CloseOutData", "[JobNumber]= '" _
        & Me!JobNumber & "'" > 1 And Me!SalesYear & "'") > 1 Then

I am getting a type mismatch error, both fields are text fields.

[This message has been edited by BukHix (edited 03-12-2002).]

 

[Dembrey]

There appears to be an erroneous '>1' in your criteria and maybe a field missing in the year expression. Is this something closer to what you want (I've assumed a missing field {YearField}).

If DCount("[Jobnumber]", "CloseOutData", "[JobNumber]= '" _
& Me!JobNumber & "' And [{YearField}] = '" & Me!SalesYear & "'") > 1 Then

Hope this helps.

 

[BukHix]

Thanks Dembrey, I am closer now but I am still have a problem somewhere. Look at this block of code:

    If DCount("[Jobnumber]", "CloseOutData", "[JobNumber]= '" & _
        Me!JobNumber & "' > 1 And [SalesYear]= '" & _
            Me!SalesYear & "'") > 1 Then
        MsgBox ("A match found")
    Else
        MsgBox ("No match was found")
    End If

I am just using the message box to test my code. For some reason I am getting no match was found no matter what I enter into the fields. Any idea of what I may be doing wrong.

 

[Dembrey]

Your criteria reads:

X = Y > 1 And A = B

The >1 after Me!JobNumber is wrong if you want your criteria to read:

([JobNumber] = Me!JobNumber)
AND
([SalesYear]= Me!SalesYear)

you need to remove this '>1'

HTH

 

[BukHix]

Ok I am lost now. I am trying to check and see if the job number exists with the same sales year.

So if:

X > X and Y > Y then a record was found.

But if:
X < X and Y < Y then no record was found

Thinking about it a little more I bet I should be using => because I am missing any that = 1 which is going to be the majority of them.

 

[Alexandre]

If IsNull(DLookup("[Jobnumber]", "CloseOutData", "([JobNumber]= '" _
           & Me!JobNumber & "') And ([SalesYear]= " _
           & Me!SalesYear & ")"))  Then

DCount would work, but you don t seem to need to really count all occurences, just to find if there is one.

Note that the above syntax supposes that SalesYear is a number (ex 1999) not a date.

Alex

[This message has been edited by Alexandre (edited 03-12-2002).]

 

[BukHix]

Alex, SalesYear is a text field so how should I adjust the quote mark's position? I tried placing them where I thought it made sense but recieved an error each time.

 

[Alexandre]

Around the string variable.
I put spaces here for easier visualization, but don t: ' " & YourVariable & " '

If IsNull(DLookup("[Jobnumber]", "CloseOutData", "([JobNumber]= '" _
           & Me!JobNumber & "') And ([SalesYear]= '" _
           & Me!SalesYear & "')"))  Then

Alex

[This message has been edited by Alexandre (edited 03-13-2002).]

 

[BukHix]

Thanks Dembrey and Alex it works now.

For any one who is interested this is how it turned out.

    If Not IsNull(DLookup("[Jobnumber]", "CloseOutData", "([JobNumber]= '" _
        & Me!JobNumber & "') And ([SalesYear]= '" _
            & Me!SalesYear & "')")) Then
        intQuestion = MsgBox("There is allready a Job Number" & vbCrLf & "entered " _
            & "in this year." & vbCrLf & vbCrLf & " Dulpicates are not allowed.", _
                vbOKOnly, "Warning")
        Exit Sub
    End If

[This message has been edited by BukHix (edited 03-13-2002).]