type mismatch error linked tables

[Guest]

I have a CSV file that I have linked a Access db to. The csv file conversion
places a zero in the date_discharge field if the record has not yet
discharged. I link the tables and have this field set as type date/time.
Works great except for those zero fields. since I do need set query criteria
based on that date is there a way to handle this data type mismatch.

 

[Jeff Boyce]

It sounds like you are saying that some of your CSV records have no date
(i.e., your "0"). So if there's no date to match on, why bother trying to
match those?

Couldn't you exclude the CSV records with a zero in that field (in a query),
then join only those with a date to your Access table?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

You are correct in that there is no data in that field. In this case the
record has not yet discharged but there are some queries where I need to pull
those record that have not yet been discharge and complete calculations using
the current date to determine how many days these records have been in our
system. Primarily done at the end of the month.

 

[Jeff Boyce]

Back to your original post ... the type mismatch issue. By excluding the
"0"s, you wouldn't have the type mismatch for THOSE queries, right? In
fact, if you needed to do more specifically with the "0"s records, you could
build a query that included only those.

Is the original issue solved and this is a new question?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Let's see if this makes it make better sense. In order to complete the
projects I am attempting to complete. The records that have a zero in the CSV
file also give a type mismatch in the access table. For example:

Record num Date in CSV File Date after link to Access
Desired Date
1 0 #Num!
Null

As we have talked through this I believe the problem must be corrected in
the application that is producing the CSV file but my question essentially is
is there a means within Access to convert that #Num! to a null field.

 

[Jeff Boyce]

I'm having trouble visualizing how you are trying to join the CSV data and
your Access table. If you intend to join on "Null" values, be aware that
Null <> Null. "Null" means unknown, so you can't compare it to anything.

Please post the SQL statement of the query you are trying to use to join the
two data sets.

And where (?form, table, report, query,...) are you seeing #Num!?

Regards

Jeff Boyce
Microsoft Office/Access MVP