[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?

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

Quote:

---

Originally Posted by DataMiser

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?

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.

Quote:

---

Originally Posted by LaVolpe

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?

You can use the Format$() function

Code:

---

Debug.Print Format$(DTPicker1.Value, "YYYY/MM/DD")

---

Quote:

---

Originally Posted by DataMiser

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

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"

---

Quote:

---

Originally Posted by DataMiser

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.

Quote:

---

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!

Quote:

---

Originally Posted by Arnoutdv

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

Quote:

---

Originally Posted by Arnoutdv

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.

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.

Quote:

---

Originally Posted by DataMiser

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

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

Quote:

---

Originally Posted by DataMiser

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)

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

Quote:

---

Originally Posted by DataMiser

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

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:

Attachment 150407

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.

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)?

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

@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.

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:

Attachment 150413

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.

You are so nice.....

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

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.

Quote:

---

Originally Posted by dilettante

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

Quote:

---

Originally Posted by techgnome

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.

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

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.

Quote:

---

Originally Posted by dilettante

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

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

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

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