How to have the Seek function to seek for a value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

How to have the Seek function to seek for a value

Author

Topic

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-11-09 : 04:17:36

How to have the Seek function to seek for a value on two fields.

I had two tables as shown below. I wanted to compare the field NAI_UNIT_NUMBER in the NAI_TABLE
to the field NAI_UNIT_NUMBER and to the field NEW_UNIT_NUMBER in the UNIT_REFERENCE_TABLE.
If there is a match in either of the two fields, then it will write a message to the field
RESULT_OF_MATCH in the NAI_TABLE.

For example.

The record with a NAI_UNIT_NUMBER (001000) in the NAI_TABLE will result
a match of 001000 in the UNIT_REFERENCE_TABLE.

The record with a NAI_UNIT_NUMBER (002000) in the NAI_TABLE will result
a match of 002000 in the UNIT_REFERENCE_TABLE.

The record with a NAI_UNIT_NUMBER (007534) in the NAI_TABLE will not
result in a match in either fields of the UNIT_REFERENCE_TABLE.

When I did a seek, Access did find a match for value 001000, and 002000 but
for the value 007534, it did not perform a seek. I know that it did
not perform a seek because the message "There is No Match" was not
written to the field RESULT_OF_MATCH.

QUESTION:

How do I have the seek function to search on both fields:
NAI_UNIT_NUMBER and NEW_UNIT_NUMBER. If the value
appear in either of the two fields, then there is a match. If the
value do not appear in any of the two fields, then there is no match

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Table Name: NAI_TABLE

NAI_UNIT_NUMBER RESULT_OF_MATCH
001000 There is a match in the field NAI_UNIT_NUMBER
002000 There is a match in the field NEW_UNIT_NUMBER
007534 There is No Match <----------- ACCESS DID NOT WRITE THIS TEXT

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Table Name: UNIT_REFERENCE_TABLE

NAI_UNIT_NUMBER NEW_UNIT_NUMBER
001000 004000
00ABC 002000

The UNIT_TABLE has an index named NAI_And_New_Unit_Number.
This index is comprises of two fields: NAI_UNIT_NUMBER and NEW_UNIT_NUMBER.

//////////////////////////////////////////////////////////

With Recordset_Unit_Table
.Index = "NAI_And_New_Unit_Number"
.Seek ">=", Var_Unit_Number
End With

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources