Query result error

[Noob]

ftp://ukcassassin:winston@www.ukcass...ary%20Form.bmp

Hi all
I will appologise in advance for my lack of knowledge of access and its working as i am quite new to the program and have been self taught, so here goes.

As you can see by the image link i am creating an appointment diary for a garage,its going ok untill i created the "Hours remaing" text box The main subform adds up the hours booked in and displays them in the "Hours Booked" text box, this works fine. The Subform "Techs Absent" is based on a query that checks a "holiday table" that has a "Tech ID","Name","Hol iday Start Date" , "Holiday End Date"and "Bookable Hours" field in it. Basically it checks the date selected on the calender control is >= the hol start date and <= the holiday end date and if so it displays the tech names and their bookable hours in this subform (not sure if thats clear) this runs realtime with each date selected in the calender control and shows me who is off on that particular day and how many hours they are worth. (hope your still with me...lol) I.E: if a tech is absent his bookable hours must be deducted from the total hours for that day which is normally 26

The total hours for the 4 techs is 26 hours, and my problem is that i based the record source for the "Hours Remaing" text box on a value of "26 hours" minus the "Total Hours Booked" value, Minus the sum of the "Bookable Hours" field in the Techs absent subform. This works fine when the "Techs absent subform" shows a value but when there are no techs absent on the date selected , the query shows no results in the subform and the "Hours Remaining" text box shows ERROR. Is there a way to still do the calculation even if the query result shows no value?

I know it was long winded and probably very confusing but your help would be so much appreciated
Many thanks in advance
Chris

[Phille]

Hi

I couldn't me more confused, I understood about 10% of your text (my head gets twisted halfway) but I think it should work with an IF sentence in the criteria field of your query. In that way you could tell your query to do the calculation if there is a value in the abcent field otherwise it just skips the calculation.

I'm in a hurry for work but hopefully this helps a bit

[MMcCarthy]

The total hours for the 4 techs is 26 hours, and my problem is that i based the record source for the "Hours Remaing" text box on a value of "26 hours" minus the "Total Hours Booked" value, Minus the sum of the "Bookable Hours" field in the Techs absent subform. This works fine when the "Techs absent subform" shows a value but when there are no techs absent on the date selected , the query shows no results in the subform and the "Hours Remaining" text box shows ERROR. Is there a way to still do the calculation even if the query result shows no value?

I know it was long winded and probably very confusing but your help would be so much appreciated
Many thanks in advance
Chris

You will have to change 'Minus the sum of the "Bookable Hours" field in the Techs absent subform' to

... - IIf(Not IsNull([Bookable Hours],[Bookable Hours],0)

Therefore if there is nothing in this field the calculation can still take place. The other option is to set the default value of the field to 0 in the table.

Mary

[NeoPa]

...Or the shorthand for that is :

... - Nz([Bookable Hours],0)

[Noob]

Hi
Firstly thanks for your replies, I have tried all your suggestions but I am still struggling at the mo,it still shows ERROR in the text box on the form,I dont think I explained it very well either,so i'll try again. The TECHS ABSENT SUBFORM (top left on the picture ) is basing its record source on this query:

SELECT DISTINCT TechnicianTable.[Technician Name],
                TechnicianTable.[Bookable Hours],
                JobTable.Date
FROM JobTable, 
     TechnicianTable INNER JOIN HolidayTable
  ON TechnicianTable.[Technician ID] = HolidayTable.[Technician ID]
WHERE (((JobTable.Date)>=[HolidayTable]![Holiday Start Date] 
  And (JobTable.Date)<=[HolidayTable]![Holiday Finish date]));

This displays a result if a tech is absent on any day that work is booked in on the job table.(subform is in datasheet view) on the footer of this subform is an unbound text box named (TotalBookableH ours) with its record source as" =Sum([Bookable Hours])" this works fine when a value is shown on the subform but when no tech is absent on the date selected the subform shows no values in the datasheet view and the (TotalBookableH ours) text box is blank.

On the Main Diary form is an unbound text box with the following as its record source =26-[TotalHoursBooke d]-[TechAbsences subform].[Form]![TotalBookableHo urs] which again works ok untill there is no value in the subformthen the dreaded ERROR shows in this box.I have tried with your suggestions but I may be putting them in the wrong place or just being plain stupid,hope this explains better what i am trying to achieve.

Many thanks Chris

[NeoPa]

Just to quickly post a slightly better version of your SQL.
This uses the Between ... And ... format to check the dates.

SELECT DISTINCT TechnicianTable.[Technician Name],
                TechnicianTable.[Bookable Hours],
                JobTable.Date
FROM JobTable, 
     TechnicianTable INNER JOIN HolidayTable
  ON TechnicianTable.[Technician ID] = HolidayTable.[Technician ID]
WHERE (JobTable.Date Between HolidayTable.[Holiday Start Date] 
                         And HolidayTable.[Holiday Finish date]);

[NeoPa]

Hi
Firstly thanks for your replies, I have tried all your suggestions but I am still struggling at the mo,it still shows ERROR in the text box on the form,I dont think I explained it very well either,so i'll try again. The TECHS ABSENT SUBFORM (top left on the picture ) is basing its record source on this query:

SELECT DISTINCT TechnicianTable.[Technician Name],
                TechnicianTable.[Bookable Hours],
                JobTable.Date
FROM JobTable, 
     TechnicianTable INNER JOIN HolidayTable
  ON TechnicianTable.[Technician ID] = HolidayTable.[Technician ID]
WHERE (((JobTable.Date)>=[HolidayTable]![Holiday Start Date] 
  And (JobTable.Date)<=[HolidayTable]![Holiday Finish date]));

This displays a result if a tech is absent on any day that work is booked in on the job table.(subform is in datasheet view) on the footer of this subform is an unbound text box named (TotalBookableH ours) with its record source as" =Sum([Bookable Hours])" this works fine when a value is shown on the subform but when no tech is absent on the date selected the subform shows no values in the datasheet view and the (TotalBookableH ours) text box is blank.

On the Main Diary form is an unbound text box with the following as its record source =26-[TotalHoursBooke d]-[TechAbsences subform].[Form]![TotalBookableHo urs] which again works ok untill there is no value in the subformthen the dreaded ERROR shows in this box.I have tried with your suggestions but I may be putting them in the wrong place or just being plain stupid,hope this explains better what i am trying to achieve.

Many thanks Chris

Try setting the RecordSource of [TotalBookableHo urs] to "=Nz(Sum([Bookable Hours]),0)".

[Noob]

Try setting the RecordSource of [TotalBookableHo urs] to "=Nz(Sum([Bookable Hours]),0)".

Tried this but still shows ERROR. Any idea's of another way to achieve the same result ?

Cheers Chris

[MMcCarthy]

Tried this but still shows ERROR. Any idea's of another way to achieve the same result ?

Cheers Chris

Check the name of the [Bookable Hours] textbox in the properties under the other tab. The name may not be what you think.

Secondly check the datatype and make sure it is actually a number.

Mary

[Noob]

I have checked both of these and they are all ok,Is there a completely different approach to achieve the same result possibly? I'm a little baffled now even if my query showed a row with zero in it when there were no techs absent ,that would allow the calculation to complete and hey presto my head would not be so battered..lol,

Thanks for your help so far

Chris

[MMcCarthy]

I have checked both of these and they are all ok,Is there a completely different approach to achieve the same result possibly? I'm a little baffled now even if my query showed a row with zero in it when there were no techs absent ,that would allow the calculation to complete and hey presto my head would not be so battered..lol,

Thanks for your help so far

Chris

Sorry Chris but I've just realised that your query has an INNER JOIN which will only return techs that have a record in the holiday table. If you change this to a LEFT JOIN so that all techs are returned does this help.

Mary

[Noob]

Sorry Mary it says "Join not supported" when i try to save the query, I wish I could think of a way round it but im well baffled.......l ol.

Thanks anyway

Chris

[MMcCarthy]

You're not the only one Chris ...

OK,

What is the relationship between JobTable and TechnicianTable ?

[NeoPa]

Sorry Mary it says "Join not supported" when i try to save the query, I wish I could think of a way round it but im well baffled.......l ol.

Thanks anyway

Chris

Does this code ( I assume you haven't changed it greatly from the original question) work for you, or does it give a "Join not supported" error?

SELECT DISTINCT T.[Technician Name],
                T.[Bookable Hours],
                J.Date
FROM TechnicianTable AS T LEFT JOIN HolidayTable AS H
  ON T.[Technician ID] = H.[Technician ID], JobTable AS J
WHERE (J.Date Between H.[Holiday Start Date] And H.[Holiday Finish date]);

[Noob]

Hi

It also gives join not supported Error.

There is no relationship between Job table and Technician table

Perhaps I may have to scrap the whole hours subtraction thing,It seems that because the text box value is based on a query result,the whole calculation falls down when the query returns no data which will be more often than not,except when a tech is absent on the day in question.

Cheers Chris