SQL parameters VBA (2000/2002)

Topic

New Reply

I am trying to set the row source of a series of combo boxs in vba, where the value chosen in the ‘parent’ of a particular combo become the value used in the WHERE clause for the row source sql statement.

I’ve done this before and had it working the way I wanted.

The difficulty I’m having this time is that if the ‘parent’ combo is blank I want to return ALL records for my row source, not none.
I’ve had a look at some queries with parameters and can achieve this, what I can’t do is recreate this in vba. I’m sure I’m getting confused with my strings and having a zero length string when I really want a NULL.

Additionally, the value picked from cboE is a string, whereas the value from cboM is a NUMBER

Here’s myCode, where I have 3 combo boxes cboE, cboM and cboC.

for cboE the row source is:
strESQL = “SELECT DISTINCT tblDataTest.FieldE FROM tblTest”

for cboM the row source is:
strMSQL = “SELECT DISTINCT tblDataTest.fieldM FROM tblDataTest”
strMSQL = strMSQL & ” WHERE (tblTest.fieldE = ‘” & strE & “‘ Or (‘” & strE & “‘ Is Null))”
strMSQL = strMSQL & ” ORDER BY tblTest.fieldM;”

for cboC the row source is:
strCSQL = “SELECT DISTINCT tblDataTest.fieldC FROM tblDataTest”
strCSQL = strCSQL & ” WHERE (tblDataTest.fieldE = ‘” & strELR & “‘ Or (‘” & strE & “‘ Is Null))”
strCSQL = strCSQL & ” AND ((tblDataTest.fieldM = ” & intMile & ” Or (” & intMile & ” Is Null))”
strCSQL = strCSQL & ” ORDER BY tblDataTest.fieldC;”

so before anything is picked from cboE the row source is
SELECT DISTINCT tblDataTest.fieldM FROM tblDataTest WHERE (tblDataTest.ELR = ” Or (” Is Null)) ORDER BY tblDataTest.fieldM;
which obviously is not going to return all my rows as I know ” is not the same as null, but how can I get it to be null

similarly (for cboC)
SELECT DISTINCT tblDataTest.fieldC FROM tblDataTest WHERE (tblDataTest.fieldE = ” Or (” Is Null)) And (tblDataTest.Mileage = 0 Or (0 Is Null)) ORDER BY tblDataTest.fieldC;
as 0 isn’t null its not gonna work

Any ideas?, its the OR parts I’m stuck on. The examples use variables but I also had no luck with referencing the cbo values directly.

Reply | Quote

Replies

I noticed that you are using both tblTest and tblDataTest in the same SQL string strESQL and also in strMSQL. As far as I can see, you should use either one or the other. Since I don’t know which, I have left it as posted by you in the code below. Try

strMSQL = “SELECT DISTINCT tblDataTest.fieldM FROM tblDataTest” & _
” WHERE tblTest.fieldE = Forms!FormName!cboE Or Forms!FormName!cboE Is Null” & _
” ORDER BY tblTest.fieldM”

where FormName is the name of the form; if it contains spaces, enclose it in square brackets. I’m less sure about the next one, since I can’t visualize how the dependencies work.

strCSQL = “SELECT DISTINCT tblDataTest.fieldC FROM tblDataTest” & _
” WHERE (tblDataTest.fieldE = Forms!FormName!cboE Or Forms!FormName!cboE Is Null)” & _
” AND (tblDataTest.fieldM = Forms!FormName!cboC Or Forms!FormName!cboC Is Null)” & _
” ORDER BY tblDataTest.fieldC”

(In fact, you don’t need to set this in code)

Reply | Quote

Thanks Hans, the different table names were a typing error (the real table is something different altogether – I tried to shorten for clarity in the example but appear to have failed miserably )

I’m sure I have tried what you’ve suggested originally (as I built the query in the query grid and then copied the sql)

I’ll give it another go and let you know how it goes

Reply | Quote

Ok, its working, I’m not sure why it didn’t seem to be yesterday.

Out of curiosity, is there a way to make the parameter thing (with the nulls) work using variables like my first (posted attempt) or does it need to reference the controls so the values (if none are picked) are seen as NULL?

Reply | Quote

The alternative would be to create a different SQL string depending on the value of the combo box(es), for example

strMSQL = “SELECT DISTINCT tblDataTest.fieldM FROM tblDataTest”
If Not IsNull(Me.cboE) Then
strMSQL = strMSQL & ” WHERE tblTest.fieldE = ” & Chr(34) & Me.cboE & Chr(34)
End If
strMSQL = strMSQL & ” ORDER BY tblTest.fieldM”

The WHERE part is only added if cboE is not empty.

Reply | Quote

Thanks for clearing that up.

I’ve just used something similar to get the sql string for a record source to be used for a report.

The purpose of the combos was to allow the user to choose the filter criteria they wanted and to ensure they can’t choose conflicting criteria, getting no records returned.

The form now has 6 combos, each potentially dependent on those ‘above’ so by the 6th, the WHERE part of the statement could comprise six elements of none, and of course everything needs refreshing when something above it changes. Whats your opinion on the most appropriate method to set the row source for the combos, this ‘alternative’ or the original solution?

Everything now seems to be working fine by the way, your help was much appreciated (as always)

Reply | Quote

In theory, the “alternative” is slightly more efficient, since the SQL is more specific, but in practice, I think the difference will not be noticeable. I would just use whatever works for you, or you feel most comfortable with.

Reply | Quote

(Edited by HansV to indent code – see next reply)

I thought I try it but then thought it looked a bit complicated with the requirement for a number of nested ‘ifs’

The example below would be for cboC where there could be a WHERE and an AND from the 2 higher combos (E and M) – I’ve renamed the sql variable for clarity

strSQL = “SELECT DISTINCT tblDataTest.fieldC FROM tblDataTest”
If Not IsNull(Me.cboE) Then
strSQL = strSQL & ” WHERE tblDataTest.fieldE = ” & Chr(34) & Me.cboE & Chr(34)
If Not IsNull(Me.cboM) Then
strSQL = strSQL & ” AND tblDataTest.fieldM = ” & Chr(34) & Me.cboM & Chr(34)
End If
Else
If Not IsNull(Me.cboM) Then
strSQL = strSQL & ” WHERE tblDataTest.fieldM = ” & Chr(34) & Me.cboE & Chr(34)
End If
End If
strSQL = strSQL & ” ORDER BY tblDataTest.fieldC”

Couldn’t this get horribly complicated looking where 6 combos are involved?
ps how do you indent code in the forum?

Reply | Quote

1. There are two ways to indent code:

• Put

   before and 

  after the block of code. The code will be displayed “as is” in a fixed-width font (probably Courier New.)

• Use [tab] tags to indent.
  [/list]Both types of tag can be typed directly, or obtained from the 1-Click TagPanel link above the edit area.

  2. To avoid endless nesting of If Then statements, I would do it like this:

  Dim strSQL As String
  Dim strWhere As String
  strWhere = “”
  If Not IsNull(Me.cboE) Then
  strWhere = strWhere & ” AND tblDataTest.fieldE = ” & Chr(34) & Me.cboE & Chr(34)
  End If
  If Not IsNull(Me.cboM) Then
  strWhere = strWhere & ” AND tblDataTest.fieldM = ” & Chr(34) & Me.cboM & Chr(34)
  End If
  ‘ Repeat for other combo boxes if necessary
  If strWhere “” Then
  ‘ Replace initial ” AND” by ” WHERE”
  strWhere = ” WHERE” & Mid$(strWhere, 5)
  End If
  strSQL = “SELECT DISTINCT tblDataTest.fieldC FROM tblDataTest” & _
  strWhere & ” ORDER BY tblDataTest.fieldC”

Reply | Quote

So you’re using mid without specifying an end and just sticking WHERE at the start – pretty obvious really. Funny how I often seem to go the complicated route – cheers

Reply | Quote

So you’re using mid without specifying an end and just sticking WHERE at the start – pretty obvious really. Funny how I often seem to go the complicated route – cheers

Reply | Quote

1. There are two ways to indent code:

• Put

   before and 

  after the block of code. The code will be displayed “as is” in a fixed-width font (probably Courier New.)

• Use [tab] tags to indent.
  [/list]Both types of tag can be typed directly, or obtained from the 1-Click TagPanel link above the edit area.

  2. To avoid endless nesting of If Then statements, I would do it like this:

  Dim strSQL As String
  Dim strWhere As String
  strWhere = “”
  If Not IsNull(Me.cboE) Then
  strWhere = strWhere & ” AND tblDataTest.fieldE = ” & Chr(34) & Me.cboE & Chr(34)
  End If
  If Not IsNull(Me.cboM) Then
  strWhere = strWhere & ” AND tblDataTest.fieldM = ” & Chr(34) & Me.cboM & Chr(34)
  End If
  ‘ Repeat for other combo boxes if necessary
  If strWhere “” Then
  ‘ Replace initial ” AND” by ” WHERE”
  strWhere = ” WHERE” & Mid$(strWhere, 5)
  End If
  strSQL = “SELECT DISTINCT tblDataTest.fieldC FROM tblDataTest” & _
  strWhere & ” ORDER BY tblDataTest.fieldC”

Reply | Quote

(Edited by HansV to indent code – see next reply)

I thought I try it but then thought it looked a bit complicated with the requirement for a number of nested ‘ifs’

The example below would be for cboC where there could be a WHERE and an AND from the 2 higher combos (E and M) – I’ve renamed the sql variable for clarity

strSQL = “SELECT DISTINCT tblDataTest.fieldC FROM tblDataTest”
If Not IsNull(Me.cboE) Then
strSQL = strSQL & ” WHERE tblDataTest.fieldE = ” & Chr(34) & Me.cboE & Chr(34)
If Not IsNull(Me.cboM) Then
strSQL = strSQL & ” AND tblDataTest.fieldM = ” & Chr(34) & Me.cboM & Chr(34)
End If
Else
If Not IsNull(Me.cboM) Then
strSQL = strSQL & ” WHERE tblDataTest.fieldM = ” & Chr(34) & Me.cboE & Chr(34)
End If
End If
strSQL = strSQL & ” ORDER BY tblDataTest.fieldC”

Couldn’t this get horribly complicated looking where 6 combos are involved?
ps how do you indent code in the forum?

Reply | Quote

In theory, the “alternative” is slightly more efficient, since the SQL is more specific, but in practice, I think the difference will not be noticeable. I would just use whatever works for you, or you feel most comfortable with.

Reply | Quote

Thanks for clearing that up.

I’ve just used something similar to get the sql string for a record source to be used for a report.

The purpose of the combos was to allow the user to choose the filter criteria they wanted and to ensure they can’t choose conflicting criteria, getting no records returned.

The form now has 6 combos, each potentially dependent on those ‘above’ so by the 6th, the WHERE part of the statement could comprise six elements of none, and of course everything needs refreshing when something above it changes. Whats your opinion on the most appropriate method to set the row source for the combos, this ‘alternative’ or the original solution?

Everything now seems to be working fine by the way, your help was much appreciated (as always)

Reply | Quote

The alternative would be to create a different SQL string depending on the value of the combo box(es), for example

strMSQL = “SELECT DISTINCT tblDataTest.fieldM FROM tblDataTest”
If Not IsNull(Me.cboE) Then
strMSQL = strMSQL & ” WHERE tblTest.fieldE = ” & Chr(34) & Me.cboE & Chr(34)
End If
strMSQL = strMSQL & ” ORDER BY tblTest.fieldM”

The WHERE part is only added if cboE is not empty.

Reply | Quote

Ok, its working, I’m not sure why it didn’t seem to be yesterday.

Out of curiosity, is there a way to make the parameter thing (with the nulls) work using variables like my first (posted attempt) or does it need to reference the controls so the values (if none are picked) are seen as NULL?

Reply | Quote

Thanks Hans, the different table names were a typing error (the real table is something different altogether – I tried to shorten for clarity in the example but appear to have failed miserably )

I’m sure I have tried what you’ve suggested originally (as I built the query in the query grid and then copied the sql)

I’ll give it another go and let you know how it goes

Reply | Quote