2 Part Filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to filter a form on 2 parts one based on a zero in the AnalystKey
field and By Manager.

If I just use AnalystKey=0 - it works -
When I add the second part for Manager it says syntax error.

stLinkCriteria = "([AnalystKey] =0" & _
") AND ([Manager] =Allen Thompson"
 
If Manager is a Text field, it needs extra quotes around the name.

To put quotes inside quotes, you have to double them up so Access does not
think you have come to end of the string, e.g.:
"This string has a ""word"" in quotes"

Adding the ending bracket as well, you will get:
stLinkCriteria = "([AnalystKey] = 0" & _
") AND ([Manager] = ""Allen Thompson"")"
 
-----Original Message-----
I am trying to filter a form on 2 parts one based on a zero in the AnalystKey
field and By Manager.

If I just use AnalystKey=0 - it works -
When I add the second part for Manager it says syntax error.

stLinkCriteria = "([AnalystKey] =0" & _
") AND ([Manager] =Allen Thompson"
.
Click to expand...
Hi Allen,
when using numeric values as criteria no delimitors are
required.

when using date and/or time value, you must include # as a
dilimitor. Dates must also be in US date format. For
example:
stCriteria="([DateField]=#9/20/2004#)"

When using string values, you must use a quote delimitor
to identify a string within a string.

Using your example:
stLinkCriteria = "([AnalystKey] =0" & _
") AND ([Manager] ='Allen Thompson')"

Notice the single quotes delimiting the string value:
Allen Thompson.

However, it gets more interesting when the contained
string includes one or more quotes; both single and double
quotes. This may happen when a name, such as French names
include an apostraphy. If this is a possibility use the
following as a solution.

stLinkCriteria = "([AnalystKey] =0" & _
") AND ([Manager] =" _
& Chr(34) _
& "Allen Thompson" _
& Chr(34) & ")"

ascii chr(34) seems to handle none or multiple single
quotes.

Luck
Jonathan
 
Hey Allen -

Appreciate your help - I tried using this -

stLinkCriteria = "([AnalystKey] = 0" & _
") AND ([Manager] = ""Allen Thompson"")"

When I run it - Says

"The OpenForm Action was Cancelled"




Allen Browne said:
If Manager is a Text field, it needs extra quotes around the name.

To put quotes inside quotes, you have to double them up so Access does not
think you have come to end of the string, e.g.:
"This string has a ""word"" in quotes"

Adding the ending bracket as well, you will get:
stLinkCriteria = "([AnalystKey] = 0" & _
") AND ([Manager] = ""Allen Thompson"")"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen Thompson said:
I am trying to filter a form on 2 parts one based on a zero in the
AnalystKey
field and By Manager.

If I just use AnalystKey=0 - it works -
When I add the second part for Manager it says syntax error.

stLinkCriteria = "([AnalystKey] =0" & _
") AND ([Manager] =Allen Thompson"
Click to expand...
Click to expand...
 
Open the form without the criteria.

Open the Immediate Window (Ctrl+G)

Set the form's Filter property by typing this into the Immediate window:
Forms![PutYourFormNameHere].Filter = "([AnalystKey] = 0) AND ([Manager]
= ""Allen Thompson"")"
and then
Forms![PutYourFormNameHere].FilterOn = True

If that also fails, then either:
- the AnalystKey field is missing from the form's RecordSource,
- the Manager field is missing, or
- the AnalystKey field is not a Number type, or
- the Manager field is not a Text type.

For example, Manager may be a Number field, and you have used the dreaded
lookup wizard so that it displays the name, even though it stores the
number.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen Thompson said:
Hey Allen -

Appreciate your help - I tried using this -

stLinkCriteria = "([AnalystKey] = 0" & _
") AND ([Manager] = ""Allen Thompson"")"

When I run it - Says

"The OpenForm Action was Cancelled"




Allen Browne said:
If Manager is a Text field, it needs extra quotes around the name.

To put quotes inside quotes, you have to double them up so Access does
not
think you have come to end of the string, e.g.:
"This string has a ""word"" in quotes"

Adding the ending bracket as well, you will get:
stLinkCriteria = "([AnalystKey] = 0" & _
") AND ([Manager] = ""Allen Thompson"")"


message
I am trying to filter a form on 2 parts one based on a zero in the
AnalystKey
field and By Manager.

If I just use AnalystKey=0 - it works -
When I add the second part for Manager it says syntax error.

stLinkCriteria = "([AnalystKey] =0" & _
") AND ([Manager] =Allen Thompson"
Click to expand...
Click to expand...
Click to expand...
 
Back
Top