Results 1 to 3 of 3

Thread: Crosstab Issue

  1. #1
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267

    Unanswered: Crosstab Issue

    To All--

    I have a crosstab query that for certain fields returns nothing. This is to be expected because there is no data for that field under that column. The columns are dates.

    The issue is that I want to convert that blank into a zero. I can't get anything to work. I've tried using IsNull, Nz, and even tested to see if it is = "" or even = Null. Nothing works. I need to do this in the query.

    C

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    A field containing a date is a, by definition, a Date/Time Datatype.

    A field containing a Zero is, by definition, a Numbers Datatype.

    You can't put one into the other.

    I suppose you could redefine the field as Text, which would allow 'dates' and 'numbers' to be entered, but you'd then have to convert the field to a date, using CDate() every time you needed to use it as a date.

    A lot of work for a cosmetic effect, but only you know your needs.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Sorry I wasn't clear enough...I meant to say the column headings are dates. It's a dynamic crosstab that only shows to 10 most current dates. The values for the columns are all integers. So I'm at a loss as to how to convert the "blanks" to zeros.

    Anyone else had anything like this?

    C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •