Querying outputs from DateAdd DatePart and DateDiff 1

[dmarsh16946]

Have an annual subscription database with various dates

Join Date - when a subscription is started; defines the RenewalMonth

for later years :-

LastPaidDate - from which a MonthPaid is derived using DatePart.

A query then calculates

LastRenewedDate - if the payment is late, the month of LastPaidDate is extracted using DatePart and the difference in months applied to LastPaidDate to give this LastRenewedDate

CurrentExpiryDate - a year added to LastRenewedDate using DateAdd

Further queries include these calculated dates and month differences, and they look like normal dates and numbers.

But if I try to use criteria on them, say

>0 for overdue month
<Date() for years

I get 'Data type mismatch in criteria expression' errors. How can I avoid these?

Looking at the Format properties of say the date fields in the queries, the drop-down is blank, so it appears that Access isn't seeing them as true dates. Similarly the month numbers aren't seen as true numbers. Have tried adding Val to the numbers but still no joy.

 

[SkipVought]

Please post your SQL code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dmarsh16946]

Here goes, sorry it's bit complex

SELECT [LastName] & ", " & [FirstName] AS FullName, qryContacts.Deceased, qryContacts.Archived, qryContacts.Subscriber, qryContacts.Complimentary, qryContacts.Cancelled, qryContacts.LastRenewedDate, qryContacts.RenewalMonth, IIf(IsNull([LastRenewedDate]),"",DatePart('m',[LastRenewedDate])) AS LastRenewedMonthNo, IIf(IsNull([JoinDate]),0,-[LastRenewedMonthNo]+[RenewalMonthNo]) AS Diff, IIf([Diff]>0,[Diff]-12,[Diff]) AS Adj, IIf(IsNull([LastRenewedDate]),"",DateAdd("m",[Adj],[LastRenewedDate])) AS LastRenewalWasDue, IIf([Complimentary]=-1,"",DateAdd("yyyy",1,[LastRenewalWasDue])) AS SubscriptionExpiryDate, IIf([complimentary]=-1,"",DateDiff("m",[SubscriptionExpiryDate],Date())) AS OverdueMonths, IIf([complimentary]=-1,"",[OverdueMonths]) AS Overdues, DatePart('yyyy',[JoinDate]) AS YearJoined
FROM qryContacts
WHERE (((qryContacts.Deceased)=No) AND ((qryContacts.Archived)=No) AND ((qryContacts.Subscriber)=Yes) AND ((qryContacts.Cancelled)=No)) OR (((qryContacts.Deceased)=No) AND ((qryContacts.Archived)=No) AND ((qryContacts.Complimentary)=Yes) AND ((qryContacts.Cancelled)=No));

 

[dhookom]

I haven't looked at the logic but there are some issues I would fix. IMHO, an expression should never be created that might return either a string or a number. An expression should always return a specific data type or null. Your expression

IIf(IsNull([LastRenewedDate]),"",DatePart('m',[LastRenewedDate])) AS LastRenewedMonthNo

could return either a number or a string. Change the "" to Null or 0. You do this several times in your query.

Also, I make it a point to not use an alias in other calculations in the query. You create LastRenewedMonthNo and then use it elsewhere in the query. I generally repeat the expression rather than using the alias.


Duane
Hook'D on Access
MS Access MVP

 

[dmarsh16946]

Great thanks, good advice.

 

[PHV]

In fact, depending on how you want to deal with null, use either this:

DatePart('m',[LastRenewedDate]) AS LastRenewedMonthNo

or this:

Nz(DatePart('m',[LastRenewedDate], 0)) AS LastRenewedMonthNo

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886