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.
case1)
Data_DocNew2: DateValue(Replace([Data_Doc],".","/"))
case2)
Data_DocNew1: CDate(Replace([Data_Doc],".","/"))
case3)
Data_DocNew: DateSerial(CInt(Right(Trim([Data_Doc]),4)),CInt(Mid(Trim([Data_Doc]),4,2)),CInt(Left(Trim([Data_Doc]),2)))
as soon as I create new query based on the new date with a simple
criteria as filter: >=#01/10/2003# I get in case 1) and 2) "data
mismatch", in case 3) "Invalid use of Null".
can you please help?
Giulio