Update query for updating a field based o the content of another field

[lynque]

I'd like to create an update query for an existing table that will check the contents of the productName field and populate the productImg field based on the results.

i.e.

productName
Compaq, Presario AG450, DT
Dell, Optiplex GX110, DT
Dell, Optiplex GX150, DT

productImg
Images/CompaqPreAG450DT.jpg
Images/DellOptiplexGX11DT.jpg
Images/DellOptiplexGX110DT.jpg

As you can see by the products I'm listing, our inventory is quite extensive and it changes frequently so I'd like to programmatically fix this.

Being a relative newby I'd appreciate any and all help regarding this

 

[GingerR]

Hi.
How would the programming know what the jpg file name is? At first I thought it was built by concantonating Manufacturer + Model + whatever "DT" is, but that's not true in the first case (the Compaq).

If it IS true and your post has a typo, you do not need to save this data in a table. It can just always be built on the fly by concantonating the data using the ampersand.

One more question before I continue: Is the data really those three bits of information separated by commas? Or is it in three separate fields? Ideally, it should be in three separate fields.

 

[lynque]

Sorry, there is a typo there which I'm sure could throw people off.

I agree with you on having the data in three different fields, currently it is only in one but this is a new project and still in development so it's better to catch it now.

With the data in seperate fields this will be a much easier query to build.

Thanks for the response

 

[PHV]

Create a public function in a standard code module:
Public Function getProductImg(ProdName)
getProductImg = "Images/" & Replace(Join(Split(ProdName, ",")), " ", "")
End Function

And then in query grid:
productImg: getProductImg(productName)

Note: needs ac2k or above.

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

 

[GingerR]

well, you can use instr(), left(), right() functions to parse out your string. But think of the future: Someone's gonna want to sort by DELL some day, or search on a model number. The best thing to do is to split it out now.

So try that, and see how far you get concantonating the three fields, and let us know how it goes...

 

[lynque]

Thanks for all the help.