DCount Help

Experts,

Can you tell me what I am doing wrong here?  Cant get DCount to work for the life of me.  I want to count records in tblInventory where [FormCode] = Forms![frmTicketEntry]![cboFormCode], [RcvdDate] Is Null, and [Control#] = Forms![frmTicketEntry]![txtTicketNo]

Thanks,
Cody
InvCount = DCount("[FormCode]", tblInventory, "[FormCode] = Forms![frmTicketEntry]![cboFormCode]" And "[RcvdDate]" Is Null And "[Control#] = Forms![frmTicketEntry]![txtTicketNo]")

Open in new window

Cody VanceSr. Analyst - ERPAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
us e this codes


Private Sub txtTicketNo_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyReturn Then
        KeyCode = 0
        Me.cboEmployeeName.SetFocus
        CheckInventory
        txtTicketNo.Value = ""
        DoCmd.GoToControl "[txtTicketNo]"
    End If
End Sub
0
 
Rey Obrero (Capricorn1)Commented:
if [FormCode] and  [Control#] are Number Data type

InvCount = DCount("[FormCode]", tblInventory, "[FormCode] = " & Forms![frmTicketEntry]![cboFormCode] & " And [RcvdDate] Is Null And [Control#] =" & Forms![frmTicketEntry]![txtTicketNo])


if [FormCode] and  [Control#] are Text Data type use


InvCount = DCount("[FormCode]", tblInventory, "[FormCode] = '" & Forms![frmTicketEntry]![cboFormCode] & "' And [RcvdDate] Is Null And [Control#] ='" & Forms![frmTicketEntry]![txtTicketNo] & "'")
0
 
Patrick MatthewsCommented:
Assuming that FormCode is text and Control# is numeric...

InvCount = DCount("[FormCode]", "tblInventory", "[FormCode] = '" & Forms![frmTicketEntry]![cboFormCode] & " And [RcvdDate] Is Null And [Control#] = " & Forms![frmTicketEntry]![txtTicketNo])

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Cody VanceSr. Analyst - ERPAuthor Commented:
What If FormCode is Text Data and Control# is number data?

Thanks,
Cody
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Nevermind just saw Matthews response, testing now.
0
 
Rey Obrero (Capricorn1)Commented:
use this

InvCount = DCount("[FormCode]", tblInventory, "[FormCode] = '" & Forms![frmTicketEntry]![cboFormCode] & "' And [RcvdDate] Is Null And [Control#] =" & Forms![frmTicketEntry]![txtTicketNo])

0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Capricorn,

On your code I received Error1.

Matthews,

On your code I received Error2.

Thanks,
Cody
error1.jpg
error2.jpg
0
 
Rey Obrero (Capricorn1)Commented:
do you have values in Forms![frmTicketEntry]![txtTicketNo] ?

test this

copy and paste

InvCount = DCount("[FormCode]", tblInventory, "[FormCode] = " & chr(34) & Forms![frmTicketEntry]![cboFormCode] & chr(34) & " And [RcvdDate] Is Null And [Control#] =" & Forms![frmTicketEntry]![txtTicketNo])
0
 
Rey Obrero (Capricorn1)Commented:
test this

copy and paste

InvCount = DCount("[FormCode]", "tblInventory", "[FormCode] = " & chr(34) & Forms![frmTicketEntry]![cboFormCode] & chr(34) & " And [RcvdDate] Is Null And [Control#] =" & Forms![frmTicketEntry]![txtTicketNo])
0
 
SheilsCommented:
Try:

InvCount = DCount("[FormCode]", "tblInventory", "[FormCode] ='" & Forms![frmTicketEntry]![cboFormCode] & "' And   [RcvdDate]'" & Is Null & "' And [Control#] ='" & Forms![frmTicketEntry]![txtTicketNo] & "'")
0
 
SheilsCommented:
opps! looks like I've missed the boat
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Capricorn,

Yes there is a value in txtTicketNo.  Your first repost produced the same error.  Your second one produced attached error.

Cody-
error3.jpg
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Would it be easier to use Count function to count records rather than DCount?
0
 
Rey Obrero (Capricorn1)Commented:
test this


InvCount = DCount("*", "tblInventory", "[FormCode] = " & chr(34) & Forms![frmTicketEntry]![cboFormCode] & chr(34) & " And [RcvdDate] Is Null And [Control#] =" & Forms![frmTicketEntry]![txtTicketNo])
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Same Error.
0
 
Rey Obrero (Capricorn1)Commented:
test this one too

InvCount = DCount("*", "tblInventory","[Control#] =" & Forms![frmTicketEntry]![txtTicketNo] & " And [RcvdDate] Is Null And [FormCode] = " & chr(34) & Forms![frmTicketEntry]![cboFormCode] & chr(34))
0
 
SheilsCommented:
The seem to be quotation mark missing in capricorn's code.

Try mine. Also see this website for the syntax of DLookup http://www.mvps.org/access/general/gen0018.htm

Use the same syntax to set up the criteria of yur DLookup.

 DCount("FieldName", "TableName", "Criteria1 = " & Forms!FormName!Control1  _
             & " AND Criteria2 = '" & Forms!FormName!Control2 & "'" _
            & " AND Criteria3 ='" & Forms!FormName!Control3 & "'")
0
 
Rey Obrero (Capricorn1)Commented:
if you are still getting the error, upload a copy of the db...

sb9, what quotation mark missing are you talking about?
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Same Error Capricorn.  Attached is database.  Update Query isnt working either...

Cody-
Drink-Ticket-Tracking.mdb
0
 
Rey Obrero (Capricorn1)Commented:
you are calling the Function before the afterupdate of the textbox txtTicketNo
so the code is seeing the textbox txtTicketNo as Null

what are you trying to do?
0
 
SheilsCommented:
Capricorn, the last criteria in post No 34984365 did not have criteria but the chr(34) in later post take care of that. They were not up when I started to write my comments.

Cody, which form is the dcount on
0
 
TinTombStoneCommented:
Dcount seems to work OK, you just need to recalc the form before you call CheckInventory

Private Sub txtTicketNo_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyReturn Then
        KeyCode = 0
        Me.Recalc
        CheckInventory
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
sb9,

It is in txtTicket On Key DOwn on frmTicketEntry

Capricorn,

I am trying to check and see in tblInventory if the Ticket # they have entered has already been redeemed by checking if RcvdDate is filled already based off of the FormCode and Control#.  If it is, it needs to display MsgBox saying it has already been redeemed or has not been added to inventory.  If it is not, continue and run qryUpdateInv to fill that record with the information from frmTicketEntry.

Hope that makes sense.

Cody-
0
 
Rey Obrero (Capricorn1)Commented:
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Amazing!  Thank you so very much!!

Cody-
0
 
Helen FeddemaCommented:
Make variables of the appropriate data types, and set them from the controls (possibly after checking for valid values), then use the variables in your DCount expression.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.