Search Access 2010 Table Using VBA (1 Viewer)

ECEstudent · Jun 12, 2013

Hi. I have a problem with my vba code and I would appreciate any help I can get!

What my code is supposed to be doing is taking in a user input (example: 38A1018X002) searching Table1 for it's associated value (ECX29-C7) Then taking in that found value and looking it up in a different table. I know how to do all of that no problem. My problem is that the value found is just PART of the new value that I would be looking for in a different table. For example: I would be looking up ECX29-C7 in a table field that contains values like:

ECX29-C7-D9-E6
ECX29-C8-U9
ECX30-C7-K5
ECX29-C7
.
.
.
[And so on]

What I need it to return in this example is ECX29-C7-D9-E6 AND ECX29-C7

I am just stuck on this one part. It needs to be in VBA. I can't do a query with this just because everything else I have is in code and this would be SO much simpler. Please help

MarkK · Jun 12, 2013

Hi there and welcome to the forum

Search around here or Google the Jet SQL LIKE operator, which allows you to specify wild-card characters in your WHERE clause. An example . . .

Code:

SELECT [I]Fieldlist[/I]
FROM [I]Table[/I]
WHERE Field LIKE 'ECX29-C7*'

. . . so that WHERE clause would return the two records you indicate.

ECEstudent · Jun 12, 2013

Thanks! I'm trying to incorporate that into an If statement though...
I'm probably really confusing right now. Let me explain, so what is supposed to happen is that the user enters in a part number..through the IF statement, the code detects if the user input even exists in the table. And if it does, then store all the associated fields in an array for later use. If not, print out to user that that part number does not exist.

pleaaaaaaaase help. I've been working on this for days

way2bord · Jun 12, 2013

ECEstudent said:
Thanks! I'm trying to incorporate that into an If statement though...
I'm probably really confusing right now. Let me explain, so what is supposed to happen is that the user enters in a part number..through the IF statement, the code detects if the user input even exists in the table. And if it does, then store all the associated fields in an array for later use. If not, print out to user that that part number does not exist.

pleaaaaaaaase help. I've been working on this for days

pseudocode:
if DCount("[FieldName", "TableName", "[FieldName] Like '*" & [inputbox] & "*'") > 0 ' the item exists
then

myarray(0) = recordset items. ' if you can't use SQL here to establish your recordset, you need to iterate by loop through your entire table to locate your matching values.

else
msgbox "no part"
end if

ECEstudent · Jun 13, 2013

Thank you! But When I try that it still doesn't go through the IF statement.

Okay. Let me explain more clearly what I'm trying to do:
1)User inputs part number
2)Check user input (part number) exists in table1
3)If it does, take all associated values and put in array
4)Take each value in array and search it in table2

This is where I get stuck. Because the values found in table1 look like: ECX29-C7

But I want to search that in table2 whose values look like:
ECX29-D9-C7-E5

What I'm trying to do in my code is go through an if statement ( "If ECX29-C7 exists in table2 do ...whatever")

But it's not even registering ECX29-C7 to be in table2 because the values in table2 are a bit crazy-looking

What My code so far looks like:

If DCount("Structure", "RoicStructure", "[Structure] Like '*" & [varCode] & "*'") > 0 Then

MsgBox "Structure Found!" 'print to user

'Rest of code

Else 'If user input does not exist

MsgBox "Structure Not Found!" 'print to user

End If

What it's doing is printing out "Structure Not Found!"

Please help.

jdraw · Jun 13, 2013

Can you post your database - remove anything confidential?

I think the issue is primarily because of the intelligence built into your codes. Relational databases generally work well with "atomic fields" -- one field, one fact.

Here's an article that puts Design Principles into context. Very much worth the read.
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

ECEstudent · Jun 13, 2013

I'm not sure how to do that?...I just joined yesterday

ECEstudent · Jun 13, 2013

Okay. I tried this and it finds all fields that have ECX29-C7 at the beginning. But what about ECX29-D2-C7 ? It still has ECX29-C7 but with a D2 in between. How do I change the - to a * in order for it the database to search for the whole thing?

ECEstudent · Jun 13, 2013

I forgot to attach my code. Here it is:

If DCount("Structure", "RoicStructure", "[Structure] Like '*" & u & "*'") > 0 Then

ECEstudent · Jun 13, 2013

Where u is equal to each value in the array that I'm searching for in table2.
Did I confuse you yet?

jdraw · Jun 13, 2013

"ECX29" & "*" & "C7" should work.
How many different patterns do you have?

ECEstudent · Jun 13, 2013

A lot...
That can't work. ECX29-C7 was just an example. So that line of code won't work...

ECEstudent · Jun 13, 2013

Ahhh! I just figured it out!!

This is what I did and it worked:

varCode = Replace(varCode, "-", "*") 'replace each - with *

jdraw · Jun 13, 2013

Well tell us about the various patterns. What formats might be involved in the search?

xxxxx-xx
xxxxx-xx-xx etc,

Also for posting
http://www.access-programmers.co.uk/forums/showthread.php?t=140587

ECEstudent · Jun 13, 2013

That replaced every - in between every ECX29 and C7 (for this example) with a *. and then I used the used the code you suggested to add * before ECX29 and after C7

Thanks a lot! Your replies were VERY helpful!!

Search Access 2010 Table Using VBA (1 Viewer)

ECEstudent

Registered User.

MarkK

bit cruncher

ECEstudent

Registered User.

way2bord

Registered User.

ECEstudent

Registered User.

jdraw

Super Moderator

ECEstudent

Registered User.

ECEstudent

Registered User.

ECEstudent

Registered User.

ECEstudent

Registered User.

jdraw

Super Moderator

ECEstudent

Registered User.

ECEstudent

Registered User.

jdraw

Super Moderator

ECEstudent

Registered User.

Similar threads

Users who are viewing this thread