Birthday Date

  • Thread starter Thread starter john
  • Start date Start date
J

john

I have a table that has the Date of Birth field that is set as a text field
that is formated 99/99/00;;_ I need to create a query that returns anyone
whos birth month is equal to the current month. Can anyone help?
 
Why are you carrying a date as text? That only makes life harder for you.
There is not advantage to carrying a date as text.

But, to answer your question, assuming the actual field content is in mmddyy
order and today would be represented as 072309, you can use this

WHERE CLng(Left([DOB],2)) = Month(Date)

If you are using an international format of ddmmyy (230709)

It would be

WHERE Clng(Mid([DOB],3,2)) = Month(Date)

And if you were doing it correctly, it would be

WHERE Month([DOB]) = Month(Date)
 
Why are you carrying a date as text? That only makes life harder for you.
There is not advantage to carrying a date as text.
Click to expand...

Yes there is -- if, for example, you don't know the exact date, and want to
enter the year only, or a year and month but you don't know the day, then the
YYYY-MM-DD format works well. You can enter 1583-07-00 and it will still sort
in chronological order.

I realise that doesn't answer the original poster's question, but if you know
any algorithms for dealing with approximate dates it would be good to know
them.
 
you would have to be very careful with such a scheme. It would be a trick to
write queries to retrieve exactly what you want if you use that field as a
filter. But, I do see your point.
 
It will sort correctly, however, you will either get an error or an incorrect
date, depending on the value. For example:
Using the value you showed as an example:

x = "1583-07-00"
?dateserial(left(x,4),mid(x,6,2),right(x,2))
6/30/1583

And here, it gets even further out:

x = "1583-00-00"
?dateserial(left(x,4),mid(x,6,2),right(x,2))
11/30/1582

Now were are in a different century

x = "0000-00-00"
?dateserial(left(x,4),mid(x,6,2),right(x,2))
11/30/1999

It gets even more strange

x = "0000-01-01"
?dateserial(left(x,4),mid(x,6,2),right(x,2))
1/1/2000


So, as you can see, the values would be very difficult to deal with.

It will translate a month off because the 0 date of a date becomes the last
day of the previous month. This is a technique used purposely when you want
to create a date range without having to worry about how many days are in the
month.
 
It will sort correctly, however, you will either get an error or an incorrect
date, depending on the value. For example:
Using the value you showed as an example:

x = "1583-07-00"
?dateserial(left(x,4),mid(x,6,2),right(x,2))
6/30/1583

And here, it gets even further out:

x = "1583-00-00"
?dateserial(left(x,4),mid(x,6,2),right(x,2))
11/30/1582
Click to expand...

Yes, but you could, presumably, write a routine in VBA to display a value like
1684-05-00 as "May 1684".
 
True, but that is the easy part. The issue is comparing that value to a date
or a range of dates. I am not saying it can't be done, it is that there are
better ways to do it.
 
Back
Top