DCount Help

[mattc66 via AccessMonster.com]

Hi All,

I want to lookup data in a query based on the CustomerID. When the user
enters a CustomerID in the form I want to use a DCount to go to the query
find the CustomerID that matches and if the Field OnHold = "H". From that I
am going to show a label on the Form that says "Account On Hold".

SID = Me.CustomerID.Value
stLink = "[CustomerID]=" & "'" & SID & "'"

If DCount("CustomerID", "qryARInvoiceSum", stLink, "Hold1") = "H" Then
Me.lblOnHold.Visible = True
End If

Obviously it doesn't work. Can anyone help on my syntax?

 

[Tom Lake]

Hi All,

I want to lookup data in a query based on the CustomerID. When the user
enters a CustomerID in the form I want to use a DCount to go to the query
find the CustomerID that matches and if the Field OnHold = "H". From that I
am going to show a label on the Form that says "Account On Hold".

SID = Me.CustomerID.Value
stLink = "[CustomerID]=" & "'" & SID & "'"

If DCount("CustomerID", "qryARInvoiceSum", stLink, "Hold1") = "H" Then
Me.lblOnHold.Visible = True
End If

Obviously it doesn't work. Can anyone help on my syntax?

You need DLookup, not DCount.

If DLookup("OnHold", "qryARInvoiceSum", "[CustomerID] = '" & [custidfield] & "'") =
"H" Then
Me.lblOnHold.Visible = True
End If

Replace custidfield with the name of the textbox on the form into which the user
types the CustomerID
to be matched.

Tom Lake

 

[PJFry]

I looks like you really need a Dlookup instead of a Dcount.

If DLookup("Hold1","qryARInvoiceSum","CustomerID = " & Me.CustomerID) = "H"
Then
Me.lblOnHold.Visible = True
End If

PJ

 

[mattc66 via AccessMonster.com]

Yes - you both are totally correct. Thanks for your help. That worked great..

Hi All,

[quoted text clipped - 11 lines]

Obviously it doesn't work. Can anyone help on my syntax?

You need DLookup, not DCount.

If DLookup("OnHold", "qryARInvoiceSum", "[CustomerID] = '" & [custidfield] & "'") =
"H" Then
Me.lblOnHold.Visible = True
End If

Replace custidfield with the name of the textbox on the form into which the user
types the CustomerID
to be matched.

Tom Lake