Error 3075

[lmcc007]

When the below code runs and there is no data, I get Error #3075 -- Syntax error (missing operator) in query expression '[CompanyID] = And [ActivityID] = 1'.

Public Function JobTotal(ByVal strActivity As Integer) As Integer

JobTotal = DCount("[ActivityID]", "Event", "[CompanyID]= " & Forms!frmViewJobList!txtCompanyID & " And " & _
"[ActivityID]= " & CStr(strActivity))

End Function

How can I resolve this when there are no record (no activity yet for this record) to total?

 

[genomon]

Trap it with Nz() with a value that will never appear in the table (I used zero):

JobTotal = DCount("[ActivityID]", "Event", "Nz([CompanyID],0)= " & Forms!frmViewJobList!txtCompanyID & " And " & _
        "[ActivityID]= " & CStr(strActivity))

Ni neart go cur le cheile.

 

[lmcc007]

I just tried:

If (Forms!frmViewJobList!txtCompanyID Is Null) Then
JobTotal = 0
Else
JobTotal = DCount("[ActivityID]", "Event", "[CompanyID]= " & _
Forms!frmViewJobList!txtCompanyID & " And " & _
"[ActivityID]= " & CStr(strActivity))
End If

But now I get Error 424: Object Required

 

[genomon]

Oops. How about

JobTotal = DCount("[ActivityID]", "Event", "[CompanyID]= " & Nz(Forms!frmViewJobList!txtCompanyID, 0) & " And " & _
        "[ActivityID]= " & CStr(strActivity))

Ni neart go cur le cheile.

 

[genomon]

Actually just noticed that CompanyID and ActivityID look like text fields. If they are not numeric you need to enclose them in single quotes:

JobTotal = DCount("[ActivityID]", "Event", "[CompanyID]= '" & Nz(Forms!frmViewJobList!txtCompanyID, 0) & "' And " & _
"[ActivityID]= '" & CStr(strActivity) & "'")

Ni neart go cur le cheile.

 

[lmcc007]

They are numeric.

 

[lmcc007]

I think that did it, it seems to be working.

Thanks!!!

 

[genomon]

Glad it worked, but FYI you are converting ActivityID to a string here: CStr(strActivity)

Ni neart go cur le cheile.

 

[lmcc007]

Okay. I copied this code and was trying to make it work for me.

How shall I write it correctly?

 

[genomon]

I think that did it, it seems to be working.

I thought it was working? What else can I try and help with?
Is it still throwing an error?

Ni neart go cur le cheile.

 

[lmcc007]

this statement: ...but FYI you are converting ActivityID to a string here: CStr(strActivity)

 

[lmcc007]

Meaning, shall I do it like:

JobTotal = DCount("[ActivityID]", "Event", "[CompanyID]= '" & Nz(Forms!frmViewJobList!txtCompanyID, 0) & "' And " & _
"[ActivityID]= '" & strActivity & "'")

or is it best I rename strActivity to intActivity?

 

[genomon]

Didn't you write the code? Sorry, you have me completely confused now. I was only pointing out that you said the fields were numeric, but you are explicitly converting one of them to a string in your code. Have a look at the table in design view and let me know what the datatypes are for the two fields. If they are numeric, be sure and tell me what kind (Integer, Long, etc). If we need to use any of the Coercive (conversion) functions in the lookup, I will add them & repost. Otherwise, if it works (thought you said it did), just use it.

Ni neart go cur le cheile.

 

[lmcc007]

I copied and pasted the code and tried to revise it to make it work for me. I thought I understood it, but apparently I do not since I tried to convert a number to a string. I am still learning .

Anyway, here are the two fields:

Field Name = CompanyID
Data Type = Number
Field Size = Long Integer

and

Field Name = ActivityID
Data Type = Number
Field Size = Long Integer

Thank you for your time.

 

[genomon]

Voilá!

JobTotal = DCount("[ActivityID]", "Event", "[CompanyID]= " & CLng(Nz(Forms!frmViewJobList!txtCompanyID, 0)) & " And " & _
        "[ActivityID]= " & CLng(strActivity))

If that doesn't work, beat it with a stick

Ni neart go cur le cheile.

 

[lmcc007]

It works in one form, but not the other. I copied and pasted the same code in another form, but I get the following error message:

Error # -2147352567
Description: You entered an expression that has no value

This is the code I pasted for the other form:


Public Function ActivityTotal(ByVal strActivity As Integer) As Integer

ActivityTotal = DCount("[ActivityID]", "Event", "[CompanyID] = " & CLng(Nz(Forms!frmViewEventHistoryList!txtCompanyID, 0)) & " And " & _
"[ActivityID] = " & CLng(strActivity))

End Function

 

[genomon]

You probably need to change the field references here to whatever is used on the other form:

Forms!frmViewEventHistoryList!txtCompanyID

and

strActivity



Ni neart go cur le cheile.

 

[lmcc007]

Okay, I redid everything again and I get the same error message for frmViewEventHistoryList, but frmViewJobList works. I don't see anything that's different.

 

[lmcc007]

Here the example from

http://msdn.microsoft.com/en-us/library/aa211471(office.11).aspx

The following function returns the number of orders shipped to a specified country after a specified ship date. The domain is an Orders table.

Public Function OrdersCount(ByVal strCountry As String, _
ByVal dteShipDate As Date) As Integer

OrdersCount = DCount("[ShippedDate]", "Orders", _
"[ShipCountry] = '" & strCountry & _
"' AND [ShippedDate] > #" & dteShipDate & "#")
End Function

To call the function, use the following line of code in the Immediate window:

:OrdersCount "UK", #1/1/96#

 

[lmcc007]

Hey genomon,


I did:

If Not IsNull(Forms!frmViewJobList!ActivityType) Then...

in my form on the On Load Event. That did it.

Thanks for your help!