Question regarding 2 Fields in a combo box.

[FunkyFlea]

Hi,

I have a combo box acting as a filter for a form which contains data from 2 seperate fields using the following SQL code:

SELECT tblData.Field1 FROM tblData GROUP BY tblData.Field1 UNION SELECT tblData.Field2 FROM tblData GROUP BY tblData.Field2;

This combo box works fine in showing the data from both fields and when I select data from Field1 it gives me the correct results in my form. However when I select data from Field2 i get no results.

I currently have the following VB code and was wondering how I would implement Field 2 into it:

If Not IsNull(Me.cboFilter1) Then
strWhere = strWhere & "([Field1] = """ & Me.cboFilter1 & """) AND "
End If

I think thats how I would get this working? If not, let me know please.

Many thanks.

 

[Pauldohert]

You could put another column into the combo box ie


SELECT tblData.Field1, 1 as FieldType FROM tblData GROUP BY tblData.Field1 UNION SELECT tblData.Field2, 2 as Fieldtype FROM tblData GROUP BY tblData.Field2;


- !!!THis may affect how the group by works and the data in your combo!!!

Then


!!Where column(1) is the second column that you have just added column(0) being the existing column. Be sure to change the properties of the combo box to include the data for the second column and to adjust its width for display purposes.

If Not IsNull(Me.cboFilter1) Then
If Me.cboFilter1.Column(1) = "1" Then
strWhere = strWhere & "([Field1] = """ & Me.cboFilter1 & """) AND "
End If
If Me.cboFilter1.Column(1) = "2" Then
strWhere = strWhere & "([Field2] = """ & Me.cboFilter1 & """) AND "
End If
End If

Something like that may help???

 

[pbaldy]

The fact that you're combining 2 fields in the combo implies that they contain similar information, which sounds like a normalization problem. You could get around it by adding a second column (hidden) to the combo, which contains the field name. Then use that to determine which field to use in the WHERE clause.

 

[FunkyFlea]

Pauldohert:

Sorry I should of specified that I wanted Field2 seperate as opposed to a column alongside. If I dont manage to get it right I may have to use the column approach but it isnt really ideal for this type of data.

Thanks for your response.

 

[FunkyFlea]

The fact that you're combining 2 fields in the combo implies that they contain similar information, which sounds like a normalization problem. You could get around it by adding a second column (hidden) to the combo, which contains the field name. Then use that to determine which field to use in the WHERE clause.

Yes they contain the exact same information. I will try and see if I can do this hidden column. This may work.

Edit: I have no idea how to implement this...any help?

The data is cities and areas for example

Tbl
Field1 | Field2
City1 | North
City2 | North
City3 | East

The combo box is currently reading:
City1
City2
City3
North
East

....which is fine but the North and East selections do not give any results

Thanks.

 

[pbaldy]

Actually I think the other Paul's code would also work, but mine is more flexible. Add a second field containing the field name to the UNION, so the result is:

City1 Field1
City2 Field1
City3 Field1
North Field2
East Field2

Then your code would look like:

strWhere = strWhere & "(" & Me.cboFilter1.Column(1) & " = """ & Me.cboFilter1 & """) AND "

That's untested, but the concept is sound.

 

[FunkyFlea]

Actually I think the other Paul's code would also work, but mine is more flexible. Add a second field containing the field name to the UNION, so the result is:

City1 Field1
City2 Field1
City3 Field1
North Field2
East Field2

Then your code would look like:

strWhere = strWhere & "(" & Me.cboFilter1.Column(1) & " = """ & Me.cboFilter1 & """) AND "

That's untested, but the concept is sound.

Ok, you mean edit my SQL to read:

SELECT tblData.Field1,Field1 FROM tblData GROUP BY tblData.Field1 UNION SELECT tblData.Field2,Field2 FROM tblData GROUP BY tblData.Field2;

...and then set 2 columns?

Im a bit rusty with this, sorry. I do understand how that VB would work though.

 

[pbaldy]

I think you'll find that produces:

City1 City1
City2 City2
City3 City3
North North
East East

You want "Field1" AS FieldName

 

[FunkyFlea]

I think you'll find that produces:

City1 City1
City2 City2
City3 City3
North North
East East

You want "Field1" AS FieldName

You absolute legend! It works perfectly! I owe you one cos I been trying to get that sorted for a while now. (I do have another quick question regarding a different box but i'll post that as seperate.)

Thanks again.

 

[Pauldohert]

No worries!