Query Field not showing up as available on report???

B

BobC

Why would a query field (EXPL_TF_PROP_TOTAL) that prints fine in data
sheet view not be available to a report that has that query as it's only
source? All other fields show up as available to the report. I have
included the query SQL if that will help?

SELECT qryClaimDetails.DACC, qryClaimDetails.DRPT,
qryClaimDetails.[CLAIM#], qryClaimDetails.COV, qryClaimDetails.HANAME,
qryClaimDetails.POLYR, qryClaimDetails.SUBIND, qryClaimDetails.HA,
qryClaimDetails.DESCR, qryClaimDetails.CLAIMANT, qryClaimDetails.STATUS,
qryClaimDetails.CURRES, qryClaimDetails.DCURRES, qryClaimDetails.RESCHG,
qryClaimDetails.SumOfPDLEGAL, qryClaimDetails.SumOfPDLOSS,
qryClaimDetails.SumOfPDOTHEREXP, qryClaimDetails.Incurred,
qryClaimDetails.MEMDED_PROP, qryClaimDetails.MEMDED_TF,
qryClaimDetails.MEMDED_AUTOPHY,
Nz([MEMDED_TF],0)+Nz([MEMDED_AUTOPHY],0)+Nz([MEMDED_PROP],0) AS
MEMDED_PROP_TF_AUTOPHY_TOTAL, qryClaimDetails.MHAPDED_PROP,
qryClaimDetails.MHAPDED_TF, Nz([MHAPDED_TF],0)+Nz([MHAPDED_PROP],0) AS
MHAPDED_PROP_TF_TOTAL, qryClaimDetails.PLSA_PROP,
qryClaimDetails.PLSA_TF, Nz([PLSA_TF],0)+Nz([PLSA_PROP],0) AS
PLSA_PROP_TF_TOTAL, qryClaimDetails.EXPL_PROP, qryClaimDetails.EXPL_TF,
Nz([EXPL_TF],0)+Nz([EXPL_PROP],0) AS EXPL_TF_PROP_TOTAL,
qryClaimDetails.EXLL_GL, qryClaimDetails.EXLL_EPL,
Nz([EXLL_GL],0)+Nz([EXLL_EPL],0) AS EXLL_GL_EPL_TOTAL,
qryClaimDetails.AutoPhyGndUp, qryClaimDetails.PropGndUp,
qryClaimDetails.OtherGndUp, [PropGNDUp]+[AutoPhyGndUp]+[OtherGndUp] AS GNDUP
FROM qryClaimDetails
WHERE (((qryClaimDetails.DACC) Between
DateSerial([Forms]![frmClaimDetail]![cboBeginYear],[Forms]![frmClaimDetail]![cboBeginmonth],1)
And
DateSerial([Forms]![frmClaimDetail]![cboEndYear],[Forms]![frmClaimDetail]![cboEndmonth]+1,0)))
ORDER BY qryClaimDetails.DACC, qryClaimDetails.[CLAIM#];
 
A

Allen Browne

I'm not clear what you mean by "not available" in the report.

If you mean that it is not shown in the Field List in design view:
1. Close the report and re-open it.

2. Double-check the RecordSource is the query expect (and it not being
changed in the report's events.)

3. Make sure Name AutoCorrect is turned off:
http://allenbrowne.com/bug-03.html

4. Compact/repair the database.
In fact, you might want to run this repair sequence:
http://allenbrowne.com/recover.html

If the field is available in the field list, but Access complains when you
try to use it (e.g. popping up a parameter box asking for the value), this
is probably due to the way the report optimizer runs. Depending on a range
of factors (e.g. sorting/grouping in the report), it may decide not to fetch
a field if it can't find a control bound to it. So when you try to refer to
the value as part of an expression in another box, it acts like the field is
not there. To solve this, add a text box bound to that field, and it will
fetch it for you.

A third possibility is that Access not understanding the data type of this
field correctly. When you use Nz() or any VBA function that returns a
variant, Access may understand the results as Text (rather than numeric.) If
this is the problem, you will see the field left-aligned when you view the
query results, instead of right-aligned as a number. (You may find that the
plus is enough for it to get this, but it just might concatenate instead of
summing.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

BobC said:
Why would a query field (EXPL_TF_PROP_TOTAL) that prints fine in data
sheet view not be available to a report that has that query as it's only
source? All other fields show up as available to the report. I have
included the query SQL if that will help?

SELECT qryClaimDetails.DACC, qryClaimDetails.DRPT,
qryClaimDetails.[CLAIM#], qryClaimDetails.COV, qryClaimDetails.HANAME,
qryClaimDetails.POLYR, qryClaimDetails.SUBIND, qryClaimDetails.HA,
qryClaimDetails.DESCR, qryClaimDetails.CLAIMANT, qryClaimDetails.STATUS,
qryClaimDetails.CURRES, qryClaimDetails.DCURRES, qryClaimDetails.RESCHG,
qryClaimDetails.SumOfPDLEGAL, qryClaimDetails.SumOfPDLOSS,
qryClaimDetails.SumOfPDOTHEREXP, qryClaimDetails.Incurred,
qryClaimDetails.MEMDED_PROP, qryClaimDetails.MEMDED_TF,
qryClaimDetails.MEMDED_AUTOPHY,
Nz([MEMDED_TF],0)+Nz([MEMDED_AUTOPHY],0)+Nz([MEMDED_PROP],0) AS
MEMDED_PROP_TF_AUTOPHY_TOTAL, qryClaimDetails.MHAPDED_PROP,
qryClaimDetails.MHAPDED_TF, Nz([MHAPDED_TF],0)+Nz([MHAPDED_PROP],0) AS
MHAPDED_PROP_TF_TOTAL, qryClaimDetails.PLSA_PROP, qryClaimDetails.PLSA_TF,
Nz([PLSA_TF],0)+Nz([PLSA_PROP],0) AS PLSA_PROP_TF_TOTAL,
qryClaimDetails.EXPL_PROP, qryClaimDetails.EXPL_TF,
Nz([EXPL_TF],0)+Nz([EXPL_PROP],0) AS EXPL_TF_PROP_TOTAL,
qryClaimDetails.EXLL_GL, qryClaimDetails.EXLL_EPL,
Nz([EXLL_GL],0)+Nz([EXLL_EPL],0) AS EXLL_GL_EPL_TOTAL,
qryClaimDetails.AutoPhyGndUp, qryClaimDetails.PropGndUp,
qryClaimDetails.OtherGndUp, [PropGNDUp]+[AutoPhyGndUp]+[OtherGndUp] AS
GNDUP
FROM qryClaimDetails
WHERE (((qryClaimDetails.DACC) Between
DateSerial([Forms]![frmClaimDetail]![cboBeginYear],[Forms]![frmClaimDetail]![cboBeginmonth],1)
And
DateSerial([Forms]![frmClaimDetail]![cboEndYear],[Forms]![frmClaimDetail]![cboEndmonth]+1,0)))
ORDER BY qryClaimDetails.DACC, qryClaimDetails.[CLAIM#];
Click to expand...
 
B

BobC

You must have seen this problem before!
I meant ... The field is not available in the field list.

I do not understand what you are saying in "2. Double-check the
RecordSource is the query expect (and it not being changed in the
report's events.)"

Thanks,
Bob
 
A

Allen Browne

If the report is bound bo Query2, but you are looking in Query1, you won't
understand why the field doesn't show up. (Don't laugh: it happens!)

Sometimes developers use the Open event of the report to change its
RecordSource property, e.g.:
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT ...
End Sub
If you do that, the actual fields listed at design time, may not be in the
SELECT statement used in the code, so a field that seems to be there at
design time is not found when you run the report, and vice versa.
 
B

BobC

Thanks for your help.
Something in the Decompile/compile ... recovery sequence got rid of the
field in the query ... when I reentered it, it came up as available in
the report design. I had deleted and reentered the field before ... to
no avail.
It now works! ... thanks,
Bob
 
Top