DSum With Variable expression based on TextBox Value

OasisUnknown

New Member
Joined
Oct 15, 2015
Messages
46
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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Don't put anything in quotes unless you want that to be the literal value.
Since you are expecting some result to change when you change a combobox value (why not just use the combobox value, anyhow) then you probably need to prompt a requery - the form won't automatically go and rerun queries just because you have change a combobox selection.
 
Upvote 0
Thanks for the reply Xenou,

I have already set up a docmd.requery on the combo box's after update event.

The reason why I need to use the text box is because I need to append a value on the end of the combobox value.

Ie the combo box value is 25B and I need to append a 1, 2, 3 on the end to identify the specific position that is required.

basically the combo box gives me a list of positions 25B, 35M ect ect. and then those position have a skill level on top of that. which is why I append the additional information.

I need to see all the skill levels at the same time rather than one at a time which is why I am working the data the way that I am.

In any case nothing I have tried is working with quotes or without quotes.

Dsum([Forms]![MySubForm]![MyTextBox], "myQuery")
and
Dsum("[Forms]![MySubForm]![MyTextBox]", "myQuery")

both fail with a #name error.
 
Upvote 0
It works for me without the quotes. for what it's worth.

Note, using afterUpdate in the first control (text0) to get a result in the second control (text4):


Code:
Private Sub Text0_AfterUpdate()
    Me.Text4 = DSum([Text0], "Query1")
End Sub


I wouldn't directly put this formula in a textbox itself because until something is chosen in the first control, there's a problem in the second one (no valid value to use in the first DSum argument).
 
Last edited:
Upvote 0
I had not resulted to using VBA just yet. I guess I could do that I was just trying to avoid as much coding as I could and have the database do the work through calculated controls rather than VBA.

Which is faster
A calculated control
or
a control that gets its value through vba code?

I guess a good example of this would be
Is Dcount faster to count records
or Could I count faster using VBA and record loops?
 
Upvote 0
The advantage with VBA here is controlling when the calculation occurs (after a value is chosen in the control, not before). That's all. Not sure about performance. I don't think you can put the formula in a textbox because until something is chosen there is not a real column to use in the DSUM formula. Or if you like you can ignore the #Name error and when you do choose a value then it probably go away (but that's a bit ugly even for me).
 
Upvote 0
*Update*

So I got my fields to finally start counting using this code.

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

all is almost fine.

Here is the next error that I am having. In my combo box that I am using to select my search criteria there are values that are not in my cross tab.

for example If I select the position 25B I get the expected result of 5. but If I select 48Q I get the error #Name?.
I know the reason for this error. It is because 48Q does not exist and wont ever exist in my cross tab query.

What I need help with is finding a solution to when I run into a #Name? value I just want to display 0.

I have tried wrapping my dsum value in a Nz but it did not fix the issue.

Code:
Nz(DSum([myTextBox].[Value],"[myQuery_CrossTab]",0))

I tried an isnull check but I might have been using it incorrectly.
I also tried and iif(iserror) check but again I could have been using it incorrectly.


Any one have any suggestions?
 
Upvote 0
So I just did a debug check with this code.

Code:
=IIf(IsNull(DSum([myTextBox].[Value],"[myQuery_CrossTab]")),"true","false")

If I choose a value that is in my query the textbox returns the value of false. which indicates that the expression check is not null.
if I choose a value that is not in my query I get a #Name? error. Indicating what is being returned is neither true nor null. it is something else.

The same thing happened with
Code:
=IIf(IsError(DSum([myTextBox].[Value],"[myQuery_CrossTab]")),"true","false")

indicating that the #Name? is neither a null or an error.
 
Last edited:
Upvote 0
I don't think there is a solution without vba. The column doesn't exist so the result isn't null, it's literally impossible to evaluate. From a business needs standpoint, if you want "all" values represented you need to seed the table with dummy values (zeros) for all the possible results, so that crosstab represents every field you want, even if there is no real data.

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).
 
Last edited:
Upvote 0
what would be the solution with VBA?

I am thinking a check to see if the column exists first. If it does then run the code if not then don't.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top