Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Date Range Criteria for Crosstab Query

Discussion in 'Information Technology' started by Mark Cline, Jul 28, 2006.

  1. Mark Cline

    Mark Cline
    Expand Collapse
    Guest

    I have a cross tab query that I am using as a base for a report. I've
    created a form with a BeginningDate text box and an EndingDate text box that
    I want to use as user inputs for a date range for the report. When I run the
    query I get the following error:

    The Microsoft Jet database engine does not recognize '[Forms]![Report
    Creator]![BeginningDate]' as a valid field name or expression.

    I've made the Date part of the crosstab query grouped by "Where" and use the
    following expression to prompt the Report Creator form.

    Between [Forms]![Report Creator]![BeginningDate] And [Forms]![Report
    Creator]![Ending Date] Or [Forms]![Report Creator]![BeginningDate] Is Null

    Why am I getting an error and what can I do to alow the date range to be
    input?
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    You can use parametersin a crosstab query if you explicitly declare them.

    In query design view, choose Parameters on the Query menu.
    Enter two rows in the dialog:
    [Forms]![Report Creator]![BeginningDate] Date/Time
    [Forms]![Report Creator]![Ending Date] Date/Time

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Mark Cline" <MarkCline@discussions.microsoft.com> wrote in message
    news:E2459C6E-127A-4373-8436-EC190251D8BF@microsoft.com...
    >I have a cross tab query that I am using as a base for a report. I've
    > created a form with a BeginningDate text box and an EndingDate text box
    > that
    > I want to use as user inputs for a date range for the report. When I run
    > the
    > query I get the following error:
    >
    > The Microsoft Jet database engine does not recognize '[Forms]![Report
    > Creator]![BeginningDate]' as a valid field name or expression.
    >
    > I've made the Date part of the crosstab query grouped by "Where" and use
    > the
    > following expression to prompt the Report Creator form.
    >
    > Between [Forms]![Report Creator]![BeginningDate] And [Forms]![Report
    > Creator]![Ending Date] Or [Forms]![Report Creator]![BeginningDate] Is Null
    >
    > Why am I getting an error and what can I do to alow the date range to be
    > input?
     
  4. Mark Cline

    Mark Cline
    Expand Collapse
    Guest

    Thanks, that worked with allowing me to input a date range. Now it won't let
    me generate a report. I initially had date as a row heading so I stopped
    having it as a row heading. Now when I go to create a report no fields show
    up in the report wizard when it prompts for which fields you'd like reported.
    What happened and how can I make a report?

    "Allen Browne" wrote:

    > You can use parametersin a crosstab query if you explicitly declare them.
    >
    > In query design view, choose Parameters on the Query menu.
    > Enter two rows in the dialog:
    > [Forms]![Report Creator]![BeginningDate] Date/Time
    > [Forms]![Report Creator]![Ending Date] Date/Time
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Mark Cline" <MarkCline@discussions.microsoft.com> wrote in message
    > news:E2459C6E-127A-4373-8436-EC190251D8BF@microsoft.com...
    > >I have a cross tab query that I am using as a base for a report. I've
    > > created a form with a BeginningDate text box and an EndingDate text box
    > > that
    > > I want to use as user inputs for a date range for the report. When I run
    > > the
    > > query I get the following error:
    > >
    > > The Microsoft Jet database engine does not recognize '[Forms]![Report
    > > Creator]![BeginningDate]' as a valid field name or expression.
    > >
    > > I've made the Date part of the crosstab query grouped by "Where" and use
    > > the
    > > following expression to prompt the Report Creator form.
    > >
    > > Between [Forms]![Report Creator]![BeginningDate] And [Forms]![Report
    > > Creator]![Ending Date] Or [Forms]![Report Creator]![BeginningDate] Is Null
    > >
    > > Why am I getting an error and what can I do to alow the date range to be
    > > input?

    >
    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Because the column names come from the data, Access must run the crosstab
    query to completion before it knows what field names it will have for the
    report.

    This creates problems for designing the report:
    a) It can take quite a while before the fields show up in report design.
    b) There is no guarantee that those field names will be used again next
    time, so the report may end up with #Name errors.

    To avoid these problems, declare all the possible field names in the query
    if possible. To do that, open the crosstab query in design view, open the
    Properties box, and a list of separated values beside the Column Headings
    property.

    If the column names will be constantly changing, you cannot create a report
    that just uses those constantly changing field names. You either have to
    alias the fields in the query somehow, or save the report with generically
    named unbound controls and use the Open event of the report to
    programmatically assign their Control Source, Left, and Visible properties.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Mark Cline" <MarkCline@discussions.microsoft.com> wrote in message
    news:E3A5D87E-B0E1-44E9-A07D-60382C489CF6@microsoft.com...
    > Thanks, that worked with allowing me to input a date range. Now it won't
    > let
    > me generate a report. I initially had date as a row heading so I stopped
    > having it as a row heading. Now when I go to create a report no fields
    > show
    > up in the report wizard when it prompts for which fields you'd like
    > reported.
    > What happened and how can I make a report?
    >
    > "Allen Browne" wrote:
    >
    >> You can use parametersin a crosstab query if you explicitly declare them.
    >>
    >> In query design view, choose Parameters on the Query menu.
    >> Enter two rows in the dialog:
    >> [Forms]![Report Creator]![BeginningDate] Date/Time
    >> [Forms]![Report Creator]![Ending Date] Date/Time
    >>
    >> "Mark Cline" <MarkCline@discussions.microsoft.com> wrote in message
    >> news:E2459C6E-127A-4373-8436-EC190251D8BF@microsoft.com...
    >> >I have a cross tab query that I am using as a base for a report. I've
    >> > created a form with a BeginningDate text box and an EndingDate text box
    >> > that
    >> > I want to use as user inputs for a date range for the report. When I
    >> > run
    >> > the
    >> > query I get the following error:
    >> >
    >> > The Microsoft Jet database engine does not recognize '[Forms]![Report
    >> > Creator]![BeginningDate]' as a valid field name or expression.
    >> >
    >> > I've made the Date part of the crosstab query grouped by "Where" and
    >> > use
    >> > the
    >> > following expression to prompt the Report Creator form.
    >> >
    >> > Between [Forms]![Report Creator]![BeginningDate] And [Forms]![Report
    >> > Creator]![Ending Date] Or [Forms]![Report Creator]![BeginningDate] Is
    >> > Null
    >> >
    >> > Why am I getting an error and what can I do to alow the date range to
    >> > be
    >> > input?
     
  6. Mark Cline

    Mark Cline
    Expand Collapse
    Guest

    Once I did that it started saying I had column names that existed in more
    than one place, which is an error that had never popped up before. Is there
    a better way to get the effect of a crosstab query without the reporting
    problems?

    "Allen Browne" wrote:

    > Because the column names come from the data, Access must run the crosstab
    > query to completion before it knows what field names it will have for the
    > report.
    >
    > This creates problems for designing the report:
    > a) It can take quite a while before the fields show up in report design.
    > b) There is no guarantee that those field names will be used again next
    > time, so the report may end up with #Name errors.
    >
    > To avoid these problems, declare all the possible field names in the query
    > if possible. To do that, open the crosstab query in design view, open the
    > Properties box, and a list of separated values beside the Column Headings
    > property.
    >
    > If the column names will be constantly changing, you cannot create a report
    > that just uses those constantly changing field names. You either have to
    > alias the fields in the query somehow, or save the report with generically
    > named unbound controls and use the Open event of the report to
    > programmatically assign their Control Source, Left, and Visible properties.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Mark Cline" <MarkCline@discussions.microsoft.com> wrote in message
    > news:E3A5D87E-B0E1-44E9-A07D-60382C489CF6@microsoft.com...
    > > Thanks, that worked with allowing me to input a date range. Now it won't
    > > let
    > > me generate a report. I initially had date as a row heading so I stopped
    > > having it as a row heading. Now when I go to create a report no fields
    > > show
    > > up in the report wizard when it prompts for which fields you'd like
    > > reported.
    > > What happened and how can I make a report?
    > >
    > > "Allen Browne" wrote:
    > >
    > >> You can use parametersin a crosstab query if you explicitly declare them.
    > >>
    > >> In query design view, choose Parameters on the Query menu.
    > >> Enter two rows in the dialog:
    > >> [Forms]![Report Creator]![BeginningDate] Date/Time
    > >> [Forms]![Report Creator]![Ending Date] Date/Time
    > >>
    > >> "Mark Cline" <MarkCline@discussions.microsoft.com> wrote in message
    > >> news:E2459C6E-127A-4373-8436-EC190251D8BF@microsoft.com...
    > >> >I have a cross tab query that I am using as a base for a report. I've
    > >> > created a form with a BeginningDate text box and an EndingDate text box
    > >> > that
    > >> > I want to use as user inputs for a date range for the report. When I
    > >> > run
    > >> > the
    > >> > query I get the following error:
    > >> >
    > >> > The Microsoft Jet database engine does not recognize '[Forms]![Report
    > >> > Creator]![BeginningDate]' as a valid field name or expression.
    > >> >
    > >> > I've made the Date part of the crosstab query grouped by "Where" and
    > >> > use
    > >> > the
    > >> > following expression to prompt the Report Creator form.
    > >> >
    > >> > Between [Forms]![Report Creator]![BeginningDate] And [Forms]![Report
    > >> > Creator]![Ending Date] Or [Forms]![Report Creator]![BeginningDate] Is
    > >> > Null
    > >> >
    > >> > Why am I getting an error and what can I do to alow the date range to
    > >> > be
    > >> > input?

    >
    >
    >
     
  7. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    I don't understand the problem, Mark.
    Haven't seen that error message.

    Did you type in some names twice perhaps?

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Mark Cline" <MarkCline@discussions.microsoft.com> wrote in message
    news:FC3A3D78-DB22-4ADC-8F20-2604B1E74C03@microsoft.com...
    > Once I did that it started saying I had column names that existed in more
    > than one place, which is an error that had never popped up before. Is
    > there
    > a better way to get the effect of a crosstab query without the reporting
    > problems?
    >
    > "Allen Browne" wrote:
    >
    >> Because the column names come from the data, Access must run the crosstab
    >> query to completion before it knows what field names it will have for the
    >> report.
    >>
    >> This creates problems for designing the report:
    >> a) It can take quite a while before the fields show up in report design.
    >> b) There is no guarantee that those field names will be used again next
    >> time, so the report may end up with #Name errors.
    >>
    >> To avoid these problems, declare all the possible field names in the
    >> query
    >> if possible. To do that, open the crosstab query in design view, open the
    >> Properties box, and a list of separated values beside the Column Headings
    >> property.
    >>
    >> If the column names will be constantly changing, you cannot create a
    >> report
    >> that just uses those constantly changing field names. You either have to
    >> alias the fields in the query somehow, or save the report with
    >> generically
    >> named unbound controls and use the Open event of the report to
    >> programmatically assign their Control Source, Left, and Visible
    >> properties.
    >>
    >> "Mark Cline" <MarkCline@discussions.microsoft.com> wrote in message
    >> news:E3A5D87E-B0E1-44E9-A07D-60382C489CF6@microsoft.com...
    >> > Thanks, that worked with allowing me to input a date range. Now it
    >> > won't
    >> > let
    >> > me generate a report. I initially had date as a row heading so I
    >> > stopped
    >> > having it as a row heading. Now when I go to create a report no fields
    >> > show
    >> > up in the report wizard when it prompts for which fields you'd like
    >> > reported.
    >> > What happened and how can I make a report?
    >> >
    >> > "Allen Browne" wrote:
    >> >
    >> >> You can use parametersin a crosstab query if you explicitly declare
    >> >> them.
    >> >>
    >> >> In query design view, choose Parameters on the Query menu.
    >> >> Enter two rows in the dialog:
    >> >> [Forms]![Report Creator]![BeginningDate] Date/Time
    >> >> [Forms]![Report Creator]![Ending Date] Date/Time
    >> >>
    >> >> "Mark Cline" <MarkCline@discussions.microsoft.com> wrote in message
    >> >> news:E2459C6E-127A-4373-8436-EC190251D8BF@microsoft.com...
    >> >> >I have a cross tab query that I am using as a base for a report.
    >> >> >I've
    >> >> > created a form with a BeginningDate text box and an EndingDate text
    >> >> > box
    >> >> > that
    >> >> > I want to use as user inputs for a date range for the report. When
    >> >> > I
    >> >> > run
    >> >> > the
    >> >> > query I get the following error:
    >> >> >
    >> >> > The Microsoft Jet database engine does not recognize
    >> >> > '[Forms]![Report
    >> >> > Creator]![BeginningDate]' as a valid field name or expression.
    >> >> >
    >> >> > I've made the Date part of the crosstab query grouped by "Where" and
    >> >> > use
    >> >> > the
    >> >> > following expression to prompt the Report Creator form.
    >> >> >
    >> >> > Between [Forms]![Report Creator]![BeginningDate] And [Forms]![Report
    >> >> > Creator]![Ending Date] Or [Forms]![Report Creator]![BeginningDate]
    >> >> > Is
    >> >> > Null
    >> >> >
    >> >> > Why am I getting an error and what can I do to alow the date range
    >> >> > to
    >> >> > be
    >> >> > input?
     
  8. Mark Cline

    Mark Cline
    Expand Collapse
    Guest

    No, it said that the name "Line" could exist in more than one table.

    "Allen Browne" wrote:

    > I don't understand the problem, Mark.
    > Haven't seen that error message.
    >
    > Did you type in some names twice perhaps?
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Mark Cline" <MarkCline@discussions.microsoft.com> wrote in message
    > news:FC3A3D78-DB22-4ADC-8F20-2604B1E74C03@microsoft.com...
    > > Once I did that it started saying I had column names that existed in more
    > > than one place, which is an error that had never popped up before. Is
    > > there
    > > a better way to get the effect of a crosstab query without the reporting
    > > problems?
    > >
    > > "Allen Browne" wrote:
    > >
    > >> Because the column names come from the data, Access must run the crosstab
    > >> query to completion before it knows what field names it will have for the
    > >> report.
    > >>
    > >> This creates problems for designing the report:
    > >> a) It can take quite a while before the fields show up in report design.
    > >> b) There is no guarantee that those field names will be used again next
    > >> time, so the report may end up with #Name errors.
    > >>
    > >> To avoid these problems, declare all the possible field names in the
    > >> query
    > >> if possible. To do that, open the crosstab query in design view, open the
    > >> Properties box, and a list of separated values beside the Column Headings
    > >> property.
    > >>
    > >> If the column names will be constantly changing, you cannot create a
    > >> report
    > >> that just uses those constantly changing field names. You either have to
    > >> alias the fields in the query somehow, or save the report with
    > >> generically
    > >> named unbound controls and use the Open event of the report to
    > >> programmatically assign their Control Source, Left, and Visible
    > >> properties.
    > >>
    > >> "Mark Cline" <MarkCline@discussions.microsoft.com> wrote in message
    > >> news:E3A5D87E-B0E1-44E9-A07D-60382C489CF6@microsoft.com...
    > >> > Thanks, that worked with allowing me to input a date range. Now it
    > >> > won't
    > >> > let
    > >> > me generate a report. I initially had date as a row heading so I
    > >> > stopped
    > >> > having it as a row heading. Now when I go to create a report no fields
    > >> > show
    > >> > up in the report wizard when it prompts for which fields you'd like
    > >> > reported.
    > >> > What happened and how can I make a report?
    > >> >
    > >> > "Allen Browne" wrote:
    > >> >
    > >> >> You can use parametersin a crosstab query if you explicitly declare
    > >> >> them.
    > >> >>
    > >> >> In query design view, choose Parameters on the Query menu.
    > >> >> Enter two rows in the dialog:
    > >> >> [Forms]![Report Creator]![BeginningDate] Date/Time
    > >> >> [Forms]![Report Creator]![Ending Date] Date/Time
    > >> >>
    > >> >> "Mark Cline" <MarkCline@discussions.microsoft.com> wrote in message
    > >> >> news:E2459C6E-127A-4373-8436-EC190251D8BF@microsoft.com...
    > >> >> >I have a cross tab query that I am using as a base for a report.
    > >> >> >I've
    > >> >> > created a form with a BeginningDate text box and an EndingDate text
    > >> >> > box
    > >> >> > that
    > >> >> > I want to use as user inputs for a date range for the report. When
    > >> >> > I
    > >> >> > run
    > >> >> > the
    > >> >> > query I get the following error:
    > >> >> >
    > >> >> > The Microsoft Jet database engine does not recognize
    > >> >> > '[Forms]![Report
    > >> >> > Creator]![BeginningDate]' as a valid field name or expression.
    > >> >> >
    > >> >> > I've made the Date part of the crosstab query grouped by "Where" and
    > >> >> > use
    > >> >> > the
    > >> >> > following expression to prompt the Report Creator form.
    > >> >> >
    > >> >> > Between [Forms]![Report Creator]![BeginningDate] And [Forms]![Report
    > >> >> > Creator]![Ending Date] Or [Forms]![Report Creator]![BeginningDate]
    > >> >> > Is
    > >> >> > Null
    > >> >> >
    > >> >> > Why am I getting an error and what can I do to alow the date range
    > >> >> > to
    > >> >> > be
    > >> >> > input?

    >
    >
    >
     

Share This Page