Keep Update Query from Deleting Null Fields

[ChuckRobert]

I have a “Schedule Updates” subform to allow users to schedule future changes to a record on a specific date.

These future changes populate tbl_ScheduledUpdates

Simple Query qry_ScheduledUpdatesBase finds records <=Date() that now need to be changed.

Update Query qry_ScheduledUpdates transfers these updates to tbl_Personnel. Then a Delete Query clears that row from tbl_ScheduledUpdates

The process works great if all fields were completed on the “Schedule Updates” subform, but if any fields are left blank, that field in the tbl_Personnel record is deleted.

My goal is to make this work while only requiring the user to enter the effective date and the given field on the “Schedule Updates” subform set to change.

I have tried to work with the Update Query, with some variation of the following to keep a Null field on Update Query qry_ScheduledUpdates from deleting that field in the tbl_Personnel record, by changing the “Update To:” line to the following, but this has not worked.

IIf(IsNull([qry_ScheduledUpdatesBase].[SchUpdateShift]),[tbl_Personnel].[Shift],[qry_ScheduledUpdatesBase].[SchUpdateShift])

I’m losing faith that an Update Query can accommodate this task as a group. Does anyone have a suggestion to handle this? Perhaps a VBA loop that references qry_ScheduledUpdatesBase and only updates fields which are not Null?

 

[SydneyGeek]

Try adding Not Null to the criteria row for the field in question. If you ONLY want to delete records where there are no null entries, put Not Null under the criteria for all fields.
Note: check this as a SELECT query, then change it to a DELETE.

Denis

 

[ChuckRobert]

Denis,<o></o>
Thanks for your response! However, I may be misunderstanding your suggestion, or perhaps I didn’t explain it well enough. The challenge of this process isn’t in the Delete process, but in the Update query. To clarify…<o></o>
<o></o>
tbl_ScheduledUpdates temporarily holds only data that will change, the Individual ID, and the effective date, similar to this:<o></o>
· Effective Date (10/28/13) - Required<o></o>
· Individual ID (A1234) - Required<o></o>
· Rank (Will be Null if not changing) – Optional field<o></o>
· Shift (2)…This field changing from 1 to 2 on 10/28/13 - Optional field<o></o>
· Special Status (Will be Null if not changing) - Optional field<o></o>
· Days Off (Fri/Sat)…This field changing from Mon/Tue to Fri/Sat on 10/28/13 - Optional field<o></o>
· Etc…<o></o>
<o></o>
· The next record in tbl_ScheduledUpdates may just have data in Effective Date, Individual ID, and Rank….<o></o>
<o></o>
Putting Is Not Null in every field criteria on Simple Query qry_ScheduledUpdatesBase to find records to change on a given date, just keeps the query from pulling up records from tbl_ScheduledUpdates, as most have several Null fields. <o></o>
<o></o>
Likewise, putting Is Not Null in the Update Query qry_ScheduledUpdates criteria, will keep it from updating records unless none of the fields contain Nulls. <o></o>
<o></o>
The goal is to transfer only the updates from tbl_ScheduledUpdates as filtered by Simple Query qry_ScheduledUpdatesBase on <=Date(), to tbl_Personnel. The Delete query clears updated records from tbl_ScheduledUpdates just fine. Perhaps there is another way?<o></o>

 

[SydneyGeek]

Look at the descriptions you wrote down. Based on that, I would put Not Null for Rank and Special Status, and any other fields that meet that same condition, in the Update query.

Denis

 

[ChuckRobert]

<o>Denis,<o></o>
Thanks again for your response, and helping so many people on this forum! <o></o>
<o> </o>
In my testing; putting Is Not Null (typing Not Null, is automatically converted to Is Not Null) in every field criteria of the Update Query limits the query to only pulling records in which these conditions are met. In this case, only updating records in which all fields of tbl_ScheduledUpdates have data in them. If any field is Null, the update query no longer pulls up that record to update the main table. Since many fields of tbl_ScheduledUpdates will not have a value in them, I have not been able to make this work. <o></o>
<o> </o>
Removing the Is Not Null criteria from the update query allows all updates to process, but empty fields in tbl_ScheduledUpdates overwrite current/correct values in the main table to Null. <o></o>
<o> </o>
Perhaps there is another way, or I am missing something?<o></o>
</o>

 

[xenou]

What's your update query?

 

[ChuckRobert]

Denis,<o></o>
I’m not sure exactly what you are asking. As an overview…<o></o>
<o> </o>
tbl_Personnel is the master table<o></o>
<o> </o>
tbl_ScheduledUpdates holds items that will changed on a given date (Effective Date, and Individual ID will have data. 11 other attributes may, or may not have data. These include items like Rank, Shift, Assignment, Special Status….<o></o>
<o> </o>
Simple Query qry_ScheduledUpdatesBase finds records <=Date() in tbl_ScheduledUpdates. This list is feed to the Update Query.<o></o>
<o> </o>
Update Query qry_ScheduledUpdates references Simple Query qry_ScheduledUpdatesBase then transfers these entries to tbl_Personnel. <o></o>
<o> </o>
Once this is complete, a Delete Query clears those rows from tbl_ScheduledUpdates<o></o>
<o> </o>
The Update Query references tbl_Personnel and qry_ScheduledUpdatesBase. These are linked by the Individual ID <o></o>
The sample query structure is like this:<o></o>
Field : Shift<o></o>
Table: tbl_Personnel<o></o>
Update To: [qry_ScheduledUpdatesBase].[SchUpdateShift]<o></o>
Criteria: if left blank, changes and Null fields are transferred to tbl_Personnel. Since Is Not Null will only return records where the value is not missing in the field, assuming a given record contains at least 1 Null, putting Is Not Null is all field criteria will not pull any records up to update tbl_Personnel. <o></o>
<o> </o>
The SQL view of this query looks like this:<o></o>
UPDATE tbl_Personnel INNER JOIN qry_ScheduledUpdatesBase ON tbl_Personnel.Serial = qry_ScheduledUpdatesBase.SerialReference SET tbl_Personnel.Serial = [qry_ScheduledUpdatesBase].[SerialReference], tbl_Personnel.Rank = [qry_ScheduledUpdatesBase].[SchUpdateRank], tbl_Personnel.Shift = [qry_ScheduledUpdatesBase].[SchUpdateShift], tbl_Personnel.Assignment = [qry_ScheduledUpdatesBase].[SchUpdateAssignment], tbl_Personnel.SpecialStatus = [qry_ScheduledUpdatesBase].[SchUpdateSpecialStatus], tbl_Personnel.StatusStartDate = [qry_ScheduledUpdatesBase].[SchUpdateStatusStart], tbl_Personnel.StatusEndDate = [qry_ScheduledUpdatesBase].[SchUpdateStatusEnd], tbl_Personnel.StatusComments = [qry_ScheduledUpdatesBase].[SchUpdateComments], tbl_Personnel.RDOs = [qry_ScheduledUpdatesBase].[SchUpdateRDOs], tbl_Personnel.RDO1 = [qry_ScheduledUpdatesBase].[SchUpdateRDO1], tbl_Personnel.RDO2 = [qry_ScheduledUpdatesBase].[SchUpdateRDO2], tbl_Personnel.RDO3 = [qry_ScheduledUpdatesBase].[SchUpdateRDO3];<o></o>

 

[xenou]

From your earlier post:

I have tried to work with the Update Query, with some variation of the following to keep a Null field on Update Query qry_ScheduledUpdates from deleting that field in the tbl_Personnel record, by changing the “Update To:” line to the following, but this has not worked.

IIf(IsNull([qry_ScheduledUpdatesBase].[SchUpdateShift]),[tbl_Personnel].[Shift],[qry_ScheduledUpdatesBase].[SchUpdateShift])

There are no IIFs in your update query. Are you really using the IIF() clause or not?


Edit: as a general rule, you aren't separating the data properly, it seems to me. you should be only updating the data relative to shift changes. There appears to be much to much data here. Only hours and days should be changing - and those are completely dependent on the shifts. Other aspects of employees that don't change from shift to shift do not belong in this query.

 

[ChuckRobert]

Denis,
Victory! Thanks! I know that I tried that formula previously, but suspect I may have mis-spelled something on the Update To line. Thanks for your guidance in working this out, as I started to doubt this was possible.

As related to separating the data, I can't see a problem in this case as all fields are attributes to define an employee's availablity in their assigned position, as used in a Workplace Roster report. Thanks again!