Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

# causing syntax error

Status
Not open for further replies.

jozino01

Technical User
Apr 25, 2003
257
CA
hi,
i tried to format recordset, but '#' is causing syntax error

sSQL = "SELECT [BOOM], Format([volume],"#,##0.000") AS BVolume, Format([total],"Standard") AS BValue FROM qLogs_Inventory;"

is # a special character for visual basic? how to make it work?

original, unformated and working sql
sSQL = "Select [boom], [volume], [total] From qLogs_Inventory;"

thanks
 
# in VB usually signals a date as in #1/1/2007#

Your problem is that you have unclosed quotations in your select statement.

Code:
"SELECT [BOOM], Format([volume],"#,##0.000") AS BVolume, Format([total],"Standard") AS BValue FROM qLogs_Inventory;"

Code:
"SELECT [BOOM], Format([volume],""#,##0.000"") AS BVolume, Format([total],""Standard"") AS BValue FROM qLogs_Inventory;"

I would really think that you would want to cast and round the formatted number in your SQL statement like this:

[Code SQL]

"SELECT [BOOM], Cast(Volume as Decimal(10,4)) AS BVolume, Cast(total as Decimal(10,2)) AS BValue FROM qLogs_Inventory;"

[/code]

I hope this helps.


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.
 
I'm not quite sure what you're trying to do here, but you can't do it this way whatever it is. The problem is that the " character is a special character in VB--it's a string delimiter. One goes on each end of a string. So, as you can see, your string has a bunch of "s in it so VB gets confused.

The next problem is that you can't apply the format function while you're in the middle of pulling data from SQL Server. You either have to use SQL Server's formatting capabilities (investigate the convert function), or wait until you are done and go through the result set and apply the format function there.

HTH

Bob
 
I would be a bit careful there. It's important to understand that where you format your response determines the underlying value. If you just format the result from SQL Server the underlying number still has all the decimal places.
 
>I would be a bit careful there

The recordset is what sould be worked off of when using the DataGrid, usually not the data in the DataGrid itself, and the recordset holds the original unformated values. When using Standard Data Format Object for a DataGrid, the actual values will not change.
 
Yes, my point exactly. If you are under the impression that formatting changes the underlying values, you can run into subtle rounding errors.
 
yes, you are right. originally, i wanted to format datagrid; however, for unknown reason datagrid formating didn't work so i was just looking for other way around.

this code

With DataGrid1
.Columns(1).DataFormat.Format = "#,##0.000"
.Columns(1).Alignment = dbgRight
.Columns(2).DataFormat.Format = "#,##0.00"
.Columns(2).Alignment = dbgRight
End With

simply doesn't work - no error, but no change in format/alignment.???
 
I don't know the datagrid control well at all, so I can't tell you why it isn't working.

It's important to understand though that formatting the cell doesn't change the underlying value, and I'm not as sure that you do as I would like to be. So, for example, if you go into Excel, and put 123.456789 in a cell, and then format the cell to 2 decimal places, it will say 123.46 in the cell but if you look at the contents of the cell it will still be 123.456789. This is an analogous situation to using the Format function on a value.

If you want to round the actual underlying number to -1 decimal place, as your format implies, you can do this: round(123.45678 / 10, 0)*10 will give 120.
 
jozino01, Use .NumberFormat instead. If you use DataFormat, you first have to declare a Standard Data Formating Object(StdDataFormats) and assign it to the column.
 
@ jozino01
From the format of the SQL in your original post, I am going to hazzard a guess that you're using MS Access and not MS SQL Server.

If this is the case, you can fix your problem by substituting the embedded double quotes with single quotes. (This is a quick hack).

sSQL = "SELECT [BOOM], Format([volume],'#,##0.000') AS BVolume, Format([total],'Standard') AS BValue FROM qLogs_Inventory"

That sould do it.
 
Thanks everybody.
Yes, it's Access database.
Double quotes work as well as single quotes.
I am aware of possible issues with formating (actual vs. showed value); however, it works fine in this case since these values are used just for visual confirmation by user not for further operations/calculation.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top