Auto Categorize in Access 2007 1

[valgore]

ok. i have a table called Category Codes. the 2 fields are Vendor and Category Code. the Vendor field are businesses and the Category Code field has two letters in it. when i import a bill into AmexCurrent, i want the Vendor field from AmexCurrentto look at the CategoryCodes table and say

If Vendor field in Category Codes is like the Vendor field in AmexCurrent, then insert CategoryCodes field from Vendor into Category Codes field in AmexCurrent.

my problem is, the vendor field in AmexCurrent looks something like this when it is imported

ALASKA WILD BERRY D49504-69695


and the vendor field in CategoryCodes table looks something like this

Alaska Wild Berry

will the like function match those?

hope this all makes sense.

Valgore

 

[PHV]

You may try a criteria like this:

UCase(AmexCurrent.[vendor field]) Like UCase(CategoryCodes.[vendor field]) & '*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[valgore]

so does this code input data into the vendor field in AmexCurrent? from what i know (not much), it doesn't.

i changed it to

UCase([AmexCurrent!Merchant Name/Location!]) Like UCase([CategoryCodes!Vendor!]) & '*'

because of Access 2007 syntax, but i get a compile error: expected epression on the first '
i also got an error on your original code as well.

Valgore

 

[PHV]

Could you please post the whole code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[valgore]

DoCmd.RunSQL "INSERT INTO AmexCurrent Select [CategoryCodes]![Vendor] as [Vendor] From CategoryCodes WHERE UCase(AmexCurrent.Merchant Name/Location) Like UCase(CategoryCodes.Vendor) & '*'"

i get syntax error.

 

[PHV]

What about something like this ?

DoCmd.RunSQL "UPDATE AmexCurrent,CategoryCodes SET AmexCurrent.[[i]name of category code[/i]]=CategoryCodes.[Category Code] WHERE UCase(AmexCurrent.[Merchant Name/Location]) Like UCase(CategoryCodes.Vendor) & '*'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[valgore]

wow. you are amazing. works perfectly!!!!!!

Valgore