I have a formula that at first glance I thought would be simple to do as an expression:
Take a range of days for two fields and multiply their values for each day. Then add each of those values to the other days. Finally, divide by the sum value of one of the fields.
you have a table that hold these fields: [Date], [numItems], [numBuys]
first you want to calculate this for every date: MultiplyXX = [numItems]*[numBuys]
and last you want to calculate this between two dates: Sum(MultiplyXX)/Sum([numItems])
I think you should use a query (Or QueryString) for this: "Select (Sum([numItems]*[numBuys]))/(Sum([numItems])) As [XX] WHERE [Date] >= #" & Date1 & "# AND [Date] <= #" & Date2 & "# "
SELECT
Sum(a.numItems) AS SumOfnumItems,
Sum(a.NumItemsXnumBuys) AS SumOfNumItemsXnumBuys,
[SumOfNumItemsXnumBuys]/[CountOfnumItems] AS TheResult
FROM
(
SELECT
myTable.DayDate,
myTable.numItems,
myTable.numBuys,
[numItems]*[numBuys] AS NumItemsXnumBuys
FROM
myTable
WHERE
(((myTable.DayDate)>=#4/1/2012# And (myTable.DayDate)<=#4/2/2012#));
) AS a;
I am unsure of the dates still since they would not be definitive...
yes, Date1 and Date2 are StartDate and EndDate
They are joined the string as parmameters. That's why you write it like this: #" & Date2 & "#
here is a full function for you
Code:
Function fnCalcWhatINeed(StartDate as date, EndDate as sate) as double
dim strQRY as string
dim sunItems as long
dim sumItemsBuys as long
strQRY = [COLOR=#8b0000]"Select [numItems], [numItems]*[numBuys] As [ItemsBuys] " & _
"From [Yourtable] " & _[/COLOR]
[COLOR=#8b0000]"WHERE Format([ItemDate], 'yyyymmdd') >= " & Format(StartDate, "yyyymmdd") & " AND Format([ItemDate], 'yyyymmdd') <= " & Format(EndDate, "yyyymmdd")[/COLOR]
[COLOR=black]sunItems = DSUM("[NumItems]", strQRY)[/COLOR]
sumItemsBuys = DSUM("[ItemsBuys]", strQRY)
fnCalcWhatINeed = sumItemsBuys/sumItems
end function
One does not convert dates to yyyymmdd strings then compare them alphanumerically. That is hideously bad firstly because the date in every record would need to be converted and secondly because ordering by strings is inefficient.
The format should be applied to the parameter variables so the function is applied just once for each parameter. The date field should be indexed in the table so the index can be used to select the matching records.
Code:
"WHERE [ItemDate] BETWEEN " & Format(StartDate, "\#mm\/dd\/yyyy\#") & " AND " & Format(EndDate, "\#mm\/dd\/yyyy\#")
This expression can easily be one hundred times faster and better as the number of records grows.
These date things caused me so much headache till now
I use dates as dd/mm/yyyy (Non all the world are from US) and it caused me so much troubles - sorting, updating, comparing.
What I use now work for me.
If I try to order by dates, as they are now, I get wrong results.
I'll try what you suggest for compering, but what about ordering ?
Indexes are very nice but they are blowing the db real quick
I have actually taken a different approach, but still arrive at a problem.
I create a field in the query that multiplies the two fields.
I then create a textbox and add an expression to take the created field and divide it by the total sum of the first field.
However, it only multiplies the Start Date, and no other dates. I'm trying to figure out how to tell the created field in the query to use all dates, not just the start date.
Firstly it is important to realise that dates are held in Access tables as a Double datatype with the integer section being the number of days since 12/30/1899. The decimal part is the time as fractions of a day.
Access then displays these dates using the system date format for date fields, date controls and when creating queries within the designer. Comparison and ordering using dates within controls and fields is handled automatically.
However when it comes to the actual SQL, Access (or more precisely, JET/ACE, the system database engine) uses #mm/dd/yyyy#.
When referring to a control or field, Access converts the date to the correct SQL format before passing to the database engine. Consequently the following works everywhere.
Code:
SELECT whatever FROM sometable WHERE datefield = Forms!myform.datecontrol
Provide the dates are held as dates rather then strings then ordering should work correctly.
However constructing the SQL directly in VBA is a different matter. The following will simply pass the control value as a string in the regional date format. It won't work sensibly at all.
Code:
"SELECT whatever FORM sometable WHERE datefield=" & Forms!myform.datecontrol
The follwing is what is required:
Code:
"SELECT whatever FORM sometable WHERE datefield=" & Format(Forms!myform.datecontrol, "\#mm\/dd\/yyyy\#")
The hash delimiters are required to indicate ithe value is a date otherwise it will be treated as numeric expression using the division operator.
mm/dd/yyyy is required because that is the native SQL format. In the USA developers can get away with just adding the hashes but their code will fail internationally.
A backslash in the format string indicates the next character is literal. The format string used with dates is often seen without the backslash before the forward slashes and many developers wonder why some of us use these literals.
It is because the forward slash without the literal is actually a placeholder for the regional date separator. In the regions that don't use the forward slash the format will be rendered incorrectly for the database engine. Only by literally inserting the forward slash can the code be (almost) sure work internationally.
I said "almost" because there is a little known gotcha. In some languages the d, m and y are not the placeholders for day, month and year. For example in Greek the m will be used literally because the format string requires the first character of the Greek word for month.
(I don't know that this is the case in the format function itself but it is certainly the case in the Format property string used with controls and fields.)
BTW JET/ACE also accepts the ISO format. "#yyyymmdd#"
BTW Dates in MS SQL Sever and ADO don't use the hash but the single quote. Dates should be formatted:
To add a little to Galaxiom's excellent treatise: the culprit is the SQL-requirement for US date and number formats, paired with locale-dependent casting to string.
When you write an SQL string in code, all the exposed variables are converted to strings, according to you locale settings; numbers, dates .. the whole enchillada. This bites you when dealing with dates outside US, or when dealing with decimal numbers using "," as decimal separator.
In similarity with dates, decimal numbers with "," as separator will confuse the heck out of the SQL.
DIm a as Double
a=1,5 ' (in a country where "," is used as decimal separator)
Eg :
strSQL= "UPDATE tblSomeTable SET someValue= " & a
This will then become :
"UPDATE tblSomeTable SET someValue= 1,5"
and when passed to DoCmd.RunSql strSQL
Access will wail about that comma.
For decimal numbers the cure is to wrap them in the function Str, which always uses "." as decimal separator.
so
strSQL= "UPDATE tblSomeTable SET someValue= " & Str(a)
will become
"UPDATE tblSomeTable SET someValue= 1.5"
Like for dates, the problem arises only when you have a conversion to string (like you do, when writing SQL in code). In a stored query, there is no such conversion and hence no problem.
Another related issue is that regions with the comma as a decimal separator use semicolons instead of commas to separate arguments, for obvious reasons.
I expect the implications of these regional difference is that an mdb or accdb needs to be compiled on a system with the same regional settings as where it was designed. Presumably the resulting mde or accde will be intrnationally compatible.
I have 2 unbound TextBox. Both are formated as dd/mm/yyyy
If I try to compare them (Making sure EndDate is higher then StartDate) using this I get wrong results:
Code:
if Format(Me.StartDate, "\#mm\/dd\/yyyy\#") > Format(Me.EndDate, "\#mm\/dd\/yyyy\#") Then
....
The Format is only done where the dates are being passed to an SQL query string. Remember that the Format function returns a string. We need a string because SQL queries are strings too.
If you are directly comparing the dates between date fields or controls then simply compare the two values directly.
Code:
If Me.StartDate > Me.EndDate Then
One thing to watch for with comparisons is where the value also contains a time component. This will often leave out a whole day of records if not taken into account.
