Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab Bug? (2000)

    I am getting the message 'The Microsoft Jet Engine does not recognize '[Forms]![frm_MainForm]![txtDate1] as a valid field name or expression' when I include the form reference as part of a Between .. And ... statement in the criteria for a crosstab query. The SQL comes out as 'WHERE (((qry_2003_lolworkCodesExcluded_InDateRange.deliv _date) Between [Forms]![frmMainForm]![txtDate1] And [Forms]![frmMainForm]![txtDate2]))' which looks OK to me.

    I copied the same line into a standard select query and it worked OK!
    So, I thought I'd just remove the Where clause from the crosstab and base the crosstab on the select query I had just created. Unfortunately this produced the same error message.
    I know there's nothing wrong with the where clause because it works in the straight select query. However the crosstab has a problem with the where clause whether it is in the crosstab itself or in the query the crosstab is based on.

    I'm sure I have created a lot of crosstabs with this sort of form referencing where clause in the past. Have I got false memory syndrome, am I doing something silly or is this a 2000 issue?

    Ian

  2. #2
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Bug? (2000)

    I have solved the problem by setting up the form references as parameters. Is this how it always used to be or is 2000 stricter than 97?

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

    Re: Crosstab Bug? (2000)

    This occurred in Access 97 too. You may get away without specifying the parameters explicitly if they are text, but not if they are of type date/time.

Posting Permissions

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