Part of field matches part of another field

[fishhead]

I'm trying to create a query that will match part of 2 fields from 2 different tables.



table 1 = [po_notes]![po#]
table 2 = [shptlist]![po#]

table one has the exact PO formatted correctly - eg. 10101
table two usually has extra charaters - eg. po.10101/

what i would like to do is have the query go through all the PO#'s in table one and find the match in table 2.

btw I can get this to work when the extra characters are in table 1 by using : Like "*" & [Forms]![po_notes]![po#] & "*" , but i can't seem to get this to work the other way...

is this possible?

thanks,
Steve

[June7]

You have a table and form both named po_notes?

SELECT * FROM shplist WHERE [po#] Like "*" & [Forms]![po_notes]![po#] & "*";

Should work.


Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[fishhead]

You have a table and form both named po_notes?

SELECT * FROM shplist WHERE [po#] Like "*" & [Forms]![po_notes]![po#] & "*";

Should work.


Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

THANKS for the suggestions. I was hoping to put this together myself and sort out the specifics afterwards but fear i've gotten in over my head.

more specifically I have two forms
Forms![fSHANPONote]![PO] ( where the EXACT PO number is stated without any unnecessary characters - because we can control this)

[Forms]![fSHANSHPTSCREEN]![Customer Ref #] (where the [Customer Ref#] field will have the PO I'm trying to match along with unnecessary characters - which we can not prevent)

I've created a macro to open [fSHANPONOTE] to the record that has the sequence of numbers in the PO that matches the [Customer Ref #] while ignoring the characters that done match.

eg. [PO] = 10101 , [Customer Ref#] = PO10101/

What would the 'where' condition be in the open form macro as :

SELECT * FROM fSHANPONote WHERE [PO] Like "*" & [Forms]![fSHANSHPTSCREEN]![Customer Ref #] & "*"

just returns errors

Thanks,

[June7]

Try:

SELECT * FROM fSHANPONote WHERE InStr([Forms]![fSHANSHPTSCREEN]![Customer Ref #], [PO])>0

If the extra characters are always just the "PO" at beginning, try:

SELECT * FROM fSHANPONote WHERE [PO]=Mid([Forms]![fSHANSHPTSCREEN]![Customer Ref #],3)

[fishhead]

Try:

SELECT * FROM fSHANPONote WHERE InStr([Forms]![fSHANSHPTSCREEN]![Customer Ref #], [PO])>0

If the extra characters are always just the "PO" at beginning, try:

SELECT * FROM fSHANPONote WHERE [PO]=Mid([Forms]![fSHANSHPTSCREEN]![Customer Ref #],3)

thanks for the reply.
the extra characters can (and are) anything the user decides to put in there. that's the problem we can't control this.

I tried inserting:
SELECT * FROM fSHANPONote WHERE InStr([Forms]![fSHANSHPTSCREEN]![Customer Ref #], [PO])>0

in the "where" condition of the open form macro but am getting an invalid value in the where condition error.

i tried removing the word SELECT and the * but this still produced the same error.
Can you suggest what I may be doing wrong?

thanks,

[June7]

I thought that SQL statement was the RecordSource of form.

I don't use macros, only VBA.

DoCmd.OpenForm "fSHANPONOTE", , , "InStr(" & [Forms]![fSHANSHPTSCREEN]![Customer Ref #] & ", [PO])>0"

[fishhead]

thanks again,
this works perfectly if the [Customer Ref #] is identical to the [PO].
as soon as the [Customer Ref # ] = something like PO10101/ i get an error using this code (note the [PO] in this scenario would be 10101

is there any change we can make to the DoCmd code?

[June7]

Okay, maybe need apostrophe delimiters.

DoCmd.OpenForm "fSHANPONOTE", , , "InStr('" & [Forms]![fSHANSHPTSCREEN]![Customer Ref #] & "', [PO])>0"

Works with test in my db.

[fishhead]

this works PERFECTLY!
thanks a lot!

[fishhead]

I originally wanted this search to be done after a long list of other things i have built into my macro.
I understand there is the ability to add the RUNCODE function at the end of my macro but i'm being asked to provide a function name.
when I pasted your code into this field I get an error.
Sorry i'm not used to VBA so i don't know what the function name should be. Can you help me sort that out possibly?

thanks, (again)

[June7]

To provide a function name means you will have to build VBA procedure - a user defined function in a general code module - that runs the DoCmd.OpenForm method. Might as well just build and run the VBA procedure directly and eliminate the macro.

However, VBA code should not be necessary with this macro. Macro has equivalent to the OpenForm method with argument for the WHERE condition.

[fishhead]

To provide a function name means you will have to build VBA procedure - a user defined function in a general code module - that runs the DoCmd.OpenForm method. Might as well just build and run the VBA procedure directly and eliminate the macro.

However, VBA code should not be necessary with this macro. Macro has equivalent to the OpenForm method with argument for the WHERE condition.

I tried pasting the code you gave me into the where condition but I'm getting an invalid where condition warning. Can you suggest what I'm missing in the argument?
( i pasted a screen shot of what I did but it didn't come through.... what I pasted was :

"fSHANPONOTE", , , "InStr('" & [Forms]![fSHANSHPTSCREEN]![Customer Ref #] & "', [PO])>0"

in the where condition of the openform macro

[June7]

Only use the WHERE condition argument in the macro, not the preceding arguments. In others words, remove: "fSHANPONOTE", , ,

However, since I don't use macros, not sure about correct syntax.

[fishhead]

Only use the WHERE condition argument in the macro, not the preceding arguments. In others words, remove: "fSHANPONOTE", , ,

However, since I don't use macros, not sure about correct syntax.

I think i have this figured out now.
thanks again for all your help!