I am working on an import spec from a text field and there is a field with 4 digits only. Is it possible to import this directly as a date field or will I need to import as number or text and then run a conversion to get it to a date field?
View RepliesI'm trying to derive the month in digits from a date/time stamp field. Any month between 10 and 12, I need the first 2 digits but anything <=9 is only 1 digit.
MONTH: IIf(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],2)="10" Or "11" Or "12",
(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],2),(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],1))))
IIF/left statement not returning false argument
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 series of fairly large csv files which I need to consolidate by importing them to a single Access database. There are of the order of 50-60,000 'lines' per csv and about a dozen different files (all comma de-limited and text-qualified with inverted commas).The problem I have is, there are several date fields within the csv which are not formatted to Access' liking. Namely <Mmm_dd_yyyy_hh:ssAM> with underscores used here to represent spaces.
So for example, today's date appears thus :
Feb 6 2013 12:00AM (i.e. two spaces between 'Feb' and '6')
And this day next week would appear thus :
Feb 13 2013 12:00AM (i.e. one space between 'Feb' and '13')
I will obviously need this field recognised as Date/Time so I can query appropriately but I have no control over the source data and need to find a way of converting the format.One option would be to run a script on the csv's themselves prior to importing but I'm not sure how to do that.import this field as Text and then edit the contents after import (VBA?), such that the field can be redesignated with a data type of Date/Time.
I have a table that imports data - including date of birth. the format is dd.mm.yyyy - the db keeps throwing up the error - Birthdate must be 8 digits. The only validation I have for the field is "<date()" to ensure that a future date is not entered.
Help!
I need to import an excel file with incorrectly formatted date field and it worked only to import them as text:
1 jan 2015
1 feb 2015
1 mar 2015
etc.
Using CVdate converts jan, feb, nov and dec to correct date, but gives an error message with mar to oct.
I am having massive problems with data – I have imported data only to find out that Access has not imported all of it as there is a max of 255 fields, and I have loads more!!
I think I can get round this by importing without the delimited or fixed widths into a memo field but this gives me another problem, the the first 12 digits are the ‘Ref_No’
So I have a table called tblCustMemo that I have imported, and I have 3 fields, ID (Primary Key), Ref_No (new and totally empty) and Memo.
How can I put the first 12 characters from the memo field into the Ref_No field?
Then remove the first 13 characters from the memo field (there is a space after the Customers reference number)
I presume I would do this by update queries, but I am currently at a loss!!
I tried this, just to get the first 13 characters then was going to try update query, but this only returned 7 records out of 8687!!
SELECT Custmemo_TEST.ID, Custmemo_TEST.Ref_No, Custmemo_TEST.Memo
FROM Custmemo_TEST
WHERE (((Custmemo_TEST.Memo)=Left([Custmemo_TEST]![Memo],13)));
I'm trying to export from a text file. However one of the fields contains 2 items of data, ie the code and name of a person.
eg 'po2 Paul O Mahony'. What I want to do is query only the code(first 3 digits) from this field. ie 'po2'. Is there a way of doing this within a query (sql) or do I have to use vbscript to solve this?
thanks
I need a field to permit only numbers. Upon selecting number for the data type I select Long Integer for a longer number... But it only allows 9 digits.. I need it to allow to enter in longer numbers... Any help?
Thank you very much in avdance
I'm trying to use select case on the 1st two digits of a field. I f it equals 1- then the case is meet but its not working. Will this work or no?
[CODE Case (Left(Me.Step42, 2) = "1-")][/CODE]
The field SECL DDI has the users phone number unfortunately over time these have been entered in different formats so there are 5 digits, 6 digits, 7 digits etc...Can I run a query that counts the number of digits in each filed and then tell me how many of each exist
Ie
4 digits 3412
5 digits 5000
Hi,
I have created a table to import data from another source (a text file). The date in the file to import is like this...
11 January 2008
I made the 'date' field in the access database a text field, and all is imported ok. however, i now need to manipulate dates in my database. changing the 'date' field data type to 'long date' coverted all data currently in the database, but now i can't import any new data.
I can't see any differences between the format of the date in the text file to import and the data type of the 'date' field in the table.
any ideas?
thanks, rik.
I get the following error in both blocks of code: Field 'F1' does not exist in destination table <myTable>
Code:
Private Sub ImportFile(strFullFile As String)
Application.SetOption "Confirm Action Queries", 0
Application.SetOption "Confirm Document Deletions", 0
Application.SetOption "Confirm Record Changes", 0
DoCmd.RunSQL "Delete * from do_not_call'"
DoCmd.TransferText acImportDelim, "", "Do_Not_Call", strFullFile, False
[code]...
I am importing a txt file from a vbform. However, sometimes the file creation is delayed so I need to check the DOS modified/created date prior to running the query. Can you please provide some insight as to how to determine the txt file's modified/created date from vba?
THanks,
I am importing data that has been put onto Notepad into an Access table - this works fine except the date in notepad comes across as a date and a time. When I put the table into a query and ask for the date to be Between [Specify date 1] and [Specify date 2] - does the user have to then put 7/02/2005 00:00:00 AM and xxx. I have tried this but it doesn't return all values. I have also tried just the dates but this doesn't return all values either - any ideas?
View 6 Replies View RelatedI want a string of code that allows me to import a series of delimited text files into access using VBA. I have the first bit of code down:
Code:
DoCmd.TransferText acImportDelim, deltxtimptbl, "Delivery(local)", "msfs3109data1shareeveryoneprorep ranhistDelivery" & currentdate & ".txt"
The text files hold data for a specific days work. Each day has its own text file. I would like to be able to import the text files within a specific date range, specified by the user. Below is the total code i have for my form button:
rivate Sub Command0_Click()
Dim startdate As String
Dim enddate As String
Dim currentdatex
Dim count As Integer
count = 0
[Code] .....
is there is an easy way to create a table with defined field properties.
I have an excel file with over 300 column names and there field length.
I can import the name by transposing the list and then importing however the field length will have to manually edited which i don't fancy doing,
any ideas???
Field NameLength
BATCH NBR5
BATCH DATE8
BATCH CODE3
SUB PRODUCT3
FILE TYPE1
We are importing data from XML files manually to an access Database.
The imported tables are all text fields with field width 255.
Some text fields are too big and we end up loosing some records as import errors. Is there a way to let access generate an imported table with a text field that is longer than 255 character ?
I am building an Access database for a client. It is an employee staffing database. With that being said they would like the ability to automatically populate the "T2PPCD" date field based on what is entered into the "Report Date" field. (Same table)
The date is the Monday after 180 days from the report date.
I already know how to get it to auto fill 180 days from the "Report Date" but I'm not sure how to tell it to give me the Monday after 180 days.
I'm able to do the import, but I can't get the time of day to show up in military format, which seems more useful when I'm wanting to filter out certain blocks of time later on.
The attached jpg shows the format of the source txt file. The time is shown as 09:30 AM for instance. The attached screen shot for the import text wizard doesn't mean much to me at this point, because no settings seem to work. I've also attached the Import Specification window.
What's been happening is I've been getting a table with field2 showing time in this format 09:30:00 AM where the AM is random and unreliable followed by field3 showing the correct AM/PM designation. This is when I just let all the text wizard stuff run by default settings.
I can then go into the table in design view and change the format of the time of day to hh:nn which gives me 09:30, but then I'm still stuck with the problem of converting all the figures in fields 2 and 3 into something I can work with. BTW, Hh:mm:ss defaults to hh:nn.
Would it be better perhaps to first convert my source file to another format such as csv?
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?
Hi
I have imported 8000 records into a table called competency.
the table does not have the emp id, but needs it. I have a table called employee information which has the emp id as the primary key. I want to create a simple update query to copy the emp id (autonumbered primary key) field from the Employees table to the (now blank) emp id field in the competency table.
I created a query in design view, added the two tables
included the fields from Competency, Emp ID, Last Name, First Name and in the update field for Emp id I added this
"copy[tbEmployeeInformation].[empid]" In the criteria field of last name I typed a name and ran the query, it gave me the correct amount of records to update, but when I ran it, it gives the type conversion failure error. I checked the fields and they are the same in both tables (type, length, indexing)
What am I doing wrong?
Marlie
I am importing information into a table from excel. The number is formatted correctly in excel and is displayed as 10309976464180, but when it is imported into access it displays as 1.030998E+13. I have tried all of the possible formatting for numbers but nothing corrects this, and if I change to Long Integer it actually removes the numbers. What is the correct formatting in Access to get these numbers to display correctly?
View 2 Replies View RelatedI'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
I have a database that uses the SSN as the primary key, but only the last 4 digits of the SSN need to be shown on the reports that are generated. I can't figure out how to use the Left function to extract only the last 4 digits. For example if a SSN were: 123-45-6789 I would want the field to read ***-**-6789, or just 6789 whichever is easiest. I would appreciate any help that could be given.
View 3 Replies View RelatedI need to pull only the last 4 digits of the SSN. How do I do that in my query?
View 1 Replies View Related