How to format a date in list Box

[ganesh3084]

I have a Listbox, the RowSource is:



lstView.RowSource = "select ID,Chop_Date, GWIS_ID,Card_Number,Customer_Name,Complaint_Category,Status from tbl_PaperTeam"



i need to format the date. so i modified the row source as




lstView.RowSource = "select ID,format([Chop_Date],"dd-mmm-yy"), GWIS_ID,Card_Number,Customer_Name,Complaint_Category,Status from tbl_PaperTeam"



But after writing the code and going to next line i got the following error

"Compile Error.Expected: end of statement"
and the cursor is pointing on "dd-"
How ever that query got executed when i checked in Query Window.

Please assist me to get my expected format date

[hansup]

VBA uses double quotes to mark the start and end of string values. If your string includes additional double quotes inside, you must double them up so VBA doesn't get confused. Look at this example from the Immediate Window.

Debug.Print "quotes "inside" mystring"
quotes mystring

Debug.Print "quotes ""inside"" mystring"
quotes "inside" mystring
I think you should use a different approach for your list box row source.

Dim strSql As String
strSql = "select ID,format([Chop_Date],""dd-mmm-yy"")," & _
"GWIS_ID,Card_Number,Customer_Name,Complaint_Category,Status from tbl_PaperTeam"
Debug.Print strSql
lstView.RowSource = strSql
Use a string variable, strSql, to hold your SELECT statement. Then you can use Debug.Print to check it for correctness by viewing it in the Immediate Window. Don't rely on your imagination to guess at what the completed string looks like ... look at it instead.

BTW, seven columns seems a rather large number for a list box, IMO.

[ganesh3084]

VBA uses double quotes to mark the start and end of string values. If your string includes additional double quotes inside, you must double them up so VBA doesn't get confused. Look at this example from the Immediate Window.

Debug.Print "quotes "inside" mystring"
quotes mystring

Debug.Print "quotes ""inside"" mystring"
quotes "inside" mystring
I think you should use a different approach for your list box row source.

Dim strSql As String
strSql = "select ID,format([Chop_Date],""dd-mmm-yy"")," & _
"GWIS_ID,Card_Number,Customer_Name,Complaint_Category,Status from tbl_PaperTeam"
Debug.Print strSql
lstView.RowSource = strSql
Use a string variable, strSql, to hold your SELECT statement. Then you can use Debug.Print to check it for correctness by viewing it in the Immediate Window. Don't rely on your imagination to guess at what the completed string looks like ... look at it instead.

BTW, seven columns seems a rather large number for a list box, IMO.


Hi thanks for ur valuable suggestion .. I got my expected output

[hiten]

Hi thanks for ur valuable suggestion .. I got my expected output

[hiten]

Hi thanks for ur valuable suggestion .. I got my expected output

I am designing a VBA user form, I have used the following code to insert data from existing worksheet to ListBox

With Me.ListBox1
.RowSource = Range("Y6:AA131").Address
End With

One Collumn in the range consists of date format.

However while transferring the data to the Listbox it is getting converted to number format.

Can anyone advice what can be the right method of doing it?