How to sort blank date field in a query

[sabrecoach]

Good morning everyone,

I'm presently working on a small data base that include vacations date of my employees

In a query that I builded, I've got their names, other fields and date choosen. I'd like to choose those date if they are higher or equal to the present date. It seem like there's a bug with blank date (some employees have 3 weeks, others 4 or 5 ). If it's blank in one field (let's say third week of vacation), it doesn't show data of those having only two weeks of vacation.

Can someone help me with this

Thanks

Patrice

 

[Norie]

Patrice

Can you explain your layout further?

And perhaps post the SQL for your current query. (Goto View>SQL view when in query design)

 

[sabrecoach]

My layout is like this

Empl.#-- Firstname -- Name -- Week1 -- week 2-- .....week 6

SELECT [Informations de base].Prénom, [Informations de base].Nom, [Informations de base].UB, [Informations de base].[Date d'embauche], [Vacances 2005].[Semaine 1], [Vacances 2005].[Semaine 2], [Vacances 2005].[Semaine 3], [Vacances 2005].[Semaine 4], [Vacances 2005].[Semaine 5], [Vacances 2005].[Semaine 6]
FROM [Informations de base] INNER JOIN [Vacances 2005] ON [Informations de base].UB = [Vacances 2005].UB
ORDER BY [Informations de base].[Date d'embauche];

I just want to make sure that i've got displyaed only those weeks wich are after the actual date.

 

[Norie]

How about a small example of data and result expected?

And also an example of when it doesn't work as wanted.

By the way you really shouldn't have 6 fields for the weeks - you should have one field for whatever data is in those fields and another indicating week 1-6.