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]

Re: access problem on date conversion please hlp

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" <red.yul@tiscal i.it> wrote in message
news:266913ad.0 407030708.77efd 916@posting.goo gle.com...[color=blue]
> 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:[/color]
DateSerial(CInt (Right(Trim([Data_Doc]),4)),CInt(Mid( Trim([Data_Doc]),4,2)),C
Int(Left(Trim([Data_Doc]),2)))[color=blue]
>
> 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[/color]

[red yul]

Re: access problem on date conversion please hlp

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!