Access VB Script inserting special characters instead of text

Intelli-Seeker
Intelli-Seeker used Ask the Experts™
on
I have an older Access database (probably created in 2003) that I am opening in newer versions (tried 2010 and 2016). It is a rather complex database. Everything works except one button that calls a vbscript to update data using delete, update, and insert into statements. I cannot say for certain that it worked perfectly in previous versions as I inherited this from a former employee.

The vbscript concatenates data based on information it receives from the queries. The problem I am encountering is that after I click the button, the query inserts special characters instead of the text that it is supposed to grab from one of the other tables.

The fields that are showing correctly in the source table are Long Text data types with Plain Text text format.

I attached a screenshot of a brief sample of the problem I am experiencing.

Special Characters
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Well, hard to diagnose.. but do you really mean VBScript? Or do you refer do code as part of the Access database? Then it is VBA (VisualBasic for Applications), looks similar as code, but it is in fact a separate language.

For better insight: craft a concise example from this database. Remove everything you don't need and upload it. Reduce also the data to some rows only.
John TsioumprisSoftware & Systems Engineer

Commented:
I am under the impression that you don't have a special characters issue.....you just Grouping on fields with Text...remove the GROUP BY and you should be fine.

Author

Commented:
ste5an: My mistake - yes, it is VBA. I am working on multiple projects at the same time and vbscript is in a different program. I am trying to avoid providing the table structure unless necessary to solve the problem.

You are correct John - I do not have any special characters.The query is using GROUP BY. What should I change the Total to rather than Group By? When I remove the Group By in the design view, I get an error "Your query does not included the specified 'Short Description' as part of an aggregate function"
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

John TsioumprisSoftware & Systems Engineer

Commented:
Text CANNOT GROUP BY
Distinguished Expert 2017

Commented:
It is quite possible that the table is corrupted.  Do you see the special characters if you export the data to Excel or display it in a report?
Distinguished Expert 2017

Commented:
Group by isn't the problem.  You can group by short text.  You may not be able to group long text (memo) but short text is fine.
John TsioumprisSoftware & Systems Engineer

Commented:
My memory plays tricks...i am sure that you get that side effect when you preform aggregation on text...but i can't remember the conditions it occurs.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Keep the Group By but replace the Long Text (memo) field with:

Group By
    Left([YourMemoField], 255)

Open in new window

Author

Commented:
Seeing Gustav's response, it may be helpful if I provide the full portion of the GROUP BY in the SQL view. You can see that this is a fairly complex query in my opinion. This is after the WHERE clause. The RA_Threats[Short Description], RA_Threats.Description, and RA_System_Threats.Comment are the offenders.

GROUP BY RA_System_Threats.ID, RA_System_Threats.System, RA_Threats.[Short Description], RA_ThreatRelevance.Title, [RA_Impact]![Score]*[RA_InitiationLikelihood]![Score], RA_Threats.Description, RA_Impact.Title, RA_InitiationLikelihood.Title, RA_System_Threats.Comment, RA_Threats.Identifier, RA_Threats.ID;

Open in new window

Distinguished Expert 2017

Commented:
The group by doesn't help us in this case.  We don't know how each item being grouped is defined.  As we have told you, long text (memo) is a problem and it has always been a problem.  So this wouldn't just pop up.  What does the data look like in the table?  What does the data look like when you export it to excel using the group by and then without the group by.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Well, use Left on the offending fields, like:

GROUP BY RA_System_Threats.ID, RA_System_Threats.System, RA_Threats.[Short Description], RA_ThreatRelevance.Title, [RA_Impact]![Score]*[RA_InitiationLikelihood]![Score], Left(RA_Threats.Description, 255), RA_Impact.Title, RA_InitiationLikelihood.Title, Left(RA_System_Threats.Comment, 255), RA_Threats.Identifier, RA_Threats.ID;

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
If my memory serves me right (maybe... :)  ) in similar case i would perform the Group BY on everything else but the Memo and the resulting query i would join it to the Memo field (the table that contains it)....so you will get all your numeric aggregation and still keep the Info..
I am afraid i don't have handy a sample to test it...

Author

Commented:
Gustav - should the Left also be included on the Select statement or is it only applicable on the GROUP BY?
Distinguished Expert 2017

Commented:
Intelli, did you just change the data type from short to long text?  If not, you are barking up the wrong tree.  If the field has ALWAYS been long text and it used to work but doesn't now, then it could be because the behavior of grouping on memo fields changed but if the field is now short text and always has been, then grouping is NOT the issue.
Long story short, I have no idea if this particular VBA script has worked in the past. I inherited this database. I know that this button is broken and producing special characters instead of the text that is in the field from the source table. I am making the changes Gustav recommended and will post back.
Thanks everyone for your assistance. Adding the Left to each portion of the query that uses the Long Text fixed the issue.
John TsioumprisSoftware & Systems Engineer
I am curious ..if I haven't diagnosed that the error was due to text grouping if a solution would come so easily from the other Experts that shared the points....
I gave you credit for the question also John. You were all helpful. I marked Gustav's as the solution so that someone else with the same issue will see that as the answer. All your responses were helpful since I was completely lost on this.
Most Valuable Expert 2015
Distinguished Expert 2018
It is old knowledge.
It may be old knowledge but it is new to me.  :)

This was an old database. It may have worked in an older version of Access... not sure.
John TsioumprisSoftware & Systems Engineer
I don't have any points ...I will check on my computer if there is a bug on the mobile view of EE...but I doubt.
Keep in mind that Gustav gave a work around...what if your field contains critical information and this is pass the 255 limit...let's say it's about medical allergies and it lists all the foods that if someone consume will die...oops he/she has too many issues with a lot of foods..but we gave them something that was listed beyond the 255 characters..no big deal...the subject died...just pure bad luck...
My suggestion .. while more difficult and maybe harder or " impossible" to implement it is not cutting corners.
Not sure why you would not have points. I used the slider bars to provide credit. If it's not there, then we will need to contact EE support. That's a good point about the character length. I will check the source tables.
John TsioumprisSoftware & Systems Engineer
Just checked...actually you gave some points...maybe it was something in the mobile view of the web page of EE...
50 points compared to 3050 for the "good" solution..... but for 50 points you can take them back and give to the other Experts..
Its like your house is sinking in the mud ...i say to you it comes from the nearby pond ...it has an under ground connection and water is gathered under your foundation...someone else just came and dumped a load of sand...the house is steady for now...but for now......in the meantime you paid the guy with the sand ...but for me you gave me sοme pocket money...maybe i will buy an ice cream.
Distinguished Expert 2017
I see that John has points.
I gave everyone points because you all make good suggestions. I marked Gustav's as the solution because it solved my problem.
My apologies John if this is upsetting. I do not set up the points system in EE. I used the slider bars. It doesn't tell me how many points it gives you when I do that. My goal is that the next person who has the same problem will be able to see the solution.
John TsioumprisSoftware & Systems Engineer
Don't worry...just take the 50 points and give to other i don't need pocket money...just ate an ice cream.... :)
Distinguished Expert 2017
The solution process has been changed multiple times in the past couple of years and in my opinion, not for the better.  I rarely ask questions so I don't get to work with the process of accepting answers.  

I strongly disagree with the practice of accepting multiple solutions as the "answer" especially when they conflict just to reward people for attempting to help so I think accepting one answer is best unless it actually took multiple steps to answer the question.  This makes the thread more valuable in the future to people trying to solve a similar problem.  They can easily identify what worked for you.  When people select one answer from each participant, it makes the point award meaningless and the thread is pretty much useless for reference because there is no way to identify the bad suggestions from the one that worked.

Awarding points to other participants is nice but entirely unnecessary.  It's not like they are worth any money and how many ugly t-shirts do you need anyway?
Good point Pat. My goal was to make this post useful for someone like me who has no clue where to start on this issue. I had no intention of offending anyone. I just want to make sure that the post is useful in the future. EE has been invaluable for me.

John - please continue to participate in EE. I hope this does not keep you from doing that. Your feedback was valuable.
John TsioumprisSoftware & Systems Engineer
@Pat...this is a case a bit  unusual....we have a diagnosis of the problem (by me)...we have a workaround (G) and we have a  solution (maybe...because its a bit harder and maybe we will have some issues implementing it...but without the data i couldn't post the complete code...only directions/guidance ......by me) ..and some usefull Comments (Pat)...i think we all deserve points...but more balanced...
John TsioumprisSoftware & Systems Engineer
@Intelli-Seeker ...valuable advise for 1,6% points ..compared to....how much incentive do you think i will have on the next question...
Maybe i should consider dropping out of EE and start playing Elite Dangerous...i will fly among the stars...fight in a vast universe ...and given the fact i am a great pilot (or at least i was 35 years ago when i was playing the original Elite...why should Tom Cruise should be in great shape as Maverick and not me.....i had some tough long hours fights...) i could make a virtual fortune that i could exchange for real money....:)
Distinguished Expert 2017
I understand your frustration John.  It happens to me all the time.  Sometimes you cause it.  I identify the problem and before the user decides if it makes sense, you post code and your post gets accepted as the "answer".  Technically providing the code is more of an answer to most people than identifying the problem because identifying the problem doesn't actually fix it.  Of course to the expert, identifying the problem is in most cases all that is required.  Implementation, as in this case, is trivial once you identify the problem.   I never suggested code because we never got the answer on whether or not the field in question was long text or short text so we didn't know if we were dealing with corruption or just code that was needed to convert the long text to short text for grouping.   Intelli said the app was old so assuming it had previously been working, corruption was most likely the culprit and I have run into this before and the table was corrupted.  Gus took a stab at code and hit paydirt.
Unfortunately, based on the content of the database, I could not provide the database due to the confidential nature.
Distinguished Expert 2017
I don't think anyone asked you to post the data.  we asked you to look at it under different conditions.  Anyway, I'm moving on.  Good luck.
John TsioumprisSoftware & Systems Engineer
It was time to make some additions to my "log"...moving on also...question closed...
I submitted your concerns over the ratings to EE.
John TsioumprisSoftware & Systems Engineer
@Pat... drop me a note if you see me jumping into...usually you are close but not entirely....i try to read and understand solutions provided by other experts and i try to offer something else that might resemble but is different....after all the tricks are known to all of us...
I talked to to the EE support. It is a UI issue that the developers are working on. That's why there is such a large discrepancy in the point value. The sliders did not register the points. I asked them to correct the issue so that you could get the correct points. They said that they would reach out to you individually.
John TsioumprisSoftware & Systems Engineer
Ok..no problem...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial