469,588 Members | 2,688 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,588 developers. It's quick & easy.

Error 3075, 2448: Cannot Assign Value - Access 2003

Dököll
2,364 Expert 2GB
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
Nov 30 '07 #1
8 2365
Rabbit
12,516 Expert Mod 8TB
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.
Nov 30 '07 #2
Dököll
2,364 Expert 2GB
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:-)
Dec 1 '07 #3
Rabbit
12,516 Expert Mod 8TB
Good luck.
Dec 1 '07 #4
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.

Expand|Select|Wrap|Line Numbers
  1. Function FixQuotes(text) As String
  2.     If Len(Nz(text, "")) = 0 Then Exit Function
  3.     FixQuotes = Replace(text, "'", "''")
  4. End Function
  5.  
Dec 1 '07 #5
Dököll
2,364 Expert 2GB
Hey gang!

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. If Nz(Me.ID) <> "" Then 'Loads data searched if dates available
  3. strWhere = strWhere & " AND " & "DataCentral.ID Like '*" & Me.ID & "*'"
  4. End If
  5.  
  6.  
  7.  
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!
Dec 7 '07 #6
Dököll
2,364 Expert 2GB
I wondered if something like this would work to catch the error popping up:

Expand|Select|Wrap|Line Numbers
  1.  
  2. If Err.Number = 3075 Then
  3.         .....
  4.      Else
  5.         .....
  6.      End If
  7.  
  8.  
Perhaps I can surpress the incoming error to tell VBA it's okay to process. Will let you know...
Dec 7 '07 #7
Rabbit
12,516 Expert Mod 8TB
Hey gang!

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. If Nz(Me.ID) <> "" Then 'Loads data searched if dates available
  3. strWhere = strWhere & " AND " & "DataCentral.ID Like '*" & Me.ID & "*'"
  4. End If
  5.  
  6.  
  7.  
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.
Dec 7 '07 #8
Dököll
2,364 Expert 2GB
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
Dec 11 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Geoff Cox | last post: by
6 posts views Thread by Peter Frost | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.