constructiing criteria for opening task form

Table for recording Tasks to be completed includes the following fields:

      Reaff_Reference, text field
      bln_Task_Complete,boolean

I have constructed the following to count the number of tasks for a particular Reaff_Reference and this works accurately:

count_Pending = DCount("Reaff_Reference", "tbl_Tasks", "Reaff_Reference='" & Me.Reaff_Creditor_ID & "'" & " And " & "bln_Task_Complete=" & False)

-----------------------
I now want to open a form which would permit me to enter completion dates for each task applicable to a particular Reaff_Creditor_ID.

However, I am having difficulty in authoring the docmd for the form.

DoCmd.OpenForm "frm_Pending_Tasks", acNormal, , "Reaff_Reference='" & Me.Reaff_Creditor_ID & "'" & " AND " & "bln_Task_Complete=" & "False" & "'"

I get the following message which is attached.




vba-error.docx
James_MoroneyAsked:
Who is Participating?
 
James_MoroneyConnect With a Mentor Author Commented:
I attempted to use the  suggested solutions from  capricorn1 and thewild  but would not process. I have concern that I did not copy the suggestions properly because i am having one hell of a time recognizing quotes, etc. on the screen.

I tried the suggested solution from Nick67 and also ran into problems. Using Snagit, I have copied the command I used and the resulting error message. I agree with Nick67 that using the qoutes is a nightmare. Thanks to each of you for helping me. I would like to award 500 points to each of you. Can I do this?
need-help.docx
0
 
Rey Obrero (Capricorn1)Commented:
try this

DoCmd.OpenForm "frm_Pending_Tasks", acNormal, , "Reaff_Reference='" & Me.Reaff_Creditor_ID & "' AND bln_Task_Complete=False"
0
 
thewildCommented:
You have a quote in excess at the end of your filter :
"Reaff_Reference='" & Me.Reaff_Creditor_ID & "'" & " AND " & "bln_Task_Complete=" & "False" & "'"
should be :
"Reaff_Reference='" & Me.Reaff_Creditor_ID & "'" & " AND " & "bln_Task_Complete=False"
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Nick67Connect With a Mentor Commented:
I have tortured myself too many times to try "" & me.SomeControlWithAString & ""
The quotes are a pain to debug.
String Variables need to be surrounded by quotes
Chr(34) is a quote mark

Try this
DoCmd.OpenForm "frm_Pending_Tasks", acNormal, , "Reaff_Reference= " & Chr(34)  & Me.Reaff_Creditor_ID & Chr(34) & " AND bln_Task_Complete  = False"
You can see immediately that your string variable has its need quotes much easier.
Both of the previous posters corrections will work too.

I just despise a gazillion quotes because they are so hard to debug.
Imagine if your filter had three or four string variables!
This hangs together as an easily identifable unit
Chr(34)  & Me.Reaff_Creditor_ID & Chr(34)
 
This, to me, not so much
'" & Me.Reaff_Creditor_ID & "'

Did you get them in the right order '" or wrong "'
And they switch '" at the beginning "' at the end
I'll pass on that showcase, Bob
I'll take, did I have & Chr(34) & both before and after, for $100, Alex

Let's not even talk about the possibility your string may contain apostrophes or quotes
0
 
Nick67Commented:
Just what is 'bln_Task_Complete'?
Given that it doesn't have ME in front of it, we all assumed it was a field from the query/table underlying your form.
Dumb question...does the form have bln_Task_Complete in its data source?
Even dumber question...what does ME refer to in  Me.Reaff_Creditor_ID?
It can't be "frm_Pending_Tasks" since you're opening that.
Is it frm_Reaff_Creditor?

If your record source for frm_Pending_Tasks is correct, you should be able to add 'bln_Task_Complete' as a control
Can you?
0
 
James_MoroneyAuthor Commented:
Nick,

Thank you so much. I have spent hours and hours trying different combinations. I did not include the bln_Task_Compete filed on the form to be opened. I read your comment and immediately realized the mistakei had made. Thanks again for your assistance. Your're the greatest!
0
 
Nick67Commented:
Not a problem.
Quotes drive me mad!
You should accept a comment that has the actual code you used as the solution.
And the original two posters suggestions would have worked too, so spliting the points is courteous.

Thanks

Nick67
0
 
James_MoroneyAuthor Commented:
I need help to finish up but the suggestions thus far have been very helpful. If Nick67 will send follow up to complete the answer I would be most grateful.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.