UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dcount Missing Operator Error Multiple Criteria, Access 2016    
 
   
Ceebaby
post Oct 18 2018, 05:34 PM
Post#1



Posts: 16
Joined: 17-October 18



Hi Everyone

I am a newbie building an employee leave database and I am working on the employee annual leave request form. I would like to have a message box pop up informing staff where they have entered a duplicate leave requests for leave start dates and leave end dates by mistake. I am using the following dcount function to count the number of duplicate records within the date range for the annual leave request. I am using it in if if statement in that if it chkdat >1 then msgbox "a leave request has already been entered for this date range etc. It will then undo the record and prompt the user to input in a non conflicting annual leave request.

However I am now getting a runtime 3075 error (syntax) error missing operator and I cannot find where I have gone wrong. Can anyone please help before I completely tear my hair out. Many thanks in advance for the help.

Dim chkdat as integer
dim strCriteria as string

strCriteria ="[allocationID]=" & Me![AllocationID]

chkDat = DCount("LeaveID", "EmployeeLeave_tbl", "[LeaveStart] <= " & Format([txLeaveEnd], "\#yyyy\-mm\-dd\#") & " And [LeaveEnd] >= " & Format([txLeaveStart], "\#yyyy\-mm\-dd\#") _
& "And " & strCriteria)


Go to the top of the page
 
RJD
post Oct 18 2018, 06:14 PM
Post#2


UtterAccess VIP
Posts: 9,731
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

Well, the first thing I noticed is that you are missing a space before the And in the last line ...

& "And " & strCriteria)

...should be ...

& " And " & strCriteria)

And I would have written that last part differently as well. I would simply use the definition you assigned to a variable instead of using the variable...

& " And [allocationID]=" & Me![AllocationID]

... or actually just strung the line out rather than using the extender ...

chkDat = DCount("LeaveID", "EmployeeLeave_tbl", "[LeaveStart] <= " & Format([txLeaveEnd], "\#yyyy\-mm\-dd\#") & " And [LeaveEnd] >= " & Format([txLeaveStart], "\#yyyy\-mm\-dd\#") & " And [allocationID]=" & Me![AllocationID])

I also always have a problem with European dates, and do not use them the way you have shown. So, correct the space and line and let's see if that works. If not, let us know and we can work on the dates...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Ceebaby
post Oct 19 2018, 04:10 AM
Post#3



Posts: 16
Joined: 17-October 18



Hi Joe

Thank you so much for kind and quick response. I'm in the UK and so decided to use iso date format. Your adjustments works thanks.gif

I had been working on it all day yesterday and could not see the wood for the trees. You have saved my hair notworthy.gif and sanity. I just hope I can be as helpful to others on the forum as my access skills increase.

Have a good day.

Best wishes

Ceebaby
Go to the top of the page
 
RJD
post Oct 19 2018, 06:43 AM
Post#4


UtterAccess VIP
Posts: 9,731
Joined: 25-October 10
From: Gulf South USA


Ceebaby: You are very welcome. Glad that solved the issue. thumbup.gif

Continued success with your project.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th July 2019 - 03:53 AM