Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DCount Help

Posted on 2011-02-25
26
Medium Priority
?
474 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

971 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