Issues building query criteria 1

[virtuapete]

Hello everyone,

I've been out of the loop for a while and getting back into this development with Access 2003. I have made a DB to use as a report tool(automation), taking in a plotter log from AutoCAD. the field giving me trouble contains a path and filename which i want the query (or something) to filter out all the crap and just display the client's name like this >>>

table field "JOB" contains >

\\JMKARCH-1\Documents\JMKARCH\NEWJOBS\30 WILD DUCK RD-04-04\INTER-ELEV 04-04.dwg

and all i care about is the words "WILD DUCK" in this case. I want the query to display "WILD DUCK" instead of the whole path and get rid of everything else so when i run a report it will group the records(jobs) together who have same names. I do not want to alter the table data at all if possible because it is meant to be a tool where you import the data file as an Excel file, then print the report. "WILD DUCK" is one of 8 or 9 names it needs to filter the field for and return just what was filtered (i.e. - in this case just the words WILD DUCK).

Do I need to do something when the data gets imported that can be automated to take care of this?? Or what is the expression I need to use to format my data in that field to match that small portion for each client name? Perhaps a special relationship can be made to another table with the clients names and will associate that field to the record that contains it in a certain field? Please help.

 

[PHV]

"WILD DUCK" is one of 8 or 9 names
Create a table with the names, say tblClientNames with single text field named, say, ClientName.
You may now try to join the 2 tables ON JOB Like '*' & ClientName & '*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[virtuapete]

ok how do i perform the join as you explained? I am just beginning to familiarize myself with access 2003 and dont know how to go about this, although i understand what you have written

 

[traingamer]

If JOB is a fixed length string use the Mid(string, start[, length]) function otherwise use the InStr([start, ]string1, string2[, compare]) function to find the client name. One way or the other you just need to parse the client name from the string.

e.g. Mid([JOB], 42, 9)

You can do it whan you import or when you select.

traingamer