By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,961 Members | 2,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,961 IT Pros & Developers. It's quick & easy.

Using VBA to define variable for query criteria

P: 4
Hi

Basicaly i have a combo box with two options "ug" and "dh". this is the VBA code i have so far:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cbosample_AfterUpdate()
  2.  
  3. Dim SampleType As String
  4.  
  5. If Me.cbosample = "dh" Then
  6.     SampleType = "Like dh"
  7. Else
  8.     SampleType = "Not Like dh"
  9. End If
  10.  
  11. End Sub

So if "dh" is selected in the combo box then the query criteria will be equivlaent to Like "dh" and if "ug is selected in the combo box then the query criteria will be equivalent to Not Like "dh"

Below is the code is used to reference the variable in the query. In the query criteria i put GetSampleType()


Expand|Select|Wrap|Line Numbers
  1. Public Sub SetSampleType(Value As String)
  2.  
  3. SampleType = Value
  4.  
  5. End Sub
  6.  
  7. Public Function GetSampleType()
  8.  
  9. GetSampleType = SampleType
  10.  
  11. End Function

Thanks
Apr 30 '13 #1

✓ answered by Rabbit

You could use an iif or switch to change the comparison value so that each is grouped under the correct category.

Share this Question
Share on Google+
7 Replies


Seth Schrock
Expert 2.5K+
P: 2,941
I'm not sure what your question is. Can you let us know what you need help with as well as what isn't working and the results (ie. error message along with the EXACT wording and number, or invalid values).

Also, please use the [CODE/] button when posting code.
Apr 30 '13 #2

Rabbit
Expert Mod 10K+
P: 12,366
Please use code tags when posting code.

A query does not see "Like 'dh'" as SQL. It sees it as a literal string. You will have to edit the query definition itself if you want to do it that way.

But I don't understand the need for any code at all. You can just do this directly in the query:
Expand|Select|Wrap|Line Numbers
  1. fieldName = Forms!formName!controlName
Apr 30 '13 #3

P: 4
Ok, i am not getting any error messages, but the query criteria does not seem to be enforced as the query table does not return any values.

Let me know if this is not clear and I'll try again.
Apr 30 '13 #4

P: 4
Ok, thanks.

The need for the code is because the "ug" value in the combo box has to encompass several different values which would be easiest described as all values except for "dh". ie in my table i have values "dh", "fc", "b", "st" and i want to group all values except "dh" under the "ug" combo box value.

I have been editing the query box manually for quite a while to get what i needed, but i am new to VBA and just trying to speed things up a bit using combo boxes and VBA code.

let me know if any of this doesn't make sense

Thanks.
Apr 30 '13 #5

Rabbit
Expert Mod 10K+
P: 12,366
You could use an iif or switch to change the comparison value so that each is grouped under the correct category.
Apr 30 '13 #6

P: 4
Thanks Rabbit, got it sorted, just calculated a new query field
Expand|Select|Wrap|Line Numbers
  1. SampleType: IIf([collar]![type]="dh","dh","ug")
then used the value from the combo box as criteria.
Expand|Select|Wrap|Line Numbers
  1. [forms]![f_create_surpac_str]![cbosample].[value]
Apr 30 '13 #7

Rabbit
Expert Mod 10K+
P: 12,366
Glad you got it working!
May 1 '13 #8

Post your reply

Sign in to post your reply or Sign up for a free account.