If- then statement to check if input is from a lookup column

[JasonWinchester]

Hi, all

I'm having trouble figuring out how to use an if-then statement to check whether or not a variable is pointing to a look-up column.

What I have is a form that contains a combobox and a text box. The combobox contains the list of fields available in my table. When a field is selected, the user then enters text in to the text box. These two inputs create a query.

The problem is that some of my fields are look-up columns, and some are not. If I insert ".value" into the SQL, it works great IF the field is a look-up column, but not if it's just a regular field. If I remove the ".value", it works great IF the field is a regular field, but not if it is a look-up column.

So is there a way to say:
If a = look-up column then
use this SQL
Else
use this SQL
End If

??

Here's the code:

 
Private Sub Command0_Click()
 
 
On Error Resume Next
 
Dim dbs As Database
Dim Rs As DAO.Recordset
Dim a As String
Dim b As String
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef
 
Set dbs = CurrentDb
 
a = Me.Combo1
b = Me.Text7
strQueryName = "myQuery"
 
 
dbs.QueryDefs.Delete strQueryName
 
If '**What do I put here?** Then
    'lookup column
    strSQL = "SELECT ProjectRegForm.*, ProjectRegForm." & a & " FROM ProjectRegForm WHERE (((ProjectRegForm." & a & ".Value)LIKE '*" & b & "*'))"
 
    Else
    'not lookup column
    strSQL = "SELECT ProjectRegForm.*, ProjectRegForm." & a & " FROM ProjectRegForm WHERE (((ProjectRegForm." & a & ")LIKE '*" & b & "*'));"
 
End If
 
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
 
DoCmd.OpenQuery "myQuery", acViewNormal
 
End Sub
 

Thanks for having a look at this!

Cheers,
Jason

[MikeTheBike]

Hi

I think it must boil down to the "Values" returned from the bound column of Combo1.

You may need to tweek the Control Source query for the combo list.

What are the different values returned from Combo1 for each case, and which work for which case?


MTB

[JasonWinchester]

Thanks for your reply, Mike.
Actually, I figured this out late last night.

What I did was, I added this to the declarations area:

Dim tdf as DAO.Tabledef
set tdf = Dbs.tabledefs(ProjectRegForm)

and then I used this as my if-then statement:

If tdf.Fields(a).Type = dbText Then
 
   'not lookup column
    strSQL = "SELECT ProjectRegForm.*, ProjectRegForm." & a & " FROM ProjectRegForm WHERE (((ProjectRegForm." & a & ")LIKE '*" & b & "*'))"
 
    Else
    'lookup column
    strSQL = "SELECT ProjectRegForm.*, ProjectRegForm." & a & " FROM ProjectRegForm WHERE (((ProjectRegForm." & a & ".value)LIKE '*" & b & "*'));"
 
End If
 

It checks to see if the field is regular text, so if it is a look-up column (complex text) it uses the Else SQL.

Now I can probably put in an else-if statement for dates and numbers, as well - not sure what I should change ".value" to for that, or if I even need to change it.

Thanks to anyone who had a look at this and tried to figure it out!

Cheers,
Jason

[JasonWinchester]

Oh, and I just realized that you should put the field name (variable "a") in square brackets. That solves the problem of field names with spaces in them.