Set a flag query based on false detection (1 Viewer)

chizzy42

Registered User.
Local time
Today, 16:43
Joined
Sep 28, 2014
Messages
115
Hi , ive put together a database that records when a user has viewed a safety message for the day and also the ability to update the database if they miss a day by using an update form. What ive been trying to do is have the user notified on logging in that they have missed a day on a certain week.
I don't need to alert the user to a particular day just to the fact a day is missed. What ive been trying to do is run a query that checks the username against a table containing the username and 5 yes/no fields one for each day and a weekno, so for example if they missed tuesday on week 32 it would set a flag and the user would be alerted.
SELECT tbl_login.UserName, tbl_login.mon, IIf([mon]=0," monfalse,","") AS Expr1, tbl_login.tue, IIf([tue]=0," tuefalse,","") AS Expr2, tbl_login.wed, IIf([wed]=0," wedfalse,","") AS Expr3, tbl_login.thu, IIf([thu]=0," thufalse,","") AS Expr4, tbl_login.fri, IIf([fri]=0," frifalse,","") AS Expr5, tbl_login.weekno
FROM tbl_login
WHERE (((tbl_login.UserName)="bs"));

As above this creates a field if a field is empty, ive tried to use count to count any values created in the expr fields, all i care about is being able to run the query and somehow get a flag a day has been missed. i cant seem to think how to get a false detected using COUNT or with an OR function if any expr field has data

Could someone please point in the right direction with some suggestions if this is makes any sense....

thanks

ian
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:43
Joined
Oct 29, 2018
Messages
21,358
Hi ian. Have you tried using the DCount() function?
 

chizzy42

Registered User.
Local time
Today, 16:43
Joined
Sep 28, 2014
Messages
115
hi dbguy thanks for the reply, do you mean use Dcount on a form in a text box and count the values in a column?

IntX = DCount(“[expr1] & [expr2]& [expr3]& [expr4]& [expr5]”, “tbl_logon”)

ill give this a go ...ithink it ignores nulls and should only count a field with data
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 12:43
Joined
Jan 23, 2006
Messages
15,364
If your objective is to have employees/users see the daily safety message, then why not make that form/message part of the logon? That is, make it a byproduct of logging into the system; not a separate process. Perhaps a button to move past the message would emphasize/require the user to "read the message" as best you can.
Seems your code is to identify and possibly punish employee/user for not viewed the message.

Just a thought for consideration.
 

chizzy42

Registered User.
Local time
Today, 16:43
Joined
Sep 28, 2014
Messages
115
hi jdraw, the idea is that they log in each day and a form with a safety message pops up and after they read it click a button and it saves it. if i can get the table checked each time they logon then a message will appear to say that a day has been missed and to update . When the update form opens it has the week number and days missed on it. ...hope im making sense.....
 

Users who are viewing this thread

Top Bottom