report error when no data in txt box from query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I probably have a design flaw - but here goes..... I need to add how many
hours employees work on various jobs in a weeks time..... I first run a make
table query to extract the work week ... from this I run a crosstab query to
accumulate the hours .... I then run a report from the crosstab ..... The
report has text fields for eery day of the week - (Mon - Sun) .....Here is
my issue - if, in a given week the crosstab query has no data for a given day
- the report will not run - it will ask for data for "Sunday" and just
bomb..... Sooo how do I design my report to accept the fact that there is no
data for Sunday and use zero hours or just blank...... I kinda think my
answer is compose some vb code some "if" statement on the "has data" property
and if so - what does it say?? or whatever solution will work to allow a
report textbox to have no data
 
No design flaw. You just need to make a modest change to your Crosstab query.

If you are using the query grid, one of the queries properties is Column Headings

Just open the query up and type
"Sunday","Monday","Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"
in the property


If you are doing this in the SQL view then

TRANSFORM ...
SELECT ...
FROM ...
GROUP BY ...
PIVOT On [SomeFieldOrExpession] IN ("Sunday","Monday","Tuesday", "Wednesday",
"Thursday", "Friday", "Saturday")
 
Back
Top