Sean
|
| Posted: 01/30/2004, 12:47 PM |
|
I am trying to show a list of today's birthdays based on current date. I have a grid that has the person's name and birthday, and I know I need to put a WHERE statement in the grid so it only shows a person's name if their birthday equals today's date, but I am having trouble with the syntax.
SELECT *
FROM FamilyDir
WHERE Birthday = #{HELP HERE}#
ORDER BY FirstName
This is using ASP, CCS and Access 2000.
Any help would be appreciated.
|
|
|
 |
peterr
Posts: 5971
|
| Posted: 01/30/2004, 1:59 PM |
|
Since your SQL looks simple, I recommend using Table instead of SQL.
Once you specify Table in your form "Data Source" property, you can click on the "+" in the Where section to create a new WHERE Parameter.
Once there, you can setup your property like this:
Field: Birthday
Type: Date
Condition: Equals (=)
Parameter Soruce: Date()
Type: Expression
Though if you choose to use SQL then simply replace "#{HELP HERE}#" with "Date()":
SELECT *
FROM FamilyDir
WHERE Birthday = Date()
ORDER BY FirstName
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
 |
|
Sean
|
| Posted: 02/02/2004, 7:18 AM |
|
peterr,
Thanks for the reply. That works, but how would I get it to disregard the year? It only seems to work if mm/dd/yyyy matches exactly.
In other words, I only want the SQL to match mm/dd from the database and match it to today's mm/dd (not year).
Thanks.
|
|
|
|
DonB
|
| Posted: 02/02/2004, 7:48 AM |
|
Although this is probably a small database and performance is not critical,
there is the issue of whether you can use an index or not in fetching the
data. This does tie into your question, however.
By using a datetime field and extracting the day/month (within the WHERE
clause), you make it impossible for the query to use an index. As you have
seen, it also complicates querying for the information you want . In this
situation you would be better off to store the day, month and year as
separate columns in the table. You can define an index on each one
individually. You also can find all the matches for a given day, month or
other combination much more easily. You could keep the combined datetime
and just add 3 other columns to the table, then put the functions Day,
Month, Year into a custom insert/update so that all 4 are populated when you
edit the record. By retaining the complete datetime field, then you will be
more easily able to do date arithmetic. So, the denormalization I am
recommending provides worthwhile benefits to you.
--
DonB
http://www.gotodon.com/ccbth
"Sean" <Sean@forum.codecharge> wrote in message
news:6401e6a2d5572c@news.codecharge.com...
> peterr,
>
> Thanks for the reply. That works, but how would I get it to disregard the
year? It only seems to work if mm/dd/yyyy matches exactly.
>
> In other words, I only want the SQL to match mm/dd from the database and
match it to today's mm/dd (not year).
>
> Thanks.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Sean
|
| Posted: 02/02/2004, 7:54 AM |
|
Thanks for the reply Don. I thought that may be my only option after trying this for a while, but oh well......
I guess I will do it that way.
|
|
|
|
|
