Spliting Numeric From Letters
Jan 14, 2005
i have a field called Items, description
Items Description
1021 125BL/WH
1022 5RD
1023 1458BL/BU/YL
1024 2500GR
1025 5GR
i want to be able to split the numeric from the color code. and add make a new field with the color code, and then have the quantity as a record.
Any Help is apreciated
Mark
View Replies
ADVERTISEMENT
Feb 5, 2008
Can someone show me how I can split the Zip+4 in this query, if it can be done at all?
SELECT
RTrim([CLIENT TABLE].CITY)+' , '+RTrim([CLIENT TABLE].ST)+' '+RTrim([CLIENT TABLE].ZIP) AS [ADD],
RTrim([CLIENT TABLE].FN)+' '+RTrim([CLIENT TABLE].LN) AS NAME,
[CLIENT TABLE].MEMBER, [CLIENT TABLE].PIN,
[CLIENT TABLE].A1, [CLIENT TABLE].A2,
[CLIENT TABLE].MEMBER_CASE_STATUS,
[CLIENT TABLE].RELATION_CODE INTO [CLIENT TABLE 2]
FROM [CLIENT TABLE];
Thanks
View 9 Replies
View Related
May 29, 2005
Hi, Would it be possible to get the first word from a sentance.
For example
Peter James would Return Peter.
John Michael would Return John etc.
The first word could range from 1 to 50 characters.
Regards
Pete
View 2 Replies
View Related
Mar 11, 2014
I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.
The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.
Code for automatic number generation is:
Dim dbs As Database, rst As Recordset, Response
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _
[Code]....
My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?
View 2 Replies
View Related
Sep 29, 2005
Hi
I have the field fullname in my database which is in the format
Mr John Smith
What I have been trying to do is to extract the Mr then the John then the Smith into 3 separate columns. Can anbody help please?
Thank you
Marcus
View 3 Replies
View Related
Sep 21, 2006
Hi!
The structure is like:
C query is based on 2 tables, A and B.
Then D form is created based on query C.
What I did:
Now I split table A to 2 tables, A1 and A2 which are indepandant from each other, and I updated query C to retrive data from A1 and A2.
Problem:
Then the form D which is based on C doesn't work properly.
Question:
Do I need to recreate a Query C because I split table A?
or do I need to recreate a From D because Query C is updated?
Thanks very much
View 2 Replies
View Related
Sep 10, 2004
I have a field in a table that looks like this:
hahafdhzuiwre8878878s
agahh87878jkjkkjk
etc...
there is no set length to the bits between the slashes.
I want to write a query that moves the data into a new table in three separate fields using the slashes as field separators.
Can anyone help ??
View 1 Replies
View Related
Sep 21, 2006
Hi!
The structure is like:
C query is based on 2 tables, A and B.
Then D form is created based on query C.
What I did:
Now I split table A to 2 tables, A1 and A2, which are independant from each other, and I updated query C to retrive data from A1 and A2.
Problem:
Then the form D which is based on C doesn't work properly.
Question:
Do I need to recreate a Query C because I split table A?
or do I need to recreate a From D because Query C is updated?
Thanks very much
View 4 Replies
View Related
Aug 13, 2007
Hi
I am just wondering if there is any way that you can use access to input information into a letter
i.e. you could just click a command button and it would print a standard leter with the details from an address on access e.g. name etc
Thanks
View 10 Replies
View Related
Jun 25, 2007
Hi, I have an address database and in the zipcode/postcode box, i want all letters entered to go to capitals ie rj20 4ls would go to RJ20 4LS after update. how could i do this, what do i add to text boxes after updat?
Thanks
Alex
View 2 Replies
View Related
Apr 20, 2007
How would I about filtering out all results that contain a letter in them? The entries are in a field called Code, and I only want to display the resources that don't have a letter in the code.
View 1 Replies
View Related
Sep 21, 2007
Hi there,
I have one question with regards to the WHERE part of an SQL query. I want to limit my query on datasets, where the first three letters of a certain field (which contains strings) equal "Exp". Is there a more efficient way than the following?
SELECT A.Date, A.Name
From tblA as A
WHERE A.Name="Exp_A" OR A.Name="Exp_B" OR A.Name="Exp_C";
Best regards,
JapanFreak
View 3 Replies
View Related
Aug 31, 2005
We are in the process of converting from Lotus Approach to MS Access.
In Approach we could create letters that would add the fields we told it to.
We do not want to have to use the mail merge to Word. Can letters be created in Access?
View 2 Replies
View Related
Nov 21, 2005
hello
I have a access DB with several names in a table. I need to get only the first letter of each name, but only one for each A, B, C, etc. For example, if I have 3 names starting with a A and 5 starting with the C, it will give me only 2 records, one with the letter A and other with the letter C. I'm using ASP with access database.
tks for the help.
Sneakky
View 11 Replies
View Related
May 5, 2005
Hi all: MS Access 2003. I have two text fields family_name and given_name I need to extract 1st, 2nd & 5th letter of family_name along with 2nd & 3rd letter of given_name to form a link_key field. If either name has insufficient letters the missing letter is replaced by the numeral 2. Does anybody know how to help me acheive this please!
View 2 Replies
View Related
Apr 19, 2007
Now,
I have a big table with sales data downloaded from DB2.
We have branches codes of 1 digit only.
Because of too many branches we have branch codes in upercase and lowercase. So Q ans q are different branches.
I'd like to substitute all lowercase codes like q with something like q_ to make them searchable.
How I can substitute all lowercase letters in one column of the large table in one shot?
Or may be you know any solution how to make Access queries sensitive to the case.
View 11 Replies
View Related
Mar 16, 2005
Hi I'm new round here, and just wondering if anyone could shed some light on a few issues which I am trying to resolve in Access 2000.
Is it possible to make each word start with a capital letter even if the user inputs all the text in lower case,
for example:
user inputs: john smith
Desired display: John Smith
Any help much appreciated
Nick
View 10 Replies
View Related
Jun 25, 2007
Hi!
I am a new member of this forum and new to access.
I need to make a data base and I would to have my primary Key as an autonumber. I would like to use ID numbers that have already been assigned but they have letters in the beginning, they are GKAD0001 etc. so at the moment I just have them as text that wont allow duplicates, but really I would like them as and autonumber so when new data is added it automatically assigns the next number.
Is this possible?
Thankyou very much for your help.
View 2 Replies
View Related
Dec 15, 2005
Hello again.
I’m trying to find a way in a query to remove any data except for numbers
Examples
NET 90
NET 10
NET 10 DAYS
NET 110
NET 15
Any help would be greatly appreciated
Corey
View 5 Replies
View Related
Feb 8, 2006
Hi all, I'm not sure if this is possible ...
I have a table which has contact details in it... two fields are FirstName and Surname. I want to create a new field called initials which takes the first letter from each of the former two fields. Is it possible to do this using an update query?
Or any other way maybe?
View 3 Replies
View Related
Mar 2, 2006
I've been stumped on this and could use some help...
Fields in my table contains some words that are in uppercase and some in lowercase. I would like to delete all the lowercase letters and keep only the uppercase ones.
For example, if a field contains the following text:
CHLOROSULFONIC ACID with or without sulfur trioxide
I would like to change this to:
CHLOROSULFONIC ACID
Can this be done via an automated process?
Thanks!
View 7 Replies
View Related
Dec 20, 2007
ex
columns
Name Day1 Day2 Day3 Day4 Day5 Day6
Jackie s s
Jackie v v
Jackie p p
I would like to be combined into
Jackie s s p v v p
all in one row.
View 1 Replies
View Related
Jan 18, 2008
Hello, I am a new member...thanks for any help anyone can provide.
I have a table with a column in which the fields will come in with various lengths and combinations of characters. I need to look up certain values from this field, and assign a value to another field based on that. For example if field1 begins with an "H*", then field2 will equal "INS".
My problem is that I need to write a statement saying something like if field1 starts with "H" and is followed by only numbers for any length, then assign field2 "blank"
so I cant use "H*" because that includes letters...and Ive tried criteria like "H[!a-z]*" but cant get anything to work(i know the last example only looks at the space after H) Does anyone have any ideas?
View 4 Replies
View Related
Nov 14, 2005
Hi all,
I am new to this game, so please bear with me...
I am creating a form in my database and would like to know if there is a way I can default each starting letter of my surname and address fields with a capital (but leave the rest in lower case)?
I feel that it looks more professional.
Many thanks for any help.
Mark
View 8 Replies
View Related
May 10, 2006
I have an inlogform and pasword change form
i have a table "authorized"
userid is the same as pasword if someone wants to try
the problem is: he can't seperate capitals from little letters,
you can try it...
How can I program it that i can make a difference between
capitals and little letters in userid and password
Greetz
veerle
View 1 Replies
View Related
Dec 23, 2004
Hello,
I would like to create a custom letter where I can type the body of the letter and it will print the letter for every customer in the database.
Thanks
View 1 Replies
View Related