Syntax error in switchboard

[bajanJedi]

Hi all,

I have a command button on my switchboard that was working before and now it's not. The button is supposed to open a report showing books that have been overdue for 1 week. When I click the button I get this error:

Syntax error (missing operator) in query expression 'count[UserID]'.

The button houses the following code:

'OPENS THE REPORT FOR OVERALL BOOKS DUE/OVERDUE
Private Sub cmdOverallBooksDue_Click()
On Error GoTo Err_cmdOverallBooksDue_Click

Dim stDocName As String

If DLookup("count[UserID]", "[qryOverallBooksDue]", "criteria") < 1 Then
MsgBox "No books overdue."
Exit Sub
Else
DoCmd.OpenReport "rptOverallBooksDue"
End If

Exit_cmdOverallBooksDue_Click:
Exit Sub

Err_cmdOverallBooksDue_Click:
MsgBox Err.Description
Resume Exit_cmdOverallBooksDue_Click

End Sub

Any thoughts?

bajanJedi

 

[Blorf]

In the following
DLookup("count[UserID]",
"[qryOverallBooksDue]", "criteria")

Is Count[UserID] an actual fieldname?

Also, if you have no Criteria, then eliminate the criteria from the dlookup function.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[Golom]

It looks like you've lost some parentheses in your "DLookUp" statement

This

count[UserID]

should be

count([UserID])

 

[bajanJedi]

ChaZ, UserID is the actual field, I tried removing the "criteria", but it brings up the same error msg.


Golom, I tried that and it produced another error:
You canceled the previous operation.

 

[randy700]

Try this....
if dlookup("userid","qryoverallbooksdue","criteria < 1) then

 

[bajanJedi]

Nope, that still doesn't work.

 

[randy700]

Can you post the sql for the query?

 

[bajanJedi]

OK, I saw somthing about SQL, SQL Pass through and this popped up. I wasn't sure if this is what you meant.


SELECT tblUsers.UserID, tblUsers.Title, tblUsers.Name, tblUsers.Address, tblUsers.Parish, tblUsers.[PhoneNo(H)], tblUsers.[PhoneNo(W)], tblUsers.CellNo, tblUsers.Email, tblBookInfo.BDSNo, tblBookInfo.Title, tblUserLoans.DateBorrowed, tblUserLoans.DateDue
FROM tblUsers INNER JOIN (tblBookInfo INNER JOIN tblUserLoans ON (tblBookInfo.BDSNo = tblUserLoans.BDSNo) AND (tblBookInfo.BDSNo = tblUserLoans.BDSNo)) ON tblUsers.UserID = tblUserLoans.UserID
WHERE (((DateDiff('ww',[DateDue],Date()))=1));

 

[randy700]

If I'm reading your query right, this might work....

if isnull(dlookup("userid","qryOverallBooksDue)) Then
msgbox "No books overdue."
exit sub
end if

 

[bajanJedi]

ok, yes i put that in and now it's working... much thanks


Lisa