Default value in a number/date field 3

[SherryLynn]

Hello,

Is it possible to have a text word as a default in a date field? I have a table that records a date of birth in this format d/m/y (3-Aug-04). I would like to have default text that says "Unknown" in this box until a date of birth is known. Is this possible and how will that be recorded in the table?

Thanks very much.

Sherry

 

[scriverb]

No you cannot do it that way. If the control is a date field control then it will only accept a date field format.

But, with a little slight of hand you can fool it into displaying the word UNKNOWN if you wish. Try something like this.

Create an Unbound textbox control and name it "txtUnknown". Set the Control Source Property to the following:

="UNKNOWN"

Now delete the label that is associated with this control. Make this control exactly the same size(both height and width) as the date control. Set its visible property = Yes; Tab Stop property = No; Locked property = Yes. Now place this control directly on top of your date field control.

Now what we want to do is manipulate the visibile property of this control depending upon if this is a new record with no value yet entered, an old record with no date data entered, or an old record with date data pressent. This is done in the FORMS OnCurrent Event Procedure:

If IsNull(Me![[i][red]datefieldcontrol[/red][/i]]) then
   Me.[txtUnknown].visible = true
else
   Me.[txtUnknown].visible = false
End If

Put the following VBA code in the GotFocus event procedure of the control txtUnknown:

Me![[i][red]datefieldcontrol[/red][/i]].setfocus

Lastly, put the following VBA code in the AfterUpdate event procedure of the Date control:

If IsNull(Me![[i][red]datefieldcontrol[/red][/i]]) then
   Me.[txtUnknown].visible = true
else
   Me.[txtUnknown].visible = false
End If

This should do the trick. Let me know how this works in your project.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[willir]

Hmmm
One way to handle this is to use an unbound text box, hide the date field. Then for the On Current event and After Update of the unbound text field use event procedures. You can toggle between the date and unbound text field, or transfer the contents of the unbound text field to the date field.

Assumptions:

Name of Date field: MyDateFld
Name of Unbound text field: MyUnboundTxtFld

Example where data is transferred from text box to

On Current Event

Me.MyDateFld.Visible = False 'not neccessary here, used as an example

If IsDate(Me.MyDateFld) Then
    Me.UnboundTxtFld = Format(Me.MyDateFld, "dd-mmm-yy")
Else
    Me.UnboundTxtFld = "Unknown"
End If

After Update event for Me.UnboundTxtFld 

If IsDate(Me.UnboundTxtFld) Then
    Me.MyDateFld = Me.UnboundTxtFld
End If

...But validation becomes a little more awkward. For example, Access validates and formats date fields per your specifications. You loose this ability by capturing the data in an unbound text box.

You can address this by changing the Format of the unbound text field...

Me.UnboundTxtFld.Format = "dd-mmm-yy"

My preference is to just accept null values if the date is missing.

But I can understand why you want to present a meaningful message to the end user.

Richard

 

[SherryLynn]

Hello,

Thanks for the replies. Maybe I need to explain my problem further and hopefully you guys can help me with the solution. Ok, I work for a child protection agency and the database I run tracks famlies, their children and incidents of abuse. The alleged offenders are connected also tracked in the database and each "offense" is recorded. Now I have to track statistics on these incidents. One of the things I have to track is the age of the offender. Often we do not have a date of birth for them and so the field is left blank. Maybe "unknown" isn't what I need (that's one of the selections on the current form when a DOB is unknown). Maybe I need a way to count how many of those offender's DOB's are left blank? The statistics report has a beginning and end date and an area for parameters. What should I do here? Again, I am very inexperienced with VBA and so needs lots of direction. Any help is appreciated.

Thanks, Sherry

 

[PHV]

An unknown field should be Null.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

PHV is correct - leave them blank. And then you can count "nulls".

However, you have another statistic that may be useful. You have the approximate date of incident. Do you have an approximate age too? If so, you can capture this info in addition to the DOB. DOB is best, but with the approximate age at the time of the incident gives you another bit of info to compile your profiles.

Richard

 

[SherryLynn]

Hello,

Thanks for the replies. Richard, how do I count the nulls? Currently, I have a table which I have pasted below with the different age groups. I haven't added an "unknown" row as I wasn't sure how/what to add exactly to count the ones that are unknown.

GroupID GroupName Minimum Maximum
1 0 to 11 0 11
2 12 to 17 12 17
3 18 to 30 18 30
4 31 to 45 31 45
5 46 to 60 46 60
6 Over 61 61 150

Next I have a query that runs (the SQL is below) to count how many fall into each age bracket. Occasionally I do know that the offender is say approx. 30 years old, but no definite date of birth. Normally these would fall into the "unknown" category as well.

SELECT [tblAgeGroup-Offender].GroupID, [tblAgeGroup-Offender].GroupName, Count(queMonthlyTally.[Offender ID]) AS [CountOfOffender ID]
FROM queMonthlyTally, [tblAgeGroup-Offender]
WHERE (((queMonthlyTally.AgeOYears) Between [Minimum] And [Maximum]))
GROUP BY [tblAgeGroup-Offender].GroupID, [tblAgeGroup-Offender].GroupName
ORDER BY [tblAgeGroup-Offender].GroupID;

I have a sub report on my form for the section that records the dates of birth and pulls the results from the query above. Do you have a better suggestion, or a way to add the "null" values? Thanks very much.

Sherry

 

[willir]

[tt]
SELECT ....
WHERE [YourField] = ISNULL([YourField])
[/tt]

...or variation thereof.

 

[PHV]

Why not simply an Union query ?
SELECT A.GroupID, A.GroupName, Count(M.[Offender ID]) AS [CountOfOffender ID]
FROM queMonthlyTally M, tblAgeGroup-Offender A
WHERE M.AgeOYears Between [Minimum] And [Maximum]
GROUP BY A.GroupID, A.GroupName
UNION SELECT 0, "Unknown", Count(*)
FROM tblAgeGroup-Offender N
WHERE N.DOB Is Null
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SherryLynn]

Hello,

Willir - where would I put that select statement? create a query?

PHV - I tried what you suggested, which seems like it might work well for me. But I'm having a little trouble with getting this all written out exactly how it should be. I am not well versed with VBA. The references you make above to "M", "A" and "N" are not clear to me. A I am assuming refers to my "tblAgeGroup-Offender". M I think you are using to refer to my query queMonthlyTally. I'm not sure what you are using the "N" to refer to.

Thanks to all of you for your patience and assistance.
Sherry

 

[willir]

Hi Sherry

Where would I put that select statement?

When do you want to find out data is missing? Why do you want to know if data is missing? How you answer these two questions will dictate the the best place.

You can build a contineous form based on the query, or an unbound combo box on a form, or the basis of a report, or use it as part of record validation to make sure the end user has entered all the data before allowing the record to be committed. Kind of like buying a cyber-coffee for them.

At the very minimum, you can use the query builder to build the query, run it and get a handle on what data is missing.


By the way, I know you are trying to learn, and your project has great value and merrit. Did you know that it is customary to "reward" a helpful Tek-Tipster by clicking on the link Thank SoAndSo for this valuable post to award the person with a star. I kind-of noticed that PHV and Bob Scriver provided you with what appeared to be valuable info but never got the pat on the back.

 

[SherryLynn]

I must apologize. I did not realize that I was supposed to do that throughout the post, I thought it was something I did at the end (once I found a final solution). Please excuse my poor etiquette. You all certainly deserve a "star" for all the help you've given me.

Sherry

 

[SherryLynn]

Hi PHV,

I still haven't been able to get this to work? Help!

Thanks,
Sherry

 

[willir]

Sherry

PHV is one of the top techies at this site, and his code is quite effecient.

SELECT A.GroupID, A.GroupName, Count(M.[Offender ID]) AS [CountOfOffender ID]
FROM [COLOR=blue]queMonthlyTally [b]AS[/b] M[/color],
[COLOR=red]tblAgeGroup-Offender [b]AS[/b] A [/color]
WHERE M.AgeOYears Between [Minimum] And [Maximum]
GROUP BY A.GroupID, A.GroupName
UNION SELECT 0, "Unknown", Count(*)
[COLOR=green]FROM tblAgeGroup-Offender [b]AS[/b] N [/color]
WHERE N.DOB Is Null
ORDER BY 1;

The M, A, and N are aliases for tables - a short text or letter is substituted for a longer table name to cut down on typing, and I actually find it makes the coding more readable.

Syntax is
TableName AS Alias
queMonthlyTally AS M
...substituted the letter M for the table queMonthlyTally
M.AgeOYears is much easier to type out than queMonthlyTally.AgeOYears.

Note: I added the word "AS" in the SELECT statement. I normally include it -- I never tried a SELECT without it. Is this what is causing your SELECT statement to fail?

He also used ORDER BY 1 - order by first column.

Note:
You still need to verify the correct spelling of the field names and table names.

If this does not fix your problem, what is the problem?

Richard

 

[SherryLynn]

Thank you Richard. The M and A I was able to identify, but I was still unsure of what he was referring to as N. I'll try and figure it out.

Thanks.

 

[scriverb]

SherryLynn: I have been monitoring PHV and willir taking good care of you. What is the N for??

Let me help here a little. The query is a UNION query. This one has two selects. The first takes in a query and a table each of which as an alias and the second select just takes in the same table but with a different alias(N). You could run each of these selects seperately and get a recordset. The idea of a UNION query is to combine multiple selects into one recordset being returned.

So, the N is just the Alias for the table being used in the second Select of thei UNION query.

Hope this helps you to understand this process.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Thank you very much Bob. Now it makes sense to me. I'm going to try it out and let you all know how it works.

Thanks again.
Sherry

 

[SherryLynn]

Hello,

I seem to have this working now. Here is what I have done;

SELECT [tblAgeGroup-Offender].GroupID, [tblAgeGroup-Offender].GroupName, Count(queMonthlyTally.[Offender ID]) AS [CountOfOffender ID]
FROM queMonthlyTally, [tblAgeGroup-Offender]
WHERE (((queMonthlyTally.AgeOYears) Between [Minimum] And [Maximum]))
GROUP BY [tblAgeGroup-Offender].GroupID, [tblAgeGroup-Offender].GroupName
UNION SELECT 0, "Unknown", Count(*)
FROM [queMonthlyTally]
WHERE queMonthlyTally.[DOB-O] Is Null
ORDER BY 1;

Now here is my next question. These totals go onto a statistics sheet. How do I get a total of the results?

Thank you,
Sherry

 

[PHV]

Simply by creating a summing query on the previously posted one.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SherryLynn]

Would this work correctly? It seems to give me the right number.

SELECT Count(queMonthlyTally.[Offender ID]) AS [CountOfOffender ID]
FROM queMonthlyTally;


Thanks!
Sherry