Results 1 to 31 of 31

Thread: [RESOLVED] How To Set Records Order According To This Kind of Field?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Dec 2015
    Posts
    103

    Resolved [RESOLVED] How To Set Records Order According To This Kind of Field?

    So I want to sort my records according to tglabsen, in descending order. This field is in Varchar, I know I probably should set it as DATE, but DATE type gave me an error, so i changed it to varchar, and my database is running okay with it.
    Here is one example of what the value of this field looks like :

    31/07/2017

    I tried :

    "select * from absen where NIP='xxxxx' order by tglabsen desc"

    But what happened is that the record was sorted according to the first 2 digit number in the value.

    So, instead of setting value like 8/8/2017 on top, the program put 31/07/2017 on top. Because 31 is bigger than 8. Any idea to solve this? will it be better to change the tglabsen to DATE type?
    Last edited by xboner; Aug 7th, 2017 at 05:44 PM.

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: How To Set Records Order According To This Kind of Field?

    If you use a text field and want to place dates in it that can be sorted then you need to format the dates in a specific format
    Year then month then day

    so your example dates would be written 20170731 20170808 and would sort as expected

    You can add separators in if you want

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Dec 2015
    Posts
    103

    Re: How To Set Records Order According To This Kind of Field?

    Quote Originally Posted by DataMiser View Post
    If you use a text field and want to place dates in it that can be sorted then you need to format the dates in a specific format
    Year then month then day

    so your example dates would be written 20170731 20170808 and would sort as expected

    You can add separators in if you want
    Yes, how can i didnt think of it before. Is putting separators like "/" between year, month and day okay?

    Oh i forgot one thing, the date is from DTPicker, and the format is from day, month, to year. How to change the format?

  4. #4
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: How To Set Records Order According To This Kind of Field?

    I think you should reconsider and set it as Date data type. If you got an error, possibly one or more of your entries are not valid dates? Just a guess.

    Within your database... You might be able to do this by creating a new field in your table as a DATE data type. Then create an update query and convert your kodeabsen field to date, using your database (Access, SQL Server, something else) conversion functions, updating the new field with the conversion. If that works, then you can delete the field kodeabsen and rename your temp field to kodeabsen. Once that is done, your sort will work as expected.

    Another option is to use a database conversion from text to date in your query.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Dec 2015
    Posts
    103

    Re: How To Set Records Order According To This Kind of Field?

    Quote Originally Posted by LaVolpe View Post
    I think you should reconsider and set it as Date data type. If you got an error, possibly one or more of your entries are not valid dates? Just a guess.

    Within your database... You might be able to do this by creating a new field in your table as a DATE data type. Then create an update query and convert your kodeabsen field to date, using your database (Access, SQL Server, something else) conversion functions, updating the new field with the conversion. If that works, then you can delete the field kodeabsen and rename your temp field to kodeabsen. Once that is done, your sort will work as expected.

    Another option is to use a database conversion from text to date in your query.
    Hi LaVolpe thanks for the suggestion, there is a mistake in the sql statement that i wrote above though, it should be "order by tglabsen".

    I think the fastest solution is to change the date format generated by DTPicker to yyyy/mm/dd, as DataMister suggests. How to do this?
    Last edited by xboner; Aug 7th, 2017 at 06:03 PM.

  6. #6
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: How To Set Records Order According To This Kind of Field?

    You can use the Format$() function
    Code:
    Debug.Print Format$(DTPicker1.Value, "YYYY/MM/DD")

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Dec 2015
    Posts
    103

    Re: How To Set Records Order According To This Kind of Field?

    Quote Originally Posted by DataMiser View Post
    You can use the Format$() function
    Code:
    Debug.Print Format$(DTPicker1.Value, "YYYY/MM/DD")
    Thanks, I'll try that

    edit: But how to format the result right in DTPickers own textbox? with that, I might need to create a new textbox to store the value

  8. #8
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: How To Set Records Order According To This Kind of Field?

    No you do not need to create a textbox to store a value.

    If you want it displayed this way then you can use a custom format
    Code:
    DTPicker1.Format = dtpCustom
    DTPicker1.CustomFormat = "yyyy/MM/dd"

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Dec 2015
    Posts
    103

    Re: How To Set Records Order According To This Kind of Field?

    Quote Originally Posted by DataMiser View Post
    No you do not need to create a textbox to store a value.

    If you want it displayed this way then you can use a custom format
    Code:
    DTPicker1.Format = dtpCustom
    DTPicker1.CustomFormat = "yyyy/MM/dd"
    This is weird, when i saved it to the database using insert into, its back to dd/MM/yyyy.

  10. #10
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: How To Set Records Order According To This Kind of Field?

    Any idea to solve this? will it be better to change the tglabsen to DATE type?
    You really should change the column to a DATE type.
    Working with text fields for dates will only give you headaches!

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: How To Set Records Order According To This Kind of Field?

    Quote Originally Posted by Arnoutdv View Post
    You really should change the column to a DATE type.
    Working with text fields for dates will only give you headaches!
    I could'nt agree more Arnoutdv !

    regards
    Chris

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Dec 2015
    Posts
    103

    Re: How To Set Records Order According To This Kind of Field?

    Quote Originally Posted by Arnoutdv View Post
    You really should change the column to a DATE type.
    Working with text fields for dates will only give you headaches!
    i tried it

    But when i save/insert the date generated from DTPicker to my database, the field is empty. I checked it in phpmyadmin, it shows 0000-00-00.

  13. #13
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: How To Set Records Order According To This Kind of Field?

    I agree with the others in that you should be using a date field. I showed the format options just to show how to sort when a date is in a text field but should always use a date field in the database.

    As for your latest issue Formatting the control is for display only. You still need to use format when you write it to the db if you use a text field and want it formatted any specific way.

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Dec 2015
    Posts
    103

    Re: How To Set Records Order According To This Kind of Field?

    Quote Originally Posted by DataMiser View Post
    I agree with the others in that you should be using a date field. I showed the format options just to show how to sort when a date is in a text field but should always use a date field in the database.

    As for your latest issue Formatting the control is for display only. You still need to use format when you write it to the db if you use a text field and want it formatted any specific way.
    I tried to change it to DATE, but when i inserted it to the mysql, it showed me 0000-00-00.

    And the tglabsen field in the mshflexgrid is empty. Thats the reason i use varchar. it works

  15. #15
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: How To Set Records Order According To This Kind of Field?

    Maybe you should show how you tried to insert it when the field was a date field.

  16. #16

    Thread Starter
    Lively Member
    Join Date
    Dec 2015
    Posts
    103

    Re: How To Set Records Order According To This Kind of Field?

    Quote Originally Posted by DataMiser View Post
    Maybe you should show how you tried to insert it when the field was a date field.
    well, i inserted it straight from the control

    "insert into absen (field names) values('" & dtpicker & "','etc etc)

  17. #17
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: How To Set Records Order According To This Kind of Field?

    I meant the actual code you had an issue with. You also mentioned that it gave you are error but I did not see where you said what error it was.

    One issue that comes into play with dates is regional formatting. mm/dd vs dd/mm

  18. #18

    Thread Starter
    Lively Member
    Join Date
    Dec 2015
    Posts
    103

    Re: How To Set Records Order According To This Kind of Field?

    Quote Originally Posted by DataMiser View Post
    I meant the actual code you had an issue with. You also mentioned that it gave you are error but I did not see where you said what error it was.

    One issue that comes into play with dates is regional formatting. mm/dd vs dd/mm
    There is no error actually, just blank field in tglabsen, and the one in mysql shows 0000-00-00. No line highlighted in yellow. I should have been clearer with my words

  19. #19
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How To Set Records Order According To This Kind of Field?

    I'm not sure why people have so much trouble with a simple concept, but they insist on confusing String values for displaying and entering date and time values with actual Date type values. The main reason to use a DTPicker is to avoid handling String values for these things at all.

    Slavish reliance on copy/pasted scripting techniques left over from the early days of ASP VBScript just serves to amplify their lack of understanding. When they try to cobble together queries via string concatenation (i.e. "dynamic SQL") instead of using parameter queries it tends to blow up in their faces.

    It isn't that hard to make this stuff work, even in locales that use non-standard date formatting. Here I have forced everything to use day/month/year:

    Name:  sshot.png
Views: 330
Size:  3.2 KB

    To get around the problem of feeding Date typed data to a query you can use a proper parameter query. This is actually far easier than many examples might lead you to believe, since in simple cases you can do it without even creating named parameters:

    Code:
        Set InsertCommand = New ADODB.Command
        With InsertCommand
            .CommandType = adCmdText
            .CommandText = "INSERT INTO [SomeTable]([SomeText],[SomeDate]) VALUES(?,?)"
            .Prepared = True
            .Name = "Insert"
            Set .ActiveConnection = Connection
        End With
    Code:
        Connection.Insert Text.Text, DTPicker.Value
    If you just want the program to track the current locale it is even easier. Just specify that you want to use the "short date" format in both the DTPicker and in the Dump query's Format$() function call.
    Attached Files Attached Files

  20. #20
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: How To Set Records Order According To This Kind of Field?

    Are you using MSACCESS as your database. If so, you would need '#' signs instead of the single quotes around the dtpickerX.Value.

    Can you post the EXACT SQL you used (and what it shows when you debug the SQL statement)?

  21. #21
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How To Set Records Order According To This Kind of Field?

    I could have sworn I just explained why that is a fool's errand, and that there are far more reliable alternatives.

  22. #22
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: How To Set Records Order According To This Kind of Field?

    @dile....good advice.

    (However, "I" prefer not to use the parameter queries...as I (OFTEN) make mistakes when coding, I like to see (in Immediate Window during Debug), what I actually sent to the database...the "?" usage does not allow me to see the actual values passed (in the statement, that is),,,just easier for "ME" to write out the placeholders in the query so I can check it even before executing it.---I make a lot of mistakes!

    EDIT....I was still putting together my previous post when you posted yours. Lo Siento.

    EDIT 2...my POINT was that the use of quotes is incorrect for Access. I know what you mean by your advice, and it is probably better for most folks who don't make as many mistakes as I do.
    Last edited by SamOscarBrown; Aug 8th, 2017 at 08:53 AM.

  23. #23
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How To Set Records Order According To This Kind of Field?

    I'm not sure what you think you are "seeing" in the Immediate window that tells you any more than just printing the parameter values passed. It comes down to a simple difference:

    Name:  CrapVsZap.png
Views: 245
Size:  6.0 KB


    Those screwy habits people pick up from parroting hoary old script snippets to each other means we see this very same thread repeat itself here over and over and over again. Date values are not String values. Period. And we use DTPickers so we don't have to go though the very error-prone gyrations you seem to prefer... probably out of habit.

  24. #24
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: How To Set Records Order According To This Kind of Field?

    You are so nice.....

  25. #25
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: How To Set Records Order According To This Kind of Field?

    Quote Originally Posted by xboner
    I checked it in phpmyadmin, it shows 0000-00-00.
    That suggests he's using MySQL. I don't recall off the top of my head how MySQL handles date delimiters, but I think using single quotes around it should be sufficient.

    Quote Originally Posted by SamOscarBrown
    (However, "I" prefer not to use the parameter queries...as I (OFTEN) make mistakes when coding, I like to see (in Immediate Window during Debug), what I actually sent to the database...the "?" usage does not allow me to see the actual values passed (in the statement, that is),,,just easier for "ME" to write out the placeholders in the query so I can check it even before executing it.---I make a lot of mistakes!
    I'm the the other way around. I find I make more mistakes when dealing with stringing text along in a SQL statement. For me, parameterized queries are easier to read, and I don't have to worry about getting the right number of paired off quote marks correct. But yeah, I hear ya, sometimes it's hard to "see" the values being passed in through the parameters. It probably affects me less since I'm usually working in SQL Server, so I have SSMS where I run everything, and I'm usually running stored procs (usually, not always)... so I can easily, pause, get the parameter values, then set up a SQL script to mimic the call from VB to see what's what.

    If I was working in Access, my attitude would probably be different.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  26. #26
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: How To Set Records Order According To This Kind of Field?

    Maybe if enough convince me (nicely, that is), I COULD change my 'habits'...but, being on old dog...., well, you know....

    Anyway, outa here...dile seems to have the answers OP needs; don't need me muddying up the waters.

  27. #27
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: How To Set Records Order According To This Kind of Field?

    Quote Originally Posted by dilettante View Post
    I'm not sure what you think you are "seeing" in the Immediate window that tells you any more than just printing the parameter values passed.
    if you print the sql out to the immediate window, you get this: "insert into tbl(f1, f2) values (?, ?)"
    What Sam would rather see is: "insert into tbl (f1, f2) values ('value 1', 'value 2')"
    I can understand that. There are times when I wished I could see the complete SQL with the parameter values in their proper spots, and capture the whole SQL.
    The problem with that is it assumes that the parameters are replaced by the values, which isn't really the case. So it sometimes makes debugging a bit of a pain.
    It seems to work for him, so why not? I on the other hand work on systems that deal with the O'Brien's and O'Leary's and the Al'Abrim's of the world, so stringing SQL Statements together don't work for me.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  28. #28
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How To Set Records Order According To This Kind of Field?

    Quote Originally Posted by techgnome View Post
    It seems to work for him, so why not?
    Why not? Oh, maybe because the entire point of this thread is that it isn't working?

    With values stored as Date type, the sorting becomes a trivial matter.

  29. #29
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: How To Set Records Order According To This Kind of Field?

    1) Is this JET or MySQL ?
    2) unless we're using a date type of field, all bets are off.

    Quote Originally Posted by techgnome View Post
    That suggests he's using MySQL. I don't recall off the top of my head how MySQL handles date delimiters, but I think using single quotes around it should be sufficient.
    -tg
    MySQL dates are passed as Strings in a certain format 'YYYY-mm-dd hh:nn:ss'. MySQL also likes to fail silently about failed date conversions, and just store zeros.

  30. #30
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: How To Set Records Order According To This Kind of Field?

    Quote Originally Posted by dilettante View Post
    Why not? Oh, maybe because the entire point of this thread is that it isn't working?

    With values stored as Date type, the sorting becomes a trivial matter.
    I agree... the point is why his sort isn't working. The reason, they are strings. None of this really has to do with parameters, so quit getting your panties in a bunch simply because parameters weren't used.

    Quote Originally Posted by DEXWERX View Post
    1) Is this JET or MySQL ?
    2) unless we're using a date type of field, all bets are off.

    MySQL dates are passed as Strings in a certain format 'YYYY-mm-dd hh:nn:ss'. MySQL also likes to fail silently about failed date conversions, and just store zeros.
    Quote Originally Posted by xboner View Post
    well, i inserted it straight from the control

    "insert into absen (field names) values('" & dtpicker & "','etc etc)
    And I think that last bit there explains it all... dtpicker is an object. You need to pass the .Value of the dtp in order for it to work. And since you're stringing the SQL along, use the .ToString and give it the format ("yyyy-MM-dd") that you want: dtpicker.value.tostring("yyyy-MM-dd") that should plug it into the field correctly as a date.
    If you use parameters, then you don't need to worry about the format, and would simply pass the .Value of the DTP.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  31. #31
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: How To Set Records Order According To This Kind of Field?

    Hi xboner,

    sending Date to Access..
    Code:
    Dim sDate As String
    
    sDate = Format$(DTPicker1.Value, "\#mm\/dd\/yyyy\#"
    
    Rs.Source = "SELECT * FROM tblName WHERE Datum = " & sDate
    for MySql do it like tg said

    regards
    Chris

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width