Dcount Type Mismatch

mafhobb

Registered User.
Local time
Yesterday, 18:22
Joined
Feb 28, 2006
Messages
1,249
All right, I use Dcount to give me a count of records matching two criteria is a query:
Code:
Total = DCount("SubcallID", "Stats Customer Subcalls", "ContactID=" & CID And "CallDate>= (Date() - 30)")
ContactID is a string and CallDate is a Date (full date, with hour too). Total is defined as an integer.

I get "Type Mismatch when I run this in the immediate window.

Suggestions?

Thanks

mafhobb
 
Howzit

Try this.

If the criteria is a string you need to encase it in an apostophe

Code:
Total = DCount("SubcallID", "Stats Customer Subcalls", "ContactID='" & CID & "' And "CallDate>= (Date() - 30)")
 
Oops, I apologize, ContactID is a number

For reference, this code works fine:

Tot1 = DCount("callID", "calls", "ContactID=" & CID)

And this one also works elsewhere

Tot2 = DCount("callid", "calls", "CallDate>= (Date() - 30)")

mafhobb
 
Howzit

Ok try this then. You needed the & " AND... to join the two criteria together.

Code:
Total = DCount("SubcallID", "Stats Customer Subcalls", "ContactID=" & CID & " And CallDate>= (Date() - 30)")

Subsequent edit to remove extra "
 
Last edited:
You have a misplaced quote in there:

DCount("SubcallID", "Stats Customer Subcalls", "ContactID=" & CID & " And "CallDate>= (Date() - 30)")
 
Howzit

Quite right - subsequently amended :)
 
OK,
KiwiMan's Suggestion returns Run-Time Error 3075. Syntax error (missing operator) in query expression 'ContactID= And CallDate>=(Date()-30)'

PBaldy's suggestion returns: Compile error: Expected: list separator or) and it highlights CallDate
 
Sorry Kiwiman, the edited code still returns the same error...3075
 
Howzit

What is CID? Your post shows ContactID= AND where i would have expected (eg) Contactid=1 AND

Does this work on your current form?

Code:
Total = DCount("SubcallID", "Stats Customer Subcalls", "ContactID=" & CID)
 
Cid is short for a variable. This is defined as a number.

The idea is to match ContactID to that number (which is unique for each customer) and then also match the date field to limit results.

Mafhobb
 
Last edited:
Howzit

Ok - but does Cid return anything? It's just that your post above doesn't show anything for that, unless it was a typo ContactID= AND .

The dlookup syyntax looks fine how we have it.
Code:
msgbox cid
 
...I see...I wonder at what point it dissapeared as I literally copied and pasted your earlier code in the inmediate window...but, I may have made a mistake. Let me see.

Mafhobb
 
Hi Kiwiman, I got to run the test you suggested.

Code:
    Tot30 = DCount("SubcallID", "Stats Customer Subcalls", "ContactID=" & CID)
MsgBox Tot30

This returns "1" when run.

Code:
    Tot30 = DCount("SubcallID", "Stats Customer Subcalls", "ContactID=" & CID & " And CallDate>= (Date() - 30)")
MsgBox Tot30

This returns "Run-Time error '2471': The expression you entered as a query parameter produced this error: 'CallDate'"

Tot30 is an integer, ContactID is an integer and CallDate is a long date.

mafhobb
 
Howzit

I have tested this against a table and query (set up to have your parameters \ fields) and the dcount works just fine.

The only time I got your error was when I was running against a query where I had formatted the CallDate as part of the select syntax where I had to rename the calldate field in the query to calldate1 to get around circular referencing.

My select part looked like this at that point

Code:
SELECT StatsCustomerSubcalls.SubCallID, StatsCustomerSubcalls.ContactID, Format([CallDate],"dd mmmm yyyy") [B]AS CallDate1[/B]

The dcount came up with the error as there was no CallDate in the query now, only CallDate1. Changing CallDate to CallDate1 in the Dcount syntax enabled the dcount to work again.

I did not have this problem when formatting CallDate to LongDate in the field properties of the query, as this did not require a change of field name in the query.

Can you check the name in your query (if running against a query) to see if is CallDate or if you named it differently in the query
 
Howzit

Also check that this works (I suspect it will not work) in this instance (notwithstanding something similar has worked elsewhere) - we checked the one but not the other

Code:
Tot30 = DCount("SubcallID", "Stats Customer Subcalls", "CallDate>= (Date() - 30)")
MsgBox Tot30
 
Yeap. You are correct...the field's name on the query was misspelled!!!

AAAAAAhhhh

Sorry, sorry, sorry...

It is all working fine now.

mafhobb
 
Howzit

No problem. Glad it is all working.
 
Hi,

I am having a problem with a similar issue. I would like to search a customer table for forename and surname before adding to a table.

The problem i have is regardless of whether there is an entry in the fields or if the forename and surname doesnt exist, I am getting the msgbox to say that the information already exists when i know it definitely doesnt.

THis then stops me being able to add it the new data to the database. I have attached my code.

Code:
[SIZE=3][FONT=Times New Roman]Private Sub cmdAdd_Click()[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]Dim strSQLAdd As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim strSQLChk As String[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman] strSQLChk = DCount("*", "tblCustomers", "Forename=" & Chr(34) & txtForename & Chr(34) & "And Surname =  & Chr(34) & txtSurname & Chr(34)" > 0)[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman] strSQLAdd = "INSERT INTO tblCustomers(CustomerID, Title, Forename, MiddleName, Surname, DOB, REF1, Status, REF2)" & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     "VALUES ('" & Me.txtCustomerID & "','" & Me.txtTitle & "','" & Me.txtForename & "','" & Me.txtMiddleName & "','" & Me.txtSurname & "','" & Me.txtDOB & "','" & Me.txtREF1 & "','" & Me.txtStatus & "','" & Me.txtREF2 & "');"[/FONT][/SIZE]
 
 
[SIZE=3][FONT=Times New Roman]  If strSQLChk > 0 Then[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]     MsgBox "The information provided already exists in the database"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] Else[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman] DoCmd.RunSQL strSQLAdd[/FONT][/SIZE]

I know the strSQLAdd works, I just dont seem to be able to get the strSQLChk to work properly which stops the add kicking in.

Any help would be greatly appreciated
 
Your syntax is off. Try

strSQLChk = DCount("*", "tblCustomers", "Forename=" & Chr(34) & txtForename & Chr(34) & " And Surname = " & Chr(34) & txtSurname & Chr(34))
 

Users who are viewing this thread

Back
Top Bottom