Should Appear Blank

[davies107]

Hello everyone,

im having a problem about my DBS. there is a calculation in the Query then amount or total will appear in the Form. there are some items that doesnt have information in the Table, so obviously calculation wont be involve in that specific item. now, if theres no information to be calculated unbound box SHOULD be blank BUT in every items that doesnt involve a calculation it appear this error ----> #Type!

i want to eliminate that one and make the unbound box to appear blank instead of #Type!

because people might think that is really an error when in fact it will just appear because no calculation took place.

Thanks!
​

 

[bob fitz]

Can you show us the expression used in the query that performs the calculation.

 

[davies107]

Like for example this one:

Procurement Estimate: [Base Cost]+[Contingency Amount]

While Base Cost and Contingency Amount has its own calculation also. Sometimes, Base Cost and Contingency Amount doesnt have values, so I wanted the box to be blank for Procurement Estimate instead of this one #Type!

 

[bob fitz]

Try:
Procurement Estimate: Nz([Base Cost],"")+Nz([Contingency Amount],"")

 

[davies107]

Thanks a lot.

The value appeared is 0 (zero) this is better than #Type!

but, is there a chance it could be blank or no value even 0? if none, then i will have to settle for 0.

 

[bob fitz]

Thanks a lot.

The value appeared is 0 (zero) this is better than #Type!

but, is there a chance it could be blank or no value even 0? if none, then i will have to settle for 0.

I'm not sure. Try:
Procurement Estimate: Iif(Nz([Base Cost],"")+Nz([Contingency Amount],"")=0,"", [Base Cost]+[Contingency Amount)

 

[davies107]

i think i have to settle for the first one because the second one prompted me with an error, it says: "data type mismatch in criteria expression"

 

[bob fitz]

Can you post a copy of your db with a few dummy records for us to play with.

 

[pr2-eugin]

Try this and see what happens..

Procurement Estimate: IIf(Nz([Base Cost],[COLOR=Red][B]0[/B][/COLOR])+Nz([Contingency Amount],[COLOR=Red][B]0[/B][/COLOR])=0, "", Nz([Base Cost],[COLOR=Red][B]0[/B][/COLOR])+Nz([Contingency Amount],[COLOR=Red][B]0[/B][/COLOR]))

 

[davies107]

attached is my database... i deleted most of the items in my DBS and some functions...

try to search for these items:

Fuel Gas Filter
Fuel Gas Superheater

 

[davies107]

@ pr2-eugin

#Type! comes out again with that code, hehe...

 

[bob fitz]

Can you post a copy in A2003 mdb format

 

[davies107]

Can you post a copy in A2003 mdb format

i cant convert it into 2002-2003 .mdb format. it says "the specified database sort order, General, is not supported for the target database format". i dont know what that means. im using ms access 2010 by the way. maybe theres a problem or a function not compatible with the older version, IDK.

 

[pr2-eugin]

What form or field am i looking at here?

 

[davies107]

Form is FRM_SearchMulti.... then the Procurement tab (Contingency Amount and Procurement Estimate)

i have lots of item that has #Type! that i deleted.... but i saw 2 items that has that error. i still have problems with my DBS, been posting here before but it seems i still cant get to solve it.

will be back tomorrow. its going home time, hehe...

 

[missinglinq]

You can use Conditional Formatting for this:

1. Use the expression that is giving you the Zero in your Textbox
2. In Form Design View, Right-Click on the Textbox
3. Click on Conditional Formatting
4. Under Condition1 select Field Value Is
5. In next box select Equal To
6. Then, in the last box enter 0
7. Set the ForeColor to the same color as the Textbox BackColor
8. Click on OK

Linq ;0)>

 

[davies107]

Thanks missinglinq. but, this conditional formating gives me the #type! result

i will have to settle for zero (0) for now if theres no other way.