Unanswered: Problem with date convertion from string to date format, URGENT HELP NEEDED !!THANKS
I make an import of a huge file txt table with headers at each page and date format as "20.12.2004"
in order to use the data I first qo a query that exclude all line representing the headers repeated for each page.
now that I have a txt table with several colums I convert back the comlumns in to the format I need to perform other table:
I mainly have to convert text fields in to Dates and numbers.
I have a problem with dates:
considering that in the query i always start from a field of a table that has been imported as a text I have converted the date in the same way multile times but as soon as I create a new query that select a group of dates that I previously converted access tells me that there is eather a "type mismatch" or "invaid use of NULL"
in order to convert the date in string in to a date format I have tried several formula.
they seams all to work since I obtain a date as in example below in each of the 3cases: all theses case they convert the date from : 20.01.2004 in to 20/01/2004.
Import the text file into a new table with the correct Access data types (not all text). All of the dates will be implicitly converted automatically without the need for any data transformation afterwards.
If you are using a generic text data type for all fields so that all rows are always imported then use the IsDate function in your query to highlight non-dates and then copy all validated rows to the new table with the correct Access data types
I have to bring an Excel file every week and it has date that will not work
It is in the following format....18062004 and 7062004 etc and it is something weird.
I use the Len function in fields to get whether it is 7 or 8 characters and then the Mid function and the join them with / in the join. Sometime ago someone posted up how do it with one query field but I lost it
I need the Len field, then 3 Mid fields to strrip off days, mths and year and then the join.