using a substring in a join

[tomf]

Hi,

I am using access 2000 for a simple shipment tracking database. I need to setup a join in a query that links the SHIPMENTS table with a STYLE MASTER table.

An example is that we ship part number 123-45678-90abcd. The style number is the second segment (45678). This style has more information stored in a STYLE MASTER table. I would like to create a query that links the style portion of the part number to the style master table. I have tried using the mid$ function and am able to extract the style, but cannot use this in my join. Any help would be appreciated.

Thanks,
Tom F. [sig][/sig]

 

[MichaelRed]

Look up the 'parameter' query. It does what you want. The "trick" is in how you insert the parameter into the query. The example(s) are all from the perspecitve of 'interactive' parameters, but the process is more-or-less the same for programatically inserting a parameter.

The following is an example or placing a parameter into an SQL string, and using the querydef to generate a recordset. Obviously, your application is quite different, however the function does show the use of a variable as a parameter for a querydef object. The part to look at is the bold line of the strSQL statement. If you "execute" this through the complete generation of the strSQL varaible, then look at strSQL in the immediate window, the process should become clear.



Public Function basUniqueCd(PubCode As String) As Boolean

Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset

Dim strSQL As String 'Query String

Set dbs = CurrentDb

'Check the Input code is Unique

'SQL Code to Get the count of Records in the Table w/ the Same PubCd
'Really could be a parameter Query, but its done this way (at least for NOW)
strSQL = "Select Count([NewCode]) As Num "
strSQL = strSQL & "From tblAllPubsUniq4Fields "
strSQL = strSQL & "WHERE (tblAllPubsUniq4Fields.NewCode = "
strSQL = strSQL & Chr(34) & PubCode & Chr(34) & ""
Set qdf = dbs.CreateQueryDef("", strSQL)
Set rst = dbs.OpenRecordset(strSQL)
basUniqueCd = (rst!Num = 0)


End Function
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]