Iif false enter any date

[Louis Grandjean]

Dear All,

I'm working with a tuberculosis database. If patients don't have tuberculosis then I want the term "not applicable" to appear in the date diagnosed variable. But if they do have tuberculosis I want to be able to enter any date into the date diagnosed box. I've been using the IIf function to get the not applicable to appear, but I cant find out how to enter any date. Could you help?

Best wishes Louis

PS. So I have a table with a binary tuberculosis variable (1 or 0), within the same table is another variable with the diagnostic date. I'm working on a form to enter data to this table. When I enter 0 in the tuberculosis column I want the term "not applicable" to automatically be entered in the table in the diagnostic date variable. Does that help explain better?

But If I enter 1 on the form to the tuberculosis variable I want to then be able to add whichever date they were diagnosed to the date diagnosed box on the form. Sorry not to have explained in sufficient detail.

[patjones]

What do you mean by "date diagnosed variable"?

Are you referring to the table that you store this information in, the form or report that you use to display the data, or an actual variable in VBA? These are all very different things.

Pat

[patjones]

That explains it a little better. It's always important to get the semantics correct before proceeding. To a programmer, the term "variable" has a specific meaning that doesn't directly relate to tables. That's why I asked for clarification.

The standard way to arrange a table like this is to have a date column where each record either has an entry for that column, or does not have an entry. The existence or non-existence of an entry makes the bit typed column redundant, and so the bit column would just be eliminated. You would not store anything in the date column other than a date, if applicable.

On the form and/or reports where you display the data, if you want to have it show "not applicable" when there is no date, you can certainly do so. It's a simple matter to query the table using that date column as criteria and finding out whether there's an entry or not (for instance, SELECT ... FROM ... WHERE date IS NULL).

In taking this approach, you simplify your table and simplify the queries that use the table.

[Louis Grandjean]

OK thanks that's helpful but it sort of raises another question. Part of the problem is to ensure that the data is entered correctly. So perhaps then rather than automatically enter not applicable, I should make a rule that if tuberculosis is 1 (i.e. the patient has tuberculosis) then the box must be filled in with a date and not left blank. How therefore should I do this? Really appreciate your help with this.

I suppose it's almost like saying the date diagnosed entry is a required field ONLY IF the corresponding tuberculosis entry is 1.

[patjones]

I guess this raises an interesting question: is it possible that someone can have tuberculosis - and you KNOW they do - but a diagnosis date is not available? If that is the case, it may make sense to keep your bit typed column.

On the data entry form, what you would need to do is prevent the user from saving the record if the check box is ticked off but no date is entered in the date field. You need to write some (relatively simple) VBA code in order to do that. For example, if you have a command button that users save records with:

Private Sub cmdSaveRecord()

If Me.chkHasTuberculosis <> 0 And (Me.txtDiagnosisDate = "" Or IsNull(Me.txtDiagnosisDate) Then
     MsgBox "Please enter a diagnosis date for this patient!", vbExclamation+vbOK
     Exit Sub
Else
    'Proceed to save record...
End If

End Sub

But again, going back to the point in my first paragraph...are you sure this is what you want to do?

[Louis Grandjean]

Having thought about it more I think it is important to enter "not applicable" or indeed "not known". The idea is that there are no unexplained blanks in the database as the distinction between not applicable and not known is important. What do you think?

[Louis Grandjean]

Thanks, yes in answer to your question it is possible that someone has tuberculosis but we don't know their diagnosis date. Makes things trickier. I like your idea of preventing a save with a message box if TB is 1 and there is no date. It would still reassure me though to have a "not applicable" (or equivalent code such as 11/11/1111 for not applicable) entered if TB is zero. This would create a database with no empty spaces (even though I appreciate this can be done at the review stage). Sorry to ask so many questions, thanks again for helping.

[patjones]

I cannot support that point of view because it violates a basic rule of database design...which is that a single column hold only one kind of data.

Remember, the user doesn't see the table. If you feel that it's important for the user to see "not applicable" - then it should be done on the form where they view the data.

Two problems can arise with storing "not applicable" or "not known" in the date column. One is that those records will then show up as having non-null diagnosis dates in any query that checks for such a thing. Another is that if you have the column properly typed as a date column, Access will not accept a non-date string in the column anyway.

I think that if you feel the need to explain null dates in the table itself, then it's fine to have another column that gives that explanation in the form of some code (1 = not known, 2 = not applicable, etc). I don't see that as the optimal solution, but it is certainly preferable to putting (or trying to put) a string in a date typed column.

[patjones]

Well, in my job we have legacy mainframe databases that store some "empty" or "null" dates as "9999-99-99". This creates a problem for people like me who go to query the database to find records where that column is legitimately not empty. That is the issue with storing something like "11/11/1111". Not to mention that, when such a record is displayed on the form, you need to add extra code to tell Access to display something other than "11/11/1111" in the text box.

I could easily help you do this the way that you're asking for it to be done...it is possible. But I think that it would be wrong for me to do so.

[Louis Grandjean]

So I totally agree with the need not to violate database design hence the storage of 11/11/1111. Overall I guess the question is do you prefer databases with blanks, but strict rules governing the blanks, or no blanks with rules govening what can be entered.

Just so you know the statistical analysis software that we're using can quickly sort out these issues once the data is in, but it's more an issue of ensuring correct data entry.

[Louis Grandjean]

If I'm correct you recommend I should leave blanks where it's appropriate to leave blanks?

In that case how can I actively leave a blank when TB is 0 but actively add any date when TB is 1? This way I wouldnt have to use 11/11/1111.

Thanks for being so helpful. I think if you can help me answer this question then I can do what I need without creating more difficulties for myself whilst also ensuring that the data is entered correctly.

PS. Just to try to explain what I'm going on about in incorrect code:

IIF([TB]=0, ISNULL[datediagnosed], OTHERWISE ENTER ANYDATE THATS NOT IN THE FUTURE)

Or would something like this work:

IIF([TB]=0, isnull[datediagnosis], isnotnull[datediagnosis])

[patjones]

The code doesn't do anything to further this discussion, and in fact the way it's written I cannot make sense of it.

I've explained to you 1) how to design the table column in accord with standard methodology and 2) how to force an entry in the date text box on the form (provided that you're certain it's what you want to do - you know the business rules of your environment better than I do). This is all I can do. I believe that if you implement it, you'll find that it works well.

[Louis Grandjean]

Sorry I'm a beginner to coding in access so that's the only way I can try to explain my dilemma.

Apologies if I've misunderstood. I want to automatically enter information (null information or actual date information as appropriate) into a table based on the data in another variable in the same table. I still don't see how you've explained this, sorry if I'm being slow and thanks for helping either way.

I agree with everything you've said above (not to enter a string to a date variable etc), but accepting this how can I automatically enter null data or date information based on another variable in the same table.

[patjones]

I think we're having a small problem with language/semantics here. You cannot "automatica lly enter null data or date information based on another variable in the same table". Tables don't have variables. They have columns and rows.

All you need is to have a check box and text box on your form that corresponds to the columns in the table, and save the values of those controls to the table.

You can enhance this procedure in the following way. If the check box is NOT checked, it makes no sense to enter a diagnosis date, right? So what we usually do in a situation like that is to disable the text box so that the user cannot enter anything in it. Likewise, if the check box IS checked, we want the text box to be enabled so that the user CAN enter a date in it.

If you need to go a step further and force an entry in the text box when the check box is checked, then you would use code very similar to what I wrote above.

This procedure ensures that the date column remains null for people who do not have tuberculosis. If this is what you're looking for then I can help you with the code.

[Louis Grandjean]

Yes, this is exactly what I'm looking for, the only thing I don't like the sound of is the check box (as I'd like to use a binary column/variable so that I can export the whole table to excel or whichever program is required). So, based on the value of an entry in a binary column/variable, I want to be able to, or not be able to enter data that is relevant.