?
Solved

DCount Help

Posted on 2011-02-25
26
Medium Priority
?
475 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:Cody Vance
  • 10
  • 9
  • 4
  • +3
26 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34984233
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 34984248
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
 

Author Comment

by:Cody Vance
ID: 34984250
What If FormCode is Text Data and Control# is number data?

Thanks,
Cody
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Cody Vance
ID: 34984255
Nevermind just saw Matthews response, testing now.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34984257
use this

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

0
 

Author Comment

by:Cody Vance
ID: 34984289
Capricorn,

On your code I received Error1.

Matthews,

On your code I received Error2.

Thanks,
Cody
error1.jpg
error2.jpg
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34984308
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34984314
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
 
LVL 16

Expert Comment

by:Sheils
ID: 34984315
Try:

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

Expert Comment

by:Sheils
ID: 34984331
opps! looks like I've missed the boat
0
 

Author Comment

by:Cody Vance
ID: 34984336
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
 

Author Comment

by:Cody Vance
ID: 34984349
Would it be easier to use Count function to count records rather than DCount?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34984365
test this


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

Author Comment

by:Cody Vance
ID: 34984379
Same Error.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34984387
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
 
LVL 16

Expert Comment

by:Sheils
ID: 34984405
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34984526
if you are still getting the error, upload a copy of the db...

sb9, what quotation mark missing are you talking about?
0
 

Author Comment

by:Cody Vance
ID: 34984552
Same Error Capricorn.  Attached is database.  Update Query isnt working either...

Cody-
Drink-Ticket-Tracking.mdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34984636
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
 
LVL 16

Expert Comment

by:Sheils
ID: 34984652
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
 
LVL 6

Expert Comment

by:TinTombStone
ID: 34984669
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
 

Author Comment

by:Cody Vance
ID: 34984688
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 34984720
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34984735
0
 

Author Comment

by:Cody Vance
ID: 34984737
Amazing!  Thank you so very much!!

Cody-
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 34984741
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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question