Dynamic recordsource SQL statement - help -

[fluffyvampirekitten]

I have a combo box (cbo_source)
The choices are : SAP, EPOS , All
(cbo_category) : Potential Dup , Wrong Reg

Basically, When user click , "SAP" and "Potential Dup" .
I wanna show all the duplicate records under "SAP"

I have this code

Public Sub Data_Retrived3()
Dim DB As String
Dim DataSource3 As String
Dim C_SOURCE, c_Category, C_QUERYTYPE, c_FieldMatched, c_insitution As String
On Error GoTo ErrorHandler
    'DB = "CrossSystemData"
    
    c_Category = "='" & cbo_category & "'"
    
    'If cbo_source = "ALL" Then
   '    C_SOURCE = "Is Not Null"
   ' Else
   '     C_SOURCE = "='" & cbo_source & "'"
   ' End If
    
    If cbo_Type = "ALL" Then
        C_QUERYTYPE = "Is Not Null"
    Else
        C_QUERYTYPE = "='" & cbo_Type & "'"
    End If
    
    If cbo_FieldMatched = "ALL" Then
        c_FieldMatched = "Is Not Null"
    Else
        c_FieldMatched = "='" & cbo_FieldMatched & "'"
    End If
    
    If cbo_institution = "ALL" Then
        c_institution = "Is Not Null"
    Else
        c_institution = "like '" & cbo_institution & "*'"
    End If
    
    
   DataSource3 = "SELECT * FROM CrossSystemData " _
                & " WHERE C_TYPE = 'CROSS' AND C_SOURCE = 'SAP' AND " & _
                 " Category " & c_Category & " AND Query_Type " & C_QUERYTYPE & _
                 " AND FieldMatched " & c_FieldMatched & " AND Institution " & c_institution & " AND "
                 -I'm Stuck here-
                 
                 
                 
                 
                 
                
   
                        
    RecordSource = DataSource3
    
    DoCmd.Requery
    
    
ErrorHandlerExit:
    Exit Sub
    
ErrorHandler:
    MsgBox "Error No: " & Err.Number & " ;  Description: " & _
    Err.Description
Resume ErrorHandlerExit
    
End Sub

Based on https://support.office.com/en-us/art...re_sample_data

I created the below statement , however , it was highligted in red when i copy over to access

DataSource3 = "SELECT * FROM CrossSystemData 
WHERE C_TYPE = 'CROSS' AND C_SOURCE = 'SAP' AND " & _
                 " Category " & c_Category & " AND Query_Type " & C_QUERYTYPE & _
                 " AND FieldMatched " & c_FieldMatched & " AND and Institution " & c_institution & " 
IN ( SELECT [NAME] FROM [CrossSystemData] As Tmp
GROUP BY CrossSystemData.[GROUP_NO], CrossSystemData.[C_TYPE], CrossSystemData.[C_SOURCE], 
   CrossSystemData.[CATEGORY], CrossSystemData.[QUERY_TYPE], CrossSystemData.[FIELDMATCHED], 
   CrossSystemData.[FIELDUSED], CrossSystemData.[SOURCE], CrossSystemData.[P_NBR], 
   CrossSystemData.[PATIENT_ID], CrossSystemData.[RESIDENT_TYPE], CrossSystemData.[ID_TYPE], 
   CrossSystemData.[NATIONALITY], CrossSystemData.[NAME], CrossSystemData.[DOB], 
   CrossSystemData.[GENDER], CrossSystemData.[RACE], CrossSystemData.[MARITAL_STATUS], 
   CrossSystemData.[LANGUAGE], CrossSystemData.[ADD_ID], CrossSystemData.[HSE_NBR], 
   CrossSystemData.[UNIT_LEVEL], CrossSystemData.[UNIT_NBR], CrossSystemData.[POSTAL_CODE], 
   CrossSystemData.[STREET], CrossSystemData.[COUNTRY], CrossSystemData.[ADD_CD], 
   CrossSystemData.[DOB_GEND_CD], CrossSystemData.[DOCUMENT_NO], CrossSystemData.[CREATE_USER], 
   CrossSystemData.[CREATE_DATE], CrossSystemData.[LOADED_DATE], CrossSystemData.[FORMATTED_NAME], 
   CrossSystemData.[S_EPOS], CrossSystemData.[S_SAP], CrossSystemData.[ID_CHECK], 
   CrossSystemData.[FV_DATE], CrossSystemData.[FV_INS], CrossSystemData.[FV_DEPT], 
   CrossSystemData.[LV_DATE], CrossSystemData.[LV_INS], CrossSystemData.[LV_DEPT], 
   CrossSystemData.[INSTITUTION]
HAVING Count(*) > 1 AND [DOB] = CrossSystemData.[DOB]
AND [GENDER] = CrossSystemData.[GENDER]
AND [POSTAL_CODE] = CrossSystemData.[POSTAL_CODE])))
ORDER BY CrossSystemData.[NAME] , CrossSystemData.[DOB],
CrossSystemData.[GENDER] , CrossSystemData.[POSTAL_CODE] ;

[ranman256]

Test all controls for a possible filter then build the where clause.

if not isnull(cbo_source) then   sWhere = sWhere & " and [Source]='" & cbo_source & "'"
if not IsNull(cbo_category) then    sWhere = sWhere & " and [Cata]='" & cbo_category & "'"


    'remove 1st And
if len(sWhere)>0 then sWhere= mid(sWhere,5)


  'just use the filter
iLen = Len(sWhere) - 5
If iLen <= 0 Then
    me.filterOn = false
Else
    me.filter = sWhere
    me.filterOn = true
End If

[John_G]

In the SQL statement you created and copied to Access, if those are all separate lines, you cannot create a long statement that way. Each line must have the continuation character (the underscore: _) at the end, and strings must be delimited on each line.

The first 5 lines would look like this:

DataSource3 = "SELECT * FROM CrossSystemData " & _
  "WHERE C_TYPE = 'CROSS' AND C_SOURCE = 'SAP' AND " &  _
                 " Category " & c_Category & " AND Query_Type " & C_QUERYTYPE & _
                 " AND FieldMatched " & c_FieldMatched & " AND and Institution " & c_institution & " _
    " IN ( SELECT [NAME] FROM [CrossSystemData] As Tmp " & _

and the same for all the others.

[orange]

This
Dim C_SOURCE, c_Category, C_QUERYTYPE, c_FieldMatched, c_insitution As String

does not do what you think. In Access you must explicitly Dim your variables to assure data type, otherwise they default to Variant.

Dim c_source as string , c_category as string, c_querytype as string..... OR
Dim c_source as string
Dim c_category as string
Dim c_querytype as string...

[fluffyvampirekitten]

Test all controls for a possible filter then build the where clause.

if not isnull(cbo_source) then   sWhere = sWhere & " and [Source]='" & cbo_source & "'"
if not IsNull(cbo_category) then    sWhere = sWhere & " and [Cata]='" & cbo_category & "'"


    'remove 1st And
if len(sWhere)>0 then sWhere= mid(sWhere,5)


  'just use the filter
iLen = Len(sWhere) - 5
If iLen <= 0 Then
    me.filterOn = false
Else
    me.filter = sWhere
    me.filterOn = true
End If

I'm So lost , I don't understand . omg

[orange]

It appears that you are trying to do too much without a plan, or have no certainty/confidence in what you are doing. Focus on something --do some googling for examples --set up your own test.
See this video from Datapig.
Good luck.