Default Values for a Cross-Tab query 1

[FractalWalk]

I have Access 2002 and I want to create a Cross-tab query for export into a text file. The goal of this is to have each field be a fixed length (e.g. 10 characters). The fields are all text and 10 characters in length in the originating tables.

However, in the crosstab query some values can return as null and therefore they are 0 characters in length. I want these null values to default to 10 empty spaces (or 10 0's). I tried adding an input mask, to the value field of the crosstab query but it didn't help. The query still exports null data as 0 characters in length.

Any ideas on how to force a default value in a crosstab query?

 

[dhookom]

Query questions are usually best asked by including your SQL in the question.

You can probably set the value to something like:
First(Nz([YourField],"0000000000"))

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[FractalWalk]

That didn't work. I had used a conditional to fill null values with "0000000000" and it works with the query output. But when I export the data to a csv file, the values are trimmed and so my "0000000000" becomes "". I guess the root of my problem is that I want the output to be text but the cross-tab query forces it to be values.


Here is teh SQL:

TRANSFORM Sum(IIf(IsNull([Std Rate]),"0000000000 ",[Std Rate])) AS Expr
SELECT [CB270 History].[Std Rate Key]
FROM [CB270 History]
GROUP BY [CB270 History].[Std Rate Key]
PIVOT [CB270 History].Date;

 

[dhookom]

I'm not sure how you can get around the value not converting to text. Try this:
TRANSFORM Format(Nz(Sum([Std Rate]),0),"0000000000")
AS Expr
SELECT [CB270 History].[Std Rate Key]
FROM [CB270 History]
GROUP BY [CB270 History].[Std Rate Key]
PIVOT [CB270 History].Date;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[FractalWalk]

I think that will do it! I need to format in some decimal places, but my test export had 10 characters for all fields.

Thanks so much!!