Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access data with apostrophe and pass through query

Posted on 2013-05-15
4
Medium Priority
?
733 Views
Last Modified: 2013-05-15
I have a form that collects parameters from users to perform a search (frmSearchEstToEdit)

After filling in their criteria, it brings up another form with the results (frmSearchEstToEditResults).  This form also passes the data through to a pass-through query to execute a stored procedure to query the tables in the onOpen event.

If the user inputs any data with an apostrophe, then it obviously wont work in the pass through query.

I adjusted my stored procedure so that when it runs, it does not take into account any apostrophes.  Then, on the Access end I tried to remedy this by stripping the apostrophe before sending it to the pass through query.  I did this by creating a hidden text box on the input form that uses the replace function and simply takes out the apostrophes.  Doing this solves the problem on that field, but then creates a "data type mismatch" error if the user enters any data in any of the other fields on the input form.  So, I tried solving it by using the replace function in the code.  Again, it works beautifully on the field I was trying to fix, but then it creates a "invalid null" error if the user tries to put any data in any of the other fields on the form.  Both these errors appear to be occurring on the Access form and not even making it to the pass-through query, so I think it is an issue in my code.

Any ideas on how I can strip the apostrophe so that this will work without getting errors elsewhere?

This is the string I am passing through to the pass-through query from frmSearchEstToEditResults...

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
    Dim dbs As Database
    Dim qdf As QueryDef
    Dim frm As Form

    Set dbs = CurrentDb
    Set frm = Forms!frmSearchEstToEdit
    Set qdf = dbs.QueryDefs("qrySQLSearchEstToEdit")
    Dim rstForm As DAO.Recordset
qdf.SQL = "EXEC spSearchEstToEdit " & "'" & Forms!frmSearchEstToEdit!txtFacilityNumber & "'" & "," & "'" & Forms!frmSearchEstToEdit!txtFacilityName & "'" & "," & "'" & Forms!frmSearchEstToEdit!OwnerName & "'" & "," & "'" & Forms!frmSearchEstToEdit!txtFacilityAddress & "'" & "," & "'" & Forms!frmSearchEstToEdit!txtFacilityCity & "'" & "," & "'" & Forms!frmSearchEstToEdit!txtFacilityZip & "'" & "," & "'" & Forms!frmSearchEstToEdit!txtFacilityPIN & "'"

qdf.ReturnsRecords = True
Me.Requery
Set rstForm = qdf.OpenRecordset()
If rstForm.EOF Then
   MsgBox "There are no records to display.  Please change your search criteria."
    DoCmd.CancelEvent
    DoCmd.OpenForm "frmSearchEstToEdit"
 Else
rstForm.MoveFirst
Set qdf = Nothing
Set frm = Nothing
Me.Refresh
End If
End Sub
0
Comment
Question by:dcgimo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39169188
you need to double the quotes:

qdf.SQL = "EXEC spSearchEstToEdit " & "'" & replace(Forms!frmSearchEstToEdit!txtFacilityNumber, "'", "''") & "','" & replace(Forms!frmSearchEstToEdit!txtFacilityName, "'", "''") & "','" & replace(Forms!frmSearchEstToEdit!OwnerName, "'", "''") & "','" & replace(Forms!frmSearchEstToEdit!txtFacilityAddress, "'", "''") & "','" & replace(Forms!frmSearchEstToEdit!txtFacilityCity, "'", "''") & "','" & replace(Forms!frmSearchEstToEdit!txtFacilityZip, "'", "''") & "','" & replace(Forms!frmSearchEstToEdit!txtFacilityPIN, "'", "''") & "'"

BTW, the & "'" & "," & "'" & can be reduced to & "','" &
0
 

Author Comment

by:dcgimo
ID: 39169215
Thanks for the response!  

I did try the double quotes w/replace but then I get the "invalid use of null" error.
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 39169313
do you which field is null?

you can try to concatenate an empty string to each value:

replace(Forms!frmSearchEstToEdit!txtFacilityNumber & "", "'", "''")
0
 

Author Comment

by:dcgimo
ID: 39169413
That resolved it.  Thank you so much for your help!!!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question