SQL and VBA - please help! (1 Viewer)

J

Jalnac74

Registered User.

Local time

Today, 09:46

Joined

Apr 4, 2015

Messages

23

• Nov 15, 2016

• #1

Hi all,

I have created a form which sends an email when the button is clicked. However, I want to use multiple criteria for an SQL string which selects the recipients of the email.

Basically the end user selects criteria from one, two or three combo boxes with the idea that the SQL is build depending on the values in these boxes.

My understanding is that it should look something like below:

Code:Copy to clipboard

varwhere = "[QueryField1]=" & Me.cmbone
varcriteria = "[QueryField2]=" & Me.cmbtwo

sqlstr = "Select * From [QueryName] where " & varwhere & " OR " & varcriteria

when I try to run the send email procedure I keep getting error messages that there is a mismatch in criteria expression.

HELP!!

 

Ranman256

Well-known member

Local time

Today, 12:46

Joined

Apr 9, 2015

Messages

4,337

• Nov 15, 2016

• #2

If your combo is a string you will get an error unless you wrap the string in quotes....
VWhere = "[Field]='" & cboBox & "'"

 

J

Jalnac74

Registered User.

Local time

Today, 09:46

Joined

Apr 4, 2015

Messages

23

• Nov 15, 2016

• #3

No they are both numerical fields, the combo returns a numerical value...

 

Ranman256

Well-known member

Local time

Today, 12:46

Joined

Apr 9, 2015

Messages

4,337

• Nov 15, 2016

• #4

If either is null you will get error.
If you have criteria in both shouldn't it be an AND? As is, you can never do an OR.

 

J

Jalnac74

Registered User.

Local time

Today, 09:46

Joined

Apr 4, 2015

Messages

23

• Nov 15, 2016

• #5

Ah right, that is where the error has come from then. sometimes there will be a null value for one of the criteria.

Is there anyway to write this code so that I can have one or both criteria selected and get the results I want?

 

Ranman256

Well-known member

Local time

Today, 12:46

Joined

Apr 9, 2015

Messages

4,337

• Nov 16, 2016

• #6

Code:Copy to clipboard

if not isnull(Me.cmbone) then vWhere = " and [QueryField1]=" & Me.cmbone 
if not isnull(Me.cmbtwo) then vWhere = vWhere & " and [QueryField2]=" & Me.cmbtwo

sqlstr = "Select * From [QueryName] where 1=1 " & vWhere

 

J

Jalnac74

Registered User.

Local time

Today, 09:46

Joined

Apr 4, 2015

Messages

23

• Nov 16, 2016

• #7

Hi Ranman,

this is great. This works with the first criteria only but I am now getting a Type Mismatch error when I have 2 criteria. I have printed the Sql string in the immediate window and get the result below.

Code:Copy to clipboard

print sqlstr
Select * From [caseemail] where 1=1 and [send stage id - current]=2 and [send criteria]=2

do you think the mismatch is caused from my query? as far as i can see all the data types are correct?

thanks again.

EDIT ** I have found the issue and fixed it. I had the wrong field in my query.

THANK YOU for your help!!

 

Last edited: Nov 16, 2016