Data mismatch message

[Qtip23]

I am having trouble getting my SQL string to operate correctly. I have modified an online application for my use but I think because the fields were all text. I continuously receive an Error 13: Type mismatch.

I have three list boxes, one to capture the ID number (number), the City (text) and Area (text).
There is a button that pulls all the responses in the following variables:

strID
strCity
strArea


The SQL string is the following:

strSQL = "SELECT tbMaster_A.* FROM tbPrograms " & _
         "WHERE tbMaster_A.[GrID] = " & strID & _
         strCityCondition & "tbMaster_A.[City]" & strCity & _
             strAreaCondition & "tbMaster_A.[Area]" & strArea & "';"

The following variables represent the users selection of AND or OR when creating the query. So if the user selects the AND option button for a query, then the following string variables becomes an AND (or OR).

strCityConditio n
strAreaConditio n


I thought by making the string equaling the variable would resolve it. When I removed the equals sign and replaced with the ampersand &, to concatenate the string.

There was no change.


How can I make the [GrID] string accept a numeric user entry?

Thanks.

[Rabbit]

If this is how you're building youre SQL string,

"SELECT tbMaster_A.* FROM tbPrograms " & _ 
"WHERE tbMaster_A.[GrID] = " & strID & _ 
strCityCondition & "tbMaster_A.[City]" & strCity & _ 
strAreaCondition & "tbMaster_A.[Area]" & strArea & "';"

Assuming

strID = 1
strCity = LA
strCityCondition = Okay
strArea = East
strAreaCondition = Bad

Then here's what it would output

SELECT tbMaster_A.* FROM tbPrograms WHERE 
tbMaster_A.[GrID] = 1OkaytbMaster_A.[City]LABadtbMaster_A.[Area]East';

The extra line in there was only for formatting purposes.

[Qtip23]

@Rabbit
Yep, I see where you are going. Actually, the and strAreaConditio n and strCityConditio n represents the selection from the option button.

These conditions represent a logical AND or OR.

So let's say we have the following string variable values,

strID = 2 
strCity = LA 
strCityCondition = AND 
strArea = East
strAreaCondition = AND

The query should report all the County information who fit the strID equal to 2.

When i run the query in the Access database window, I have no problems.

I use the following:

SELECT tbMaster_A.*
FROM tbPrograms
WHERE (((tbMaster_A.[GrID]=2) AND ((tbMaster_A.[City]) Like '*') AND ((tbMaster_A.[Area]) Like '*'));

So maybe it is something else, right?

Thanks,

[Rabbit]

No, the problem stays the same. Given that the condition variables are logical operators. What you get is

SELECT tbMaster_A.* FROM tbPrograms WHERE  
tbMaster_A.[GrID] = 1ANDtbMaster_A.[City]LAANDtbMaster_A.[Area]East';

[Qtip23]

Ok, so I think I am getting closer to the answer.

I did forget to mention that this query is based on a one to many relationship.

So, the tbPrograms is on the one side and tbMaster_A is on the many side. They are related by the GrPrgID field which is the PK of tbPrograms and FK of the tbMaster_A table.

I went back to look at the query in Access, so now I have the following:

PARAMETERS [forms]![frmA]![lstGr] Long;
SELECT tbMaster_A.*,tbPrograms.ID, tbPrograms.Region, tbPrograms.StateReg
FROM tbPrograms INNER JOIN tbMaster_A ON tbPrograms.GrPrgID = tbMaster_A.GrPrgID
WHERE (((tbMaster_A.GrPrgID)=[forms]![frmA]![lstGr])) AND ((tbMaster_A.Region) Like "*") AND ((tbMaster_A.StateReg) Like "*"));

When I actually place values in the place ID, Region or State, then I receive a query output.

It's not looking good on the coding end. Can an inner join statement be placed in VBA code when building a SQL string?

[Rabbit]

Yes, you can build any SQL string in VBA code.

[Qtip23]

So while I am not getting any syntax error messages on my SQL string after clicking on button.

strSQL = "SELECT tbMaster_A.* FROM tbPrograms " & _
             "INNER JOIN tbMaster_A ON tbPrograms.GrPrgID=tbMaster_A.GrID " & _
             "WHERE tbPrograms.[GrID] = " & strID & _
             strCityCondition & "tbMaster_A.[City] " & strCity & _
             strAreaCondition & "tbMaster_A.[Area] " & strArea & ";"

However, I am getting a run-time error message 94: Invalid use of Null. Any ideas on why I am receiving this message, especially since I thougt I removed any text references in my SQL string?

' Build criteria string for Program
    For Each varItem In Me.lstGr.ItemsSelected
        'strID = strID & ",'" & Me.lstGr.ItemData(varItem) & "'"
        strID = Me.lstGr.ItemData(varItem)
    Next varItem
    
    If Len(strID) = 0 Then
        'strID = "Like '*'"
        strID = Me.lstGr.ItemData(varItem)
'    Else
'        strID = Right(strID, Len(strID))
'        strID = "IN(" & strID & ")"
    End If
' Build criteria string for City
    For Each varItem In Me.lstCity.ItemsSelected
        strCity = strCity & ",'" & Me.lstCity.ItemData(varItem) & "'"
    Next varItem
    If Len(strCity) = 0 Then
        strCity = "Like '*'"
    Else
        strCity = Right(strCity, Len(strCity) - 1)
        strCity = "IN(" & strCity & ")"
    End If
' Build criteria string for Area
    For Each varItem In Me.lstArea.ItemsSelected
        strArea = strArea & ",'" & Me.lstArea.ItemData(varItem) & "'"
    Next varItem
    If Len(strArea) = 0 Then
        strArea = "Like '*'"
    Else
        strArea = Right(strArea, Len(strArea) - 1)
        strArea = "IN(" & strArea & ")"
    End If
' Get City condition
    If Me.optAndRegion.Value = True Then
        strCityCondition = " AND "
    Else
        strCityCondition = " OR "
    End If
' Get Area condition
    If Me.optAndState.Value = True Then
        strAreaCondition = " AND "
    Else
        strAreaCondition = " OR "
    End If

Thanks,

[Rabbit]

You should print out the whole sql to debug and then see if that runs in a regular query. That will tell us whether the issue lies with the query string or with the VBA code.

[NeoPa]

Lots can be done with a SQL string when working in VBA. Have a quick look at How to Debug SQL String and think about comparing the SQL in your QueryDef with the SQL in the string as produced by your code.

Are they similar?
Where do they differ?

[Qtip23]

@Rabbit @NeoPa
I found the link discussing the testing/debugging items to be a wealth of information, especially the use of the debug.print throughout my code.

I have been using it to see the values of my variables and form control selections. What I have gathered based on my complex queries is that I need to rethink my table relationships. So for now, I am going back to the drawing board (for a minute).

Thanks

[NeoPa]

Sounds like a very intelligent and sensible reaction to me. I'm glad you found the link helpful :-)