Indicate two consecutive same values

[ppataki]

Dear All,

I have a table that has a field that may contain null values

In some cases it may happen that there are 2 null values one after another
(pls see screenshot attached)

How could I somehow indicate when there are 2 null values one after another? (eg put an X in a new field or something like that)

I would need this in order to see who had two consecutive days off in a month

Many thanks in advance!

 

[namliam]

Do a 'self join' to your own table to the previous day and see if both are null

 

[ppataki]

Could you please be more specific? What fields shall I join and how can I do that for the previous day?
Many thanks in advance

 

[namliam]

1) Make a query adding your table to calculate the previous day
PreviousDay: [YourDate] -1

2) Make a new query, add the query from 1 and your table
Join the two on PreviousDay to [yourdate] and you will have the two values on the same line.

Now its a simple IIF to detect the double null value.

 

[ppataki]

Thank you!
I managed to accomplish this in the meantime

However it turned out that instead of this it would be better to see how many hours passed between two dates

I have successfully created queries to display the dates and also the differences (please see screenshot)
But unfortunately I could not manage one thing: in case there is one null, the expression does not work, it should lookup the value in the next record
How can I do that? (I tried dlookup for the date field but it gave me an error message: Syntax error in number in query expression)

Could you please advise?
Many thanks

 

[namliam]

Lookup the NZ function in the help, also you wil l ahve to join back maybe the table a few times to find a filled in value.

 

[ppataki]

I tried the following expression in a new query:

Endof1: nz([endof];DLookUp("[endof]";"[query2]";"[query1.dateof]=" & [query1.dateof]+1))

but got the following error message:
Syntax error in number in query expression query1.date=2010.09.02

Shouldn't dlookup work to get the next record's value?

Any hints please?
Many thanks

 

[namliam]

If you are working with dates in DLookups or variables they need to be enclosed by ##
Like so
Endof1: nz([endof];DLookUp("[endof]";"[query2]";"[query1.dateof]=#" & [query1.dateof]+1 & "#"))

But... you shouldnt use DLookup, use a seperate query to look 2 days back and join that to your query...
Then a third and forth how ever many you need.