Complex Query not working on report

  • Thread starter Thread starter Broken Coder
  • Start date Start date
B

Broken Coder

I recently started using 07 on a fe/be db created in 03. The query below
worked fine in 03 but now gives me the "enter parameter value" box asking for
"tblRECORDS.CREATE" when I open a report based on it with 07. The query
still opens and displays fine, but the report seems to hangup. Is it a
timing issue? I know the query is kind of busy, but it is based on a table
where the data is stored as 1s or 0's to make big queries calculate faster.

SELECT tblRECORDS.QAC, tblRECORDS.CHS, Sum(IIf([IO]="INBOUND",[BR],0)) AS
IBR, Sum(IIf([IO]="OUTBOUND",[BR],0)) AS OBR, Sum(IIf([IO]="INBOUND",[SH],0))
AS ISH, Sum(IIf([IO]="outbound",[SH],0)) AS OSH,
Sum(IIf([IO]="inbound",[MD],0)) AS IMD, Sum(IIf([IO]="outbound",[MD],0)) AS
OMD, Sum(IIf([IO]="INBOUND",[LG],0)) AS ILG, Sum(IIf([IO]="outbound",[LG],0))
AS OLG, Sum(IIf([IO]="inbound",1,0)) AS IT, Sum(IIf([IO]="outbound",1,0)) AS
OT
FROM tblRECORDS
WHERE ((tblRECORDS.CREATE Between [Forms]![frmRptMnu]![txtStart] And
[Forms]![frmRptMnu]![txtEnd]))
GROUP BY tblRECORDS.QAC, tblRECORDS.CHS;
 
Thanks...I'll look for that. Any idea why it all displayed correctly in 03?

Marshall Barton said:
Broken said:
I recently started using 07 on a fe/be db created in 03. The query below
worked fine in 03 but now gives me the "enter parameter value" box asking for
"tblRECORDS.CREATE" when I open a report based on it with 07. The query
still opens and displays fine, but the report seems to hangup. Is it a
timing issue? I know the query is kind of busy, but it is based on a table
where the data is stored as 1s or 0's to make big queries calculate faster.

SELECT tblRECORDS.QAC, tblRECORDS.CHS, Sum(IIf([IO]="INBOUND",[BR],0)) AS
IBR, Sum(IIf([IO]="OUTBOUND",[BR],0)) AS OBR, Sum(IIf([IO]="INBOUND",[SH],0))
AS ISH, Sum(IIf([IO]="outbound",[SH],0)) AS OSH,
Sum(IIf([IO]="inbound",[MD],0)) AS IMD, Sum(IIf([IO]="outbound",[MD],0)) AS
OMD, Sum(IIf([IO]="INBOUND",[LG],0)) AS ILG, Sum(IIf([IO]="outbound",[LG],0))
AS OLG, Sum(IIf([IO]="inbound",1,0)) AS IT, Sum(IIf([IO]="outbound",1,0)) AS
OT
FROM tblRECORDS
WHERE ((tblRECORDS.CREATE Between [Forms]![frmRptMnu]![txtStart] And
[Forms]![frmRptMnu]![txtEnd]))
GROUP BY tblRECORDS.QAC, tblRECORDS.CHS;
Click to expand...


That means something in your report (a text box expression
or in Sorting and Grouping) is referencing tblRECORDS.CREATE

If you can find it, you need to decide if you should remove
it from the report or modify the query's select list to
include the CREATE field.
Click to expand...
 
When switching between different versions of either Access or of Windows -
including the installation of Service Packs - you should always make a full
decompilation of the MDB or ACCDB file first; as this will often solve many
"strange" problems. I have no idea if this will solve your problem but it
takes only a few seconds to do it: go to the References dialog window in
the VBA editor, add a dummy reference (anyone) and close the References
window (important!). As the list of references has changed, this will force
Access to recompile all the module the next time they are needed. You can
also launch the Recompile All Modules from the Debug menu. Immediately
after having changed the list of references, you can reopen the References
window and delete the dummy reference that you have just added as Access has
already noted the change when you close the window after the first step.

Another method is to create a windows shortcut to your database with the
following options:

/decompile /compact /repair

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


Broken Coder said:
Thanks...I'll look for that. Any idea why it all displayed correctly in
03?

Marshall Barton said:
Broken said:
I recently started using 07 on a fe/be db created in 03. The query
below
worked fine in 03 but now gives me the "enter parameter value" box
asking for
"tblRECORDS.CREATE" when I open a report based on it with 07. The query
still opens and displays fine, but the report seems to hangup. Is it a
timing issue? I know the query is kind of busy, but it is based on a
table
where the data is stored as 1s or 0's to make big queries calculate
faster.

SELECT tblRECORDS.QAC, tblRECORDS.CHS, Sum(IIf([IO]="INBOUND",[BR],0))
AS
IBR, Sum(IIf([IO]="OUTBOUND",[BR],0)) AS OBR,
Sum(IIf([IO]="INBOUND",[SH],0))
AS ISH, Sum(IIf([IO]="outbound",[SH],0)) AS OSH,
Sum(IIf([IO]="inbound",[MD],0)) AS IMD, Sum(IIf([IO]="outbound",[MD],0))
AS
OMD, Sum(IIf([IO]="INBOUND",[LG],0)) AS ILG,
Sum(IIf([IO]="outbound",[LG],0))
AS OLG, Sum(IIf([IO]="inbound",1,0)) AS IT,
Sum(IIf([IO]="outbound",1,0)) AS
OT
FROM tblRECORDS
WHERE ((tblRECORDS.CREATE Between [Forms]![frmRptMnu]![txtStart] And
[Forms]![frmRptMnu]![txtEnd]))
GROUP BY tblRECORDS.QAC, tblRECORDS.CHS;
Click to expand...


That means something in your report (a text box expression
or in Sorting and Grouping) is referencing tblRECORDS.CREATE

If you can find it, you need to decide if you should remove
it from the report or modify the query's select list to
include the CREATE field.
Click to expand...
Click to expand...
 
Back
Top