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

format field

100+
P: 250
i have a field with numbers in it and was wondering if i can do a query to only keep the last 4 digits in the field. thanks!
May 4 '09 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,679
@didacticone
Is it a Whole Number Field (INTEGER, LONG) or Floating Point (SINGLE, DOUBLE)?
May 4 '09 #2

100+
P: 250
its a long integer field
May 4 '09 #3

FishVal
Expert 2.5K+
P: 2,653
I guess last 4 digits of long integer is remainder of division by 10000.

= SomeLongIntegerNumber mod 10000
May 4 '09 #4

ADezii
Expert 5K+
P: 8,679
@didacticone
I'm running out the door, and I'm sure that there is an easier way, but here is what I come up with in a flash:
Expand|Select|Wrap|Line Numbers
  1. Public Function fFormatLong(lngNumber As Long) As String
  2.   fFormatLong = Format(((lngNumber / 10000) - Fix(lngNumber / 10000)) * 10000, "0000")
  3. End Function
SAMPLE RESULTS:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print fFormatLong(1)
  2. 0001
  3.  
  4. Debug.Print fFormatLong(27) 
  5. 0027
  6.  
  7. Debug.Print fFormatLong(892)
  8. 0892
  9.  
  10. Debug.Print fFormatLong(4320)
  11. 4320
  12.  
  13. Debug.Print fFormatLong(16230)
  14. 6230
  15.  
  16. Debug.Print fFormatLong(999992)
  17. 9992
  18.  
  19. Debug.Print fFormatLong(1234567)
  20. 4567
May 4 '09 #5

100+
P: 675
And with no arithmetic:
Expand|Select|Wrap|Line Numbers
  1. Right(Format(lngNumber ,"0000"),4)
May 4 '09 #6

ADezii
Expert 5K+
P: 8,679
@didacticone
didacticone, pay absolutely no attention to what that ADezii Character gave as a Reply in Post #5! Do, however, follow OldBirdman's advice in Post #6! (LOL)!
May 4 '09 #7

NeoPa
Expert Mod 15k+
P: 31,709
Not forgetting Fish's suggestion in post #4. Hard to beat for a concise answer.
May 6 '09 #8

Post your reply

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