Retrieve SQL Results (1 Viewer)

Micron

AWF VIP
Local time
Today, 09:35
Joined
Oct 20, 2018
Messages
3,476
Micron,
How would your solution look with my DoCmd.RunSQL statement ? See statement below . . .
strCompany = strQuote & [strCompany] & strQuote

DoCmd.RunSQL "Insert Into [Sales Tax Table] ([Company Name], Grocery_Amount, Amount, Grocery_Date, Category) Values ('" & [strCompany] & "', '" & [strGroAmount] & "', '" & [strAmount] & "', '" & [strToday_Date] & "', '" & [strCaregory_Name] & "')"

I gave an example in post 31. Did I use the wrong field or the wrong sql? I'm not sure which is the field(s) that may contain '. Is it only strCompany?
EDIT - these are really field names - [strSomething]?? I'm not understanding why you have [strCompany] as well as strCompany. Your amount and date fields are text?
 
Last edited:

Mark_

Longboard on the internet
Local time
Today, 06:35
Joined
Sep 12, 2017
Messages
2,111
lhooker,

For my needs, I used his code as an example, but spelled everything out to be less... generic. A cut down version is below;

Code:
Private Sub TCO_AddStudent()

' This sub will add records from the Application table to the Student table.
' This does so by first identifying which fields will be added
' Then adding the values as parameters.

Dim asSQL As String
asSQL = "Insert Into Student(" & _
    " StudentID," & _
    " LastName," & _
    " FirstName," & _
    " Address," & _
    " City," & _
    " StateOrProvince," & _
    " PostalCode," & _
    " PhoneNumber," & _
    " Email"
 
asSQL = asSQL & " SELECT p01, p02, p03, p04, p05, p06, p07, p08, p09"
    With CurrentDb.CreateQueryDef("", asSQL)
        .Parameters(0) = Me.StudentID
        .Parameters(1) = Me.LastName
        .Parameters(2) = Me.FirstName
        .Parameters(3) = Me.Address
        .Parameters(4) = Me.City
        .Parameters(5) = Me.StateOrProvince
        .Parameters(6) = Me.PostalCode
        .Parameters(7) = Me.PhoneNumber
        .Parameters(8) = Me.Email
        .Execute
    End With
End Sub

I did it this way to avoid issues with strange formatting. Works very well and does not have problems with names like Xyz's.
 

lhooker

Registered User.
Local time
Today, 09:35
Joined
Dec 30, 2005
Messages
399
The_Doc_Man/Mark/Micron,

Thanks to all of you. I ended up creating a SQL Update queries (see below). This was the easy solution for me.

UPDATE [Sales Tax Table] SET [Sales Tax Table].[Company Name] = Replace([Company Name],Chr(34),"'");
 

lhooker

Registered User.
Local time
Today, 09:35
Joined
Dec 30, 2005
Messages
399
The_Doc_Man/Mark/Micron,

Also, I replace the single quote with double quotes in the original name in [Company Name] table.
 

Micron

AWF VIP
Local time
Today, 09:35
Joined
Oct 20, 2018
Messages
3,476
Replace([Company Name],Chr(34),"'");
I don't get it. Syntax is Replace(string, find, replacement) although there is a bit more to it.

Yet you have Replace([Company Name], ""," ' ") (extra space for visualization)
which means find empty string and replace it with single quote. I cannot grasp how that solves the problem - which I thought was to remove single quotes as in O'Hara to OHara.
 

lhooker

Registered User.
Local time
Today, 09:35
Joined
Dec 30, 2005
Messages
399
Micron,

To resolve the problem, I did the following:

1. Replace all single quotes with double quotes in the [Company Name] field of the table. This eliminated the Run-time error '3075'error.
2. Added an event ('On Open') that calls an update query when the report opens.

The update query (see below) replaces all double quotes with single quotes prior to displaying the report.

UPDATE [Sales Tax Table] SET [Sales Tax Table].[Company Name] = Replace([Company Name],Chr(34),"'");

Thanks for your help ! ! !
 

Micron

AWF VIP
Local time
Today, 09:35
Joined
Oct 20, 2018
Messages
3,476
OK, seems like a convoluted way when a query behind a report could be made to just overlook the single quote in the first place. Then again, maybe my assumption is wrong. Glad you got it solved anyway.
 

lhooker

Registered User.
Local time
Today, 09:35
Joined
Dec 30, 2005
Messages
399
Micron,

Remember, I was originally getting a Run-time error '3075' with the single quote in the [Company_Name] field of the table. This solution solved my problem. I could not get the other suggestions to work. Thanks again for your help ! ! !
 

isladogs

MVP / VIP
Local time
Today, 13:35
Joined
Jan 14, 2017
Messages
18,186
I realise you have a solution. Here's a variation.
Base your report on a query where you replace your single quotes with an unusual character like ¦ (you could still use two single quotes if you prefer)

In your report, use a Replace function on the field control to change all ¦ back to '.
No update queries needed
 

lhooker

Registered User.
Local time
Today, 09:35
Joined
Dec 30, 2005
Messages
399
isladogs,

Thanks, but I have a solution. I will keep this post in mind, when I have any other problem.
 

isladogs

MVP / VIP
Local time
Today, 13:35
Joined
Jan 14, 2017
Messages
18,186
Yes I was aware of that. Just offering an alternative that is slightly simpler.
 

lhooker

Registered User.
Local time
Today, 09:35
Joined
Dec 30, 2005
Messages
399
isladogs,

I appreciate this. I will definitely keep your post in mind the next I run into a problem. Thanks again ! ! !
 

Users who are viewing this thread

Top Bottom