access problem on date conversion please hlp

[Giulio]

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(Repla ce([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(Le ft(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

[Douglas J. Steele]

Are you sure you have a value for Data_Doc for all rows? The errors you're
getting would imply that at least one row has a Null value for Data_Doc.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Giulio" <re*****@tiscal i.it> wrote in message
news:26******** *************** ***@posting.goo gle.com...

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(Repla ce([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)),C
Int(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

[red yul]

Thanks for your answer!
Yes I have double checked, there are no NULL value...

Note: I get the Null answer only in case 3) using the most complex
formula with Date serial:

DateSerial(CInt (Right(Trim([Data_Doc]),4)),CInt(Mid( Trim([Data_Doc]),4,2
)),C
Int(Left(Trim([Data_Doc]),2)))

in case 1)
Data_DocNew2: DateValue(Repla ce([Data_Doc],".","/"))
and
case2)
Data_DocNew1: CDate(Replace([Data_Doc],".","/"))

I have a small sample file I can send if you could have a look.

thanks

Giulio

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!