Error 3075, 2448: Cannot Assign Value - Access 2003

[Dököll]

Hey gang!

Below code allows me to search my database though all fields included on the form. Works great. Small issue, when I fetch partial phrases, if they contain an apostrophy, say, "Ferris Bueller's Day Off", Access does not understand it an gives me either ther 3075 error, or the other.

What are your thoughts on that?

Option Compare Database
Option Explicit

'Refresh form screen for new data entry
Private Sub ResetMeScreen_Click()
DoCmd.Close 'the form must close, then reopened
DoCmd.OpenForm "DataCentralReport"
End Sub
'Search button
Private Sub SearchMeData_Click()
Const cInvalidDateError As String = "Please enter date in proper format to continue..."

'Date format catcher. This keeps the data clean and standard
Dim strWhere As String 'Dimensioned to be called through command
Dim strError As String 'Dimensioned to be called below
strWhere = "1=1"
If Not IsNull(Me.AsTo) Then 'Loads data searched if available
strWhere = strWhere & " AND " & "DataCentral.[AsTo] = '" & Me.AssignedTo & "'"
End If
If Not IsNull(Me.OpToThen 'Loads data searched if available
strWhere = strWhere & " AND " & "DataCentral.[OpTo= '" & Me.OpenedBy & "'"
End If
If Nz(Me.Status) <> "" Then 'Loads data searched if available
strWhere = strWhere & " AND " & "DataCentral.[Status] = '" & Me.Status & "'"
End If
If Nz(Me.Category) <> "" Then 'Loads data searched if available
strWhere = strWhere & " AND " & "DataCentral.[Category] = '" & Me.Category & "'"
End If
If Nz(Me.CoName) <> "" Then 'Loads data searched if available
strWhere = strWhere & " AND " & "DataCentral.[CoName] = '" & Me.CoName & "'"
End If
If Nz(Me.Code) <> "" Then 'Loads data searched if available
strWhere = strWhere & " AND " & "DataCentral.[code] = '" & Me.Code & "'"
End If
If Nz(Me.Priority) <> "" Then 'Loads data searched if available
strWhere = strWhere & " AND " & "DataCentral.[Priority] = '" & Me.Priority & "'"
End If
If IsDate(Me.BeginningDate) Then 'Loads data searched if dates available
strWhere = strWhere & " AND " & "DataCentral.[TodayDate] >= " & GetDateFilter(Me.BeginningDate)
ElseIf Nz(Me.BeginningDate) <> "" Then
strError = cInvalidDateError
End If
If IsDate(Me.EndingDate) Then 'Loads data searched if dates available
strWhere = strWhere & " AND " & "DataCentral.[TodayDate] <= " & GetDateFilter(Me.EndingDate)
ElseIf Nz(Me.EndingDate) <> "" Then
strError = cInvalidDateError
End If
If Nz(Me.ContactPerson) <> "" Then 'Loads data searched if available
strWhere = strWhere & " AND " & "DataCentral.ContactPerson Like '*" & Me.ContactPerson & "*'"
End If
If Nz(Me.Items) <> "" Then 'Loads data searched if available
'Like helps get part of the text then searched
strWhere = strWhere & " AND " & "DataCentral.Items Like '*" & Me.IssuesItems & "*'"
End If
'If Nz(Me.LegacyDate) <> "" Then 'Loads data searched if dates available
'strWhere = strWhere & " AND " & "DataCentral.LegacyDate Like '*" & Me.LegacyDate & "*'"
'End If
If Nz(Me.ID) <> "" Then 'Loads data searched if dates available
strWhere = strWhere & " AND " & "DataCentral.ID Like '*" & Me.ID & "*'"
End If


If strError <> "" Then 'Pop up catch when search is cancelled
MsgBox "You decided to cancel your search..." 'strError
Else
If Not Me.FormFooter.Visible Then 'results loads in additional form in footer
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.DataCentralResultsForm.Form.Filter = strWhere
Me.DataCentralResultsForm.Form.FilterOn = True
End If
End Sub
Function GetDateFilter(dtDate As Date) As String
GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY") & "#"
End Function

Any information you have is appreciated.

My apologies re; Code tags, too long of a procedure, couldn't add CODE=VB.

Have a great week-end, thanks!

Dököll

[Rabbit]

Do a Replace on the string to change all single quotes to double single quotes.

'He's'
That confuses the SQL engine because it will see 'He' as a string but will not know what to do with s'. Double single quotes tell it to use the character and not to break the string and that the string continues on.

[Dököll]

Do a Replace on the string to change all single quotes to double single quotes.

'He's'
That confuses the SQL engine because it will see 'He' as a string but will not know what to do with s'. Double single quotes tell it to use the character and not to break the string and that the string continues on.

Pretty special, will give that a whirl!

Thanks:-)

[Rabbit]

Good luck.

[wvmitchell]

Single quotes can create headaches, but you can put this in a standard module & then use it any time you're searching for strings. It doubles-up any single quotes as Rabbit said. It accepts a Variant, so NULLs won't break it, it just returns a zero-length string.

Function FixQuotes(text) As String
    If Len(Nz(text, "")) = 0 Then Exit Function
    FixQuotes = Replace(text, "'", "''")
End Function
 

[Dököll]

Hey gang!

It looks like removing single quotes in this case redenred the search unusable. I am certain I am missing the big picture:

 
If Nz(Me.ID) <> "" Then 'Loads data searched if dates available
strWhere = strWhere & " AND " & "DataCentral.ID Like '*" & Me.ID & "*'"
End If
 
 
 

I really need to rid of the error for the above portion. I intend to remove the Like operator for the others.

So removing the single quotes in above give me error number: 3075, as in the previous.

It's probably pretty simple:-)

In a bit, and thanks!

[Dököll]

I wondered if something like this would work to catch the error popping up:

 
If Err.Number = 3075 Then
        .....
     Else
        .....
     End If
 
 

Perhaps I can surpress the incoming error to tell VBA it's okay to process. Will let you know...

[Rabbit]

Hey gang!

It looks like removing single quotes in this case redenred the search unusable. I am certain I am missing the big picture:

 
If Nz(Me.ID) <> "" Then 'Loads data searched if dates available
strWhere = strWhere & " AND " & "DataCentral.ID Like '*" & Me.ID & "*'"
End If
 
 
 

I really need to rid of the error for the above portion. I intend to remove the Like operator for the others.

So removing the single quotes in above give me error number: 3075, as in the previous.

It's probably pretty simple:-)

In a bit, and thanks!

Removing single quotes? We never said to remove single quotes. You have to do the opposite. In a string with single quotes, you have to double up on the single quotes.

Also, that snippet of code shouldn't throw an error in of itself.

[Dököll]

Removing single quotes? We never said to remove single quotes. You have to do the opposite. In a string with single quotes, you have to double up on the single quotes.

Also, that snippet of code shouldn't throw an error in of itself.

What do you know, I missed the pretty picture "...change all single quotes to double single quotes..."

I see now.

I think I read "...change all single quotes..." and kept on.

Alright, I'll give it another go. Many thanks, gang!

Dököll