By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

Data Type Mismatch

P: 10
Term 1- Time in Office(yrs) is a double number with data like 1.08618331053352 on my table and there are Nulls also

I want to make this data into 1.0861 and for those null to be empty-
Expand|Select|Wrap|Line Numbers
  1. IIF ([Term 1- Time in Office(yrs)] <> "", Format ([Term 1- Time in Office(yrs)], "0.0000"))
Aug 28 '17 #1

✓ answered by NeoPa

Although I suspect you really want the value rounded in the normal way (to the nearest digit) you've asked for it to be rounded down. There is nothing intrinsically available in the standard Access libraries to do this for you so I'll suggest what you need for more standard rounding and you can make it clear in your reply whether that's what you need or not.

Unfortunately, your question doesn't make it clear whether this is for display or data purposes. I guess using Format() may indicate the latter, or it could simply show a lack of understanding of the subject. I'll have to answer both ways I guess.

For data the solution is simply to use :
Expand|Select|Wrap|Line Numbers
  1. Round([Term 1- Time in Office(yrs)],4)
For display it's more involved. This actually involves converting from a numeric value to a string value. There are various possibilities which are not clear from the sparse question but if you need more later then you'll have to be clearer in your reply as to what exactly you need.
Expand|Select|Wrap|Line Numbers
  1. IIf([Term 1- Time in Office(yrs)] Is Null,Null,Format([Term 1- Time in Office(yrs)],'0.0000'))

Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,419
Although I suspect you really want the value rounded in the normal way (to the nearest digit) you've asked for it to be rounded down. There is nothing intrinsically available in the standard Access libraries to do this for you so I'll suggest what you need for more standard rounding and you can make it clear in your reply whether that's what you need or not.

Unfortunately, your question doesn't make it clear whether this is for display or data purposes. I guess using Format() may indicate the latter, or it could simply show a lack of understanding of the subject. I'll have to answer both ways I guess.

For data the solution is simply to use :
Expand|Select|Wrap|Line Numbers
  1. Round([Term 1- Time in Office(yrs)],4)
For display it's more involved. This actually involves converting from a numeric value to a string value. There are various possibilities which are not clear from the sparse question but if you need more later then you'll have to be clearer in your reply as to what exactly you need.
Expand|Select|Wrap|Line Numbers
  1. IIf([Term 1- Time in Office(yrs)] Is Null,Null,Format([Term 1- Time in Office(yrs)],'0.0000'))
Aug 28 '17 #2

Post your reply

Sign in to post your reply or Sign up for a free account.