DCount type mismatch

[Penstar]

I get a runtime error 13 type mismatch, when I run the following:

VarExisting = DCount("[event_date]", "event", "[event_date]= " &
Format(Me.event_date, "\#mm\/dd\/yyyy#") And "[event_sessionid]= " &
Me.event_sessionid)


If however I count them separately, they work individually, i.e

varEvDate = DCount("[event_date]", "event", "[event_date]= " &
Format(Me.event_date, "\#mm\/dd\/yyyy#"))

varSession = DCount("[event_sessionID]", "event", "[event_sessionid]= " &
Me.event_sessionid)

I need to do count where both [event_date]=me.event_date And
[Event_sessionID]=me.event_sessionid

Any suggestions would be appreciated
Penny

 

[Allen Browne]

The AND needs to be inside the quotes.

To help you debug this, use a string variable for the criteria:

strWhere = "([event_date]= " & _
Format(Me.event_date, "\#mm\/dd\/yyyy#") & _
") And ([event_sessionid]= " & Me.event_sessionid & ")")
VarExisting = DCount("[event_date]", "event", strWhere)

Then if it fails, you can much more easily see what's wrong by opening the
Immediate Window (Ctrl+G), and enter:
Debug.Print strWhere

(BTW, hopefully you have checked event_date and sessionid for null.
Otherwise it will still fail.)

 

[Penstar]

Thanks Allen.

(BTW, hopefully you have checked event_date and sessionid for null.
Otherwise it will still fail.)

These two fields are not able to contain any null values if that is what you
mean, or do you mean the combination of both criteria can't be null?

strWhere = "([event_date]= " & _
Format(Me.event_date, "\#mm\/dd\/yyyy#") & _
") And ([event_sessionid]= " & Me.event_sessionid & ")")
VarExisting = DCount("[event_date]", "event", strWhere)

There appears to be a spare closing brackte, I assume it is the very last one.


Now when I run the code I get: Runtime error 2001: you cancelled the
previous operation. There is nothing prior to the code other than
declarations. What does this mean?

Debug.Print strWhere shows the correct criteria i.e.
([event_date]=#02/06/2008#) and ([event-sessionid]=1)

 

[Allen Browne]

The values could still be null in some cases, even if that's not permitted
in the table. Examples: in the new record row of a form, or the outer side
of a join in a query.

Error 2001 means that Access can't make sense of the criteria, so it canned
the lookup. Something is wrong in the string. Perhaps the field is named
[event date] with a space instead of an underscore, or [event-sessionid]
should have an underscore instead of a hyphen.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen.

(BTW, hopefully you have checked event_date and sessionid for null.
Otherwise it will still fail.)

These two fields are not able to contain any null values if that is what
you
mean, or do you mean the combination of both criteria can't be null?

strWhere = "([event_date]= " & _
Format(Me.event_date, "\#mm\/dd\/yyyy#") & _
") And ([event_sessionid]= " & Me.event_sessionid & ")")
VarExisting = DCount("[event_date]", "event", strWhere)

There appears to be a spare closing brackte, I assume it is the very last
one.


Now when I run the code I get: Runtime error 2001: you cancelled the
previous operation. There is nothing prior to the code other than
declarations. What does this mean?

Debug.Print strWhere shows the correct criteria i.e.
([event_date]=#02/06/2008#) and ([event-sessionid]=1)

 

[Penstar]

Well, well, well. Lesson learned "I will triple check for typos" (took me a
while though..). Let's hope it doesn't happen again.

Thank you for your help
Penny

The values could still be null in some cases, even if that's not permitted
in the table. Examples: in the new record row of a form, or the outer side
of a join in a query.

Error 2001 means that Access can't make sense of the criteria, so it canned
the lookup. Something is wrong in the string. Perhaps the field is named
[event date] with a space instead of an underscore, or [event-sessionid]
should have an underscore instead of a hyphen.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen.

(BTW, hopefully you have checked event_date and sessionid for null.
Otherwise it will still fail.)

These two fields are not able to contain any null values if that is what
you
mean, or do you mean the combination of both criteria can't be null?

strWhere = "([event_date]= " & _
Format(Me.event_date, "\#mm\/dd\/yyyy#") & _
") And ([event_sessionid]= " & Me.event_sessionid & ")")
VarExisting = DCount("[event_date]", "event", strWhere)

There appears to be a spare closing brackte, I assume it is the very last
one.


Now when I run the code I get: Runtime error 2001: you cancelled the
previous operation. There is nothing prior to the code other than
declarations. What does this mean?

Debug.Print strWhere shows the correct criteria i.e.
([event_date]=#02/06/2008#) and ([event-sessionid]=1)