datediff

hi
I am trying to get a list of name who's birthday is 35 of days away. I have
build a query with all of the information the I need. Under the field birth I
enter the following criteria:
IIf(DateDiff("d",[birth],Date())=35,[TITLE]+" "+[LNAME]," ")

I get an error 3464 (Data Type Mismatch in criteria expression)

In my table Birth is setup the following way:
Date/Time
Medium Date

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200707/1

On Wed, 11 Jul 2007 20:46:00 GMT, greenbg via AccessMonster.com wrote:

hi
I am trying to get a list of name who's birthday is 35 of days away. I have
build a query with all of the information the I need. Under the field birth I
enter the following criteria:
IIf(DateDiff("d",[birth],Date())=35,[TITLE]+" "+[LNAME]," ")

I get an error 3464 (Data Type Mismatch in criteria expression)

In my table Birth is setup the following way:
Date/Time
Medium Date

The problem with your expression is that it is counting the days
between the date of birth and today's date, whereas you wish to count
the number of days between the month and day of birth (not including
the birth year), and today's month and day.

By the way, the Ampersand (&) is the suggested symbol to concatenate
string data, not the plus (+) symbol.

WhoseBirthday:IIf(DateDiff("d",Format([Birth],"mm/dd"),Format(Date(),"mm/dd"))
Between 0 and 35, [TITLE] & " " & [LNAME]," ")

The above will return all the records. Only the records within 35 days
will include a Title and Name.

A better method, using the query, is to have a new column...
DaysToBirthdayateDiff("d",Format([Birth],"mm/dd"),Format(Date(),"mm/dd"))

as criteria on this column, write:
Between 0 and 35

Then in a new column, write:
WhoesBirthday:[TITLE] & " " & [LNAME]

Only those persons whose birthday falls within 35 days will be
returned by the query.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

fredg
Is there any way that I can get date range without adding the Title an name??
??

fredg wrote:
hi
I am trying to get a list of name who's birthday is 35 of days away. I have
[quoted text clipped - 7 lines]
Date/Time
Medium Date

The problem with your expression is that it is counting the days
between the date of birth and today's date, whereas you wish to count
the number of days between the month and day of birth (not including
the birth year), and today's month and day.

By the way, the Ampersand (&) is the suggested symbol to concatenate
string data, not the plus (+) symbol.

WhoseBirthday:IIf(DateDiff("d",Format([Birth],"mm/dd"),Format(Date(),"mm/dd"))
Between 0 and 35, [TITLE] & " " & [LNAME]," ")

The above will return all the records. Only the records within 35 days
will include a Title and Name.

A better method, using the query, is to have a new column...
DaysToBirthdayateDiff("d",Format([Birth],"mm/dd"),Format(Date(),"mm/dd"))

as criteria on this column, write:
Between 0 and 35

Then in a new column, write:
WhoesBirthday:[TITLE] & " " & [LNAME]

Only those persons whose birthday falls within 35 days will be
returned by the query.


--
bg

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200707/1

On Thu, 12 Jul 2007 19:38:03 GMT, greenbg via AccessMonster.com wrote:

fredg
Is there any way that I can get date range without adding the Title an name??
??

fredg wrote:
hi
I am trying to get a list of name who's birthday is 35 of days away. I have
[quoted text clipped - 7 lines]
Date/Time
Medium Date

The problem with your expression is that it is counting the days
between the date of birth and today's date, whereas you wish to count
the number of days between the month and day of birth (not including
the birth year), and today's month and day.

By the way, the Ampersand (&) is the suggested symbol to concatenate
string data, not the plus (+) symbol.

WhoseBirthday:IIf(DateDiff("d",Format([Birth],"mm/dd"),Format(Date(),"mm/dd"))
Between 0 and 35, [TITLE] & " " & [LNAME]," ")

The above will return all the records. Only the records within 35 days
will include a Title and Name.

A better method, using the query, is to have a new column...
DaysToBirthdayateDiff("d",Format([Birth],"mm/dd"),Format(Date(),"mm/dd"))

as criteria on this column, write:
Between 0 and 35

Then in a new column, write:
WhoesBirthday:[TITLE] & " " & [LNAME]

Only those persons whose birthday falls within 35 days will be
returned by the query.


I'm afraid I don't understand this question.
Try again, with an example of the result you wish.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

I would like to use the datediff without the iif function. I tried to remove
the iif funcution and added TITLE and LNAME FIELD to my query and it can up
empty. I place the datediff function in the BIRTH field..

fredg wrote:
fredg
Is there any way that I can get date range without adding the Title an name??
[quoted text clipped - 31 lines]
Only those persons whose birthday falls within 35 days will be
returned by the query.

I'm afraid I don't understand this question.
Try again, with an example of the result you wish.

--
bg

Message posted via http://www.accessmonster.com