Query syntax question

[millrat]

Hi,
As part of my query I have the following code...

If Not IsNull(Me.txtScope) Then
strWhere = strWhere & " (tblIRS.strScope)" & _
" Like '*" & Me.txtScope & "*' AND"
End If

In Me.txtScope a user may type 52PU001 or 52-PU-001.
How can I modify so that it returns both inputs?

Thanks

 

[HarleyQuinn]

Providing all input's are in the same formats as your samples, something like:

If Not IsNull(Me.txtScope) Then
    strWhere = strWhere & " (tblIRS.strScope)" & _
    " Like '" & Left(Me.txtScope,2) & "*'  AND"
    End If

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[millrat]

Thanks for reply.
After going through the tables it seems I will have to strip all spaces and dashes (wherever they may be)out of the string to return all the right records... As some are entered as 52PU001, or 52-PU-001, or 52 - PU001 etc...
Any thoughts?
Cheers

 

[HarleyQuinn]

As long as the first 2 characters are consistant across all the formats the code I posted should pick them up fine, the Left() only looks for the first two characters. So, for the three examples you just posted it should retrieve them all.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[grgimpy]

Create a query based on the data table. Use SQL similar to the following to get rid of the hyphens and spaces for the field containing the identifier:

SELECT Replace([Name]," ","") AS [No Space], Replace([No Space],"-","") AS [No Hyphen]
FROM Table1;

Then just base the query on the newly created query with no hyphens and spaces instead of the table.

 

[millrat]

Hi,

Thanks for the reply

I have made the query below based on data table. The query runs fine and all the hyphens are removed from the strScope field.

SELECT tblIRS.pkeyIRSID AS [IRS No], tblArea.strArea AS [Process Area], tblType.strType AS [IRS Type], tblIRS.ysnLocked AS Locked, tblIRS.ysnRevReq AS [For Rev], Replace([tblIRS.strScope],"-","") AS Scope, tblIRS.lngArea, tblIRS.lngType
FROM tblType RIGHT JOIN (tblArea RIGHT JOIN tblIRS ON tblArea.pkeyAreaID = tblIRS.lngArea) ON tblType.pkeyTypeID = tblIRS.lngType;

When I run this query in the following I get a data type mismatch on the "Scope" field... Having trouble understanding why as it work OK when run from the table, The query works if no parameters entered for "Scope" and returns all records...

Private Sub cmdFind_Click()

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Dim ans As Integer
Set dbNm = CurrentDb()
'Constant Select statement for the RowSource
strSQL = "SELECT 'IRS-' & Right('0000' & qryIRS.[IRS No], 4) As [IRS No], qryIRS.[Process Area]" & _
", qryIRS.[IRS Type], qryIRS.Locked, qryIRS.[For Rev], qryIRS.Scope, qryIRS.lngArea, qryIRS.lngType FROM qryIRS"

strWhere = " WHERE"
strOrder = " ORDER BY qryIRS.[IRS No];"

'Set the WHERE clause for the Listbox RowSource
'if information has been entered into a field on the form
If Not IsNull(Me.cboArea) Then
strWhere = strWhere & "(qryIRS.lngArea" & _
"=" & Me.cboArea.Value & ") AND "
Else
'Otherwise apply the "Like *" statement to the QueryDef
'to return all records
strWhere = strWhere & "(qryIRS.lngArea" & _
" Like" & "'*'" & ") AND "
End If
If Not IsNull(Me.cboType) Then
strWhere = strWhere & "(qryIRS.lngType" & _
"=" & Me.cboType.Value & ") AND "
Else
strWhere = strWhere & "(qryIRS.lngType" & _
" Like" & "'*'" & ") AND "
End If
If Not IsNull(Me.txtScope) Then
strWhere = strWhere & " (qryIRS.Scope)" & _
" Like '*" & Me.txtScope & "*' AND"
End If

'Remove the last "AND" from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

Set qryDef = dbNm.QueryDefs("qryFindIRS")
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
'Pass the SQL to the RowSource of the listbox
Me.lstIRS.RowSource = strSQL & " " & strWhere & "" & strOrder
'Display a message box if no results
If Me.lstIRS.ListCount = 0 Then
ans = MsgBox("There are no records that match your search criteria!" & vbCrLf & _
"Do you want to add a new IRS?", vbExclamation + vbYesNo, "SWIPS")
If ans = vbYes Then
DoCmd.OpenForm "frmIRS", acNormal, , , acFormAdd
DoCmd.Close acForm, "frmFindIRS"
Else
End If
End If

End Sub

Help appreciated... Thanks

 

[Golom]

Nothing jumps out for me but you can tighten the code up a bit.

Private Sub cmdFind_Click()

    Dim strSQL                      As String
    Dim strOrder                    As String
    Dim strWhere                    As String
    Dim dbNm                        As Database
    Dim qryDef                      As QueryDef
    Dim ans                         As Integer

    Set dbNm = CurrentDb()
    'Constant Select statement for the RowSource
    strSQL = "SELECT 'IRS-' & Right('0000' & Q.[IRS No], 4) As [IRS No], " & _
             "       Q.[Process Area], Q.[IRS Type], Q.Locked, Q.[For Rev], " & _
             "       Q.Scope, Q.lngArea, Q.lngType " & _
             "FROM qryIRS As Q "

    strWhere = ""
    [COLOR=black cyan]' ORDER BY has problems with using a
    ' field alias. Use the ordinal position instead.[/color]
    strOrder = " ORDER BY 1 ;"

    'Set the WHERE clause for the Listbox RowSource
    'if information has been entered into a field on the form
    [COLOR=black cyan]' You really don't need those 'include everything' clauses.
    ' just use the conditions that will result in filtering.[/color]
    If Not IsNull(Me.cboArea) Then
        strWhere = strWhere & " (Q.lngArea = " & Me.cboArea.Value & ") AND "
    End If
    If Not IsNull(Me.cboType) Then
        strWhere = strWhere & " (Q.lngType = " & Me.cboType.Value & ") AND "
    End If
    If Not IsNull(Me.txtScope) Then
        strWhere = strWhere & " (Q.Scope LIKE '*" & Me.txtScope & "*') AND "
    End If

    'Remove the last "AND" from the SQL statment
    [COLOR=black cyan]' If the WHERE clause is empty then there is 
    ' no WHERE clause and everything is included.[/color]
    If Len(strWhere) > 0 Then
        strWhere = " WHERE " & strWhere
        strWhere = Left$(strWhere, Len(strWhere) - 5)
    End If

    [COLOR=black cyan]' Copy the SQL from the Immediate window[/color]
    [COLOR=black cyan]' and see if it runs from the query window.[/color]
    Debug.Print strSQL & " " & strWhere & " " & strOrder

    Set qryDef = dbNm.QueryDefs("qryFindIRS")
    qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
    'Pass the SQL to the RowSource of the listbox
    Me.lstIRS.RowSource = strSQL & " " & strWhere & " " & strOrder
    'Display a message box if no results
    If Me.lstIRS.ListCount = 0 Then
        ans = MsgBox("There are no records that match your search criteria!" & vbCrLf & _
                     "Do you want to add a new IRS?", vbExclamation + vbYesNo, "SWIPS")
        If ans = vbYes Then
            DoCmd.OpenForm "frmIRS", acNormal, , , acFormAdd
            DoCmd.Close acForm, "frmFindIRS"
        Else
        End If
    End If

End Sub

 

[millrat]

Thanks for that Golom,

It seems the problem is using the Replace function in the initial query

Using " Replace([tblIRS.strScope],"-","") " returns all records (minus the hyphens) IF no criteria in the query.
If any of the following used as criteria gives data type mismatch error...
Like'*' or Like'52' or Like'PU' or 52 ....

So what sort of data type is there after using the Replace function???

My brain hurts...

 

[Golom]

Could any of the values of tblIRS.strScope be NULL?

If so try

Replace([red]NZ([/red][tblIRS.strScope][red])[/red],"-","")

 

[millrat]

Thanks Golom, that did it!