DSum With Variable expression based on TextBox Value

[OasisUnknown]

Hello All,

Is it possible to use a DSUM to count a column of data off of a cross tab query using a textbox as a variable source.

I know the answer to the first part is yes (being able to use a DSUM to count a column of data off the query.)

=DSum("[25B1O]","qryAuthPosCount_CrossTab")

when I run this on a text box on my dashboard form I get the result 5. Which is what I expected to return.


After my base line check to see if it was possible I started to expand.

I linked up a combobox to display its value selected in a text box on the form so when I change selections the text box updates
(Ie. When I select 25B1O the textbox displays 25B1O, When I select 26A1O The textbox displays 26A1O)

I am trying to use that textbox to give a variable result to the textbox that holds the DSUM

Here are some examples of things that I have tried.

=DSum([Forms]![sfrmMOSISnapshot]![SL1MOSTxt],"[qryAuthMOSCount_CrossTab]")
=DSum([Forms]![sfrmMOSISnapshot]![SL1MOSTxt].[Text],"[qryAuthMOSCount_CrossTab]")

=DSum("[Forms]![sfrmMOSISnapshot]![SL1MOSTxt]","[qryAuthMOSCount_CrossTab]")
=DSum("[Forms]![sfrmMOSISnapshot]![SL1MOSTxt].[Text]","[qryAuthMOSCount_CrossTab]")

I have tried many other variations but nothing is working. I have even tried to cast the textbox to a string and then use that.

=DSum("CStr([Forms]![sfrmMOSISnapshot]![SL1MOSTxt].[Text])","[qryAuthPOSCount_CrossTab]")

Nothing is working except for when I hard Code the value into the DSUM
=DSum("[25B1O]","qryAuthPosCount_CrossTab")


Anyone have any suggestions on how I can base my DSum off the text that is stored in a textbox?

 

[OasisUnknown]

Otherwise, possible the question is why use crosstab at all? you want a single result. So just use a sum formula on the data (that's all crosstabs do - sum by two criteria, row and column).

Xenou, This statement got me to write my calculation in a different way and I was able to solve my current issue.

instead of

DSum([myTextBox].[Value],"[myQuery_CrossTab]")

I used

=Nz(DSum("[AUTHSTR]","[myTable]","[myPosition]=[SL1MOSTxt]"),0)

So basically my criteria went to the authorized source (the number I wanted to count).
I then took that directly from my table
and just did a criteria search on my variable.
wrapped that in an Nz and now everything works as I expected it too.