By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,715 Members | 1,219 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,715 IT Pros & Developers. It's quick & easy.

Trouble with AND

P: n/a
I need help getting this syntax to work.
I want to open a form to a specific record based on dual criteria.
Both fields are text fields as is the input.
I get an error message "type mismatch"

stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "'" AND
"[ MacPacNumber]=" & "'" & Me![ txtMacPacExisting] & "'"
HELP,
Rick

Nov 13 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a

"2D Rick" <rb*******@compuserve.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...
I need help getting this syntax to work.
I want to open a form to a specific record based on dual criteria.
Both fields are text fields as is the input.
I get an error message "type mismatch"

stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "'" AND
"[ MacPacNumber]=" & "'" & Me![ txtMacPacExisting] & "'"
HELP,
Rick


What are the data types in the tables?
Nov 13 '05 #2

P: n/a
delimiters
text: single quote, 'some text'
dates: #10/12/04#
numbers: nothing

Nov 13 '05 #3

P: n/a
You're using single quotes as delimiters. Does any of the data have an
apostrophe in it? What are the data types of the two fields in the
underlying table?

This part is probably the problem:
& "'" AND "[ MacPacNumber]="

Should be

& "' AND [MacPacNumber]="

The AND needs to be inside the quotes. It is part of the text being passed
to strLinkCriteria. The &'s are the "ands" for the concatenation.
Just to nit-pick a little.

]=" & "'" &
You don't need the extra set of quotes and & (but it shouldn't hurt
anything), this could be just
]='" &

"[ MacPacNumber]=", ![ txtMacPacExisting]
There is a space in front of the M and the t.

--
Wayne Morgan
MS Access MVP
"2D Rick" <rb*******@compuserve.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...
I need help getting this syntax to work.
I want to open a form to a specific record based on dual criteria.
Both fields are text fields as is the input.
I get an error message "type mismatch"

stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "'" AND
"[ MacPacNumber]=" & "'" & Me![ txtMacPacExisting] & "'"

Nov 13 '05 #4

P: n/a
You're using single quotes as delimiters. Does any of the data have an
apostrophe in it? What are the data types of the two fields in the
underlying table?

This part is probably the problem:
& "'" AND "[ MacPacNumber]="

Should be

& "' AND [MacPacNumber]="

The AND needs to be inside the quotes. It is part of the text being passed
to strLinkCriteria. The &'s are the "ands" for the concatenation.
Just to nit-pick a little.

]=" & "'" &
You don't need the extra set of quotes and & (but it shouldn't hurt
anything), this could be just
]='" &

"[ MacPacNumber]=", ![ txtMacPacExisting]
There is a space in front of the M and the t.

--
Wayne Morgan
MS Access MVP
"2D Rick" <rb*******@compuserve.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...
I need help getting this syntax to work.
I want to open a form to a specific record based on dual criteria.
Both fields are text fields as is the input.
I get an error message "type mismatch"

stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "'" AND
"[ MacPacNumber]=" & "'" & Me![ txtMacPacExisting] & "'"

Nov 13 '05 #5

P: n/a
stLinkCriteria = "[PartNumber]= ' " & Me![txtPartNumber] & " ' " _
& " AND [ MacPacNumber]= ' " & Me![ txtMacPacExisting] & " ' "

or

stLinkCriteria = "[PartNumber]= ' " & Me![txtPartNumber] & " ' AND [
MacPacNumber]= ' " & Me![ txtMacPacExisting] & " ' "

HTH,
Debbie
"2D Rick" <rb*******@compuserve.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...
|I need help getting this syntax to work.
| I want to open a form to a specific record based on dual criteria.
| Both fields are text fields as is the input.
| I get an error message "type mismatch"
|
|
|
| stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "'" AND
| "[ MacPacNumber]=" & "'" & Me![ txtMacPacExisting] & "'"
|
|
| HELP,
| Rick

Nov 13 '05 #6

P: n/a
stLinkCriteria = "[PartNumber]= ' " & Me![txtPartNumber] & " ' " _
& " AND [ MacPacNumber]= ' " & Me![ txtMacPacExisting] & " ' "

or

stLinkCriteria = "[PartNumber]= ' " & Me![txtPartNumber] & " ' AND [
MacPacNumber]= ' " & Me![ txtMacPacExisting] & " ' "

HTH,
Debbie
"2D Rick" <rb*******@compuserve.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...
|I need help getting this syntax to work.
| I want to open a form to a specific record based on dual criteria.
| Both fields are text fields as is the input.
| I get an error message "type mismatch"
|
|
|
| stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "'" AND
| "[ MacPacNumber]=" & "'" & Me![ txtMacPacExisting] & "'"
|
|
| HELP,
| Rick

Nov 13 '05 #7

P: n/a
pi********@hotmail.com wrote:
dates: #10/12/04#


Don't forget to mention dates in this format require to be in US format.

--
This sig left intentionally blank
Nov 13 '05 #8

P: n/a
I tried DebbieG's syntax and it didn't work.
The data will never contain an apostrophe and the table fields type are
both "text".
There should not be a space in front of the M and the t, My typo error.

This is my feeble attempt:
stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "'" And
"[MacPacNumber]=" & "'" & Me![txtMacPacExisting] & "'"
Wayne Morgan wrote:
You're using single quotes as delimiters. Does any of the data have an apostrophe in it? What are the data types of the two fields in the
underlying table?

This part is probably the problem:
& "'" AND "[ MacPacNumber]="

Should be

& "' AND [MacPacNumber]="

The AND needs to be inside the quotes. It is part of the text being passed to strLinkCriteria. The &'s are the "ands" for the concatenation.
Just to nit-pick a little.

]=" & "'" &
You don't need the extra set of quotes and & (but it shouldn't hurt
anything), this could be just
]='" &

"[ MacPacNumber]=", ![ txtMacPacExisting]
There is a space in front of the M and the t.

--
Wayne Morgan
MS Access MVP
"2D Rick" <rb*******@compuserve.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...
I need help getting this syntax to work.
I want to open a form to a specific record based on dual criteria.
Both fields are text fields as is the input.
I get an error message "type mismatch"

stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "'" AND "[ MacPacNumber]=" & "'" & Me![ txtMacPacExisting] & "'"


Nov 13 '05 #9

P: n/a
> stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "'" And
"[MacPacNumber]=" & "'" & Me![txtMacPacExisting] & "'"
You still don't have the word AND inside the quotes. It should be part of
the string being passed to stLinkCriteria. Change the above to:

stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "' And
[MacPacNumber]=" & "'" & Me![txtMacPacExisting] & "'"

I have removed the double quote on each side of the word AND.

It could be (but doesn't need to be) further shortened to:

stLinkCriteria = "[PartNumber]='" & Me![txtPartNumber] & "' And
[MacPacNumber]='" & Me![txtMacPacExisting] & "'"
--
Wayne Morgan
MS Access MVP
"2D Rick" <rb*******@compuserve.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...I tried DebbieG's syntax and it didn't work.
The data will never contain an apostrophe and the table fields type are
both "text".
There should not be a space in front of the M and the t, My typo error.

This is my feeble attempt:
stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "'" And
"[MacPacNumber]=" & "'" & Me![txtMacPacExisting] & "'"

Nov 13 '05 #10

P: n/a
Thanks Wayne, that did the trick.
Rick

Wayne Morgan wrote:
stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "'" And "[MacPacNumber]=" & "'" & Me![txtMacPacExisting] & "'"
You still don't have the word AND inside the quotes. It should be

part of the string being passed to stLinkCriteria. Change the above to:

stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "' And
[MacPacNumber]=" & "'" & Me![txtMacPacExisting] & "'"

I have removed the double quote on each side of the word AND.

It could be (but doesn't need to be) further shortened to:

stLinkCriteria = "[PartNumber]='" & Me![txtPartNumber] & "' And
[MacPacNumber]='" & Me![txtMacPacExisting] & "'"
--
Wayne Morgan
MS Access MVP
"2D Rick" <rb*******@compuserve.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...
I tried DebbieG's syntax and it didn't work.
The data will never contain an apostrophe and the table fields type are both "text".
There should not be a space in front of the M and the t, My typo error.
This is my feeble attempt:
stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "'" And "[MacPacNumber]=" & "'" & Me![txtMacPacExisting] & "'"


Nov 13 '05 #11

P: n/a
I forgot to tell you to remove the spaces between the ' and ". I put the
spaces in so that you could see the difference between the ' and the ".

Sorry for the confusion,
Debbie
"2D Rick" <rb*******@compuserve.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...
|I tried DebbieG's syntax and it didn't work.
| The data will never contain an apostrophe and the table fields type are
| both "text".
| There should not be a space in front of the M and the t, My typo error.
|
| This is my feeble attempt:
| stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "'" And
| "[MacPacNumber]=" & "'" & Me![txtMacPacExisting] & "'"
|
|
| Wayne Morgan wrote:
| > You're using single quotes as delimiters. Does any of the data have
| an
| > apostrophe in it? What are the data types of the two fields in the
| > underlying table?
| >
| > This part is probably the problem:
| > & "'" AND "[ MacPacNumber]="
| >
| > Should be
| >
| > & "' AND [MacPacNumber]="
| >
| > The AND needs to be inside the quotes. It is part of the text being
| passed
| > to strLinkCriteria. The &'s are the "ands" for the concatenation.
| >
| >
| > Just to nit-pick a little.
| >
| > ]=" & "'" &
| > You don't need the extra set of quotes and & (but it shouldn't hurt
| > anything), this could be just
| > ]='" &
| >
| > "[ MacPacNumber]=", ![ txtMacPacExisting]
| > There is a space in front of the M and the t.
| >
| > --
| > Wayne Morgan
| > MS Access MVP
| >
| >
| > "2D Rick" <rb*******@compuserve.com> wrote in message
| > news:11*********************@l41g2000cwc.googlegro ups.com...
| > >I need help getting this syntax to work.
| > > I want to open a form to a specific record based on dual criteria.
| > > Both fields are text fields as is the input.
| > > I get an error message "type mismatch"
| > >
| > > stLinkCriteria = "[PartNumber]=" & "'" & Me![txtPartNumber] & "'"
| AND
| > > "[ MacPacNumber]=" & "'" & Me![ txtMacPacExisting] & "'"
|

Nov 13 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.