If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with small result type
Hi to all expert,
I am running kind of a complex query and I want to know why I get the result as a string instead of a number/currency, the math is good : SELECT BasicRental.IdBranch, Nz(Round(IIf(Nz(IIf(Eval([DateTo] Between FirstOfMonth(DateAdd('m',1,Date())) And EndOfMonth(DateAdd('m',1,Date()))),(([AnnualRental]/12)/DaysInMonth(DateAdd('m',1,Date())))*(DateDiff('d', FirstOfMonth(DateAdd('m',1,Date())),[DateTo])+1)),0)+Nz(IIf(Eval([DateFrom] Between FirstOfMonth(DateAdd('m',1,Date())) And EndOfMonth(DateAdd('m',1,Date()))),(([AnnualRental]/12)/DaysInMonth(DateAdd('m',1,Date())))*(DateDiff('d',[DateFrom],EndOfMonth(DateAdd('m',1,Date())))+1)),0)=0,[AnnualRental]/12,Nz(IIf(Eval([DateTo] Between FirstOfMonth(DateAdd('m',1,Date())) And EndOfMonth(DateAdd('m',1,Date()))),(([AnnualRental]/12)/DaysInMonth(DateAdd('m',1,Date())))*(DateDiff('d', FirstOfMonth(DateAdd('m',1,Date())),[DateTo])+1)),0)+Nz(IIf(Eval([DateFrom] Between FirstOfMonth(DateAdd('m',1,Date())) And EndOfMonth(DateAdd('m',1,Date()))),(([AnnualRental]/12)/DaysInMonth(DateAdd('m',1,Date())))*(DateDiff('d',[DateFrom],EndOfMonth(DateAdd('m',1,Date())))+1)),0)),2),0) AS Rent FROM (BasicRental LEFT JOIN tblTenant ON BasicRental.IdBranch = tblTenant.IdBranch) LEFT JOIN Branch ON BasicRental.IdBranch = Branch.IdBranch WHERE (((BasicRental.DateFrom)=EndOfMonth(DateAdd('m',1 ,Date()))) AND ((BasicRental.DateTo)=FirstOfMonth(DateAdd('m',1, Date()))) AND ((Branch.Inactive)=False) AND ((Branch.RentRollChk)=False)) OR (((Branch.Inactive)=False) AND ((Branch.RentRollChk)=False) AND ((BasicRental.MthToMth)=True)) ORDER BY BasicRental.IdBranch, BasicRental.SpaceNo, BasicRental.DateFrom; My goal here is to cal rent for the following month and the rent need to be based on the real amount of days in the month, meaning if my lease end the 20 of the month, the amount need to be prorated to 20 instead of 30 or 31. I have been on this for the last few days ..... Anyway. my problem is why I get a string instead of a number ? Any help will be greatfull |
#2
|
|||
|
|||
Help with small result type
Nz() returns a variant data type. If you want to ensure it is numeric, wrap
it in the Val() function. BTW: I would have lots of trouble with a complex expression like you created. Have you considered creating a small user-defined function that accepts the basic fields/values and returns the appropriate numeric? -- Duane Hookom Microsoft Access MVP "Alain" wrote: Hi to all expert, I am running kind of a complex query and I want to know why I get the result as a string instead of a number/currency, the math is good : SELECT BasicRental.IdBranch, Nz(Round(IIf(Nz(IIf(Eval([DateTo] Between FirstOfMonth(DateAdd('m',1,Date())) And EndOfMonth(DateAdd('m',1,Date()))),(([AnnualRental]/12)/DaysInMonth(DateAdd('m',1,Date())))*(DateDiff('d', FirstOfMonth(DateAdd('m',1,Date())),[DateTo])+1)),0)+Nz(IIf(Eval([DateFrom] Between FirstOfMonth(DateAdd('m',1,Date())) And EndOfMonth(DateAdd('m',1,Date()))),(([AnnualRental]/12)/DaysInMonth(DateAdd('m',1,Date())))*(DateDiff('d',[DateFrom],EndOfMonth(DateAdd('m',1,Date())))+1)),0)=0,[AnnualRental]/12,Nz(IIf(Eval([DateTo] Between FirstOfMonth(DateAdd('m',1,Date())) And EndOfMonth(DateAdd('m',1,Date()))),(([AnnualRental]/12)/DaysInMonth(DateAdd('m',1,Date())))*(DateDiff('d', FirstOfMonth(DateAdd('m',1,Date())),[DateTo])+1)),0)+Nz(IIf(Eval([DateFrom] Between FirstOfMonth(DateAdd('m',1,Date())) And EndOfMonth(DateAdd('m',1,Date()))),(([AnnualRental]/12)/DaysInMonth(DateAdd('m',1,Date())))*(DateDiff('d',[DateFrom],EndOfMonth(DateAdd('m',1,Date())))+1)),0)),2),0) AS Rent FROM (BasicRental LEFT JOIN tblTenant ON BasicRental.IdBranch = tblTenant.IdBranch) LEFT JOIN Branch ON BasicRental.IdBranch = Branch.IdBranch WHERE (((BasicRental.DateFrom)=EndOfMonth(DateAdd('m',1 ,Date()))) AND ((BasicRental.DateTo)=FirstOfMonth(DateAdd('m',1, Date()))) AND ((Branch.Inactive)=False) AND ((Branch.RentRollChk)=False)) OR (((Branch.Inactive)=False) AND ((Branch.RentRollChk)=False) AND ((BasicRental.MthToMth)=True)) ORDER BY BasicRental.IdBranch, BasicRental.SpaceNo, BasicRental.DateFrom; My goal here is to cal rent for the following month and the rent need to be based on the real amount of days in the month, meaning if my lease end the 20 of the month, the amount need to be prorated to 20 instead of 30 or 31. I have been on this for the last few days ..... Anyway. my problem is why I get a string instead of a number ? Any help will be greatfull |
#3
|
|||
|
|||
Help with small result type
Thanks,
I am completly reworking this query into a much simpler way, I just found out with some specific date it returns nothing at all so I guess it is way too much complex for nothing Thanks "Duane Hookom" wrote: Nz() returns a variant data type. If you want to ensure it is numeric, wrap it in the Val() function. BTW: I would have lots of trouble with a complex expression like you created. Have you considered creating a small user-defined function that accepts the basic fields/values and returns the appropriate numeric? -- Duane Hookom Microsoft Access MVP "Alain" wrote: Hi to all expert, I am running kind of a complex query and I want to know why I get the result as a string instead of a number/currency, the math is good : SELECT BasicRental.IdBranch, Nz(Round(IIf(Nz(IIf(Eval([DateTo] Between FirstOfMonth(DateAdd('m',1,Date())) And EndOfMonth(DateAdd('m',1,Date()))),(([AnnualRental]/12)/DaysInMonth(DateAdd('m',1,Date())))*(DateDiff('d', FirstOfMonth(DateAdd('m',1,Date())),[DateTo])+1)),0)+Nz(IIf(Eval([DateFrom] Between FirstOfMonth(DateAdd('m',1,Date())) And EndOfMonth(DateAdd('m',1,Date()))),(([AnnualRental]/12)/DaysInMonth(DateAdd('m',1,Date())))*(DateDiff('d',[DateFrom],EndOfMonth(DateAdd('m',1,Date())))+1)),0)=0,[AnnualRental]/12,Nz(IIf(Eval([DateTo] Between FirstOfMonth(DateAdd('m',1,Date())) And EndOfMonth(DateAdd('m',1,Date()))),(([AnnualRental]/12)/DaysInMonth(DateAdd('m',1,Date())))*(DateDiff('d', FirstOfMonth(DateAdd('m',1,Date())),[DateTo])+1)),0)+Nz(IIf(Eval([DateFrom] Between FirstOfMonth(DateAdd('m',1,Date())) And EndOfMonth(DateAdd('m',1,Date()))),(([AnnualRental]/12)/DaysInMonth(DateAdd('m',1,Date())))*(DateDiff('d',[DateFrom],EndOfMonth(DateAdd('m',1,Date())))+1)),0)),2),0) AS Rent FROM (BasicRental LEFT JOIN tblTenant ON BasicRental.IdBranch = tblTenant.IdBranch) LEFT JOIN Branch ON BasicRental.IdBranch = Branch.IdBranch WHERE (((BasicRental.DateFrom)=EndOfMonth(DateAdd('m',1 ,Date()))) AND ((BasicRental.DateTo)=FirstOfMonth(DateAdd('m',1, Date()))) AND ((Branch.Inactive)=False) AND ((Branch.RentRollChk)=False)) OR (((Branch.Inactive)=False) AND ((Branch.RentRollChk)=False) AND ((BasicRental.MthToMth)=True)) ORDER BY BasicRental.IdBranch, BasicRental.SpaceNo, BasicRental.DateFrom; My goal here is to cal rent for the following month and the rent need to be based on the real amount of days in the month, meaning if my lease end the 20 of the month, the amount need to be prorated to 20 instead of 30 or 31. I have been on this for the last few days ..... Anyway. my problem is why I get a string instead of a number ? Any help will be greatfull |
Thread Tools | |
Display Modes | |
|
|