Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DCount Help

Posted on 2011-02-25
26
Medium Priority
?
473 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

719 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