Solved

DCount Help

Posted on 2011-02-25
26
459 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:codyvance1
  • 10
  • 9
  • 4
  • +3
26 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 92

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:codyvance1
ID: 34984250
What If FormCode is Text Data and Control# is number data?

Thanks,
Cody
0
 

Author Comment

by:codyvance1
ID: 34984255
Nevermind just saw Matthews response, testing now.
0
 
LVL 119

Expert Comment

by:Rey Obrero
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:codyvance1
ID: 34984289
Capricorn,

On your code I received Error1.

Matthews,

On your code I received Error2.

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

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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:codyvance1
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:codyvance1
ID: 34984349
Would it be easier to use Count function to count records rather than DCount?
0
 
LVL 119

Expert Comment

by:Rey Obrero
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:codyvance1
ID: 34984379
Same Error.
0
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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:codyvance1
ID: 34984552
Same Error Capricorn.  Attached is database.  Update Query isnt working either...

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

Expert Comment

by:Rey Obrero
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:codyvance1
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 119

Accepted Solution

by:
Rey Obrero earned 500 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 119

Expert Comment

by:Rey Obrero
ID: 34984735
0
 

Author Comment

by:codyvance1
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

746 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now