Update Qry

[Karkim via AccessMonster.com]

I'm using Access 2003. I have a table with a field named DeletionDate.
Majority of all the records are blank except for a couple that have dates in
them. I'm using the DeletionDate in a formula in Crystal Reports XI. I
think the formula in crystal may not be working right because I have blanks
in the DeletionDate field. I tried to do an update qry to place 0 in all the
blank spaces but am running into type conversion errors.
Is there a way to properly populate the field?

 

[Guest]

A Datetime field stores a decimal number and is displayed as a date. The
time is stored as decimal fraction of a day such as 6 am is .25 of a day and
6 pm is .75 of a day.

When you enter data it is verifying that the entry looks like a recognizable
date.

A zero is not a date format that anyone uses.

 

[John W. Vinson]

I'm using Access 2003. I have a table with a field named DeletionDate.
Majority of all the records are blank except for a couple that have dates in
them. I'm using the DeletionDate in a formula in Crystal Reports XI. I
think the formula in crystal may not be working right because I have blanks
in the DeletionDate field. I tried to do an update qry to place 0 in all the
blank spaces but am running into type conversion errors.
Is there a way to properly populate the field?

What do you want Crystal Reports to show if the date is unknown? You're using
a formula - what's the formula?

John W. Vinson [MVP]

 

[Jamie Collins]

A Datetime field stores a decimal number and is displayed as a date.

Only if you *choose* to display it as a date <g>

SELECT FORMAT(NOW(), '0')

Jamie.

--

 

[Rob Parker]

I think it's more accurate to say "unless you choose to display it in some
other format" (such as the example you posted).

Rob

 

[Jamie Collins]

I think it's more accurate to say "unless you choose to display it in some
other format" (such as the example you posted).

Note <g> means I had my tongue firmly embedded in my cheek.

I think the writer was trying to describe the default behaviour
observed in Access Forms' controls and Access's own forms' (e.g. table
datasheet view) controls when displaying DATETIME values in lieu of
explicit formatting and made an admirable attempt in the one sentence
they allowed themselves.

Jamie.

--

 

[Karkim via AccessMonster.com]

Here is the my lastest formula:
Description:

IIF({tblClass.ClassNo}=110,0,
Land Doesn't Depreciate
IIF({master.PurchaseDate}>#12/31/2006#,0, 2006
Assets Only
IIF({@Life in Months}={@Months Depreciated 12/31/2005},0, Asset
Retired before 2006
IIF(Not IsNull({master.DeletionDate}),
Asset Deleted in 2006
12-DateDiff("m",{master.DeletionDate},#12/31/2006#),
IIF(({master.PurchaseDate}>#12/31/2005#) AND Asset
Added in 2006
({master.PurchaseDate}<#12/31/2006#),
DateDiff("m",{master.PurchaseDate},#12/31/2006#),
IIF(({@Months Left to Depreciate 12/31/2005}>0) AND Asset
Useful Life Expiring in 2006
({@Months Left to Depreciate 12/31/2005}<12),
{@Months Left to Depreciate 12/31/2005},12))))))
Show 12 Default

As written - this formula calculates the # of months just for the deleted
assets.
It leaves all the rest of the active assets as blanks. When I remove the
Asset Deleted in 2006 formula then the # of months depreciated works for all
assets except the deleted assets - to which the default 12 shows up for them..

Maybe the problem exists with my formula writing in Crystal - I originally
thought that it had something to do with the blanks in my DateTime field
{DeletionDate} in Microsoft Access.
I would really appreciate any suggestions that you could give. I'm getting
hung up.

Marcus

I'm using Access 2003. I have a table with a field named DeletionDate.
Majority of all the records are blank except for a couple that have dates in

[quoted text clipped - 3 lines]

blank spaces but am running into type conversion errors.
Is there a way to properly populate the field?

What do you want Crystal Reports to show if the date is unknown? You're using
a formula - what's the formula?

John W. Vinson [MVP]