[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?
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
Re: How To Set Records Order According To This Kind of Field?
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?
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"
Re: How To Set Records Order According To This Kind of Field?
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?
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.
Re: How To Set Records Order According To This Kind of Field?
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
Re: How To Set Records Order According To This Kind of Field?
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
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:
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.
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.
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:
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.
Re: How To Set Records Order According To This Kind of Field?
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.
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.
Re: How To Set Records Order According To This Kind of Field?
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.
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.
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.
Re: How To Set Records Order According To This Kind of Field?
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.
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.
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.