Solved How to format unbound MS Access to Receive a Pure String

[nector]

I think I neglected this one, how do we format unbound control in MS Access form to hold a pure string that can be referenced in VBA and recognized as a string?

 

[theDBguy]

You shouldn't need to do anything. What are you seeing? Can you show us?

 

[GPGeorge]

Do you mean you want to validate the data in an unbound control to ensure it's a string? If so, the Before Update event is the place to do that.

But that raises the question as to what you want to include as "strings". Consider this group of digits 888 282 9999

Is that a set of three numbers or is it a string of digits and spaces?

Before you get to the task of validating input, you need to lay out the rules for what you will accept and won't accept as strings for your purposes here.

The reality is that virtually any group of characters you enter can be interpreted as a string, regardless of the actual characters entered. Even something like "@1#3%7^><?/|{}\" is a "pure string".

So, again, what you need first is a clear definition of the characters you will accept and which ones you won't.

 

[nector]

Do you mean you want to validate the data in an unbound control to ensure it's a string? If so, the Before Update event is the place to do that.

Here is what I want to enter : CZ110062536406AETY

 

[Minty]

So what are the rules ? What are you trying to check? is it always the same length?
Does it always begin with CZ and end with AETY?

Would AB99/-+5555485ZYXW be valid?

Giving us one example is not really helping answer your question is it?

 

[sonic8]

Here is what I want to enter : CZ110062536406AETY

Can you please clarify the issue you see with just entering the above?

 

[GaP42]

That is not a definition of what you want to allow or not allow, as expressed by @Minty
Length of the string - if needed up to 255
Characters in the string: A-Z, a-z, 0-9, and allowed special characters (~!@#$%^&*() etc).
Define each set.

One approach is then to check each character in the string against each char in each defined set (using instr). Set a boolean var as false on entry to the loop set. Only set it to true if it passes your tests. Any fail of a char would mean it was not a valid char in the defined sets.

 

[Mike Krailo]

Sometimes pasted text from a website adds additional unwanted invisible control characters. Is that what you are talking about? How is the text entered in?

 

[The_Doc_Man]

It would be possible to look at a "regular expression" parser. Most people here would use that, and I wouldn't tell you to not use it.

However, there IS a bit of code in the code repository that might help in more generalized parsing.

Text Parsing Support as Class Module

I've been working on something I used a lot at work to help me more easily decipher complex text inputs. For the Navy, I used this as a part of a program to analyze IIS logs, decode complex names, and I even once used a variant of this under OpenVMS to build a command-line calculator for the...

www.access-programmers.co.uk

For your proposed data entry check: that particular parser, if you set the options correctly, would immediately tell you in a single parse whether you had a "pure string" (defined in this case as an alphameric string with no punctuation marks or spaces or control characters embedded in it.) Note, however, that if there is a specific pattern to be followed - like 2 x text, 12 x digits, 4 x text - it would still help you but not in a single call. It all depends on how strenuous your test has to be. I'm not pushing my code. Other methods exist to do what you want. But my code is one possible way to get it done.

If you decide to use my parser and have questions, you can just post a message here with the @ sign immediately followed by The_Doc_Man (no spaces between the two). The forum software will alert me and I will see it.

 

[jdraw]

Does anyone understand the requirement sufficiently well -- and can provide a description of same?

 

[gemma-the-husky]

Given that @nector is working with Json files, I wonder if the problem is an issue that I also faced.

I was working with a REST API,. One of the options was to download a pdf of a delivery ticket. This worked by the rest API sending a response as a string of bytes, which had to be saved into a .pdf file.

I couldn't get it working as access insisted on making the string that was downloaded unicode (is that correct?) with 2 byte characters, but then the .pdf file was rejected and it couldn't be viewed.

The only way I thought I might be able to do this was to have a c type module (external function) that could process the string of bytes without treating it as unicode, but I didn't have the skills to do that.

So I don't know if @nector is trying to do manipulate a string of bytes in that way.

 

[GPGeorge]

Given that @nector is working with Json files, I wonder if the problem is an issue that I also faced.

I was working with a REST API,. One of the options was to download a pdf of a delivery ticket. This worked by the rest API sending a response as a string of bytes, which had to be saved into a .pdf file.

I couldn't get it working as access insisted on making the string that was downloaded unicode (is that correct?) with 2 byte characters, but then the .pdf file was rejected and it couldn't be viewed.

The only way I thought I might be able to do this was to have a c type module (external function) that could process the string of bytes without treating it as unicode, but I didn't have the skills to do that.

So I don't know if @nector is trying to do manipulate a string of bytes in that way.

Ah, that makes sense. I forgot about the lengthy previous discussions involving JSON.

If the requirement is to create strings that are "pure" in the sense that they include only ASCII or UTF-8, etc., then some REGEX approach to cleansing the strings would indeed be useful.

I may have mentioned this in one of those previous JSON-related threads. I retrieve information about books from the Google Books online catalogue with an API returning a JSON file. I often find that certain elements of the JSON include control characters and other characters that can cause problems parsing the results into Access fields. I use Tim Hall's VBA-Web and one or two procedures of my own to cleanse the input for my purposes. One of those looks for internal quotes in a string and converts them to so-called smart quotes (which ironically can cause problems in the other direction if they are inadvertently used as delimiters.). Internal quotes can throw off the parser because they create what appear to be value delimiters, when they are actually not. e.g. "The New York times calls it "one of the best novels of 2012" and gives it a thumbs up."

That's just an example of the problems when parsing JSON in VBA in general. But it also illustrates the fact that creating general purpose parsing routines is a tough nut to crack unless you have a good idea of what the incoming JSON should look like.

It would help to know the details behind the example offered here.

 

[nector]

Thank you people all your answers are valuable no answer is wrong , it now turned out that I just needed to enter the value the way it is in unbound control and Json accepted it as a string