datediff

[greenbg via AccessMonster.com]

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

 

[fredg]

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.

 

[greenbg via AccessMonster.com]

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

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.

 

[fredg]

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

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.

 

[greenbg via AccessMonster.com]

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
Is there any way that I can get date range without adding the Title an name??

[quoted text clipped - 31 lines]
I'm afraid I don't understand this question.
Try again, with an example of the result you wish.