Error # 3075

[lmcc007]

I added the below code to count records, but when a company has no record I get Error Number 3075.

Public Function JobApplCount(ByVal strJobsActivityType As Integer) As Integer

JobApplCount = DCount("[ActivityID]", "Event", _
"[CompanyID] = " & Forms!frmViewJobAppl!txtCompanyID & " And " & _
"[ActivityID] = " & strJobsActivityType)​

End Function​

Below is the error message:

An error was encountered

Description: Syntax error (missing operator) in query expression
‘[CompanyID] = And [ActivityID] = 33’.
Error Number: 3075​

Any suggestions?

 

[pbaldy]

Based on the error, there's no value in the form control. If that's a possibility, test for that first and return zero (or whatever) and exit the function before the DCount.

 

[lmcc007]

Based on the error, there's no value in the form control. If that's a possibility, test for that first and return zero (or whatever) and exit the function before the DCount.

That's correct.

How shall I do that?

 

[pbaldy]

With an If/Then block before the existing code. Give it a try, post the revised code if you get stuck.

 

[lmcc007]

With an If/Then block before the existing code. Give it a try, post the revised code if you get stuck.

Can't get pass this:

If IsNotNull("[ActivityID]") Then
​

Turns Red.

Now when I think about it ActivityID will not be blank--it's the criteria for selecting the record.

 

[pbaldy]

I'd expect you to use the same method you used here:

http://www.access-programmers.co.uk/forums/showthread.php?t=194039

and you'd want to test this:

Forms!frmViewJobAppl!txtCompanyID

 

[vbaInet]

Just wondering if ActivityID in this "[ActivityID] = " & strJobsActivityType) isn't a string since the name of the variable starts with str?

 

[lmcc007]

Just wondering if ActivityID in this "[ActivityID] = " & strJobsActivityType) isn't a string since the name of the variable starts with str?

It's a string. ActivityID is pk to a lookup table. The criteria for ActivityID is 33 which for ApplSubmitted.

I guess you can tell I copied the code (tried following another example I found--unfortunately, I have missplaced that example).

 

[lmcc007]

I'd expect you to use the same method you used here:

http://www.access-programmers.co.uk/forums/showthread.php?t=194039

and you'd want to test this:

Forms!frmViewJobAppl!txtCompanyID

Forgot all about this--let me go back and read to see what's going on.

 

[vbaInet]

You will still need to test for Null/zero-length string like Paul mentioned anyway.

What about CompanyID?

 

[lmcc007]

You will still need to test for Null/zero-length string like Paul mentioned anyway.

What about CompanyID?

CompanyiD is the PK from Company table.

 

[vbaInet]

I meant is it of type String or Number?