Is there a way to ?

[jukus]

Just wondering if there is a way to edit a value from a table and change it in a report. For example, Property is listed on an invoice as Company (WMP) and is stored in the property field of table tblinvoice. I have a report called rptEnvelope that pulls the fields property, contact_person, address, etc. What I am wondering is there a way to change the porperty to Company and delet the (WMP) part on the envelope.

Thanks for any input. Still pretty novice at access and learn a little everyday.

Thanks again.

 

[jzwp22]

If the (WMP) is always the last five characters in the property then you can do a couple of different things.

First, you can use a query similar to the following to pull the information into the report

SELECT left([property],len([property])-5), contact_person, address..
FROM yourtablename

OR

Second, you can add an unbound control to your form that uses the left function as above. You can set the visible property to the control that holds the full length property name to NO so it is not visible (but still present) on the report.

=Left([property],Len([property])-5)

 

[jukus]

Unfortunately that may be a problem. There are 22 different companies and only 6 of them have characters on the end that would need to be deleted. Is it possible to there write an if then statement for the 6 properties in the on format event of the report to accomplish this. Not sure of how the code is written, but something to if [Property] = xxxx then use the left control, etc.

This may be a lot of code, not sure. I can always go back to doing them in Word, but it is easier to just hit a button to print the envelope.

Thanks again.

 

[jzwp22]

I think this might do it:

= IIF(Left([property],5)="(WMP)",Left([property],Len([property])-5),[property])

 

[georgedwilkinson]

Or just make an alias table for your companies. If the alias is different than the original company name, use the alias.

Like:
select companies.companyname from companies where companies.id not in (select companyaliases.companyid from companyaliases)
union
select companyaliases.companynamealias from companies, companyaliases inner join companies.id = companyaliases.companyid

Seems like a pretty simple and permanent solution.

You can make it more complex by adding a type to the alias table...this is what I do in most of my production databases with things that have names that are likely to be interpreted by different people in different ways.

 

[jukus]

jzwp22, That did not work get an error when trying it. I may be going about it wrong.

 

[jukus]

After further review it is not removing the (WMP) with the IIF statement

 

[jukus]

Got the function to work, had to be IIF(right()..... to get it to pull off the end.

George your idea seems like a good one, just not sure how to do it.

 

[Brianwarnock]

Unfortunately that may be a problem. There are 22 different companies and only 6 of them have characters on the end that would need to be deleted. .

Is there anything common about this suffix is it always ( ), in which case you could use instr to look for the start of the suffix , but if the instr returned 0 then none exists. This is probably simpler done in a function.

Brian

 

[georgedwilkinson]

George your idea seems like a good one, just not sure how to do it.

Let me know if you need more info. I gave you the pseudo sql above. Just need to create a new table called "MyTableNameAliases" with a FK field from "MyTableName". Another field to hold the alias. Then use a form of the union query above. Works (consistently) like magic.

 

[jukus]

Brian, to answer your question, Yes the endings that would need to be erased are always in ( ).

 

[georgedwilkinson]

I was just thinking, if you only have 3 different strings you want to get rid of, and they are consistent, you could just use replace, as in:

replace(replace(replace(property,"(WMP)",""),(Parkside),""),"(Emory)","")

This would just strip those 3 strings from any instance of "property". You might need to replace " with ', though.

 

[Brianwarnock]

You might like to try

IIf(InStr(1, Property, "(") = 0, Property, Left(Property, (InStr(1, Property, "(") - 1)))

Brian

 

[jukus]

Brian, that seems to do the trick. Every instance of the ( ) are erased. Thanks alot.

Appreciate the help

 

[Brianwarnock]

HTH, pleased to know it worked as it was untested and my syntax is getting rusty.

Brian