Linking a Foxpro Table

[Guest]

Hi!
I am linking a FoxPro table to access do some reports.
The only problem is the dates that are blank from foxpro are showing up as
time 12:00 in Access.
How can I remove this to show blanks.
Is there something I can do in the query or report?


Thanks

 

[Ronald Roberts]

In a query you can try:

MyDate : IIF(isDate(tblDate), Format(tblDate, "mm/dd/yyyy", " ")

From FoxPro Help File.

DateTime Datatype For FoxPro
Choose the DateTime data type to store values that are either dates
or times, or both. A DateTime value is stored in eight bytes — two
four-byte integers. The first four bytes represent the date. The
remaining four bytes represent the time in milliseconds from midnight.
DateTime values can contain both a date and a time, or only a date
or only a time. If you specify no date value, Visual FoxPro adds a
default date of December 30, 1899. If you specify no time value,
Visual FoxPro adds a default time of midnight. For more information
about the specifications for this type, see the tables of Data and
Field Types.

In both Date and DateTime data types, the following rules apply:

{00:00:00AM}is equivalent to {12:00:00AM}, Midnight.
{00:00:00PM}is equivalent to {12:00:00PM}, Noon.
{00:00:00}to {11:59:59} is equivalent to {12:00:00AM} to {11:59:59AM}
{12:00:00}to {23:59:59} is equivalent to {12:00:00PM} to {11:59:59PM}

Date Datatype For FoxPro

Choose the Date data type to store dates without time values.
A Date value is stored in “yyyymmdd†character format. For more
information about the specifications for this type, see the tables
of Data and Field Types.
To assign the Date values, enclose the date value in braces:

dLastAppointment = {10/14/94}

To assign a blank Date value, use braces alone or enclosing a space or
forward slash, as in the following examples.

STORE {} to dBlankdate0
STORE { } to dBlankdate1
STORE {/} to dBlankdate2

In both Date and DateTime data types, the following rules apply:

{00:00:00AM}is equivalent to {12:00:00AM}, Midnight.
{00:00:00PM}is equivalent to {12:00:00PM}, Noon.
{00:00:00}to {11:59:59} is equivalent to {12:00:00AM} to {11:59:59AM}.
{12:00:00}to {23:59:59} is equivalent to {12:00:00PM} to {11:59:59PM}.

HTH
Ron

 

[Guest]

SELECT inspect.name, inspect.co, inspect.mun, inspect.lname,
inspect.finitial, inspect.sic, inspect.dateinvest, inspect.no_employ,
inspect.no_exposed, inspect.no_fac, inspect.reason, inspect.unable,
inspect.v, inspect.v1, inspect.v2, inspect.v3, inspect.v4, inspect.v5,
inspect.closed, inspect.enforceltr, inspect.type
FROM inspect
WHERE (((inspect.dateinvest)>=#6/1/2005# And
(inspect.dateinvest)<=#6/30/2005#))

That is my code above where would I put what you said? MyDate :
IIF(isDate(tblDate), Format(tblDate, "mm/dd/yyyy", " ")

Also, in FoxPro the field is chosen as a date only field.. and the blanks on
the table show as / /
but when its in Access the time of 12:00:00 shows?


Thanks

 

[Ronald Roberts]

Try this

SELECT inspect.name, inspect.co, inspect.mun, inspect.lname,
inspect.finitial, inspect.sic, inspect.dateinvest, inspect.no_employ,
inspect.no_exposed, inspect.no_fac, inspect.reason, inspect.unable,
inspect.v, inspect.v1, inspect.v2, inspect.v3, inspect.v4, inspect.v5,
inspect.closed, inspect.enforceltr, inspect.type,
IIF(isDate(inspect.dateinvest), Format(inspect.dateinvest, "mm/dd/yyyy",
" ") As InvestDate
FROM inspect
WHERE (((InvestDate)>=#6/1/2005# And
(InvestDate)<=#6/30/2005#))

The date in FoxPro is not a valid date, so Access will show the
default value. Access has a DateTime datatype only, not just a date.

This should cause the query to NOT select any records that have blank
dates and also do not match your criteria.

You may get a type mismatch, if so... Try this
IIF(isDate(inspect.dateinvest), Format(inspect.dateinvest, "mm/dd/yyyy",
"#01/01/1900#") As InvestDate