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?
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.
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.
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:
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
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)
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!!
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.
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.
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.
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
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.
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
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?
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.
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.
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?
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...
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 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
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
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
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:
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.
