Search for a record based on one of two fields

[Guest]

Basically..

I have a text box which is the search data (a part number) & a button that
says start the search (using the info in the text field)

I have a whole bunch of records, and because of my companies semi-adoption
of two systems, we have inadvertantly ended up with two part number
references per actual part - both have a seperate field in the record, but
what I would like to be able to do is that should a search not reveal the
part number in the first field, to search the second and then bring forth the
relevant detail

ie a record has a fldPartNumber1 (example data: Part Number) and a
fldPartNumber2 (example data: Number, Part)

I would like the user to be able to enter and search against either part
reference by entering the search into a single box, hitting 'go' and the
correct record being returned.

Fortunatly there is no possibility of duplications in either field, both
next to each other, and throughout the entire recordset......

thanks for any insight

 

[Allen Browne]

So you want to search to find a matche in either field:

Dim strWhere As String
strWhere = "([fldPartNumber1] = """ & Me.[txtSearch & _
""") OR ([fldPartNumber2] = """ & Me.[txtSearch & """)"

You can then set the form's Filter to this string and FilterOn = True, or
you could FindFirst in the form's RecordsetClone and set the Bookmark.

 

[Guest]

Hi Allen

Thanks for this - unfortunatly I cannot get it to work as it claims a
bracket is missing.

I have managed to get the compilier to accept the following (it uses my
field names)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmTechnicalPartNumberView"

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & "'")
Or _
("[altec_maxitem]=" & "'" & Me![txtPartNumber] & "'")

DoCmd.OpenForm stDocName, , , stLinkCriteria


however when run it says 'type mismatch' and nothing else. any further
insight you can add would be appreciated.

regards

Luke

So you want to search to find a matche in either field:

Dim strWhere As String
strWhere = "([fldPartNumber1] = """ & Me.[txtSearch & _
""") OR ([fldPartNumber2] = """ & Me.[txtSearch & """)"

You can then set the form's Filter to this string and FilterOn = True, or
you could FindFirst in the form's RecordsetClone and set the Bookmark.

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

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

Basically..

I have a text box which is the search data (a part number) & a button that
says start the search (using the info in the text field)

I have a whole bunch of records, and because of my companies semi-adoption
of two systems, we have inadvertantly ended up with two part number
references per actual part - both have a seperate field in the record, but
what I would like to be able to do is that should a search not reveal the
part number in the first field, to search the second and then bring forth
the
relevant detail

ie a record has a fldPartNumber1 (example data: Part Number) and a
fldPartNumber2 (example data: Number, Part)

I would like the user to be able to enter and search against either part
reference by entering the search into a single box, hitting 'go' and the
correct record being returned.

Fortunatly there is no possibility of duplications in either field, both
next to each other, and throughout the entire recordset......

thanks for any insight

 

[Allen Browne]

The quotes and line break are incorrect. Try:

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & _
"'"") Or ([altec_maxitem] = """ & Me![txtPartNumber] & """)"

You can add:
Debug.Print stLinkCriteria
to see the results in the Immediate window (Ctrl+G) after it runs.

If either field is actually a Number field (not a Text field), lose the
extra quotes.

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

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

Hi Allen

Thanks for this - unfortunatly I cannot get it to work as it claims a
bracket is missing.

I have managed to get the compilier to accept the following (it uses my
field names)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmTechnicalPartNumberView"

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & "'")
Or _
("[altec_maxitem]=" & "'" & Me![txtPartNumber] & "'")

DoCmd.OpenForm stDocName, , , stLinkCriteria


however when run it says 'type mismatch' and nothing else. any further
insight you can add would be appreciated.

regards

Luke

So you want to search to find a matche in either field:

Dim strWhere As String
strWhere = "([fldPartNumber1] = """ & Me.[txtSearch & _
""") OR ([fldPartNumber2] = """ & Me.[txtSearch & """)"

You can then set the form's Filter to this string and FilterOn = True, or
you could FindFirst in the form's RecordsetClone and set the Bookmark.

Basically..

I have a text box which is the search data (a part number) & a button
that
says start the search (using the info in the text field)

I have a whole bunch of records, and because of my companies
semi-adoption
of two systems, we have inadvertantly ended up with two part number
references per actual part - both have a seperate field in the record,
but
what I would like to be able to do is that should a search not reveal
the
part number in the first field, to search the second and then bring
forth
the
relevant detail

ie a record has a fldPartNumber1 (example data: Part Number) and a
fldPartNumber2 (example data: Number, Part)

I would like the user to be able to enter and search against either
part
reference by entering the search into a single box, hitting 'go' and
the
correct record being returned.

Fortunatly there is no possibility of duplications in either field,
both
next to each other, and throughout the entire recordset......

thanks for any insight

 

[Guest]

Afraid it still does not work - it would appear to be a syntax error (the
text comes up in red and the compiler reports a syntax error)

it is most perplexing!

thanks for your help so far

Hi Allen

Thanks for this - unfortunatly I cannot get it to work as it claims a
bracket is missing.

I have managed to get the compilier to accept the following (it uses my
field names)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmTechnicalPartNumberView"

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & "'")
Or _
("[altec_maxitem]=" & "'" & Me![txtPartNumber] & "'")

DoCmd.OpenForm stDocName, , , stLinkCriteria


however when run it says 'type mismatch' and nothing else. any further
insight you can add would be appreciated.

regards

Luke

So you want to search to find a matche in either field:

Dim strWhere As String
strWhere = "([fldPartNumber1] = """ & Me.[txtSearch & _
""") OR ([fldPartNumber2] = """ & Me.[txtSearch & """)"

You can then set the form's Filter to this string and FilterOn = True, or
you could FindFirst in the form's RecordsetClone and set the Bookmark.

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

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

Basically..

I have a text box which is the search data (a part number) & a button that
says start the search (using the info in the text field)

I have a whole bunch of records, and because of my companies semi-adoption
of two systems, we have inadvertantly ended up with two part number
references per actual part - both have a seperate field in the record, but
what I would like to be able to do is that should a search not reveal the
part number in the first field, to search the second and then bring forth
the
relevant detail

ie a record has a fldPartNumber1 (example data: Part Number) and a
fldPartNumber2 (example data: Number, Part)

I would like the user to be able to enter and search against either part
reference by entering the search into a single box, hitting 'go' and the
correct record being returned.

Fortunatly there is no possibility of duplications in either field, both
next to each other, and throughout the entire recordset......

thanks for any insight

 

[Allen Browne]

Luke, you may need to understand how to build this string.

In VBA, you have opening and closing quotes on the string. You can't just
have a quote in the middle of the string, because the compiler will think
that's the end of the string, and doesn't know what to do with the rest of
the line. The convention is to double-up the embedded quotes. So, to get:
This string has a "word" in quotes.
you code:
"This string has a ""word"" in quotes."
If the string ends with the quotes, it looks like 3 in a row:
"This string ends in ""quotes""."

You must put quotes around the literal values for a *Text* field (not a
number or date field) in criteria.

Hopefully you can build your string from there. Build it in 2 parts. When
both are working, join them with AND.

To debug what is wrong with your string, add the line:
Debug.Print strWhere
Then when it fails, look at what it printed in the Immediate WIndow (Ctrl+G)
to see what's wrong.

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

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

Afraid it still does not work - it would appear to be a syntax error (the
text comes up in red and the compiler reports a syntax error)

it is most perplexing!

thanks for your help so far

Hi Allen

Thanks for this - unfortunatly I cannot get it to work as it claims a
bracket is missing.

I have managed to get the compilier to accept the following (it uses my
field names)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmTechnicalPartNumberView"

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] &
"'")
Or _
("[altec_maxitem]=" & "'" & Me![txtPartNumber] & "'")

DoCmd.OpenForm stDocName, , , stLinkCriteria


however when run it says 'type mismatch' and nothing else. any further
insight you can add would be appreciated.

regards

Luke

So you want to search to find a matche in either field:

Dim strWhere As String
strWhere = "([fldPartNumber1] = """ & Me.[txtSearch & _
""") OR ([fldPartNumber2] = """ & Me.[txtSearch & """)"

You can then set the form's Filter to this string and FilterOn = True,
or
you could FindFirst in the form's RecordsetClone and set the Bookmark.

message
Basically..

I have a text box which is the search data (a part number) & a button
that
says start the search (using the info in the text field)

I have a whole bunch of records, and because of my companies
semi-adoption
of two systems, we have inadvertantly ended up with two part number
references per actual part - both have a seperate field in the
record, but
what I would like to be able to do is that should a search not reveal
the
part number in the first field, to search the second and then bring
forth
the
relevant detail

ie a record has a fldPartNumber1 (example data: Part Number) and a
fldPartNumber2 (example data: Number, Part)

I would like the user to be able to enter and search against either
part
reference by entering the search into a single box, hitting 'go' and
the
correct record being returned.

Fortunatly there is no possibility of duplications in either field,
both
next to each other, and throughout the entire recordset......

thanks for any insight

 

[Guest]

Hi Allen

I am getting there and now understand your point about the quotes on the
string, however it would appear that """string""" does not work but
"'"string"'" does - or am i missing something (ie apostrophies are working
not exclamation marks)

Now as per your suggestion i have got each line to work, but joining them as
a valid OR statement seems to be throwing a wobbler

ie stLinkCriteria = "[altec_switchno]=" & "'" & Me![txtPartNumber] & "'"

works fine, but

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & _
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

comes back with a "type mismatch"

Good Luck with The Ashes -its getting tight!

Afraid it still does not work - it would appear to be a syntax error (the
text comes up in red and the compiler reports a syntax error)

it is most perplexing!

thanks for your help so far

Hi Allen

Thanks for this - unfortunatly I cannot get it to work as it claims a
bracket is missing.

I have managed to get the compilier to accept the following (it uses my
field names)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmTechnicalPartNumberView"

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & "'")
Or _
("[altec_maxitem]=" & "'" & Me![txtPartNumber] & "'")

DoCmd.OpenForm stDocName, , , stLinkCriteria


however when run it says 'type mismatch' and nothing else. any further
insight you can add would be appreciated.

regards

Luke

So you want to search to find a matche in either field:

Dim strWhere As String
strWhere = "([fldPartNumber1] = """ & Me.[txtSearch & _
""") OR ([fldPartNumber2] = """ & Me.[txtSearch & """)"

You can then set the form's Filter to this string and FilterOn = True, or
you could FindFirst in the form's RecordsetClone and set the Bookmark.

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

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

Basically..

I have a text box which is the search data (a part number) & a button that
says start the search (using the info in the text field)

I have a whole bunch of records, and because of my companies semi-adoption
of two systems, we have inadvertantly ended up with two part number
references per actual part - both have a seperate field in the record, but
what I would like to be able to do is that should a search not reveal the
part number in the first field, to search the second and then bring forth
the
relevant detail

ie a record has a fldPartNumber1 (example data: Part Number) and a
fldPartNumber2 (example data: Number, Part)

I would like the user to be able to enter and search against either part
reference by entering the search into a single box, hitting 'go' and the
correct record being returned.

Fortunatly there is no possibility of duplications in either field, both
next to each other, and throughout the entire recordset......

thanks for any insight

 

[Allen Browne]

See my last post about
Debug.Print stLinkCriteria
to get Access to show you the output, so you can see what's wrong.

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

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

Hi Allen

I am getting there and now understand your point about the quotes on the
string, however it would appear that """string""" does not work but
"'"string"'" does - or am i missing something (ie apostrophies are working
not exclamation marks)

Now as per your suggestion i have got each line to work, but joining them
as
a valid OR statement seems to be throwing a wobbler

ie stLinkCriteria = "[altec_switchno]=" & "'" & Me![txtPartNumber] &
"'"

works fine, but

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & _
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

comes back with a "type mismatch"

Good Luck with The Ashes -its getting tight!

Afraid it still does not work - it would appear to be a syntax error (the
text comes up in red and the compiler reports a syntax error)

it is most perplexing!

thanks for your help so far

Hi Allen

Thanks for this - unfortunatly I cannot get it to work as it claims a
bracket is missing.

I have managed to get the compilier to accept the following (it uses my
field names)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmTechnicalPartNumberView"

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] &
"'")
Or _
("[altec_maxitem]=" & "'" & Me![txtPartNumber] & "'")

DoCmd.OpenForm stDocName, , , stLinkCriteria


however when run it says 'type mismatch' and nothing else. any further
insight you can add would be appreciated.

regards

Luke

:

So you want to search to find a matche in either field:

Dim strWhere As String
strWhere = "([fldPartNumber1] = """ & Me.[txtSearch & _
""") OR ([fldPartNumber2] = """ & Me.[txtSearch & """)"

You can then set the form's Filter to this string and FilterOn =
True, or
you could FindFirst in the form's RecordsetClone and set the
Bookmark.

message
Basically..

I have a text box which is the search data (a part number) & a
button that
says start the search (using the info in the text field)

I have a whole bunch of records, and because of my companies
semi-adoption
of two systems, we have inadvertantly ended up with two part number
references per actual part - both have a seperate field in the
record, but
what I would like to be able to do is that should a search not
reveal the
part number in the first field, to search the second and then bring
forth
the
relevant detail

ie a record has a fldPartNumber1 (example data: Part Number) and a
fldPartNumber2 (example data: Number, Part)

I would like the user to be able to enter and search against either
part
reference by entering the search into a single box, hitting 'go'
and the
correct record being returned.

Fortunatly there is no possibility of duplications in either field,
both
next to each other, and throughout the entire recordset......

 

[Guest]

Absolutley - and I can understand why you have suggested it, however the code
I have written as per the previous post is terminating the run of the program
before the debug command. I found an article about using 'On Error Resume
Next', which has enabled the program to run past the point of my dodgy code,
and has left the Immediate Window blank - this I was not expecting, so I know
it is the Or statement that is causing the problem (it 'half' works if I rem
out the Or aspect of the code)

the problem is that it is not reporting anything back to the Immediate
Window to enable me to deduce where i am going wrong

See my last post about
Debug.Print stLinkCriteria
to get Access to show you the output, so you can see what's wrong.

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

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

Hi Allen

I am getting there and now understand your point about the quotes on the
string, however it would appear that """string""" does not work but
"'"string"'" does - or am i missing something (ie apostrophies are working
not exclamation marks)

Now as per your suggestion i have got each line to work, but joining them
as
a valid OR statement seems to be throwing a wobbler

ie stLinkCriteria = "[altec_switchno]=" & "'" & Me![txtPartNumber] &
"'"

works fine, but

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & _
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

comes back with a "type mismatch"

Good Luck with The Ashes -its getting tight!

Afraid it still does not work - it would appear to be a syntax error (the
text comes up in red and the compiler reports a syntax error)

it is most perplexing!

thanks for your help so far


:

Hi Allen

Thanks for this - unfortunatly I cannot get it to work as it claims a
bracket is missing.

I have managed to get the compilier to accept the following (it uses my
field names)

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmTechnicalPartNumberView"

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] &
"'")
Or _
("[altec_maxitem]=" & "'" & Me![txtPartNumber] & "'")

DoCmd.OpenForm stDocName, , , stLinkCriteria


however when run it says 'type mismatch' and nothing else. any further
insight you can add would be appreciated.

regards

Luke

:

So you want to search to find a matche in either field:

Dim strWhere As String
strWhere = "([fldPartNumber1] = """ & Me.[txtSearch & _
""") OR ([fldPartNumber2] = """ & Me.[txtSearch & """)"

You can then set the form's Filter to this string and FilterOn =
True, or
you could FindFirst in the form's RecordsetClone and set the
Bookmark.

message
Basically..

I have a text box which is the search data (a part number) & a
button that
says start the search (using the info in the text field)

I have a whole bunch of records, and because of my companies
semi-adoption
of two systems, we have inadvertantly ended up with two part number
references per actual part - both have a seperate field in the
record, but
what I would like to be able to do is that should a search not
reveal the
part number in the first field, to search the second and then bring
forth
the
relevant detail

ie a record has a fldPartNumber1 (example data: Part Number) and a
fldPartNumber2 (example data: Number, Part)

I would like the user to be able to enter and search against either
part
reference by entering the search into a single box, hitting 'go'
and the
correct record being returned.

Fortunatly there is no possibility of duplications in either field,
both
next to each other, and throughout the entire recordset......

 

[Allen Browne]

If you can't get the program to stop on error (due to your error handlers),
then just add this line to your code:
Stop

Then the code will stop at that line when it executes, and you can press F8
to step through each line, using the Immediate Window to query the
variables, see what is wrong, and even trace where the error handler is
jumping to.

 

[Guest]

you learn a little more every day - thanks for that, a useful tip

however it demonstrates my code is still duff as it returns into the
immediate window nothing other than a line-return regardless of which part
number type is used. again if i 'rem out the part after the Or statement it
works fine, and likewise if i use the [altec_maxitem] field it works fine.

from this i know each statement on its own works as desired, the problem
being to get them to work together - the code is as follows, and it really
has me stumped!

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & _
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

thanks for your time on this so far

 

[Allen Browne]

So did you trace where the code went, using F8?

YOu really need to debug this for yourself, but your string is not the same
as suggested in the earlier post:

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & _
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

Hint: & "" is adding nothing to the string.

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

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

you learn a little more every day - thanks for that, a useful tip

however it demonstrates my code is still duff as it returns into the
immediate window nothing other than a line-return regardless of which part
number type is used. again if i 'rem out the part after the Or statement
it
works fine, and likewise if i use the [altec_maxitem] field it works fine.

from this i know each statement on its own works as desired, the problem
being to get them to work together - the code is as follows, and it really
has me stumped!

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & _
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

thanks for your time on this so far

If you can't get the program to stop on error (due to your error
handlers),
then just add this line to your code:
Stop

Then the code will stop at that line when it executes, and you can press
F8
to step through each line, using the Immediate Window to query the
variables, see what is wrong, and even trace where the error handler is
jumping to.

 

[Guest]

Hi Allen

well I am learning something anyway, thanks for the tips

your original suggestion for code came up with an error which i guess may be
because of the quotes on the end of the bracket and the use of a single
aphostophy as the second character on the second line

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & _
"'"") Or ([altec_maxitem] = """ & Me![txtPartNumber] & """)"

I have been basing my thing around this code - what stumps me the most is
that the working syntax of:

stLinkCriteria = ("[altec_maxitem] = """ & Me![txtPartNumber] & "")

returns [altec_maxitem] = "sw200-1"

or the working syntax of

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & "")

returns [altec_switchno] = "sw200-1"

(Both the above use txtPartNumber = SW200-1):

both work as individual lines, but when cojined, they return a single empty
value (line-return in Immediate window).

this code is:

stLinkCriteria = ("[altec_maxitem] = """ & Me.[txtPartNumber] & """") _
Or ("[altec_switchno] = """ & Me.[txtPartNumber] & """")


I think my problem may be down to getting the Or statement to work effectivly.

Now I am lost!

So did you trace where the code went, using F8?

YOu really need to debug this for yourself, but your string is not the same
as suggested in the earlier post:

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & _
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

Hint: & "" is adding nothing to the string.

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

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

you learn a little more every day - thanks for that, a useful tip

however it demonstrates my code is still duff as it returns into the
immediate window nothing other than a line-return regardless of which part
number type is used. again if i 'rem out the part after the Or statement
it
works fine, and likewise if i use the [altec_maxitem] field it works fine.

from this i know each statement on its own works as desired, the problem
being to get them to work together - the code is as follows, and it really
has me stumped!

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & _
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

thanks for your time on this so far

If you can't get the program to stop on error (due to your error
handlers),
then just add this line to your code:
Stop

Then the code will stop at that line when it executes, and you can press
F8
to step through each line, using the Immediate Window to query the
variables, see what is wrong, and even trace where the error handler is
jumping to.

Absolutley - and I can understand why you have suggested it, however
the
code
I have written as per the previous post is terminating the run of the
program
before the debug command. I found an article about using 'On Error
Resume
Next', which has enabled the program to run past the point of my dodgy
code,
and has left the Immediate Window blank - this I was not expecting, so
I
know
it is the Or statement that is causing the problem (it 'half' works if
I
rem
out the Or aspect of the code)

the problem is that it is not reporting anything back to the Immediate
Window to enable me to deduce where i am going wrong

 

[Allen Browne]

There was no single-quote character in the code I suggested.

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

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

Hi Allen

well I am learning something anyway, thanks for the tips

your original suggestion for code came up with an error which i guess may
be
because of the quotes on the end of the bracket and the use of a single
aphostophy as the second character on the second line

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & _
"'"") Or ([altec_maxitem] = """ & Me![txtPartNumber] & """)"

I have been basing my thing around this code - what stumps me the most is
that the working syntax of:

stLinkCriteria = ("[altec_maxitem] = """ & Me![txtPartNumber] & "")

returns [altec_maxitem] = "sw200-1"

or the working syntax of

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & "")

returns [altec_switchno] = "sw200-1"

(Both the above use txtPartNumber = SW200-1):

both work as individual lines, but when cojined, they return a single
empty
value (line-return in Immediate window).

this code is:

stLinkCriteria = ("[altec_maxitem] = """ & Me.[txtPartNumber] & """") _
Or ("[altec_switchno] = """ & Me.[txtPartNumber] & """")


I think my problem may be down to getting the Or statement to work
effectivly.

Now I am lost!

So did you trace where the code went, using F8?

YOu really need to debug this for yourself, but your string is not the
same
as suggested in the earlier post:

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & _
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

Hint: & "" is adding nothing to the string.

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

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

you learn a little more every day - thanks for that, a useful tip

however it demonstrates my code is still duff as it returns into the
immediate window nothing other than a line-return regardless of which
part
number type is used. again if i 'rem out the part after the Or
statement
it
works fine, and likewise if i use the [altec_maxitem] field it works
fine.

from this i know each statement on its own works as desired, the
problem
being to get them to work together - the code is as follows, and it
really
has me stumped!

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & _
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

thanks for your time on this so far

:

If you can't get the program to stop on error (due to your error
handlers),
then just add this line to your code:
Stop

Then the code will stop at that line when it executes, and you can
press
F8
to step through each line, using the Immediate Window to query the
variables, see what is wrong, and even trace where the error handler
is
jumping to.

message
Absolutley - and I can understand why you have suggested it, however
the
code
I have written as per the previous post is terminating the run of
the
program
before the debug command. I found an article about using 'On Error
Resume
Next', which has enabled the program to run past the point of my
dodgy
code,
and has left the Immediate Window blank - this I was not expecting,
so
I
know
it is the Or statement that is causing the problem (it 'half' works
if
I
rem
out the Or aspect of the code)

the problem is that it is not reporting anything back to the
Immediate
Window to enable me to deduce where i am going wrong

 

[tina]

try

PMFJI, but your expression looks to me like the "Or" is outside the string.
try

stLinkCriteria = "[altec_maxitem] = '" & Me.[txtPartNumber] _
& "' Or [altec_switchno] = '" & Me.[txtPartNumber] & "'"

hth

Hi Allen

well I am learning something anyway, thanks for the tips

your original suggestion for code came up with an error which i guess may be
because of the quotes on the end of the bracket and the use of a single
aphostophy as the second character on the second line

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & _
"'"") Or ([altec_maxitem] = """ & Me![txtPartNumber] & """)"

I have been basing my thing around this code - what stumps me the most is
that the working syntax of:

stLinkCriteria = ("[altec_maxitem] = """ & Me![txtPartNumber] & "")

returns [altec_maxitem] = "sw200-1"

or the working syntax of

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & "")

returns [altec_switchno] = "sw200-1"

(Both the above use txtPartNumber = SW200-1):

both work as individual lines, but when cojined, they return a single empty
value (line-return in Immediate window).

this code is:

stLinkCriteria = ("[altec_maxitem] = """ & Me.[txtPartNumber] & """") _
Or ("[altec_switchno] = """ & Me.[txtPartNumber] & """")


I think my problem may be down to getting the Or statement to work effectivly.

Now I am lost!

So did you trace where the code went, using F8?

YOu really need to debug this for yourself, but your string is not the same
as suggested in the earlier post:

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & _
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

Hint: & "" is adding nothing to the string.

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

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

you learn a little more every day - thanks for that, a useful tip

however it demonstrates my code is still duff as it returns into the
immediate window nothing other than a line-return regardless of which part
number type is used. again if i 'rem out the part after the Or statement
it
works fine, and likewise if i use the [altec_maxitem] field it works fine.

from this i know each statement on its own works as desired, the problem
being to get them to work together - the code is as follows, and it really
has me stumped!

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & _
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

thanks for your time on this so far

:

If you can't get the program to stop on error (due to your error
handlers),
then just add this line to your code:
Stop

Then the code will stop at that line when it executes, and you can press
F8
to step through each line, using the Immediate Window to query the
variables, see what is wrong, and even trace where the error handler is
jumping to.

Absolutley - and I can understand why you have suggested it, however
the
code
I have written as per the previous post is terminating the run of the
program
before the debug command. I found an article about using 'On Error
Resume
Next', which has enabled the program to run past the point of my dodgy
code,
and has left the Immediate Window blank - this I was not expecting, so
I
know
it is the Or statement that is causing the problem (it 'half' works if
I
rem
out the Or aspect of the code)

the problem is that it is not reporting anything back to the Immediate
Window to enable me to deduce where i am going wrong

 

[Guest]

Thank you both for your assistance

for whatever reason, Tinas code works - now to understand why!

thanks again

regards

Luke

try

PMFJI, but your expression looks to me like the "Or" is outside the string.
try

stLinkCriteria = "[altec_maxitem] = '" & Me.[txtPartNumber] _
& "' Or [altec_switchno] = '" & Me.[txtPartNumber] & "'"

hth

Hi Allen

well I am learning something anyway, thanks for the tips

your original suggestion for code came up with an error which i guess may be
because of the quotes on the end of the bracket and the use of a single
aphostophy as the second character on the second line

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & _
"'"") Or ([altec_maxitem] = """ & Me![txtPartNumber] & """)"

I have been basing my thing around this code - what stumps me the most is
that the working syntax of:

stLinkCriteria = ("[altec_maxitem] = """ & Me![txtPartNumber] & "")

returns [altec_maxitem] = "sw200-1"

or the working syntax of

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & "")

returns [altec_switchno] = "sw200-1"

(Both the above use txtPartNumber = SW200-1):

both work as individual lines, but when cojined, they return a single empty
value (line-return in Immediate window).

this code is:

stLinkCriteria = ("[altec_maxitem] = """ & Me.[txtPartNumber] & """") _
Or ("[altec_switchno] = """ & Me.[txtPartNumber] & """")


I think my problem may be down to getting the Or statement to work effectivly.

Now I am lost!

So did you trace where the code went, using F8?

YOu really need to debug this for yourself, but your string is not the same
as suggested in the earlier post:
stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & _
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

Hint: & "" is adding nothing to the string.

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

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

you learn a little more every day - thanks for that, a useful tip

however it demonstrates my code is still duff as it returns into the
immediate window nothing other than a line-return regardless of which part
number type is used. again if i 'rem out the part after the Or statement
it
works fine, and likewise if i use the [altec_maxitem] field it works fine.

from this i know each statement on its own works as desired, the problem
being to get them to work together - the code is as follows, and it really
has me stumped!

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] & _
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

thanks for your time on this so far

:

If you can't get the program to stop on error (due to your error
handlers),
then just add this line to your code:
Stop

Then the code will stop at that line when it executes, and you can press
F8
to step through each line, using the Immediate Window to query the
variables, see what is wrong, and even trace where the error handler is
jumping to.

Absolutley - and I can understand why you have suggested it, however
the
code
I have written as per the previous post is terminating the run of the
program
before the debug command. I found an article about using 'On Error
Resume
Next', which has enabled the program to run past the point of my dodgy
code,
and has left the Immediate Window blank - this I was not expecting, so
I
know
it is the Or statement that is causing the problem (it 'half' works if
I
rem
out the Or aspect of the code)

the problem is that it is not reporting anything back to the Immediate
Window to enable me to deduce where i am going wrong

 

[tina]

it might help you to understand it, if you first write the expression using
"hard-coded" text values, as

"[altec_maxitem] = 'something' Or [altec_switchno] = 'something else'"

that's a WHERE clause. note how the double quotes enclose the whole
expression, and the single quotes enclose each hard-coded value. now you
need to replace the hard-coded text values with control references. start by
"closing" the string before and after each hard-coded value, with a double
quote, as

"[altec_maxitem] = '" something "' Or [altec_switchno] = '" something else
"'"

note how the single quotes are *inside* of each opening/closing set of
double quotes. now you have three separate strings to connect - just be
patient, we'll get there. replace the hard-coded values with the control
references, as

"[altec_maxitem] = '" Me.txtPartNumber "' Or [altec_switchno] = '"
Me.txtPartNumber "'"

now you have to connect the three strings and the control references, using
the & concatenator *outside of the strings* and making sure to leave a space
before and after each concatenator, as

"[altec_maxitem] = '" & Me.txtPartNumber & "' Or [altec_switchno] = '" _
& Me.txtPartNumber & "'"

i added the line continuation character ( _ ) just to make it clear that the
expression runs all on one line (unless you use the continuation character
to break it up).

hth

Thank you both for your assistance

for whatever reason, Tinas code works - now to understand why!

thanks again

regards

Luke

try

PMFJI, but your expression looks to me like the "Or" is outside the string.
try

stLinkCriteria = "[altec_maxitem] = '" & Me.[txtPartNumber] _
& "' Or [altec_switchno] = '" & Me.[txtPartNumber] & "'"

hth

Hi Allen

well I am learning something anyway, thanks for the tips

your original suggestion for code came up with an error which i guess

may
be

because of the quotes on the end of the bracket and the use of a single
aphostophy as the second character on the second line

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & _
"'"") Or ([altec_maxitem] = """ & Me![txtPartNumber] & """)"

I have been basing my thing around this code - what stumps me the most is
that the working syntax of:

stLinkCriteria = ("[altec_maxitem] = """ & Me![txtPartNumber] & "")

returns [altec_maxitem] = "sw200-1"

or the working syntax of

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & "")

returns [altec_switchno] = "sw200-1"

(Both the above use txtPartNumber = SW200-1):

both work as individual lines, but when cojined, they return a single empty
value (line-return in Immediate window).

this code is:

stLinkCriteria = ("[altec_maxitem] = """ & Me.[txtPartNumber] & """") _
Or ("[altec_switchno] = """ & Me.[txtPartNumber] & """")


I think my problem may be down to getting the Or statement to work effectivly.

Now I am lost!


:

So did you trace where the code went, using F8?

YOu really need to debug this for yourself, but your string is not

the
same

as suggested in the earlier post:
stLinkCriteria = ("[altec_switchno]=" & "'" &

Me![txtPartNumber] &
_

"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

Hint: & "" is adding nothing to the string.

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

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

you learn a little more every day - thanks for that, a useful tip

however it demonstrates my code is still duff as it returns into the
immediate window nothing other than a line-return regardless of

which
part

number type is used. again if i 'rem out the part after the Or statement
it
works fine, and likewise if i use the [altec_maxitem] field it

works
fine.

from this i know each statement on its own works as desired, the problem
being to get them to work together - the code is as follows, and

it
really

has me stumped!

stLinkCriteria = ("[altec_switchno]=" & "'" &

Me![txtPartNumber] &
_

"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

thanks for your time on this so far

:

If you can't get the program to stop on error (due to your error
handlers),
then just add this line to your code:
Stop

Then the code will stop at that line when it executes, and you

can
press

F8
to step through each line, using the Immediate Window to query the
variables, see what is wrong, and even trace where the error

handler
is

jumping to.

Absolutley - and I can understand why you have suggested it, however
the
code
I have written as per the previous post is terminating the run

of
the

program
before the debug command. I found an article about using 'On Error
Resume
Next', which has enabled the program to run past the point of

my
dodgy

code,
and has left the Immediate Window blank - this I was not

expecting,
so

I
know
it is the Or statement that is causing the problem (it 'half'

works
if

I
rem
out the Or aspect of the code)

the problem is that it is not reporting anything back to the Immediate
Window to enable me to deduce where i am going wrong

 

[Guest]

Hi there Tina

thanks for this - it makes it a lot clearer, I really appreciate your help

Best regards

Luke

it might help you to understand it, if you first write the expression using
"hard-coded" text values, as

"[altec_maxitem] = 'something' Or [altec_switchno] = 'something else'"

that's a WHERE clause. note how the double quotes enclose the whole
expression, and the single quotes enclose each hard-coded value. now you
need to replace the hard-coded text values with control references. start by
"closing" the string before and after each hard-coded value, with a double
quote, as

"[altec_maxitem] = '" something "' Or [altec_switchno] = '" something else
"'"

note how the single quotes are *inside* of each opening/closing set of
double quotes. now you have three separate strings to connect - just be
patient, we'll get there. replace the hard-coded values with the control
references, as

"[altec_maxitem] = '" Me.txtPartNumber "' Or [altec_switchno] = '"
Me.txtPartNumber "'"

now you have to connect the three strings and the control references, using
the & concatenator *outside of the strings* and making sure to leave a space
before and after each concatenator, as

"[altec_maxitem] = '" & Me.txtPartNumber & "' Or [altec_switchno] = '" _
& Me.txtPartNumber & "'"

i added the line continuation character ( _ ) just to make it clear that the
expression runs all on one line (unless you use the continuation character
to break it up).

hth

Thank you both for your assistance

for whatever reason, Tinas code works - now to understand why!

thanks again

regards

Luke

try

PMFJI, but your expression looks to me like the "Or" is outside the string.
try

stLinkCriteria = "[altec_maxitem] = '" & Me.[txtPartNumber] _
& "' Or [altec_switchno] = '" & Me.[txtPartNumber] & "'"

hth


Hi Allen

well I am learning something anyway, thanks for the tips

your original suggestion for code came up with an error which i guess may
be
because of the quotes on the end of the bracket and the use of a single
aphostophy as the second character on the second line

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & _
"'"") Or ([altec_maxitem] = """ & Me![txtPartNumber] & """)"

I have been basing my thing around this code - what stumps me the most is
that the working syntax of:

stLinkCriteria = ("[altec_maxitem] = """ & Me![txtPartNumber] & "")

returns [altec_maxitem] = "sw200-1"

or the working syntax of

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & "")

returns [altec_switchno] = "sw200-1"

(Both the above use txtPartNumber = SW200-1):

both work as individual lines, but when cojined, they return a single
empty
value (line-return in Immediate window).

this code is:

stLinkCriteria = ("[altec_maxitem] = """ & Me.[txtPartNumber] & """") _
Or ("[altec_switchno] = """ & Me.[txtPartNumber] & """")


I think my problem may be down to getting the Or statement to work
effectivly.

Now I am lost!


:

So did you trace where the code went, using F8?

YOu really need to debug this for yourself, but your string is not the
same
as suggested in the earlier post:
stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] &
_
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

Hint: & "" is adding nothing to the string.

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

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

message
you learn a little more every day - thanks for that, a useful tip

however it demonstrates my code is still duff as it returns into the
immediate window nothing other than a line-return regardless of which
part
number type is used. again if i 'rem out the part after the Or
statement
it
works fine, and likewise if i use the [altec_maxitem] field it works
fine.

from this i know each statement on its own works as desired, the
problem
being to get them to work together - the code is as follows, and it
really
has me stumped!

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] &
_
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

thanks for your time on this so far

:

If you can't get the program to stop on error (due to your error
handlers),
then just add this line to your code:
Stop

Then the code will stop at that line when it executes, and you can
press
F8
to step through each line, using the Immediate Window to query the
variables, see what is wrong, and even trace where the error handler
is
jumping to.

message
Absolutley - and I can understand why you have suggested it,
however
the
code
I have written as per the previous post is terminating the run of
the
program
before the debug command. I found an article about using 'On Error
Resume
Next', which has enabled the program to run past the point of my
dodgy
code,
and has left the Immediate Window blank - this I was not expecting,
so
I
know
it is the Or statement that is causing the problem (it 'half' works
if
I
rem
out the Or aspect of the code)

the problem is that it is not reporting anything back to the
Immediate
Window to enable me to deduce where i am going wrong

 

[tina]

you're very welcome

Hi there Tina

thanks for this - it makes it a lot clearer, I really appreciate your help

Best regards

Luke

it might help you to understand it, if you first write the expression using
"hard-coded" text values, as

"[altec_maxitem] = 'something' Or [altec_switchno] = 'something else'"

that's a WHERE clause. note how the double quotes enclose the whole
expression, and the single quotes enclose each hard-coded value. now you
need to replace the hard-coded text values with control references. start by
"closing" the string before and after each hard-coded value, with a double
quote, as

"[altec_maxitem] = '" something "' Or [altec_switchno] = '" something else
"'"

note how the single quotes are *inside* of each opening/closing set of
double quotes. now you have three separate strings to connect - just be
patient, we'll get there. replace the hard-coded values with the control
references, as

"[altec_maxitem] = '" Me.txtPartNumber "' Or [altec_switchno] = '"
Me.txtPartNumber "'"

now you have to connect the three strings and the control references, using
the & concatenator *outside of the strings* and making sure to leave a space
before and after each concatenator, as

"[altec_maxitem] = '" & Me.txtPartNumber & "' Or [altec_switchno] = '" _
& Me.txtPartNumber & "'"

i added the line continuation character ( _ ) just to make it clear that the
expression runs all on one line (unless you use the continuation character
to break it up).

hth

Thank you both for your assistance

for whatever reason, Tinas code works - now to understand why!

thanks again

regards

Luke

:

try

PMFJI, but your expression looks to me like the "Or" is outside the string.
try

stLinkCriteria = "[altec_maxitem] = '" & Me.[txtPartNumber] _
& "' Or [altec_switchno] = '" & Me.[txtPartNumber] & "'"

hth


Hi Allen

well I am learning something anyway, thanks for the tips

your original suggestion for code came up with an error which i

guess
may

be
because of the quotes on the end of the bracket and the use of a single
aphostophy as the second character on the second line

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & _
"'"") Or ([altec_maxitem] = """ & Me![txtPartNumber] & """)"

I have been basing my thing around this code - what stumps me the

most
is

that the working syntax of:

stLinkCriteria = ("[altec_maxitem] = """ & Me![txtPartNumber] & "")

returns [altec_maxitem] = "sw200-1"

or the working syntax of

stLinkCriteria = ("[altec_switchno] = """ & Me![txtPartNumber] & "")

returns [altec_switchno] = "sw200-1"

(Both the above use txtPartNumber = SW200-1):

both work as individual lines, but when cojined, they return a single
empty
value (line-return in Immediate window).

this code is:

stLinkCriteria = ("[altec_maxitem] = """ & Me.[txtPartNumber] &

"""")
_

Or ("[altec_switchno] = """ & Me.[txtPartNumber] & """")


I think my problem may be down to getting the Or statement to work
effectivly.

Now I am lost!


:

So did you trace where the code went, using F8?

YOu really need to debug this for yourself, but your string is

not
the

same
as suggested in the earlier post:
stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] &
_
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

Hint: & "" is adding nothing to the string.

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

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

message
you learn a little more every day - thanks for that, a useful tip

however it demonstrates my code is still duff as it returns

into
the

immediate window nothing other than a line-return regardless

of
which

part
number type is used. again if i 'rem out the part after the Or
statement
it
works fine, and likewise if i use the [altec_maxitem] field it works
fine.

from this i know each statement on its own works as desired, the
problem
being to get them to work together - the code is as follows,

and
it

really
has me stumped!

stLinkCriteria = ("[altec_switchno]=" & "'" & Me![txtPartNumber] &
_
"'") Or ("[altec_maxitem]" = "'" & Me![txtPartNumber] & "'")

thanks for your time on this so far

:

If you can't get the program to stop on error (due to your error
handlers),
then just add this line to your code:
Stop

Then the code will stop at that line when it executes, and

you
can

press
F8
to step through each line, using the Immediate Window to

query
the

variables, see what is wrong, and even trace where the error handler
is
jumping to.

message
Absolutley - and I can understand why you have suggested it,
however
the
code
I have written as per the previous post is terminating the

run
of

the
program
before the debug command. I found an article about using

'On
Error

Resume
Next', which has enabled the program to run past the point

of
my

dodgy
code,
and has left the Immediate Window blank - this I was not expecting,
so
I
know
it is the Or statement that is causing the problem (it

'half'
works

if
I
rem
out the Or aspect of the code)

the problem is that it is not reporting anything back to the
Immediate
Window to enable me to deduce where i am going wrong