Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
redyul
Starting Member
1 Post |
 Posted - 2004-07-03 : 12:29:42
|
| 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 |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-06 : 20:34:40
|
| You probably have some empty date fields. You'll need to modify your query to remove the NULLS.e.g.IIF([Data_Doc]="", NULL, DateValue(Replace([Data_Doc],".","/")))This will return a NULL if the source field is empty. Access should recognise NULL as a valid date and not throw an error.HTH,Tim |
 |
|
|
|
|
|
|
|
problem converting string with date in to date !!!