Prev: DSum
Next: Combo Box changing data in Table ?
From: Isis on 17 Jun 2008 08:11
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
From: John Spencer on 17 Jun 2008 08:24
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 wrote:
> 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
From: Isis on 17 Jun 2008 08:34
John Spencer <spencer(a)chpdm.edu> wrote in
news:#eu5CUH0IHA.4040(a)TK2MSFTNGP04.phx.gbl:

> 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 wrote:
>> 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
From: John Spencer on 17 Jun 2008 11:30
Sorry. I cannot.

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

Isis wrote:
> John Spencer <spencer(a)chpdm.edu> wrote in
> news:#eu5CUH0IHA.4040(a)TK2MSFTNGP04.phx.gbl:
>
>> 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 wrote:
>>> 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
 | 
Pages: 1
Prev: DSum
Next: Combo Box changing data in Table ?