Populating a Null field in a form from a different column in the same table

[BettyWho]

HI me again! You've been extremely helpful in trying to sort out my quirky queries I thought I'd try again.

I've been trying to populate a field in a form with information, but only if the field is blank.

What I am trying to do is this.

I have a continuous form displaying all of our principals, however when our principals are on leave I want to populate the field with the "acting principal". I have set it up so the field grabs the acting principal first but it is blank for several schools. For these schools where there there is no "acting principal" I want it to grab the substantive principal automatically and populate the field.

I have tried Is Null, Is Not Null, NZ, IF, Where and Then statements but to no avail.

I have _no_ idea what I am doing wrong but I have been staring at this for a week and I'm hoping someone can help me.

Thanks in advance!

 

[namliam]

What do your tables look like?
What queries are your running?
etc.... more details are needed to help you along

 

[BettyWho]

Okay dokey so the table structure looks like this

School Name | Principal | Acting Principal | From Date | To Date| Reason |

I originally had the table split into 2 tables School | Principal and then School | Acting Principal | From | To but thought this was a bit like reinventing the wheel so combined them instead.

The query I run pulls basically all the information as listed above. Should I limit what I'm querying? or is there another way of doing things? I'm all ears for a solution that works!

Thanks!!

 

[namliam]

I wouldnt use this structure, simply because a Principal can change while the Acting Principal might stay the same... or reverse.

How in this structure would you identify if a Principal is on leave?

 

[BettyWho]

The Acting principal is the one that changes and it is only populated when there is cause to populate it. And once an acting principal is populated the dates are also entered as well as the reason for the [principal] taking leave.

 

[namliam]

Wouldnt there in general be one person/function of (Acting) Principal?

Anyways.... Something like using an
Iif(Date()>=FromDate and date()<=ToDate, Acting, Principle)

 

[liddlem]

Hi BettyWho
I am thinking along another line.
I assume that you know who the acting principal will be? What table does that informatikn reside in?
How about a few update queries that run in the forms 'on load' event?
Qry1 . Sets the acting value to null if the current date > Date ()
Qry2. Update the blank acting from your acting list where current date = from date.

 

[BettyWho]

Both of those would be great except the form shows all schools not just one school and the dates continuously change and aren't the same for each school. I was hoping for a IsNull or IIF type deal but to date have had _no_ luck writing anything.. I basically want it to check the "acting principal" field first and if that is "blank" select the Principal field instead.. Does that make sense?

<snip> Ok I tried the expression

IIf(Date()>=StartDate and date()<=EndDate, Acting, Principle)

but it returned a compile error Expected: =

What am I doing wrong? :S I feel so very confused!!!!

 

[Chrafangau]

Try =IIf(IsNull([Acting Principle]), [Principle], [ActingPrinciple])

 

[BettyWho]

It doesn't work It Shows #Name? on the form when you view it in a form.. Seriously this is doing my head in now! I thought I had a fairly good knowledge of Access and VB but I realise I am but an amateur!

 

[namliam]

School Name | Principal | Acting Principal | From Date | To Date| Reason |
Did you update my Iif to match your columns?
IIf(Date()>=StartDate and date()<=EndDate, Acting, Principle)

IIf(Date()>=[From Date] and date()<=[To Date], [Acting Principal], [Principal])

Might have to change , to ; depending on your regional settings....