Building an expression

[boucher222]

Hello,
I am building a customer database for my friend who has a tanning business.

In my "Enter Customer Info" form I want my text box to return a YES or NO value if the date value entered in another field plus 16 years is before (NO) or after (YES) today's date.
The object is to quickly show if a customer is younger than 16 years old and prompt whomever is entering the information to print a report I've created that is a waiver that must be signed by the customer.
I know it seems easy, but I'm having trouble with the date plus 16 part...
Can anyone help me with this?

Thanks in advance,
Tammy

[Rabbit]

Hello,
I am building a customer database for my friend who has a tanning business.

In my "Enter Customer Info" form I want my text box to return a YES or NO value if the date value entered in another field plus 16 years is before (NO) or after (YES) today's date.
The object is to quickly show if a customer is younger than 16 years old and prompt whomever is entering the information to print a report I've created that is a waiver that must be signed by the customer.
I know it seems easy, but I'm having trouble with the date plus 16 part...
Can anyone help me with this?

Thanks in advance,
Tammy

Try this:

iif(DateDiff("y", Birthdate, Date) < 16, "YES", "NO")

[missinglinq]

Actually, that should be

iif(DateDiff("yyyy", Birthdate, Date) < 16, "YES", "NO")

The single y for interval stands for "day of year" not years.

The problem with this code is that it won't return an accurate age! If it's run on any day in 2007, and the Birthdate is any day in 1991, it will yield an age of 16 and thus return "No." But if the person hasn't yet had a birthday in 2007, they're not yet 16, and it should return "Yes."

This will return the actual age and yield the appropriate Yes or No:

IIf(DateDiff("yyyy", [Birthday], Date) - IIf(Format$(Date, "mmdd") < Format$([Birthday], "mmdd"), 1, 0) < 16, "Yes", "No") 
 

[boucher222]

Thanks for the help.
I entered the expression and it's returning #Name? in that field.
Not sure what is wrong...
Tammy

[theaybaras]

Message deleted by poster. Will post again shortly. Sorry for the delay.

[theaybaras]

Message deleted by poster. Will post again shortly. Sorry for the delay.

Try the following in your unbound textbox. This is assuming that the field that contains the date of birth is named "Birthdate" not "Birthday"

also this is based off of the now() function rather than a specified date.

With the example above your date of birth field would have to be named birthday and you needed to change date to a specified date not the word date.

This will compare their birthday to "today"

IIf(DateDiff("yyyy", [Birthdate], Now())-IIf(Format$(Now(), "mmdd")<Format$([Birthdate], "mmdd"),1,0)<16,"NO","YES")

[missinglinq]

You have to replace [Birthday] in my code or [Birthdate] in the version by theaybaras with the actual name of your own textbox.

[boucher222]

Okay.
My field is named Birthdate. I changed Birthday to Birthdate when I entered the code. I was using Date() in my Date field. So I used that in the code also. Is that the problem?
I'm not sure that using Date() in the Date field is what I want anyway. When I open the database, I want the current date to fill in automatically on a new record, but after that date, I don't want it to change to the current date every time I look at the record. How do I accomplish that?
Thanks,
Tammy

[theaybaras]

Okay.
My field is named Birthdate. I changed Birthday to Birthdate when I entered the code. I was using Date() in my Date field. So I used that in the code also. Is that the problem?
I'm not sure that using Date() in the Date field is what I want anyway. When I open the database, I want the current date to fill in automatically on a new record, but after that date, I don't want it to change to the current date every time I look at the record. How do I accomplish that?
Thanks,
Tammy

So then, assuming you have a date field that will be storing the date in your table. Go into your table design, and set the default value of this date field to Date(). Then when you create a new record, today's date will fill in automatically, but this will be stored in the table. When you view the record again later, that date will still be there because you are not asking it to compute today's date in a field, you are storing the data in a field just like always... the field was just populated by the default value date().

Does this work for you?

[theaybaras]

when you are calculating the clients age... is there a reason you are adding 16 to years to their birthdate rather than just finding the difference between birthdate and date()? I'm just curious as I haven't seen the other approach very often.

[NeoPa]

Okay.
My field is named Birthdate. I changed Birthday to Birthdate when I entered the code. I was using Date() in my Date field. So I used that in the code also. Is that the problem?
I'm not sure that using Date() in the Date field is what I want anyway. When I open the database, I want the current date to fill in automatically on a new record, but after that date, I don't want it to change to the current date every time I look at the record. How do I accomplish that?
Thanks,
Tammy

Tammy,
If you set the default for the field (in the table design is better, but it can also be done on the form control) to =Date(), then it will only populate it with that value when it is originally entered.

theAybaras,
It is a good idea always to use the Date() function rather than the Now() function when dealing with dates and date ranges. Typically, next week (from Monday 16th May ????) is Monday 23rd May ???? and not 15:09 on Monday 23rd May ????. This can be important, especially in situations where you're checking date ranges.

Lastly, it is often more straightforward when checking dates, to compare today with a date plus x, rather than subtract the two dates and test the period in between. Consider this case : How would you determine if the difference between the two dates matched 16 years? Which years are leap years? etc.
More reliable to compare Birthdate+16 years (done for you by DateAdd()) with today. If the result is after today, they're < 16 Yrs. Does that make sense.

[theaybaras]

That makes a lot of sense! Thanks for the advice! That answers some issues I ahve had in the past! Thanks!

theAybaras

[NeoPa]

No problem.
Happy to share experience (Why else would I be here ;)).

[boucher222]

Okay. So you kinda lost me.
Not sure now what I need to enter in my field to get a YES response if the customer is less than 16 years old (or NO if 16 or older)?
Tammy

[NeoPa]

Okay. So you kinda lost me.
Not sure now what I need to enter in my field to get a YES response if the customer is less than 16 years old (or NO if 16 or older)?
Tammy

OK Tammy,
I think all you need is in post #11.
Set the default in the table design to use Date() (=Date()).
In the query that you want to let you know if someone is 16 or older include :

SELECT ...
       IIf(DateAdd('yyyy',16,[BirthDate])>Date(),'No','Yes') AS OldEnough,
       ...,
FROM [BlahBlahBlah] etc

[boucher222]

I don't have enough experience with expressions or Access for that matter
to be able to see where you're going with this....but I don't think this is all
I need?
=IIf(DateAdd('yyyy',16,[Birthdate])>Date(),'NO','YES')
I guess I'm not following. I'm sorry for being such a pain.
P.S. I did set the default to use Date() and it is working how I want.

OK Tammy,
I think all you need is in post #11.
Set the default in the table design to use Date() (=Date()).
In the query that you want to let you know if someone is 16 or older include :

SELECT ...
       IIf(DateAdd('yyyy',16,[BirthDate])>Date(),'No','Yes') AS OldEnough,
       ...,
FROM [BlahBlahBlah] etc

[NeoPa]

I don't have enough experience with expressions or Access for that matter
to be able to see where you're going with this....but I don't think this is all
I need?
=IIf(DateAdd('yyyy',16,[Birthdate])>Date(),'NO','YES')
I guess I'm not following. I'm sorry for being such a pain.
P.S. I did set the default to use Date() and it is working how I want.

OK.
Well, first of all, it's good that you've got the Date() of entry working properly. That's actually the harder part ;)

Now, do you have a query somewhere which needs a Yes/No result depending on the age of a person? You mention a field - where is this? In a query? On a form?
If I can understand exactly what you're trying to do, I'll be in a better position to help you along step by step.

[boucher222]

OK.
Well, first of all, it's good that you've got the Date() of entry working properly. That's actually the harder part ;)

Now, do you have a query somewhere which needs a Yes/No result depending on the age of a person? You mention a field - where is this? In a query? On a form?
If I can understand exactly what you're trying to do, I'll be in a better position to help you along step by step.

WooHOO! Thank you SOO much for your help!
I figured it out on my own...it's a field in a form- I had to make the
code look at the necessary fields on the table.. if that makes sense!
Also, I had to change the > to a < to make it calculate properly (it was
returning Yes for ages greater than 16 and No for ages less than 16. I
wanted it to return "yes" for "signature req'd?" for clients less than 16 years old.

Now that I have that figured out, I would like to ask you another
question...
Currently, when you open the database, it opens to the 1st entry.
If my friend wants to open the database and then go to a specific record,
what do I need to do to give her the easiest way to find a specific record
quickly? I assume this search would be based on 1st or last name.
Thanks!
Tammy

[NeoPa]

WooHOO! Thank you SOO much for your help!
I figured it out on my own...it's a field in a form- I had to make the
code look at the necessary fields on the table.. if that makes sense!
Also, I had to change the > to a < to make it calculate properly (it was
returning Yes for ages greater than 16 and No for ages less than 16. I
wanted it to return "yes" for "signature req'd?" for clients less than 16 years old.

Now that I have that figured out, I would like to ask you another
question...
Currently, when you open the database, it opens to the 1st entry.
If my friend wants to open the database and then go to a specific record,
what do I need to do to give her the easiest way to find a specific record
quickly? I assume this search would be based on 1st or last name.
Thanks!
Tammy

Congratulations. it's always fun when you get something sorted out.
You're right about my code. it was answering the question "Are they 16 or over?"
To answer your last question I'd need to know what type of form you have and what it's bound to. I would guess that it's bound to a table, but what fields are shown and would you want to implement a form of filtering or just do a search (Using the binoculars button)?

[NeoPa]

Congratulations. it's always fun when you get something sorted out.
You're right about my code. it was answering the question "Are they 16 or over?"
To answer your last question I'd need to know what type of form you have and what it's bound to. I would guess that it's bound to a table, but what fields are shown and would you want to implement a form of filtering or just do a search (Using the binoculars button)?

I was in a hurry leaving work for the last post.
How were you thinking that someone would choose which record they wanted?

[boucher222]

I was in a hurry leaving work for the last post.
How were you thinking that someone would choose which record they wanted?

I was thinking that they would search based on names (first & last).
I am using a switchboard. The switchboard opens 1st. From there you can
choose to enter/view client records, preview reports, etc. I was thinking of
adding a button there that would be "search for a client record". Not sure
after that how it would function. I am using a form to enter data into a table.
I want the search to open the form to the record of the person they are
searching for.
Tammy

[NeoPa]

In that case I would say that you have two options :

1. First Option
   1. Create an unbound TextBox control on your form (We'll call it txtSearch).
   2. In the After_Update event procedure have :
      

      Private Sub txtSearch_AfterUpdate()
        With Me.Recordset
          Call .FindFirst("[YourNameField] Like '" & Me.txtSearch & "*'")
          If .NoMatch Then Call MsgBox("No record found!")
        End With
      End Sub

2. Second Option
   1. Create an unbound ComboBox control on your form which lists all the records in the recordset (The same records that the form can select from - We'll call it cboSearch).
   2. In the After_Update event procedure have :
      

      Private Sub cboSearch_AfterUpdate()
        Call Me.Recordset.FindFirst("[YourNameField]='" & Me.cboSearch & "'")
      End Sub

[boucher222]

In that case I would say that you have two options :

1. First Option
   1. Create an unbound TextBox control on your form (We'll call it txtSearch).
   2. In the After_Update event procedure have :
      

      Private Sub txtSearch_AfterUpdate()
        With Me.Recordset
          Call .FindFirst("[YourNameField] Like '" & Me.txtSearch & "*'")
          If .NoMatch Then Call MsgBox("No record found!")
        End With
      End Sub

2. Second Option
   1. Create an unbound ComboBox control on your form which lists all the records in the recordset (The same records that the form can select from - We'll call it cboSearch).
   2. In the After_Update event procedure have :
      

      Private Sub cboSearch_AfterUpdate()
        Call Me.Recordset.FindFirst("[YourNameField]='" & Me.cboSearch & "'")
      End Sub

I don't know (or understand) why I would put a text box or a combo box on the form- what would they do?
What if I wanted to create a command button (I would call it "Search Records")
on the form that when clicked brings up a box to enter last name, then a box
to enter 1st name like a query?
Is that possible?