Trouble with AND

[2D Rick]

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

[Mike Turco]

"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?

[pietlinden]

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

[Wayne Morgan]

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] & "'"

[Wayne Morgan]

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] & "'"

[DebbieG]

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

[DebbieG]

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

[Trevor Best]

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

[2D Rick]

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] & "'"

[Wayne Morgan]

> 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] & "'"

[2D Rick]

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] & "'"

[DebbieG]

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] & "'"
|