Problem with Variable in DAvg

[Cafe2UEMP]

Hi Folks



I am sorry to post this as it must be a really simple issue but I just cannot get it!

I have an expression in a query which returns the current year as CY: Format([Date1],"yyyy",2,2)

I then have a Public Function as:

Public Function AvePerDayTY()


Dim ThisYear As Integer


ThisYear = Year(Date)


AvePerDayTY = Nz(DAvg("[TotalSales]", "QryYTDSales", "CY=ThisYear"))
End Function

This produces an error. If I change the criteria to actual year, eg. 2017, the function works.

What am I doing wrong here?

Many thanks

David

[ranman256]

you had Thisyear inside the quotes...it must be outside to resolve...

Nz(DAvg("[TotalSales]", "QryYTDSales", "CY=" & ThisYear))

[Cafe2UEMP]

Thank you so much. That works perfectly!

I am now trying to add a second criteria but keep getting lost on the syntax.

I have added ThisDay = Weekday(Date, 2) + 1 as a variable (integer) and now wish to add this as criteria two so that the function finds this day in this year.

Are you able to assist?

Much appreciated.

David

[June7]

Getting the current day of week doesn't help get year to date average. Don't you need the number of days elapsed? Use DateDiff function or:

ThisDay = Int(Format(Date(), "y"))

What field has the day number? If you have not calculated a day number in query, do it in the DAvg()

Nz(DAvg("TotalSales", "QryYTDSales", "CY=" & ThisYear & " AND Int(Format(DateField, 'y'))<=" & ThisDay))

or

Nz(DAvg("TotalSales", "QryYTDSales", "DateField BETWEEN #1/1/" & Year(Date()) & "# AND Date()"))

[Cafe2UEMP]

Thank you so much. That works perfectly!

I am now trying to add a second criteria but keep getting lost on the syntax.

I have added ThisDay = Weekday(Date, 2) + 1 as a variable (integer) and now wish to add this as criteria two so that the function finds this day in this year.

Are you able to assist?

Much appreciated.

David

Thanks again for the assistance.

The weekday is calculated in the query so no problems there.

I wasn't clear enough in my post, sorry.

What I am trying to achieve is a comparison between sales for a day (say Friday) compared with average sales for all Fridays in a given year so I need the function to calculate averages per day.

Many thanks
David

[June7]

Okay, use your field name and day of week calc and change <= to just =.

[Cafe2UEMP]

you had Thisyear inside the quotes...it must be outside to resolve...

Nz(DAvg("[TotalSales]", "QryYTDSales", "CY=" & ThisYear))

Sorry but I am not too sure what you mean.

This is what I have to date which produces the average per day for a year.

Public Function AvePerDayTY()


Dim ThisYear As Integer
Dim ThisDay As Integer






ThisYear = Year(Date)
ThisDay = Weekday(Date, 2) + 1


AvePerDayTY = Nz(DAvg("[TotalSales]", "QryYTDSales", "CY=" & ThisYear))


End Function

All I am trying to do is add an "And" statement to include "ThisDay" so I am looking at an average for that day in that year.

Thanks again

[June7]

Still not clear what you want.

I already gave you 2 examples with " AND " operator in post 4. The function will return the average of all records meeting the criteria.

Do you want the average of records for a single Friday? Then use a full date value. If you want to average records for Friday of the current week:

Nz(DAvg("[TotalSales]", "QryYTDSales", "DateField=Date()+(6-Weekday(Date()))"))

The constant 6 restricts this to calculate only a Friday date. If you want to average records based on value in DateField, consider:

SELECT *, Nz(DAvg("TotalSales", "QryYTDSales", "DateField=" & DateField)) AS DayAverage FROM tableORquery;


You are calling the UDF from a query? Perhaps you should provide the query SQL statement. Also, a sample of raw data as well as desired output can be helpful. I suspect VBA is not really necessary.