SELECT a product of two fields

[jaanisf]

I need an sql statement in my form which selects a product of two fields in a table and then counts them all together.
The statement should look as follows:

sum1 = &quot;SELECT Count([Field1] * [Field2]) WHERE [Field3] <> 'ml' AND [FieldDate] >= txtDate1 AND [FieldDate] <= txtDate2

sum2 = &quot;SELECT Count([Field1] * ([Field2]/100)) WHERE [Field3] = 'ml' AND [FieldDate] >= txtDate1 AND [FieldDate] <= txtDate2

txtSum = sum1 + sum2

When I input dates in Text Boxes txtDate1 and txtDate2, and press button btnCalculate, I need the sum of sum1 and sum2 to be filled automatically in a Text Box txtSum.

How should the code look like, to be written behind a button's btnCalculate on click event?

Thanks in advance.

 

[wtmckown]

There is no reason that I can see to use the count function on the product of an arithmatic operation like [Field1]*[Field2]. You could use the domain aggregate Dsum function to sum this stuff up. The following code could be used in the click event of your command button to assign a value to your text box or you could simply set the text box's control source to this. Good luck.

txtSum=dsum(&quot;([Field1]*[Field2])&quot;,&quot;TableName&quot;,&quot;[Field3]<>'ml' And [FieldDate] Between #&quot; & txtDate1 & &quot;# And #&quot; & txtDate2 & &quot;#&quot + dsum(&quot;(([Field1]*[Field2])/100)&quot;,&quot;TableName&quot;,&quot;[Field3]<>'ml' And [FieldDate] Between #&quot; & txtDate1 & &quot;# And #&quot; & txtDate2 & &quot;#&quot

 

[jaanisf]

Thanks wtmckown, but this one gives an error:

Run-time error '3075':
Syntax error in date in query expression '[Field3]<>'ml' And [FieldDate] Between #01.05.03.# And #04.05.03.#'.

 

[wtmckown]

I would check to make sure data types match. Make sure Field3 is text, and that FieldDate is type date. Also, is that a legal date format? &quot;MM.DD.YY.&quot; is one that I have not seen (trailing period?).

 

[jaanisf]

The date format is dd.MM.yy (short date form used in Latvia). Well this one works:

txtSum = DSum(&quot;([F1]*([F2]/100))&quot;, &quot;T1&quot;, &quot;[F3]='ml'&quot + DSum(&quot;([F1]*[F2])&quot;, &quot;T1&quot;, &quot;[F3]<>'ml'&quot

so, something's wrong with the dates.

 

[jaanisf]

Well, now this works

txtSum = DSum(&quot;([F1]*([F2]/100))&quot;, &quot;Table1&quot;, &quot;[F3]='ml' And [FDate] >= txtDate1 And [FDate] <= txtDate2&quot + DSum(&quot;([F1]*[F2])&quot;, &quot;Table1&quot;, &quot;[F3]<>'ml' And [FDate] >= txtDate1 And [FDate] <= txtDate2&quot

just.. when both txtDate1 and txtDate2 are equal, for example 01.05.03, then it does not count anything. Just like, when there were > and <, not >= and <=.

Any ideas?

 

[wtmckown]

Test it without the between operator....with a single date. Also.....have you considered the trailing period in the date from the run time error message that you pasted? You can also test it like this:

txtSum = DSum(&quot;([F1]*([F2]/100))&quot;, &quot;T1&quot;, &quot;[F3]='ml' and [FieldDate] Between #01/05/03# and #04/05/03#&quot + DSum(&quot;([F1]*[F2])&quot;, &quot;T1&quot;, &quot;[F3]<>'ml' and [FieldDate] Between #01/05/03# and #04/05/03#&quot

Hard coding the dates in this manner will tell you if the date format is the offender. If this works try hard coding 01.05.03. as a test.

 

[wtmckown]

The problem you describe with >= <= not getting the = selectionis usually caused by storing dates in general date format. This format includes the time and when compared to a date without the time they don't match. Check the date data in your table (with any masking disabled) to see if the time has been stored along with the date.