MS Access:Replacing some characters in a result column query result

[accessforum93]

I have two columns from MS Access query result. The table columns are:

Original Result Query Result:

Product_Solution Product_Description
888 THNopl This product have an invalid $Detail key solution

I will like the final query result to look like:

Final Result Query Result:

Product_Solution Product_Description
888 THNopl This product have an invalid 888 THNopl key solution

As you can seefrom the "Final Result Query Result", we replaced "$Detail" with Product_Solution (888 THNopl) information if $Detail appears on this record row and we do not do anything if there is no $Deatil word on the Product_Description column. I would like to do this in MS Access Query only and not in VBA.


Thanks a lot.

 

[Rick_A]

In your query, create a calculated field with this:

Replace([Product_Description],"$Detail",[Product_Solution])

 

[accessforum93]

Rick_A,
Thanks a lot, but it did not work.
I created a calculated field (e.g. Detail_Run: Replace([Product_Description],"$Detail",[Product_Solution]). The output looks like the below:

Original Data:

Product_Solution Product_Description
888 THNopl This product has an invalid $Detail key solution
y7re record2 2nd product and the other options


Query Result Data:

Product_Solution Product_Description
888 THNopl This product has an invalid 888 THNopl
y7re record2 #Error

As you can see from the above"
1) "key solution" is missing from the first record
2) I am getting #Error on Product_Decription column because there is no $Detail information for the 2nd record.

Do you think that we should use IIF function and how?

 

[vbaInet]

Just this:
Detail_Run: Replace(Nz([Product_Description], ""), "$Detail", Nz([Product_Solution], ""))

 

[accessforum93]

VBAInet,

Thanks a lot, but what does Nz represent?
It is partially working, but it is truncating some fields.

Oringinal Query Data:

Product_Solution
<ul><li>The certificate names (axebrandguide.com, secure.axebrandguide.com) do not match the host's fully qualified name.</li></ul>
Product_Description
The remote host has presented a certificate that does not meet the requirements for establishing a secure session. The problems detected were: $DETAIL

Query Result:
Detail_Run

The remote host has presented a certificate that does not meet the requirements for establishing a secure session. The problems detected were: <ul><li>The certificate names (axebrandguide.com, secure.axebrandguide.com) do not match the host's fully qualif

As you can see above, it should be
The remote host has presented a certificate that does not meet the requirements for establishing a secure session. The problems detected were: <ul><li>The certificate names (axebrandguide.com, secure.axebrandguide.com) do not match the host's fully qualified name.</li></ul>
instead of
The remote host has presented a certificate that does not meet the requirements for establishing a secure session. The problems detected were: <ul><li>The certificate names (axebrandguide.com, secure.axebrandguide.com) do not match the host's fully qualif

We are almost there. Is ther a way I can define the data type for a calculated field to solve the truncation problem or do you have any other solution?
I really appreciate your help and thanks a lot because we are almost there.

 

[vbaInet]

I suspect you ran a query to update a field that has a datatype of TEXT?

Nz() substitues Null for what is in the second argument.

 

[accessforum93]

VBAInet,

I declared the original field as a memo datatype. However, the calculated field does not have any datatype assigned to it. I displayed the Product_Decription field before using it as part of a calculated field and the entire data was displayed correctly. Is there a way I can assign a datatype to the calculated field before using it? Do you have any other suggestions?

Thanks a lot for your help.

 

[vbaInet]

In that case your query is a Totals query which is causing the text to be truncated.

 

[accessforum93]

Do you have any suggestions on how to solve this problem? I need your help. You have been very helpful.

Thanks!

 

[vbaInet]

In the Total row for the Replace() field, try changing GROUP BY to EXPRESSION.

 

[accessforum93]

I tried it, but it did not work. This is driving me me crazy.


Thanks a lot

 

[vbaInet]

Here are some other workarounds. Give them a go:

http://allenbrowne.com/ser-63.html

 

[accessforum93]

Thanks a lot. I will try it.

 

[accessforum93]

Unfortunately, I was unable to get enough information. I mean ran into a "dead end" solution.

Thanks for your help anyway.

 

[vbaInet]

All hope is not lost.

Do not include that memo field in the query, create a new query, select all the fields from the Grouped query into this new query, drop the table that contains the memo field onto the query grid and link it up, then include the memo field.