Birthday Date

[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?

 

[Klatuu]

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)

 

[Steve Hayes]

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.

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.

 

[Klatuu]

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.

 

[Klatuu]

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.

 

[Steve Hayes]

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

Yes, but you could, presumably, write a routine in VBA to display a value like
1684-05-00 as "May 1684".

 

[Klatuu]

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.