Results 1 to 10 of 10
  1. #1
    mikesal57 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    48

    Data Type?

    Hi guys....



    How can I fix this so that numbers follow in order?

    thxsClick image for larger version. 

Name:	Capture.JPG 
Views:	20 
Size:	48.6 KB 
ID:	48211

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Dim tPGM as integer (or long) . It has to be numeric.

    You could use CInt(tPGM) also.

  3. #3
    mikesal57 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    48
    I'm sorry but I don't understand ....

    Is there something here to change?

    Click image for larger version. 

Name:	Capture.JPG 
Views:	19 
Size:	60.1 KB 
ID:	48217

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Yes, make it a number type NOT text.
    Possibly make it the same as nRace ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    mikesal57 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    48
    more problems..

    heres original & errorsClick image for larger version. 

Name:	a.JPG 
Views:	16 
Size:	70.3 KB 
ID:	48218Click image for larger version. 

Name:	1.JPG 
Views:	16 
Size:	78.6 KB 
ID:	48219Click image for larger version. 

Name:	2.JPG 
Views:	16 
Size:	91.3 KB 
ID:	48220

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    seems to me that your data is not normalised and with regards your datatype not changed error it will be because there is some text in one of more of the records

  7. #7
    mikesal57 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    48
    Quote Originally Posted by CJ_London View Post
    seems to me that your data is not normalised and with regards your datatype not changed error it will be because there is some text in one of more of the records
    Yes , some data could be 1a 1b 2a 2b 3x along with the normal numbers .....so your telling me it cant be done?

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    so your telling me it cant be done?
    number sort differently to text so within the context of a column of data only by changing the values so

    1>01
    10>10
    1a>01a
    etc

    and if the numbers can go to 3 digits then you would need

    1>001
    10>010
    1a>001a
    etc

    or you need an additional column with the formula

    =val([tPGM])

    to sort on.

  9. #9
    mikesal57 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    48
    Quote Originally Posted by CJ_London View Post
    number sort differently to text so within the context of a column of data only by changing the values so

    1>01
    10>10
    1a>01a
    etc

    and if the numbers can go to 3 digits then you would need

    1>001
    10>010
    1a>001a
    etc

    or you need an additional column with the formula

    =val([tPGM])

    to sort on.

    thank you for your time......

  10. #10
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Quote Originally Posted by mikesal57 View Post
    Yes , some data could be 1a 1b 2a 2b 3x along with the normal numbers
    If the maximum length is 2, you could try:
    Code:
    SELECT tPGM FROM T1 ORDER BY IIf(Len([tPGM])=1 And IsNumeric([tpgm]),"0" & [tPGM],IIf(Len([tPGM])=2 And Not IsNumeric(Mid([tPGM],2,1)),"0" & [tPGM],[tPGM]));

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-23-2019, 05:11 AM
  2. Replies: 0
    Last Post: 09-05-2017, 10:35 AM
  3. Replies: 4
    Last Post: 02-03-2017, 12:12 PM
  4. Replies: 3
    Last Post: 05-31-2013, 04:32 PM
  5. Replies: 2
    Last Post: 03-18-2010, 08:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums