Replacing strange text
I'm pulling text out of a LIMS Database via ODBC and the bit I want is surrounded by strange characters which I'd like to remove (example attached).
Any ideas how to do it in VBA or what the character code is
Count characters between other characters
What's the VBA code to return the number of characters between specific characters? For example, I need to find the number of characters between two hyphens - "-xxxxxx
How to query large table for matching beginning characters?
Trying to screen and find duplicate companies in a data base of over 18K records. Can't use specific characters (ex. all starting with ABC).can I ask to match 3 or 4 duplicate characters of records without specifying the characters themselves? Can these be a number of characters in the beginning, end, or in the middle of the word(s)? Can I ask to ignore characters in name sucha s - or / or space between multiple word names?
Import in Access (2003)
I want to import a txt.file in Access. If I do it manually, everything is OK. But if I want to do it by VBA : DoCmd.TransferText acImportDelim, "ImportSpecificationBasicFile", "BasicFile", GetImportFile(), True
where BasicFile the name is of the file where to import
GetImportFile() the name is of the file to import
then I receive at some places in the table strange characters :for exemple : Pav d'Ath / Rue Flix Bovie / Rosires
It seems that there is a problem with the special french characters : ,
Could it be a prbl with the codepage? and so how can I resolve it and include in this VBA sentence
Counting Used Characters in a Microsoft Access Text Field
You should be aware that, when in Microsoft Access, that a text field will hold a maximum of 255 characters. If you require more characters then you should be using the memo type data field.
There may be the time where you want to restrict your users to using only a certain amount of characters when using a text field. You can set the field size when designing the table and limit the amount of characters allowed, however it would be nice to inform the user of how many characters they have used when they are entering data.
Hiding Part Of a Field
I have a field with 12 characters. I only want to show the last 4 characters in the field. How can I hide the first 8 characters? Everette A little more information, what I have is a 12 digit order number which is stored as a text file. I need the last 4 characters in another field. I don't want to delete it from the order number field, but just want to show the last 4 characters in another field. Currently I have to manually do this process, it would be very nice to have it done automatically
Storing ISO Latin-1 Characters in Access
Is it better to store in Access (or any other database) ISO Latin-1 characters, such as those with a grave or acute accent, with the characters themselves, or with the HTML code that is used to display those characters correctly on a web page (when the database is used with something like ColdFusion)? Are there advantages to one way over the other? Disadvantages? Please, spell it out
Creating a Unique Employee ID
I am trying to create a unique Employee ID, but I am having a few issues.
The ID must contain 7 characters in the following Format.
* Characters 1-3 = "LTS"
* Characters 4-5 = Starts at "AA", then when 6-7 reaches 99 changes to "AB" and so on.
* Characters 6-7 = Starts at "00" or "10" (Doesn't matter), and rolls through to "99" before reseting to it's start point, as well as change characters 4-5 to the next in the sequence.
We already use this method but on paper and a looking to move to an electronic system,
Help with access expression
I was hoping someone could assist with the following expression in access that combines the Left function and Len function:
I have column called ID, which has about 700,000 records and each ID can be anywhere from 7 to 10 characters; however, I all IDs to have only 7 characters and this means removing characters that are greater than 7. For example, smith09 is fine, but smith09990 would need to reduced to smith09. Basically, I need an expression that will check every ID and, if ID 7 characters, use the left function to cut it to 7 characters.
There are over 700,000 records in this table:
filter by the number of character
our trading partner code used to be three characters long. However since the company growth we now using the 4 characters code. How do I filter our all the 1 that have 4 characters.
Mid, Left, Right Functions
The records in my table field contains [first name], [last name] together and I can't remember how to separate them without defining the number of characters. What I would is to read any alpha characters before the comma (for first name) and alpha characters after the comma (for last name).
Field value that contains Alpha Characters
I am looking for a phrase based on if a field (NAME) has 4 characters or more. If the field starts with 4 characters or more. I want to take everything up to the first space or the end of the field, whichever comes first, and put that into another field called (SECNAME).
Query to display entries of 14 characters or more
I have a table (containing over 100,000 entries) which has a (text) field containing entires ranging from 1 character to 17 characters long. I am triying to create a query which will only list entries which have 14 or more characters. Can someone help me on this please
Account ID - Generator
Such as Students, and Courses and then create an Account Code by selecting the first 2 characters of the Course Name, the first 4 characters of thestudent's Last Name, the first 3 characters of the Student's First Name, and lastly the Course Date and creating an Account ID with that information ? Example (TN-SCHRHEA04032012)
Query to chunk out long text
I have a table and one of the fields in it is a text field with content lengths of typically 100 characters long.
I need a query break up the records in this text field to a max of, say 30 characters.
This means that if a record is 100 characters long, the query will put the first 30 characters (preferably up to the last space before the 30th character), then the next 30 characters will be on the next record and then the remaining in the next record.
So in this case, 1 single record in the table results in 3 returned record in the query.
Joins with fix width field
I have a table from an data warehouse that I am trying to tie to an access database I created.
The fields I am trying to do the join in, it's kinda difficult. In the data warehouse, the field, say ID, represents 10 characters, including spaces. it's always 10, though.
The value could be 1234568, 123456, or 1234567890. If there are only 5 characters, the other 5 characters are blank spaces.
The table I am entering the data into, in my database, the values are not led off with blanks. We input 1234568, and it is only 7 characters.
How can I make the two fields recognize eachother based on the Right characters. I never know how many "actual"
I have an append query that is appending to a text field. However, I have some special characters that is causing it not to append because of a data type mismatch. Does anyone know which characters will not append
Account ID - Generator
Is it possible to Query seperate tables, such as Students, and Courses and then create an Account Code by selecting the first 2 characters of the Course Name, the first 4 characters of the student's Last Name, the first 3 characters of the Student's First Name, and lastly the Course Date and creating an Account ID with that information ? Example (TN-SCHRHEA
form won't go "full screen"
This is REALLY strange. I have a database where the continuous forms will not go full screen. They have a strange rounded edge at the top, and overlap the other forms
Checking user input is valid
I am posting today because I need some help to check user input is valid.
I have a field with 11 characters.
Characters 1-3 must be either 'OLD' or 'NEW'
Character 4 must be a double-colon
Characters 5-7 can be any number between 000 and 999
Character 8 must be a dash
Characters 9-11 can be any number between 000 and 999
I have tried to use an input mask but as far as I know it can only check if a character it a letter or number
To complicate matters further the user will be entering data in a form using datasheet view and all 11 characters must be entered\stored in one field