• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

constructiing criteria for opening task form

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

      Reaff_Reference, text field

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.

2 Solutions
Rey Obrero (Capricorn1)Commented:
try this

DoCmd.OpenForm "frm_Pending_Tasks", acNormal, , "Reaff_Reference='" & Me.Reaff_Creditor_ID & "' AND bln_Task_Complete=False"
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"
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

James_MoroneyAuthor 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?
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?
James_MoroneyAuthor Commented:

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!
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.


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.
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now