Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limit values in a crosstab (2003)

    Is there a way to limit the dataset used in a crosstab?

    I am trying to limit mine to data for a specific week using the date stored in a text field on a form but when I try I get an error:

    The Microsoft Jet database engine does not recognize Format([forms]![frmMain].[txtScheduleDate],"mm/dd/yyyy") as a valid field name or expression.

    I'm stumped.
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limit values in a crosstab (2003)

    I'd use [forms]![frmMain]![txtScheduleDate] i.e. exclamation mark instead of point.

    Open the query in design view.
    Select Query | Parameters...
    Enter [forms]![frmMain].[txtScheduleDate] in the Parameter column, and select Date/Time as data type.
    Click OK.
    Save the query.
    Open the query to test whether it works now.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit values in a crosstab (2003)

    If I do that, I don't see any restrictions on the data...I see all my data.

    I need to limit it to between the date ([forms]![frmMain]![txtScheduleDate] ) and 6 days after that date...

    Between [forms]![frmMain]![txtScheduleDate] -7 And [forms]![frmMain]![txtScheduleDate] does not work...
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  4. #4
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Limit values in a crosstab (2003)

    Can you not preselect the data before the crosstab or use a separate query with the crosstab as source and then use criteria? I run into this all the time and use either one or the other technique successfully.
    Good luck.
    itconc

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limit values in a crosstab (2003)

    Adding [forms]![frmMain]![txtScheduleDate] to Query | Parameters doesn't impose any restriction. It is only intended to make Access recognize the parameter, so that you don't get the error message.

    If you want to "limit it to between the date ([forms]![frmMain]![txtScheduleDate] ) and 6 days after that date..." you should use

    Between [forms]![frmMain]![txtScheduleDate] And [forms]![frmMain]![txtScheduleDate]+6

  6. #6
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit values in a crosstab (2003)

    Edited by HansV to shrink very large screenshot - please keep 'em small

    Hmmm....

    I'm still not getting expected results...

    I've attached screenshots that show the source data and the results of the query in question plus the QBE of the query...

    I expect to see some of the results, but I see more than the "+2" I have asked for...
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limit values in a crosstab (2003)

    The "dates" in the TheDate column are left-aligned. This means that they are text values; you cannot perform date arithmetic with text values. Why do you have dates as text values?

  8. #8
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limit values in a crosstab (2003)

    THANK YOU Hans!

    I did not know that they would be aligned differently so I'm glad I gave a screenshot.

    I changed them via Format() to display differently. I undid the Format() and it works awesomely now.

    I never even thought about Format() changing them to text...but it makes complete sense...
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

Posting Permissions

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