Query against list

[AndyDevine1]

Hi there

Hoping you can help

I have (eg) 500 records in a list.

Records include some information and a campaign name eg "AXX campaign 12 ssss ddd", "SSSSS campaign Bb dddddd" etc

I'd like to have a table that contains all of my campaign names (there are hundreds) and index each of the records against this table, so for example "AXX campaign 12 ssss ddd" finds the campaign name 'campaign 12' in the table and returns this in a separate column in the original table

And "SSSSS campaign Bb dddddd" returns 'Campaign Bb" etc, etc

If you are willing please see my other post, as this could be an alternative way for us to do this..

Many thanks

Andy

 

[ranman256]

Build a append table query that writes the 'campaigns' to a table (unique names keyed)
add an extra field for this function to convert the exting field to the pure camp name. You can add the word CAMPAIGN to the title if you want.

function CvtCampaign(byval pvCamp)
dim vWord
dim i as integer

 i = instr(pvCamp,"campaign")
 vWord = trim(mid(pvCamp,i+8))
 i = instr(vWord," ")
 CvtCampaign = trim(left(vWord,i))
end function

 

[AndyDevine1]

Sorry may not have been 100%...the word campaign is not actually in the string...could be anything...does that make a difference??

Thanks

Andy

 

[ranman256]

yes. I based it off your example. What is the string like? Which part is the campaign?

 

[AndyDevine1]

Here's a couple of (fake) examples

Row 1: Load120x600-14_04_24_M_SS14_Clothing160x600root_v1-124470-1.html
Row 2: Load120x600-14_04_24_M_SS14_Mens Beach160x600YesKIDS_BEACH-124470-12.html

Our objective is to create a pivot table grouping all of the results for each of the campaigns (Clothing, Mens Beach etc)

My plan I think is to index all of these rows against a table...eg for row 1, if "Clothing' (on table 2) can be found in row 1 on table 1, it it shows "Clothing" in the next column

Thanks

 

[ranman256]

How 'bout:

Function CvtCampaign(ByVal pvCamp)
Dim vWord, v1Chr
Dim i As Integer

i= instrRev(pvCamp,"_")
vWord = mid(pvCamp,i+1)

For i = 1 To Len(vWord)
   v1Chr = Mid(vWord, i, 1)
   If IsNumeric(v1Chr) Then
     vWord = Left(vWord, i - 1)
     GoTo endit
   End If
Next
endit:
 
CvtCampaign = Trim(vWord)
End Function