Date query

[Radhika]

I am trying to create a query with the following fields of interest: ID#,
Visit Date, VP Removal Date and IP (drop down box).
For each ID#, I want the VP Removal date to be blank for previous visit if
IP is not null. For example, I want the query to look something like this is
datasheet view:

ID# Visit Date VP Removal Date IP
111111 04/05/2007 04/30/2007
111111 06/02/2007
111111 09/06/2007 09/20/2007 2
999999 03/20/2008 04/06/2008
999999 05/07/2008 06/15/2008
999999 06/08/2008 06/19/2008

I tried to insert an IIf statement in the VP Removal Date field. However, I
am not sure about how to end it. IIf ([IP] is not null, [VP Removal
Date]=....)

How can I end it? Is there another way of getting the same result.
Thankyou,
Radhika

 

[KARL DEWEY]

I want the VP Removal date to be blank for previous visit
What is meant by 'previous visit'?

 

[Beetle]

That's not going to work because Access is going to assume that the
VP removal date you are referring to is in the same record as the IP
you are referring to. You would need to use, for example, a domain
function to retrieve the value from the previous record.

However, having said that, it's still unclear what exactly you are trying to
do.
Are you saying that there was a value in the [VP Removal Date] field in the
previous record, but if a user selects an IP in the current record you want
the [VP Removal Date] value (in the previous record) to be deleted?

 

[Radhika]

By previous visit I mean the Visit Date before the one IP is selected for.

What is meant by 'previous visit'?

--
KARL DEWEY
Build a little - Test a little

I am trying to create a query with the following fields of interest: ID#,
Visit Date, VP Removal Date and IP (drop down box).
For each ID#, I want the VP Removal date to be blank for previous visit if
IP is not null. For example, I want the query to look something like this is
datasheet view:

ID# Visit Date VP Removal Date IP
111111 04/05/2007 04/30/2007
111111 06/02/2007
111111 09/06/2007 09/20/2007 2
999999 03/20/2008 04/06/2008
999999 05/07/2008 06/15/2008
999999 06/08/2008 06/19/2008

I tried to insert an IIf statement in the VP Removal Date field. However, I
am not sure about how to end it. IIf ([IP] is not null, [VP Removal
Date]=....)

How can I end it? Is there another way of getting the same result.
Thankyou,
Radhika

 

[Radhika]

The query is an autofill query, so yes, i do want the VP Removal Date to be
deleted, or not filled in at all..

That's not going to work because Access is going to assume that the
VP removal date you are referring to is in the same record as the IP
you are referring to. You would need to use, for example, a domain
function to retrieve the value from the previous record.

However, having said that, it's still unclear what exactly you are trying to
do.
Are you saying that there was a value in the [VP Removal Date] field in the
previous record, but if a user selects an IP in the current record you want
the [VP Removal Date] value (in the previous record) to be deleted?
--
_________

Sean Bailey

I am trying to create a query with the following fields of interest: ID#,
Visit Date, VP Removal Date and IP (drop down box).
For each ID#, I want the VP Removal date to be blank for previous visit if
IP is not null. For example, I want the query to look something like this is
datasheet view:

ID# Visit Date VP Removal Date IP
111111 04/05/2007 04/30/2007
111111 06/02/2007
111111 09/06/2007 09/20/2007 2
999999 03/20/2008 04/06/2008
999999 05/07/2008 06/15/2008
999999 06/08/2008 06/19/2008

I tried to insert an IIf statement in the VP Removal Date field. However, I
am not sure about how to end it. IIf ([IP] is not null, [VP Removal
Date]=....)

How can I end it? Is there another way of getting the same result.
Thankyou,
Radhika