Importing Alphanumeric Data Into Text Field
Mar 31, 2008
I have searched this forum but didn't find the answer, I hope I'm not the only one with this problem.
I have two text fields in a table that are updated through an excel file import. In the excel file, both fields contain data that is mostly numeric, but there are always about a quarter that contain letters as well. When I go to import the excel file, it sets to null any value in the fields that contains letters. If I sort the excel file in descending order for the field, it will import, but I have two fields that do this, so this brings more problems. Does anyone know why you can't just import anything in any order into a text field?
View Replies
ADVERTISEMENT
Sep 30, 2005
I have a text field in a table that contains an alphanumeric code. i.e.
DEL998
DEL999
DEL1000
DEL1001
SUN998
SUN999
SUN1000
SUN1001
SUN1002
etc.
I want to run a query to find the highest number for a particular alpha code. In the example for DEL I would want the query to return DEL1001.
I have created a select query that asks for the alpha code, selects all codes starting with that code, sorts them in decending order and only displays the first record.
The problem is that because the field is a text field the numeric is not sorted like a number. So in the DEL case the query returns DEL999.
Any ideas on how I can get a result of DEL1001??
Thanks for your help
View 1 Replies
View Related
Nov 13, 2014
I have an existing form where users type in information and it generates a couple of reports. In one of the fields, Customer PO Number, the user enters a number from a customer. Up until yesterday all of the customers we have been dealing with have used numbers only for their PO numbers. However, we have a new customer that requires alphanumeric PO's. Is there a simple way to change this field from a number to alphanumeric without having to redo each form, report and/or query. I am using MS Access 2010.
View 9 Replies
View Related
Dec 27, 2013
I have a table having the following fields:
StaffNo TextField PrimaryKey
Name
....
...
The data in StaffNo will be alpha numeric, like AKA-111, AKA-112, LMN-100, LMN-102
Here AKA and LMN describes the Sites where employee is working.
On Add New Employee, When user enter AKA- in StaffNo, on exit the next number on that site should be generated. i.e AKA-113.
View 8 Replies
View Related
Aug 31, 2006
I'm learning as I go and this is probably a basic question but if I have an alpha numeric field of variable lenght, i.e. AUI856Z....how to I format it so that it is 19 characters long with leading zeros, i.e. 000000000000AUI856Z.:confused:
View 5 Replies
View Related
Aug 22, 2006
I've had a look through the many topics on text importing, but can't find anything specific to my problem.
One of our suppliers has started offering their catalogue as a CSV file via e-mail. For now, I am saving the file to my computer, and wanting to import it into a database.
the table is a bit awkward, as it has "useless" data in the first field. Here is the beginning of one as an example:
VIP Computer Centre Ltd. Trade Price List. 22 August 2006 3:37 PM
ORDER CODE,PROD GROUP,DESCRIPTION,WTY,BOXED IN,1 OFF,5 OFF,20 OFF,UNIT
7719-C,BAREBONE SYSTEMS,JW MINIQ 430AV INSTANT-ON B/B,0,1,50,49.75,49.25,EACH
I imported the data via the Get External Data feature into a new table. This worked fine. I then changed a couple of values, and re-imported the data, this time selecting to import it to the new table. It came up with an error, saying 295 records were lost due to key violations. I then realised that this only appended the data on the end of the table, which isn't what I want.
Here is what I am trying to accomplish:
The field names are created from the titles in row 2 of the CSV file
The table is updated by importing the latest CSV file, so that any price alterations are changed, and any new products added (but identical data is ignored). If one field is needed as a constant, this would be the ORDER CODE field
Field 2 (PROD GROUP) becomes a combo list box (probably based on another table - so the table contains the categories, e.g. BAREBONE SYSTEMS, and the text from the CSV file is converted to the appropriate ID number)
Is any of this possible? If so, how?
Thanks in advance for any advice you can give!
View 3 Replies
View Related
May 16, 2013
Here is the import statement I'm using:
DoCmd.TransferText acImportDelim, "IT315 Import Specification_txt", _
"IT315", "D:4533 Hires Rehire Status ChangeIT315.txt"
I can manually transfer the data ie thru File --> Get External Data etc but I can't seem to get the above statement to work --- with the same specification!!
The details are in the attached doc file.
View 6 Replies
View Related
Aug 13, 2013
I am having some difficulties with a Date/Time Field. I am importing a | delimted text file into a table and the Date Field is resulting in a Type Conversion Error.In the raw text file, the Date Field has the following Format (example): 01/03/2013 03:11 PM
My import Spec is as follows:
File Format: Delimited
Field Delimter: |
Language: English
[code]....
The only thing I can think of, is that the mix of Leading Zeros in the Time AND AM/PM is causing a problem. But, I do not see a way to address this with an import spec.The odd thing is that if I import the DateOpened Field as Text, THEN change the DataType to Date/Time AFTER import, then save the table, it recognized/converts the DateOpened Fields correctly.I'd LIKE to get the import spec correct (I have to update twice daily), But, barring that, if I could import as Text then build a Macro that would:
1) import text file(s)
2) change certain fields datatypes to Date/Time
3) Save Table(s)
That would suffice. I could then use VBS (and perhaps windows scheduler) to run the macro when needed.
View 1 Replies
View Related
Apr 28, 2014
I am importing an excel text field into a table. I am then appending the data to another table. I am having a hard time with the Cdate function
Excel Date Field:
YYYY.MM.DD HH:MM:SS
Example Value:
2014.04.21 18:24:30
My Query Code:
NewDate: CDate(Left([DateQ],4)+"/"+Mid([DateQ],6,2)+"/"+Right([DateQ],2))
My Query Result:
4/30/2014
What it should be:
04/21/2014
View 12 Replies
View Related
Jun 12, 2013
I have a file that I need to import on a regular basis. There are two different issues with the import:
1. the first digit in the file indicates if it is a record I want to keep. In this case a 1 indicates a repair record and an 8 indicates it is just information such as email, contact info etc.
2. I have a field in the file that may change the import specifications
For example:
If the record is a Non wheel repair then column 167 is a 50 character description
If the record is Wheel repair then column 167 is a 28 character description and the remaining 22 characters are broken down into 10 different fields
So I need to do the following
1. Read the first character in the line and determine if it is a number 1 and if not discard it
2. Read a field in column 109 that is two characters long and if it says it is a wheel repair the import will break column 167 -216 down to the appropriate 11 fields and if it is a non wheel repair it will import columns 167 - 216 as one big description field.
I have used the import specifications with Access but it seems this will have to be done in code in a module or something and I am not very good with VB. (only know the basics)
One other issue is I have dates in the file that are 130225 and 1302 (so full date and then Year/Month) if i take out the / date separator in the import spec the full date works but the Year/Month doesn't.
View 9 Replies
View Related
Feb 5, 2015
I have a database I have worked on for the sister company of the place I am employed.I have a field I am trying to make alphanumeric that has been numeric. (PO Number on the main form). I had make it alphanumeric a couple of months ago, but it disabled the Edit Customer Information button on the bottom right of the form to where I cant enter shipping addresses and things like that. It should be able to enter multiple shipping addresses.
I need making the PO Number field button alphanumeric and making sure it doesnt mess-up the Edit Customer Information button. I have attached both versions of my database. The GM at the sister company would also like me to create a Spin button where you can take an old record and keep all of the previous information on it, except it gives it a new Work Order# and you can change the date to something newer.
View 4 Replies
View Related
Dec 1, 2006
hi
i found this code here and it works IF the number comes first and is preceded by a letter
123AA
but it does not work if the letters come first
AA123
here's the code
Public Function GetString(WholeString As String) As String
Dim i As Integer
Dim Temp As String
Temp = CStr(WholeString)
For i = 1 To Len(WholeString)
If InStr(1, "0123456789.", Mid(Temp, i, 1)) = 0 Then
GetString = Mid(Temp, i)
Exit Function
End If
Next i
GetString = Temp
End Function
Public Function GetNumber(WholeString As String) As Double
Dim Temp As String
Dim i As Integer
Temp = CStr(WholeString)
For i = 1 To Len(Temp)
If InStr(1, "0123456789.", Mid(Temp, i, 1)) = 0 Then
GetNumber = Mid(Temp, 1, i - 1)
Exit Function
End If
Next i
GetNumber = Temp
End Function
the probelm is with this line but i'm not sure what it is
GetNumber = Mid(Temp, 1, i - 1)
(i also get runtime error 13) but my data is in the same format as the example i downloaded.
any ideas anyone?
thanks in advance and thanks to the person who created the code
View 1 Replies
View Related
Mar 5, 2015
I've got most of what he wants sorted but this last task I am completely flummoxed. All of his projects are allocated an ID (named Project Reference), starting from P010010 and increasing by 1 each time. I've made a form that allows a new project to be recorded by entering all the details and hitting the 'record' button, but he wants the Project Reference field to be automatically filled in each time (understandably), increasing by 1 from the last record.
So if the last record was P010311, then when the form opens the Project Reference should automatically be P010312.
I've looked into this and found many guides talking about DMax and DIM and strCriteria and whatnot, but no matter how many of them I follow and try to adapt to my own database I can't get it to work at all.
The table the ID comes from is called General, and the field is Project Reference. The ID should automatically be filled into a text box called txtRef whenever the form opens and a button to add a new record is pressed, being 1 higher than the previous ID.
View 1 Replies
View Related
May 23, 2013
I have several thousands of lines of data which I wish to manipulate programmatically, if at all possible. I think that all of the possible permutations are summed-up by the following examples :
123 A text string
2-8 Another text string
A-C Another text string here
3-20 And some more text
3A-126B More text
Some text without any numbers or letters at the left
What I need to do is :
Move the alphanumeric data at the left to the right. So the data would end up looking like this :
A text string 123
Another text string 2-8
Another text string here A-C
And some more text 3-20
More text 3A-126B
Some text without any numbers or letters at the left
I suppose it hinges on identifying where the first space in from the left appears, cutting the string at that point, and
Is this possible, programmatically ?
View 8 Replies
View Related
Dec 6, 2013
What would I need to enter into the validation rule to change the field to alphanumeric only?
View 3 Replies
View Related
Sep 11, 2012
I'm able to import new data from excel just fine, but I can't import updated data from excel due to duplicates not being allowed for a particular field. Is there a way to keep from importing duplicate records based on one field, but still import data from other fields where the information is different from the excel file?
View 5 Replies
View Related
Nov 16, 2007
I haven't seen anyone run into this particular problem on this forum...
I'm importing data from an excel spreadsheet to an MS Access (2007) table. One of the fields in the table is a text memo field able to support more than 255 characters...
Issue:
The issue is that any cell in Excel that is greater than 255 characters is truncated when imported to MS Access even though the field is a memo field. There isn't any documentation on Microsoft's website about this and I don't see any way to work around it other than manually copying the data from excel to MS Access.
Anyone have any input on this matter?
Thanks in advance!
View 3 Replies
View Related
Apr 3, 2015
I would like to enter a couple of alphanumeric groups into a field on an input form. After I enter an alphanumeric group, I hit the enter and the data will add into the field and refresh to empty box ready for next entry. If I continue to enter another group and hit enter, the next group will be added to original field with a comma and a space in between. build the VBA in after update event to accomplish the task.
View 14 Replies
View Related
Jun 2, 2015
I have a list of 30 files I need to import into my access database. The files in the folder are named
1. Txt
2. Txt
3. Txt
4. Txt
5. Txt
6. Txt
7. Txt
8. Txt
When I did the first import I did it by doing an import text file, located the file 1.txt. The Import Text Wizard opened and I picked Fixed With so I could break out the information myself.After dividing that information I hit advanced and changed the field name to match what they should be. Next, then it ask me to save Import Steps and I said yes.
First: how I can use the saved import to import this file again using the text wizard
Second: how can I set it so it imports a numbers of files, maybe one or maybe all 30?
Third: how difficult would it be to all the file name in the first or last column?
View 2 Replies
View Related
Nov 3, 2012
From a table I want a text field which has a path to a file to be copied automatically into a another field of the same table with a hyperlink text type...
View 5 Replies
View Related
Feb 8, 2006
I want to import a text file that is one continuous string at present when i try to import the file it treats the data as one row.
I would like to split so that a new row is created wherever an apostrophe
appears in the string IE (') I can use this as the delimiter when importing as one row, but for some reason it creates errors after field 30
I've included a sample of the data
Cheers
View 1 Replies
View Related
Feb 22, 2008
I have a database that a user wanted created. They gave me code used in another database to import the text file automatically using code. I looked at the code but I don't see where the file is coming from. I am not familiar with the transfertext code. I need to import a text file into an access table. The text file needs cleaning up because it comes from another program and it has breaks and the header repeats in the middle of the file several different times. I need code to clean up before it is imported into the table. I can't see how this code works enough to use it:
Code:
Private Sub cmdImport_Click()
Dim mResponse As String
Dim mDir As String
Dim intRecordLength As Integer
Dim strRecord As String
Dim strRecordType As String
Dim strSQL As String
Dim strID As String
Dim strDate As String
Dim strLev As String
Dim strCode As String
Dim strBranch As String
'************** Check for populated stat table
If DCount("[ID]", "tblStats") > 0 Then
MsgBox "Clear previous data before importing a new file.", vbOKOnly, "Clear Data"
Exit Sub
End If
'************** End Check
'************** Get File
mResponse = GetOpenFile_CLT(mDir, "Select file to be imported.")
mResponse = LCase$(mResponse)
If mResponse = "" Then
'If no path specified, abort
MsgBox "No file selected, import cancelled."
Exit Sub
End If
mDir = mResponse
Do While Right$(mDir, 1) <> ""
mDir = Left$(mDir, Len(mDir) - 1)
Loop
'************* End Get File
'************* Set Label/Form
Me.lblProcess.Caption = "Importing " & mResponse
DoCmd.Hourglass False
DoCmd.RepaintObject acForm, "frmImport"
'************* End Label/Form
'************* Start Import
Open mResponse For Input As 1
Do While Not EOF(1)
intRecordLength = 28
Input #1, strRecord
If Len(strRecord) < intRecordLength Then
GoTo sLoop
End If
'If length is 41
If Len(strRecord) > intRecordLength Then
strID = Trim(Left(strRecord, 12))
strDate = Trim(Mid(strRecord, 14, 11))
strLev = Trim(Mid(strRecord, 26, 3))
strCode = Trim(Mid(strRecord, 33, 2))
strBranch = Trim(Mid(strRecord, 40, 2))
End If
'If length is 28
If Len(strRecord) = intRecordLength Then
strDate = Left(strRecord, 11)
strCode = Mid(strRecord, 20, 2)
strBranch = Mid(strRecord, 27, 2)
End If
'Insert into table
strSQL = "INSERT INTO tblStats VALUES ('" & strID & "','" & strDate & "','" & _
strLev & "','" & strCode & "','" & strBranch & "');"
CurrentDb.Execute (strSQL)
sLoop:
Loop
Close 1
'********** End Import
sExit:
'************* Set Label/Form
Me.lblProcess.Caption = "Import Complete " & mResponse
DoCmd.Hourglass False
DoCmd.RepaintObject acForm, "frmImport"
'************* End Label/Form
End Sub
Please help
Thanks
View 5 Replies
View Related
May 8, 2006
This is simple Access but I am quite simple so help would be cool
all I want to know is how to put text from one field into another, but not every time.
lets explain
I have an order form and two fields , one date booked , and one date requested.
if date booked is empty I want to copy the info from date requested to it, if its full I want Access to say its already booked.
its porbably something along the lines of where datebooked.txt = "nothing" then datebooked.txt = date requested.txt or something like that anyway
View 1 Replies
View Related
Mar 22, 2014
I have 4 fields that are unbound on a form. img1 img2 img3 img4..When these are entered they are all combined and autofill another unbound textbox = imagename.. what i would like to do is from this unbound textbox 'imagename' ..i would like to populate a textbox that IS bound called FileName
[Event Procedure]
Private Sub imagename_Click()
Me.imagename = Me.FileName
End Sub
View 4 Replies
View Related
May 1, 2006
Hi!
I'm trying to import csv files into an Access 97 database, but the filename is different each time otherwise I would just use a commandbutton with the transfertext macro.
I've been searching for help to browse to a file and keep coming back to the same few pages but not being a programmer I can't figure out how to use the code.
Can anyone help me with simple instructions on how to do this?
Thanks.
Anna.:confused:
View 5 Replies
View Related
Aug 7, 2006
We have received a zip file from a new client containing several thousand loans. The problem is that when I open the file as text all of the data is in one "field" meaning instead of going across the data goes down.
loan type,
loan number,
origination date,
loan term,
lo type,
etc....
Then it starts over with the next loan. There are over 17,000 lines.
How can I import the text file and have the loans go across. Even when I have imported the file using comma delimited it still imports them going down. Even if the comma delimited would work, I would still have issues because Access would not know when the new loan began to move it to the next row.
Any thoughts or suggestions? Sorry for my rambling.
View 6 Replies
View Related
