how to format cell in query

[leeroi]

Is there a way to format a column in query datasheet view so that the cell contents are centered rather than left aligned?

Lee

 

[cranebill]

Do you know the max length the column can be?

 

[leeroi]

no more than 15 characters

 

[randy700]

How about..
ControlName.TextAlign = 2


Randy

 

[leeroi]

Hmmm. Doesn't seem to work. Not knowing that much about SQL, I assumed that I should locate the field name in the SQL line and insert ".TextAlign=2" after it. For example: Referrals.ICD1.TextAlign = 2, where Referrals is the name of the table, and ICD1 is the field that I want to Center align.

 

[PHV]

You may try something like this:
SELECT Space((15-Len(Trim(Referrals.ICD1)))/2) & Trim(Referrals.ICD1) AS Centered_ICD1, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cranebill]

lol I was looking for the access function for this, in oracle we use lpad lol

 

[leeroi]

Wow. This is getting complicated. Unfortunately, SQL didn't like that solution either.

 

[PHV]

SQL didn't like that solution either
What happens ? computer crash ? error message ? unexpected behaviour ? ...
Why not posting your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[leeroi]

Just get ### in the field when I run it.

Here's the way the first line of SQL code looks. I changed the 15 to a 6 as the field I want to center is 6 characters long.

SELECT Clients.LastName, Left([FirstName],1) AS [First Initial], Referrals.SchedDate, Space((6-Len(Trim(Referrals.ICD1)))/2) & Trim(Referrals.ICD1) AS Centered_ICD1, [Case Coordinators].CCName,

Lee

 

[randy700]

Doesn't seem to work.
Where did you put the code?


Randy

 

[leeroi]

It's in bold as follows:

SELECT Clients.LastName, Left([FirstName],1) AS [First Initial], Referrals.SchedDate, Space((6-Len(Trim(Referrals.ICD1)))/2) & Trim(Referrals.ICD1) AS Centered_ICD1, [Case Coordinators].CCName,


Results in #Error being displayed in the field cell.

Lee

 

[PHV]

You maybe have some Null values in ICD1 ?
Space((6-Len(Trim(Referrals.ICD1 [tt]& ''[/tt])))/2) & Trim(Referrals.ICD1 [tt]& ''[/tt])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[leeroi]

Thanks, that took care of the problem with null values. But the cell contents are still left aligned.

Lee

 

[PHV]

Even with a fixed font ?
Have a look at the Format menu.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[leeroi]

The format menu indicates Arial 10, Font style= regular

 

[PHV]

Arial is a proportional font, choose a fixed one (aka monospaced) like Courier or Lucida Console.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[leeroi]

Changed to Lucida Console. Text is still left aligned.

Lee

 

[PHV]

So, I guess the text is right padded with characters other than space.
What is the result of Asc(Right(Referrals.ICD1 & '',1)) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[leeroi]

Where exactly should I put that code in the query?
If I insert in place of Referrals.ICD1 as Referrals.asc(right(referrals.icd1 & ",1), or as asc(right(referrals.icd1 & ",1) then I get an error message that I have a missing operator.

BTW, the field in questions is a basic text field which typically includes numbers and a decimal point