DCount with string

[edojanssen]

I'm trying to find out if a record exists. The data to be found will be entered by a InputBox and is numeric.

If DCount("*", "tblFakturen", "Faktuurnr = " & sInputE & "") <> 0 Then

gives typemismatch

If DCount("*", "tblFakturen", "Faktuurnr = '" & sInputE & "'") <> 0 Then

gives type doesn't match criteriaexpression

Who can help me out?

Edo

 

[SOS]

Should be:

If DCount("*", "tblFakturen", "Faktuurnr = " & sInputE) <> 0 Then

 

[edojanssen]

Thanks SOS,

But your code gives "type doesn't match".

    Dim iJaarE As Integer
    Dim sJaarE As Integer
    Dim sInputE As String
    
InputBox:
    sInputE = InputBox("Geef het ErBo-boekstuknummer uit Exact op!", "ErBo-boekstuknummer")
    iJaarE = Format(Date, "yyyy")
    sJaarE = Right(CStr(iJaarE), 2)

    If sInputE Like sJaarE & "71*" Then
        If Len(sInputE) <> 8 Then
            GoTo InputBox
        Else
            If DCount("*", "tblFakturen", "Faktuurnr = " & sInputE) <> 0 Then
            'If DCount("*", "tblFakturen", [Faktuurnr] & " = '" & sInputE & "'") <> 0 Then
                MsgBox "Het ingevoerde boekstuknummer bestaat al", _
                "Kijk in Exact voor het juiste boekstuknummer"
                GoTo InputBox
            Else
                [Faktuurnr] = sInputE
                Me.ErBo.Value = True
                Me.Refresh
                DoCmd.GoToControl "Datum"
                GoTo NoErrorNummer
            End If
        End If
    Else
        GoTo InputBox
    End If

 

[SOS]

Is Faktuurnr a number or text? If text you would need to use quotes:

If DCount("*", "tblFakturen", "Faktuurnr = " & Chr(34) & sInputE & Chr(34)) <> 0 Then

 

[SOS]

Oh, and if it is NULL that could also cause a type mismatch problem. So, make sure that you don't have nulls there and if you do you will need to encapsulate the field with the NZ function.