Help with List Rowsource SQL syntax please

[Isis]

I have a List that I am feeding from an SQL statement like;

me!ListWhite.Rowsource = "SELECT UID, SalesDate, CSSales, RAMSales,
INTSales, CSAddOn, CSSales+RAMSales+IntSales+CSAddOn AS TotalSales
,LabelsGen, Fixed FROM WhiteBoard"

I would like to have - SalesDate AS Date - so the column header is
'Date' not 'SalesDate', but it does not seem to like me using the word
'Date' - also if I want a header which is two words, 'Total Sales' - it
does not like that either - I am sure this can be done but do not know
the right syntax.

Secondly as the SQL statement is going to be quite long - how do I split
it up over several lines ?

Thirdly can I format the fields somehow - can I right justify some of the
columns ?

Any help much appreciated.

Regards

 

[John Spencer]

1. Since Date is a reserved word you could be running into a problem with
that. If you want to include spaces and any other non alphabet characters you
should use square brackets around the alias.

"SELECT UID, SalesDate as [Sales Date], CSSales, RAMSales,
INTSales, CSAddOn, CSSales+RAMSales+IntSales+CSAddOn AS [Total Sales]
,LabelsGen, Fixed FROM WhiteBoard"

2. You can use the line continuation in VBA. That is a space followed by the
underscore. You must close the string (ending quote) and then reopen the
string on the next line.

me!ListWhite.Rowsource = "SELECT UID, SalesDate as [Sales Date]" & _
", CSSales, RAMSales, INTSales, CSAddOn" & _
", CSSales+RAMSales+IntSales+CSAddOn AS [Total Sales] " & _
", LabelsGen, Fixed " & _
" FROM WhiteBoard"

3. You cannot control alignment in a list box (or a combobox). If you really
need this capability, you can use a subform and VBA to emulate a listbox. Not
really a simple task.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Isis]

1. Since Date is a reserved word you could be running into a problem
with that. If you want to include spaces and any other non alphabet
characters you should use square brackets around the alias.

"SELECT UID, SalesDate as [Sales Date], CSSales, RAMSales,
INTSales, CSAddOn, CSSales+RAMSales+IntSales+CSAddOn AS [Total
Sales] ,LabelsGen, Fixed FROM WhiteBoard"

2. You can use the line continuation in VBA. That is a space
followed by the underscore. You must close the string (ending quote)
and then reopen the string on the next line.

me!ListWhite.Rowsource = "SELECT UID, SalesDate as [Sales Date]" & _
", CSSales, RAMSales, INTSales, CSAddOn" & _
", CSSales+RAMSales+IntSales+CSAddOn AS [Total Sales] " & _
", LabelsGen, Fixed " & _
" FROM WhiteBoard"

3. You cannot control alignment in a list box (or a combobox). If you
really need this capability, you can use a subform and VBA to emulate
a listbox. Not really a simple task.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I have a List that I am feeding from an SQL statement like;

me!ListWhite.Rowsource = "SELECT UID, SalesDate, CSSales, RAMSales,
INTSales, CSAddOn, CSSales+RAMSales+IntSales+CSAddOn AS TotalSales
,LabelsGen, Fixed FROM WhiteBoard"

I would like to have - SalesDate AS Date - so the column header is
'Date' not 'SalesDate', but it does not seem to like me using the
word 'Date' - also if I want a header which is two words, 'Total
Sales' - it does not like that either - I am sure this can be done
but do not know the right syntax.

Secondly as the SQL statement is going to be quite long - how do I
split it up over several lines ?

Thirdly can I format the fields somehow - can I right justify some of
the columns ?

Any help much appreciated.

Regards

John, Thanks for that - that seems to meet the need. Can you point me at
a source of info on writing a VB list control as I will need this at some
point.

Thanks

 

[John Spencer]

Sorry. I cannot.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

1. Since Date is a reserved word you could be running into a problem
with that. If you want to include spaces and any other non alphabet
characters you should use square brackets around the alias.

"SELECT UID, SalesDate as [Sales Date], CSSales, RAMSales,
INTSales, CSAddOn, CSSales+RAMSales+IntSales+CSAddOn AS [Total
Sales] ,LabelsGen, Fixed FROM WhiteBoard"

2. You can use the line continuation in VBA. That is a space
followed by the underscore. You must close the string (ending quote)
and then reopen the string on the next line.

me!ListWhite.Rowsource = "SELECT UID, SalesDate as [Sales Date]" & _
", CSSales, RAMSales, INTSales, CSAddOn" & _
", CSSales+RAMSales+IntSales+CSAddOn AS [Total Sales] " & _
", LabelsGen, Fixed " & _
" FROM WhiteBoard"

3. You cannot control alignment in a list box (or a combobox). If you
really need this capability, you can use a subform and VBA to emulate
a listbox. Not really a simple task.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I have a List that I am feeding from an SQL statement like;

me!ListWhite.Rowsource = "SELECT UID, SalesDate, CSSales, RAMSales,
INTSales, CSAddOn, CSSales+RAMSales+IntSales+CSAddOn AS TotalSales
,LabelsGen, Fixed FROM WhiteBoard"

I would like to have - SalesDate AS Date - so the column header is
'Date' not 'SalesDate', but it does not seem to like me using the
word 'Date' - also if I want a header which is two words, 'Total
Sales' - it does not like that either - I am sure this can be done
but do not know the right syntax.

Secondly as the SQL statement is going to be quite long - how do I
split it up over several lines ?

Thirdly can I format the fields somehow - can I right justify some of
the columns ?

Any help much appreciated.

Regards

John, Thanks for that - that seems to meet the need. Can you point me at
a source of info on writing a VB list control as I will need this at some
point.

Thanks