455,479 Members | 1,601 Online
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
7 Replies

 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

 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

 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 Public Function fFormatLong(lngNumber As Long) As String   fFormatLong = Format(((lngNumber / 10000) - Fix(lngNumber / 10000)) * 10000, "0000") End Function SAMPLE RESULTS: Expand|Select|Wrap|Line Numbers Debug.Print fFormatLong(1) 0001   Debug.Print fFormatLong(27)  0027   Debug.Print fFormatLong(892) 0892   Debug.Print fFormatLong(4320) 4320   Debug.Print fFormatLong(16230) 6230   Debug.Print fFormatLong(999992) 9992   Debug.Print fFormatLong(1234567) 4567 May 4 '09 #5

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

 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

 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