StLinkCriteria Problem with entry of Last (or partial) name

[SherryScrapDog]

I have a names table, with Last, First, Middle and Title fields (for
genealogy) where I want to enter a name or partial name and have a form come
up with Last names beginning with what was entered and ending with the last
record that begins with what was entered. I believe using 'Like' works well
for this, but am having trouble getting the code to work. I get a compile and
syntax error on the following attempt:
stLinkCriteria = "select * from Master where [Last] like " & Chr(34) &
Me![Name] & Char(34) "*' order by [Last], [First], [Middle], [Title]"
With this attempt, I get all of the records (note: Text4 is the value of
Entry&"zz"):
strLinkCriteria = "select * from Master where [Last] between " ' &
Me![Entry] & '" and "' & Me![Text4]'"
I'm not good at VBA yet and these are just two of several attempts I have
made by looking for other examples. (I'm trying to use Chr(34) instead of '
because I have names, such as O'Hara, with ' in them.)
This has a sub-form (for the results) within the Search form. Any help?
Thanks in advance if you can help! Thanks, Sherry

 

[Stuart McCall]

stLinkCriteria = "select * from Master where [Last] like " & Chr(34) &
Me![Name] & Char(34) "*' order by [Last], [First], [Middle], [Title]"
With this attempt, I get all of the records

Char(34) will not work. Chr(34)

You are missing an ampersand following your Char(34). Also you have a
surplus apostrophe. And the asterisk is wrongly placed. The whole thing
ought to read:

stLinkCriteria = "select * from Master where [Last] like " & Chr(34) &
Me![Name] & "*" & Chr(34) & " order by [Last], [First], [Middle], [Title]"

 

[SherryScrapDog]

Hi Stuart,
Thanks for this info! I knew I was getting it wrong, and couldn't figure
out what to try next. I've put this code in and I've gotten farther than I
have before. When I put 'C' in the name box and click the search, here is an
error message I get:

Syntax error. in query expression 'select * from Master where '[Last] like
"c*" order by [Last], [First], [Middle], [Title]'.

Any ideas? Also, another question that I don't think has anything to do
with this error, but I have an index for these 4 fields (called NameKey);
should I do something different than the Order By I'm doing now? Would that
cause this error? I did try taking the Order By condition off and got the
same error, so I don't think it has anything to do with it.

Sorry to bug you again, but if you have any ideas, I'd love to hear them. I
love to learn this stuff.
thanks, Sherry

stLinkCriteria = "select * from Master where [Last] like " & Chr(34) &
Me![Name] & Char(34) "*' order by [Last], [First], [Middle], [Title]"
With this attempt, I get all of the records

Char(34) will not work. Chr(34)

You are missing an ampersand following your Char(34). Also you have a
surplus apostrophe. And the asterisk is wrongly placed. The whole thing
ought to read:

stLinkCriteria = "select * from Master where [Last] like " & Chr(34) &
Me![Name] & "*" & Chr(34) & " order by [Last], [First], [Middle], [Title]"

 

[SherryScrapDog]

Hi Stuart,
Please disregard my last plea for help. It now works. I kept messing with
it. I actually just took out the 'Select' and 'Order By' portions of your
code and now it works. Thanks so much! Sherry

Hi Stuart,
Thanks for this info! I knew I was getting it wrong, and couldn't figure
out what to try next. I've put this code in and I've gotten farther than I
have before. When I put 'C' in the name box and click the search, here is an
error message I get:

Syntax error. in query expression 'select * from Master where '[Last] like
"c*" order by [Last], [First], [Middle], [Title]'.

Any ideas? Also, another question that I don't think has anything to do
with this error, but I have an index for these 4 fields (called NameKey);
should I do something different than the Order By I'm doing now? Would that
cause this error? I did try taking the Order By condition off and got the
same error, so I don't think it has anything to do with it.

Sorry to bug you again, but if you have any ideas, I'd love to hear them. I
love to learn this stuff.
thanks, Sherry

stLinkCriteria = "select * from Master where [Last] like " & Chr(34) &
Me![Name] & Char(34) "*' order by [Last], [First], [Middle], [Title]"
With this attempt, I get all of the records

Char(34) will not work. Chr(34)

You are missing an ampersand following your Char(34). Also you have a
surplus apostrophe. And the asterisk is wrongly placed. The whole thing
ought to read:

stLinkCriteria = "select * from Master where [Last] like " & Chr(34) &
Me![Name] & "*" & Chr(34) & " order by [Last], [First], [Middle], [Title]"

 

[SherryScrapDog]

Hi Stuart,
Please disregard my last plea for help. It now works. I kept messing with
it. I actually just took out the 'Select' and 'Order By' portions of your
code and now it works. Thanks so much! Sherry

Hi Stuart,
Thanks for this info! I knew I was getting it wrong, and couldn't figure
out what to try next. I've put this code in and I've gotten farther than I
have before. When I put 'C' in the name box and click the search, here is an
error message I get:

Syntax error. in query expression 'select * from Master where '[Last] like
"c*" order by [Last], [First], [Middle], [Title]'.

Any ideas? Also, another question that I don't think has anything to do
with this error, but I have an index for these 4 fields (called NameKey);
should I do something different than the Order By I'm doing now? Would that
cause this error? I did try taking the Order By condition off and got the
same error, so I don't think it has anything to do with it.

Sorry to bug you again, but if you have any ideas, I'd love to hear them. I
love to learn this stuff.
thanks, Sherry

stLinkCriteria = "select * from Master where [Last] like " & Chr(34) &
Me![Name] & Char(34) "*' order by [Last], [First], [Middle], [Title]"
With this attempt, I get all of the records

Char(34) will not work. Chr(34)

You are missing an ampersand following your Char(34). Also you have a
surplus apostrophe. And the asterisk is wrongly placed. The whole thing
ought to read:

stLinkCriteria = "select * from Master where [Last] like " & Chr(34) &
Me![Name] & "*" & Chr(34) & " order by [Last], [First], [Middle], [Title]"

 

[Stuart McCall]

Hi Stuart,
Please disregard my last plea for help. It now works. I kept messing
with
it. I actually just took out the 'Select' and 'Order By' portions of your
code and now it works. Thanks so much! Sherry

Hi Stuart,
Thanks for this info! I knew I was getting it wrong, and couldn't figure
out what to try next. I've put this code in and I've gotten farther than
I
have before. When I put 'C' in the name box and click the search, here
is an
error message I get:

Syntax error. in query expression 'select * from Master where '[Last]
like
"c*" order by [Last], [First], [Middle], [Title]'.

Any ideas? Also, another question that I don't think has anything to do
with this error, but I have an index for these 4 fields (called
NameKey);
should I do something different than the Order By I'm doing now? Would
that
cause this error? I did try taking the Order By condition off and got
the
same error, so I don't think it has anything to do with it.

Sorry to bug you again, but if you have any ideas, I'd love to hear them.
I
love to learn this stuff.
thanks, Sherry

<SNIP>
stLinkCriteria = "select * from Master where [Last] like " & Chr(34)
&
Me![Name] & Char(34) "*' order by [Last], [First], [Middle], [Title]"
With this attempt, I get all of the records

Char(34) will not work. Chr(34)

You are missing an ampersand following your Char(34). Also you have a
surplus apostrophe. And the asterisk is wrongly placed. The whole thing
ought to read:

stLinkCriteria = "select * from Master where [Last] like " & Chr(34) &
Me![Name] & "*" & Chr(34) & " order by [Last], [First], [Middle],
[Title]"

Glad you got it working.

A word to the wise: don't name a field or control 'Name'. This is a reserved
word in Access and will get you into trouble sooner or later. If a control,
rename it to (eg) 'txtName'. If a field then (eg) 'ClientName'.

 

[SherryScrapDog]

Thanks for the info on the Name field. I actually had changed it in the
meantime just in case it was and was causing my problem, but didn't know for
sure until this. I will certainly remember this! Sherry

Hi Stuart,
Thanks for this info! I knew I was getting it wrong, and couldn't figure
out what to try next. I've put this code in and I've gotten farther than I
have before. When I put 'C' in the name box and click the search, here is an
error message I get:

Syntax error. in query expression 'select * from Master where '[Last] like
"c*" order by [Last], [First], [Middle], [Title]'.

Any ideas? Also, another question that I don't think has anything to do
with this error, but I have an index for these 4 fields (called NameKey);
should I do something different than the Order By I'm doing now? Would that
cause this error? I did try taking the Order By condition off and got the
same error, so I don't think it has anything to do with it.

Sorry to bug you again, but if you have any ideas, I'd love to hear them. I
love to learn this stuff.
thanks, Sherry

stLinkCriteria = "select * from Master where [Last] like " & Chr(34) &
Me![Name] & Char(34) "*' order by [Last], [First], [Middle], [Title]"
With this attempt, I get all of the records

Char(34) will not work. Chr(34)

You are missing an ampersand following your Char(34). Also you have a
surplus apostrophe. And the asterisk is wrongly placed. The whole thing
ought to read:

stLinkCriteria = "select * from Master where [Last] like " & Chr(34) &
Me![Name] & "*" & Chr(34) & " order by [Last], [First], [Middle], [Title]"