Update query-If criteria true,value true else false in same query (1 Viewer)

merc_slk

New member
Joined
Nov 24, 2009
Messages
6
In my dbase I need to compare 4 pairs of values. If all pairs comply to my criteria in the update query the value "blnFilledIn" must be true else false. Let's say everything is according criteria, "blnFilledIn" is set true. Meanwhile a value changes and 1 or more pairs do not comply the criteria. I let the update query run but nothing changes. "blnFilledIn" fields don't change although some of the pairs don't meet up, "blnfilledin" should be false. In the example If you run the update query "blnfilledin" should be set true. Now change the next fields in the record : line 28 IdxTO = 20091130060101101 and line 31 bZone : true. Run Query again,nothing changes although some criteria are false. Clear all bFI, set all to false, run query again and you'll see line 28 and 31 are false. Now I want to do it in one update query. Is it possible to set or reset values in one run.
 

Attachments

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,828
I haven't looked at you sample but I image you could use the IIF function.
http://office.microsoft.com/en-us/access/HA012288531033.aspx

However it sounds like what you are trying to do might be against database normalization conventions. If the field blnFilledIn is derived from other information in the database then its should not be stored but the condition tested whenever you want to determine the status of records.
 

merc_slk

New member
Joined
Nov 24, 2009
Messages
6
The blnFilledIn has to be stored with the local record. I use this value to validate and check the records in the recordset. If one requirement is missing in the records, the person can only save a draft version (temp table) else he can save it on server. Maybe there's an easier method but can't figure it out.
 

Galaxiom

Super Moderator
Staff member
Joined
Jan 20, 2009
Messages
11,828
The validation should be calculated from the data in a query. Storing the Valid flag isn't really necessary.

Valid: IIF(IsNull(Field1) Or IsNull(Field2) Or IsNull(Field3),False,True)

Why save the draft in a different table? Moving records between tables is just making things complicated for no gain. They could be stored in the server table with the valid records and simply flagged as draft.

The draft flag can be set using the above derived field.
UPDATE tablename
SET tablename.draft = True
WHERE Valid = False


The form displaying the records can have a checkbox to either show or supress draft records.

In the Record Source query include:
WHERE tablename.draft = False OR tablename.draft = Forms!formname!draftswitch

You can assign the username to the draft record so they "belong" to the user entering them.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom