How to combine 3 fields and convert into a date 1

[bosk00]

I have 3 fields that when combined are a date, this data is exported form an old AS400 program and can not be changed. The fields combined are for a new contract date. The fields are "T01CONDTM" (Contract date Month), "T01CONDTD" (Contract date day), and "T01CONDTM" (Contract date Month), and T01COMCD (Contract Date Year). T01CONDTM is the number 1 - 12. T01CONDTD is 1 - 31. There are 2 types of contracts, 1 Yr and 3 Yr. so to add to the mix T01COMCD "is null" for 1 yr contacts or has a number if 3 yr. My query pulls the records it needs first by the contract month, and then by the contract year, bringing in those records with the correct year or "is null" Here is the query.

SELECT DISTINCT Q.COMPANY, Q.ADDRESS1, Q.CITY, Q.STATE, Q.ZIP, Q.T01DESM, Q.T01PLSL, Q.T01FEE, basDecNum2Words([T01FEE]) AS WORDS, Q.T01CONDTM, Q.T01CONDTD, Q.T01CONDTY, Q.T01COMCD
FROM QAMAIN Q
WHERE (((Q.T01PLSL)="P") AND ((Q.T01CONDTM)=[Please enter Contract Month]) AND ((Q.T01COMCD)=[Enter Contract Year])) OR (((Q.T01PLSL)="P") AND ((Q.T01CONDTM)=[Please enter Contract Month]) AND ((Q.T01COMCD) Is Null))
WITH OWNERACCESS OPTION;

What I need to get is a date to use further, with all nulls to be changed to the year requested.

example of the data

Q.T01CONDTM Q.T01CONDTD Q.T01COMCD wanted result
5 10 5 5/10/05
5 21 5/21/05
5 11 5 5/11/05

The date that we get will be the contract start date, then to use that date to calculate a contract end date. The contract end date is the contract start date - 1 day then add 3 years.

Any and all help is appreciated.

Thanks

Alan
Senility at its finest

 

[PHV]

A starting point:
[wanted result]: DateSerial(Year(Now),Q.T01CONDTM,Q.T01CONDTD)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bosk00]

That worked great. Thank you so much. If I understood some of what I have read, then the rest can be done using DateAdd. I really appreciate your help

Alan
Senility at its finest