By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,963 Members | 1,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,963 IT Pros & Developers. It's quick & easy.

Iif false enter any date

P: 9
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.
Oct 31 '11 #1
Share this Question
Share on Google+
18 Replies


patjones
Expert 100+
P: 931
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
Oct 31 '11 #2

patjones
Expert 100+
P: 931
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.
Oct 31 '11 #3

P: 9
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.
Oct 31 '11 #4

patjones
Expert 100+
P: 931
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSaveRecord()
  2.  
  3. If Me.chkHasTuberculosis <> 0 And (Me.txtDiagnosisDate = "" Or IsNull(Me.txtDiagnosisDate) Then
  4.      MsgBox "Please enter a diagnosis date for this patient!", vbExclamation+vbOK
  5.      Exit Sub
  6. Else
  7.     'Proceed to save record...
  8. End If
  9.  
  10. End Sub

But again, going back to the point in my first paragraph...are you sure this is what you want to do?
Oct 31 '11 #5

P: 9
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?
Oct 31 '11 #6

P: 9
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.
Oct 31 '11 #7

patjones
Expert 100+
P: 931
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.
Oct 31 '11 #8

patjones
Expert 100+
P: 931
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.
Oct 31 '11 #9

P: 9
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.
Oct 31 '11 #10

P: 9
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:

Expand|Select|Wrap|Line Numbers
  1. IIF([TB]=0, ISNULL[datediagnosed], OTHERWISE ENTER ANYDATE THATS NOT IN THE FUTURE)
Or would something like this work:

Expand|Select|Wrap|Line Numbers
  1. IIF([TB]=0, isnull[datediagnosis], isnotnull[datediagnosis])
Oct 31 '11 #11

patjones
Expert 100+
P: 931
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.
Oct 31 '11 #12

P: 9
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.
Oct 31 '11 #13

patjones
Expert 100+
P: 931
I think we're having a small problem with language/semantics here. You cannot "automatically 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.
Oct 31 '11 #14

P: 9
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.
Oct 31 '11 #15

patjones
Expert 100+
P: 931
I don't understand...at all. If you don't have a check box (or SOME control to input the information)...how are you planning on putting the binary information in the table?
Oct 31 '11 #16

NeoPa
Expert Mod 15k+
P: 31,709
Louis, The CheckBox control on a form is ideally suited to managing a binary field in your record (Hence Pat's confusion at your confusion).

Technically, Access deals with records and fields within a table. These can be considered as similar to rows and columns in a spreadsheet. I hope things are starting to clarify for you. You've been getting excellent advice from Pat, but I can tell that your inexperience inhibits your ability to comprehend it all at this stage. I recommend you continue to put your trust in his advice (Post #14 outlines the logic pretty well) and accept the offer of code help on this.

If you find it doesn't handle your problem then that would be a good time to worry about it. I suspect once you've tried it the solution will start to make more sense to you though. It's always easier to understand when you can see something in action.
Nov 1 '11 #17

P: 9
I'm entering the data with a validation rule that states a 0 or 1 must be entered, that's why Im not using a check box.

Pat did try very hard, and got me thinking along the right lines. Thanks. Altering the text box visibility in a conditional way was the key to sorting this. A quick google search of conditional disabling of text box helped to sort it.

Thanks again for your help.
Nov 1 '11 #18

NeoPa
Expert Mod 15k+
P: 31,709
Louis:
I'm entering the data with a validation rule that states a 0 or 1 must be entered, that's why Im not using a check box.
That makes sense Louis, but you might want to consider that most people expect boolean type values to be managed by a CheckBox or a ToggleButton type control. What you have can certainly work, but is it as easy to work with? I would consider it from that point of view. Ultimately it's your call of course.

Congratulations on working out your solution anyway :-)
Nov 2 '11 #19

Post your reply

Sign in to post your reply or Sign up for a free account.