If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Complex Query not working on report
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; |
#2
|
|||
|
|||
Complex Query not working on report
Broken Coder wrote:
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; 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. -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Complex Query not working on report
Thanks...I'll look for that. Any idea why it all displayed correctly in 03?
"Marshall Barton" wrote: Broken Coder wrote: 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; 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. -- Marsh MVP [MS Access] . |
#4
|
|||
|
|||
Complex Query not working on report
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" wrote in message ... Thanks...I'll look for that. Any idea why it all displayed correctly in 03? "Marshall Barton" wrote: Broken Coder wrote: 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; 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. -- Marsh MVP [MS Access] . |
Thread Tools | |
Display Modes | |
|
|