Access VBA - DCount error

[princess12]

Hi

hope this is the right place for this topic. I am creating a staff attendance system through a listbox and a textbox which automates todays current date.

this is my dcount code

If (DCount("[StaffID]", _
"tblAttendance", _
"[StaffID] = " & Me.lstStaffAttendance & " And " & _
"[StaffFirstName] = " & Me.lstStaffAttendance & " AND " & _
"[AbsentDate] = #" & Me.txtTodaysDate & "#" & " or " & _
"[AttendDate] = #" & Me.txtTodaysDate & "#")) > 0 Then


MsgBox "This Employee Already Exists"
Cancel = True
Me!lstStaffAttendance.Undo


there is an error stating runtime error 3075, syntax error missing operator in query expression '[StaffID] = AND [StaffFirstName] = And [AbsentDate] = #01/02/2015# or [AttendDate] = #01/02/2015#. I know the error lies on staffID line because I removed other code lines and error started from there.

could someone please guide me, ive been working on this for so long I don't have a clue anymore. I think due to this error, the dcount does not prevent the duplication. this section of code below enable to add the information from listbox to table incase if you need it.

Set ctl = Me.lstStaffAttendance
Const StaffFirstName As String = 1
Const StaffSurname As String = 2
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!StaffID = ctl.Column(StaffID, varItem)
rs!StaffFirstName = ctl.Column(StaffFirstName, varItem)
rs!StaffSurname = ctl.Column(StaffSurname, varItem)
rs!WorkStarted = txtTimeIn
rs!WorkFinished = txtTimeOut
rs!AttendDate = txtTodaysDate
rs.Update
Next varItem
MsgBox "Done!"


thank you I really appreciate it

[June7]

Text type fields need apostrophe delimiters for parameters and date type need # delimiters.

You are missing the apostrophes for the StaffFirstName criteria.

Why are you duplicating staff names into the attendance table?

[princess12]

Thanks for your help

sorry am confused as am a novice still learning access.

I got first name and surname into attendance table because their names are in listbox so that whoever marks the attendance can see their names in the attendance table.
I have put the # on the date and I put the 'after the " in the firstname I still get the error

[June7]

"[StaffFirstName] = '" & Me.lstStaffAttendance & "' AND " & _

The # characters were already correct.

Should not duplicate names into attendance table.

No users should be looking directly at tables or queries, only forms and reports. You control what they see on forms and reports.

[princess12]

thank you

I followed your suggestions and removed the duplicates and I still get the same error, which occurs for the staffID

thank you

[June7]

The error is still on the DCount? Is StaffID a number type field?

Also, need to be careful about mixing AND and OR operators. Should have () around the date OR phrase, otherwise the conditional logic won't be what you expect.

[princess12]

yes the error is still on dcount the staff id is a number field in tblattendance and autonumber in staffdetails table. thank you

I don't understand about () I tried putting it like this

If (DCount("[StaffID]", _
"tblAttendance", _
"([StaffID] = " & Me.lstStaffAttendance & ") And " & _
"([AbsentDate] = #" & Me.txtTodaysDate & "#")) > 0 Then
MsgBox "This Employee Already Exists"
Cancel = True
Me!lstStaffAttendance.Undo

now the error states extra ) in query expression '([StaaffID] = ) AND ([AbsentDate] = #01/02/2015#'.

just want to say thank you for helping me, I really appreciate it.

[princess12]

I removed the extra () and the old syntax errors comes again back to staffID.

[princess12]

thank you for your help
I figured out the error, you were right I should be careful in using And and OR operators, that was the issue causing the error. I did a working system and thought I lost it found it now and compared it. Thanks a lot!