I REALLY need HELP!!!

[Noreene Patrick]

I have asked this question a couple of times but maybe I was too wandering when I posed this problem. And I really need help on this!!!

A crosstab query queries only reg and ot as column headings. My report pulls from this query and is set up to show reg hours and OT hours in separate columns.

However, there are some weeks that there is no OT.. My report refuses to open because it cant find the column OT in my query.

I cannot let this problem continue. I MUST get it resolved for other users to not be confused in what to do.

I tried putting OT as a parmeter in my query but then when there is OT it doesnt show it.

Please, WHAT CAN I DO????!!

Thanks for any suggestions.

 

[3699wat]

Can you attach a "bare bone" copy of the database??? It's sometimes easier to fix when you can look at it.

 

[tehchino]

Oh i got this problem too before, i solved it by 'forcing' the Query to displace out the 2 coloum even i dont have any data. you try to set in the query properties( right click) under Column Heading and put in "OT", "Reg". It should work...

 

[Noreene Patrick]

I cant seem to grt it to attach. Could I try to email it to you?

 

[Noreene Patrick]

Techino

I have already tried that and it doesnt make an OT column heading if there is no OT.

Any suggestions?

 

[tehchino]

you give me some time now think ah...

 

[tehchino]

did your report inside have the colunm for OT?
Is it that the report can be generated but that #error is shown instead?

 

[Noreene Patrick]

My report does have the column for OT. And the report will not open if there is no OT. It says "Microsoft Jet Engine does not recognize [ot] as a valid field name or expression."

And I understand that it cant pull ot into the report if there is no ot column heading. That is the dilemma. How can I make the query create a column heading without data?

I tried the parameter but it wont show OT in the report even when there is OT.

 

[Rich]

Use the Nz function in the initial query, Nz([Overtime],0) also check that the property sheet does not specify sorting by this field, use the sort option from the toolbar instead

 

[Noreene Patrick]

Rich,

I put Nz([ot],0) in the criteria for column heading Paycode

I wasnt sure what to put in crosstab line, but I tried group by and where. Neither worked. It keeps saying doesnt recognize ot as field name or expression.

Maybe I am going about this wrong. Do I need to fix this in the query or do I need to work on the report to make it open without ot?

I would like any suggestions.

Thanks to all

 

[Rich]

Email it to me, save it as 97ver, make sure you compact the db and zip it, Rich@richies50.fsnet.co.uk

 

[Noreene Patrick]

Pat,
Thank you for answering. I admit my knowledge of Access is limited. And as far as I know, I have "fixed" the column heading for paycode to show as follows in criteria line

In("reg", "ot")

And like I said before, it does this correctly WHEN there is ot. The problem occurs when there is no ot, so it doesnt make a fieldname for ot.

Rich is helping me by looking at my db. When I "grow up" I want to be as smart in Access as the two of you are!!!!

But, if you would like to take a look at the db, I will be glad to email it to you also, I need any help I can get. My time to complete this is running out.

Thanks so much for answering..

 

[Rich]

I couldn't re-create your problem with 97, having said that however you are Summing values in the name footer, I suspect the problem with nulls is arising there, I very quickly put together a report using the wizard, try that one though you may have to use Nz(Sum[ot]),0)

 

[Noreene Patrick]

Rich,

I opened the report you sent back and it works like a charm!! Exactly what I needed....Thank you so very much

Now, why would it not want to sum in the footer with nulls?

 

[Noreene Patrick]

I want to thank everyone for helping me with this project...and a special thanks to Rich and Pat. I now have this project completed and behind me and waiting for the next one.

But, I wanted to write this for others that may be as much of a novice in Access as I am. You kept saying to set the column property headings to REG and OT to force those columns. Well, I thought that the coulmn headings was the column in the query grid where I show which field is to be the column headings so I tried to put REG and OT in the criteria section... would never work right.

Then, I happened to right click on the gray area above the grid where the tables go that you pull the fields from and went to properties and there it was... the column heading option.

I wanted to be sure others that were following this thread wasnt having the problem I had. It works great now!!!

Thanks again.

I LOVE THIS FORUM!!!!!!!