BLANKS STILL DISPLAYING

[Angelique]

I have a query that should display for a report two trainers but I cannot remove the blanks for the second trainer in the result.

The recordsource query is:
SELECT [Training Details].ID, [Training Details].[Date of Training], [Training Details].ClientName, [Training Details].Address, [Training Details].[Postal Address], [Training Details].ContactName, [Training Details].[Training Venue], [Training Details].TrainerAllocated, [Training Details].SecTrainerAllocated, [Training Details].NoOfTrainers, [Training Details].CostCentre, [Training Details].StationName, [Training Details].StartTime1st, [Training Details].FinishTime1st, [Training Details].StartTime2nd, [Training Details].EndTime2nd, [Training Details].StartTime3rd, [Training Details].EndTime3rd, [Training Details].StartTime4th, [Training Details].EndTime4th, ((CDate(NZ([FinishTime1st],0))-CDate(NZ([StartTime1st],0)))*24) AS Elapsed1, ((CDate(NZ([EndTime2nd],0))-CDate(NZ([StartTime2nd],0)))*24) AS Elapsed2, ((CDate(NZ([EndTime3rd],0))-CDate((NZ([StartTime3rd],0))))*24) AS Elapsed3, ((CDate(NZ([EndTime4th],0))-CDate((NZ([StartTime4th],0))))*24) AS Elapsed4, CDate(NZ([StartTime1st],0)-(NZ([EndTime2nd],0))) AS TotalTime, CDate(NZ([StartTime1st],0)-(NZ([EndTime2nd],0))) AS TotalTime2, CDate(NZ([StartTime1st],0)-(NZ([EndTime3rd],0))) AS TotalTime3, CDate(NZ([StartTime1st],0)-(NZ([EndTime4th],0))) AS TotalTime4
FROM [Training Details]
WHERE ((([Training Details].[Date of Training])=[Enter Date of Training]) AND (([Training Details].TrainerAllocated) Is Not Null) AND (([Training Details].NoOfTrainers)>=2));

I tried [Training Details].SecTrainerAllocated Is Not Null but still got blanks.

By using the No of Trainers being more than 2 means that the SecTrainerAllocated shouldn't be blank.

Thanks in advance


Angelique

 

[tlbroadbent]

Have you tried running a simple query such as

Select * From [Training Details]
Where NoOfTrainers>=2

to verify the data integrity? Assuming SecTrainerAllocated is the Second Trainer field you could add the criteria

AND SecTrainerAllocated IS NOT NULL

to the query.

Another suggestion is to recognize that a column may appear blank when it contains a NULL or an empty string. You may need to test for the empty string, also.

AND SecTrainerAllocated IS NOT NULL
AND SecTrainerAllocated <> &quot;&quot; Terry

People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker

 

[Angelique]

Hi Terry,

It's your last line of code is what I think I was looking for! I will give it a go!

 

[Angelique]

Terry! Spot on - worked liked a charm.


Angelique