Oscar9ertango
asked on
SQL date range question, needs to find the record within one year of one field based on another field but only in cases where there are multiples of another field...
Where there are multiple values of the same [LicenseeID] the query should only show those records where the [NewExpirationDate] (which is already filtered in the query to be after the current date) is within a year of [NewScheduleDate] but only in those cases where multiple values of the same [LicenseeID].
There may be other values in the [NewExpirationDate] where the values are greater than 1 year of [NewScheduleDate] (but in these instances there is only one value for the [LicenseeID])
SQL code is attached.
sql-unique-date.txt
There may be other values in the [NewExpirationDate] where the values are greater than 1 year of [NewScheduleDate] (but in these instances there is only one value for the [LicenseeID])
SQL code is attached.
sql-unique-date.txt
ASKER
Thx so much but I get a syntax error when I try to use this SQL, Im going to attach the screen pic to save time. Tell me if you would prefer for me to write out the error
error-msg.JPG
error-msg.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm sorry to ask but the query itself seems to work but now when I run the report that was based on the query before (which worked before but was pulling too many records, namely those duplicate LicenseID's it asks me for the parameter value for [NewExpirationDate]. I know this is a little beyond the original question which I will accept as a solution but any idea why it now needs that value entered?
Review your report design. It appears that you have a report field for NewExpirationDate, but there is no corresponding field in the query.
There is a query field for FirstOfNewExpirationDate, though. You may have to update your report to use that instead.
There is a query field for FirstOfNewExpirationDate, though. You may have to update your report to use that instead.
ASKER
Thanks so much, yes I figured that out once I looked at the report and saw that error.
Im accepting the above as a solution. Tell me if I need to do anything else. Thx again...
Chuck
Im accepting the above as a solution. Tell me if I need to do anything else. Thx again...
Chuck
ASKER
A great Help!
Glad to help :)
Open in new window