Problem with Next Birthday Query

[Guest]

I have a query that creates a field which gives the person's next birthday
based upon their birthdate. It is:

SELECT tblContacts.conFName1, tblContacts.conLName1,
DateSerial(Year(Date())-(DateSerial(Year(Date()),Month([conBirthday1]),Day([conBirthday1]))<Date()),Month([conBirthday1]),Day([conBirthday1]))
AS NextBirthday, tblContacts.conAddress, [conCity] & ", " & [conState] & " "
& [conZipCode] AS Line3, tblContacts.conHomePhone
FROM tblContacts
WHERE (((tblContacts.conBirthday1)>1));

I am now trying to add a line to only pull the values between 2 text fields
on a specific form (i.e. txtStartDate & txtEndDate). This would allow us to
pull only those birthdays for whatever period we want. However when I add in
the criteria ">=[txtStartDate] and <=[txtEndDate]", I get error messages and
it won't query properly. (The fields do have the full form info).

Any ideas?

Thanks.

 

[Duane Hookom]

Use
Between Forms!frmDates!txtStartDate And Forms!frmDates!txtEndDate

 

[Guest]

I have added this line to the code. But now I get an error message saying
that it is too complicated to process. Any recommendations?

WHERE
(((DateSerial(Year(Date())-(DateSerial(Year(Date()),Month([conBirthday1]),Day([conBirthday1]))<Date()),Month([conBirthday1]),Day([conBirthday1])))
Between [Forms]![frmBirthdayMenu]![txtStartDate] And
[Forms]![frmBirthdayMenu]![txtEndDate]) AND ((tblContacts.conBirthday1)>1))

Use
Between Forms!frmDates!txtStartDate And Forms!frmDates!txtEndDate

--
Duane Hookom
MS Access MVP

I have a query that creates a field which gives the person's next birthday
based upon their birthdate. It is:

SELECT tblContacts.conFName1, tblContacts.conLName1,
DateSerial(Year(Date())-(DateSerial(Year(Date()),Month([conBirthday1]),Day([conBirthday1]))<Date()),Month([conBirthday1]),Day([conBirthday1]))
AS NextBirthday, tblContacts.conAddress, [conCity] & ", " & [conState] & "
"
& [conZipCode] AS Line3, tblContacts.conHomePhone
FROM tblContacts
WHERE (((tblContacts.conBirthday1)>1));

I am now trying to add a line to only pull the values between 2 text
fields
on a specific form (i.e. txtStartDate & txtEndDate). This would allow us
to
pull only those birthdays for whatever period we want. However when I add
in
the criteria ">=[txtStartDate] and <=[txtEndDate]", I get error messages
and
it won't query properly. (The fields do have the full form info).

Any ideas?

Thanks.

 

[Duane Hookom]

Next birthday would be:
NextBirthday:
DateAdd("yyyy",Abs(Format(Date(),"mmdd")>Format([conBirthday1],"mmdd")),DateSerial(Year(Date()),Month([conBirthday1]),Day([conBirthday1])))

See if the above is lees complex.

--
Duane Hookom
MS Access MVP

I have added this line to the code. But now I get an error message saying
that it is too complicated to process. Any recommendations?

WHERE
(((DateSerial(Year(Date())-(DateSerial(Year(Date()),Month([conBirthday1]),Day([conBirthday1]))<Date()),Month([conBirthday1]),Day([conBirthday1])))
Between [Forms]![frmBirthdayMenu]![txtStartDate] And
[Forms]![frmBirthdayMenu]![txtEndDate]) AND
((tblContacts.conBirthday1)>1))

Use
Between Forms!frmDates!txtStartDate And Forms!frmDates!txtEndDate

--
Duane Hookom
MS Access MVP

I have a query that creates a field which gives the person's next
birthday
based upon their birthdate. It is:

SELECT tblContacts.conFName1, tblContacts.conLName1,
DateSerial(Year(Date())-(DateSerial(Year(Date()),Month([conBirthday1]),Day([conBirthday1]))<Date()),Month([conBirthday1]),Day([conBirthday1]))
AS NextBirthday, tblContacts.conAddress, [conCity] & ", " & [conState]
& "
"
& [conZipCode] AS Line3, tblContacts.conHomePhone
FROM tblContacts
WHERE (((tblContacts.conBirthday1)>1));

I am now trying to add a line to only pull the values between 2 text
fields
on a specific form (i.e. txtStartDate & txtEndDate). This would allow
us
to
pull only those birthdays for whatever period we want. However when I
add
in
the criteria ">=[txtStartDate] and <=[txtEndDate]", I get error
messages
and
it won't query properly. (The fields do have the full form info).

Any ideas?

Thanks.