Access2000: problem when appending data into table

[Arvi Laanemets]

Hi

I need to add data from an Excel table into Access table.
Excel table has structure:
Month (in format yyyy.mm)
PhoneNo (text format)
Service (text format)
EventTime (datetime format dd.mm.yyyy hh:mm:ss)
Length (time format [h]:mm:ss)
Sum (Double format)
CalledTo (text format)

I linked the Excel table into Access database, and created an append query
to collect data from linked table and add to an Access table with similar
structure (with an additional autonumeric EventID field).

The problem is, that in some records in Excel table EventTime and CalledTo
fields are empty (p.e. all calls to specific number in local phone network
are given in bill as summary for month - call times and called to numbers
aren't detailed). And all such records are left out when appending data.

At first I figured, that the problem is caused by EventTime field. I
changed the Excel table so, that all empty times are now changed to
01.01.1900 00:00:00 - i.e there is no empty values in (The CalledTo column
is text, so an empty string there mustn't matter, or what?). But this didn't
work either. Can anybody give some advice here!

Here is my append query:
INSERT INTO Bill ( Month, PhoneNo, Service, EventTime, Length, Sum,
CalledTo ) SELECT [BillX].[Month], [BillX].[PhoneNo], [BillX].[Service],
[BillX].[EventTime], [BillX].[Length], [BillX].[Sum], [BillX].[CalledTo ]
FROM BillX
WHERE (((BillX.Month) Is Not Null And (BillX.Month) Not In (SELECT
Bill.Month FROM Bill)));
The query returns all rows it must return - but some of returned rows aren't
appended into table!

Thanks in advance for help!

 

[Arvi Laanemets]

OK. The case is closed. The problem was in text field - it looks like you
can't append an empty string value ("") into text field ????

The solution was
INSERT INTO Bill ( ... )
SELECT ..., Iif([BillX].[CalledTo] ="",Null,[BillX].[CalledTo]) As
[CalledTo]
....