Problem: Dlookup with dateadd function as the criteria.
I am trying to write code to look up a date in column one of a table and average the second column from this date for a number of months entered by the user on a form. I want to present the average in a text box on the same form.
I.e. the table looks like
Table
Month Value
1/01/2002 1
1/02/2002 2
1/02/2003 3
etc.
The following code works:
(Note that txt_StartDate is a date formatted field on the form.)
Dim temp as string
Temp = DLookup("Value", "Table", "Month=txt_StartDate")
Me.txt_Average.value = Temp
As does:
Temp = DLookup("Value", "Table", "Month=Format(DateAdd(""m"", 1, txt_StartDate), ""dd-mmm-yyyy"")")
Me.txt_Average.value = Temp
However, I want to create a loop to add the value for the current month and the following n months (where n is a value entered by the user on the form) and average this total by dividing by n. E.g.
Dim i as integer
Total = 0
For i = 1 to n
Temp = DLookup("Value", "Table", "Month=Format(DateAdd(""m"", i-1, txt_StartDate), ""dd-mmm-yyyy"")")
Total = Total + Temp
Next i
Me.txt_Average.value = Total/n
However I keep getting error messages with this.
I have also tried to calculate the dateadd function outside of the dlookup function and pass the resulting date, e.g.:
For i = 1 to n
TempDate = dateadd(“m” , i-1, txt_StartDate)
Temp = DLookup("Value", "Table", "Month=TempDate")
Total = Total + Temp
Next i
In fact, the only way I can get this to work is to publish the TempDate on the form, and then pass this back to the Dlookup function, i.e.:
For i = 1 To n
TempDate = DateAdd("m", i - 1, txt_StartDate)
txt_TempDateField.Value = TempDate
Temp = DLookup("PRICE", "TOUP_BASE_PRICE", "MONTH_YEAR=txt_TempDateField")
Total = Total + Temp
Next i
Ideally I don’t want to have to publish the TempDate on my form (even if it isn’t visible) and would just prefer to either embed the dateadd function or to have it separate without the intermediate step of publishing TempDate.
I have tried this with different formats, adding “#”, “”” etc but to no avail. I would greatly appreciate any help people could give me – am rather a newbie at access VBA and have hit a major stumbling block!
It’s also possible that there’s a much better way of doing this that doesn’t involve dlookup. Am open to any and all suggestions!
I am trying to write code to look up a date in column one of a table and average the second column from this date for a number of months entered by the user on a form. I want to present the average in a text box on the same form.
I.e. the table looks like
Table
Month Value
1/01/2002 1
1/02/2002 2
1/02/2003 3
etc.
The following code works:
(Note that txt_StartDate is a date formatted field on the form.)
Dim temp as string
Temp = DLookup("Value", "Table", "Month=txt_StartDate")
Me.txt_Average.value = Temp
As does:
Temp = DLookup("Value", "Table", "Month=Format(DateAdd(""m"", 1, txt_StartDate), ""dd-mmm-yyyy"")")
Me.txt_Average.value = Temp
However, I want to create a loop to add the value for the current month and the following n months (where n is a value entered by the user on the form) and average this total by dividing by n. E.g.
Dim i as integer
Total = 0
For i = 1 to n
Temp = DLookup("Value", "Table", "Month=Format(DateAdd(""m"", i-1, txt_StartDate), ""dd-mmm-yyyy"")")
Total = Total + Temp
Next i
Me.txt_Average.value = Total/n
However I keep getting error messages with this.
I have also tried to calculate the dateadd function outside of the dlookup function and pass the resulting date, e.g.:
For i = 1 to n
TempDate = dateadd(“m” , i-1, txt_StartDate)
Temp = DLookup("Value", "Table", "Month=TempDate")
Total = Total + Temp
Next i
In fact, the only way I can get this to work is to publish the TempDate on the form, and then pass this back to the Dlookup function, i.e.:
For i = 1 To n
TempDate = DateAdd("m", i - 1, txt_StartDate)
txt_TempDateField.Value = TempDate
Temp = DLookup("PRICE", "TOUP_BASE_PRICE", "MONTH_YEAR=txt_TempDateField")
Total = Total + Temp
Next i
Ideally I don’t want to have to publish the TempDate on my form (even if it isn’t visible) and would just prefer to either embed the dateadd function or to have it separate without the intermediate step of publishing TempDate.
I have tried this with different formats, adding “#”, “”” etc but to no avail. I would greatly appreciate any help people could give me – am rather a newbie at access VBA and have hit a major stumbling block!
It’s also possible that there’s a much better way of doing this that doesn’t involve dlookup. Am open to any and all suggestions!
