Solved Change quote into an order

Isaac said:
Before I spend any time looking at it, let me suggest a troubleshooting method.

Put a Breakpoint on the strSQLQuoteInsert= line of code. Run your code. When the code breaks on that line, press F8 to execute just that one line.
After that one line executes, ask the immediate window this question:

?strSQLQuoteInsert [then press Enter]

Whatever the Immediate window spits out, with the code still in break mode (if you want - you don't have to even stop it, probably, depending on the modality of your current forms situation + status of your Ribbon): COPY the code , and go to Create > Query Design > SQL view and paste that code into it. Leave the query window in View SQL mode.

Once you've pasted in the code, try to Run the query. The error message that it will then provide, along with the bit of raw SQL text that might be highlighted or indicated next to the cursor, will usually help you determine where you went wrong.

Or, of course, you may be able to spot it simply from seeing what the Immediate window first spit out. Feel free to post here precisely what the Immediate window told you.

My guess is that either one of these wasn't surrounded by single quotes: strJobName/strNotes, or possibly you have a single quote contained in the actual text of strNotes that you haven't escaped
Click to expand...
Set the two string varibales using this: Chr(34) & varName & Chr(34) and Success!, Not really sure why though so where's the good in that.
 
Well if the result was showing you just the variable names instead of their values, that seems like the most fundamental problem. Remember when you are building up SQL using VBA, the result needs to be a literal string. That means concatenating strings to make a big string.

So it's never:

strSQL = "select * from table where id=VariableName"

It's always:

strSQL = "select * from table where id=" & VariableName

Anyway glad you got it working!
 
slharman1 said:
Set the two string varibales using this: Chr(34) & varName & Chr(34) and Success!, Not really sure why though so where's the good in that.
Click to expand...
Isaac, I can't tell you how much I appreciate your help with this! without guys like you newbs like me would struggle for a lot longer.
Thank again!
 
Isaac said:
Well if the result was showing you just the variable names instead of their values, that seems like the most fundamental problem. Remember when you are building up SQL using VBA, the result needs to be a literal string. That means concatenating strings to make a big string.

So it's never:

strSQL = "select * from table where id=VariableName"

It's always:

strSQL = "select * from table where id=" & VariableName

Anyway glad you got it working!
Click to expand...
Isaac, I can't tell you how much I appreciate your help with this! without guys like you newbs like me would struggle for a lot longer.
Thank again!
 
Chr(34) & varName & Chr(34)

chr(34) is a " symbol.

I always use this syntax, rather than entering double "" characters. I find it more readable, and it's easy to get it right. If you use "" characters directly, it's easier to get it wrong. Maybe you were only using one " character instead of two

given the above post, your string needs to look like this

where id = ABC

now because ABC is a string, ABC has to be contained in quote marks, so the string actually needs to look like this

where id = "ABC"

but because " is a string delimiter, you need 2 "" characters to get a 'real' " character so

where id = ""ABC"" or with a variable
where id = "" & varStrg & "" (I am not 100% sure whether these are correct to be honest)

but if you use chr(34) you only need one - so either this

where id = chr(34) & "ABC" & chr(34) or this
where id = chr(34) & varStrg & chr(34)

------
unfortunately, you cant declare a const
const quote = "" or even const quote="""

and then just use
where id = quote & varStrg & quote

so it's easier to just use chr(34)
 
Last edited:
gemma-the-husky said:
I always use this syntax, rather than entering double "" characters. I find it more readable, and it's easy to get it right. If you use "" characters directly, it's easier to get it wrong.
Click to expand...

I agree with Dave! I can never understand why people struggle to construct SQL Statements and in doing so, get the commas, speech marks and all that in the wrong places. It takes a lot of skill and practice to get right.

Most of the questions I see pop up on the forum regarding SQL Statements, where people can't get it right are due to syntax errors in these text strings. The problem is easily avoided by taking a slightly different approach in building the SQL string. As you can probably tell from my "nearly a rant" posting, it's one of my pet hates...

My method is very similar to Dave's, and I've done a video and blog about it on my website.

This is a clip from one of my videos where I discuss this in detail.



The YouTube is part of a Blog on my website here:-

Insert Into SQL Statement – Nifty Access

www.niftyaccess.com www.niftyaccess.com


if you want further information.
 
In this video you can hear me expressing my annoyance at having to fix these syntax challenged SQL Statements that OP's post on the forum, in the hope that someone will VET it for them! There's no need, just take a slightly different approach...

Formatting an SQL Insert Statement - Nifty Access​

 
gemma-the-husky said:
Chr(34) & varName & Chr(34)

chr(34) is a " symbol.

I always use this syntax, rather than entering double "" characters. I find it more readable, and it's easy to get it right. If you use "" characters directly, it's easier to get it wrong. Maybe you were only using one " character instead of two

given the above post, your string needs to look like this

where id = ABC

now because ABC is a string, ABC has to be contained in quote marks, so the string actually needs to look like this

where id = "ABC"

but because " is a string delimiter, you need 2 "" characters to get a 'real' " character so

where id = ""ABC"" or with a variable
where id = "" & varStrg & "" (I am not 100% sure whether these are correct to be honest)

but if you use chr(34) you only need one - so either this

where id = chr(34) & "ABC" & chr(34) or this
where id = chr(34) & varStrg & chr(34)

------
unfortunately, you cant declare a const
const quote = "" or even const quote="""

and then just use
where id = quote & varStrg & quote

so it's easier to just use chr(34)
Click to expand...
Gemma thank you. The strings are variables so I just set the variables = chr(34) & chr(34) first and then just left the SQL statement as it was. Worked perfectly. Thanks again.
 
Uncle Gizmo said:
I agree with Dave! I can never understand why people struggle to construct SQL Statements and in doing so, get the commas, speech marks and all that in the wrong places. It takes a lot of skill and practice to get right.

Most of the questions I see pop up on the forum regarding SQL Statements, where people can't get it right are due to syntax errors in these text strings. The problem is easily avoided by taking a slightly different approach in building the SQL string. As you can probably tell from my "nearly a rant" posting, it's one of my pet hates...

My method is very similar to Dave's, and I've done a video and blog about it on my website.

This is a clip from one of my videos where I discuss this in detail.



The YouTube is part of a Blog on my website here:-

Insert Into SQL Statement – Nifty Access

www.niftyaccess.com www.niftyaccess.com


if you want further information.
Click to expand...
Uncle Gizmo I actually followed your example in the 6 videos you lead me to earlier. That’s the way I will construct SQL statements from here on out. Thanks you have been a big help with me getting this accomplished.
 
@Uncle Gizmo Very slick demo. I didn't realize for a moment that the strings were being manipulated to include the correct wrappers. I thought you wrapped it up without explaining that in enough detail perhaps. Anyway, I am sure I will look at some more Nifty suggestions.
 
gemma-the-husky said:
@Uncle Gizmo Very slick demo. I didn't realize for a moment that the strings were being manipulated to include the correct wrappers. I thought you wrapped it up without explaining that in enough detail perhaps. Anyway, I am sure I will look at some more Nifty suggestions.
Click to expand...
He clearly and specifically spells it out in the video. It’s the only way a novice like me would’ve picked up on it. I had to watch the videos several times to comprehend what all is taking place. Problem is at my age I will forget most of it before I need it again. 😊
 
gemma-the-husky said:
without explaining that in enough detail perhaps.
Click to expand...
Yes, I need to improve my content. I am apt to think people already know, therefore I don't explain everything properly. I just downloaded the sample database to check it over. It is OK for USA members, but I discovered that it formats UK dates in the American form.

Whilst testing; it entered today's date - 8th of April 2021 - and the date appeared in the table as - August the 4th 2021 ... This is a common problem faced by MS Access programmers in the UK. This is the other advantage of the steps approach to entering the variable, as the date conversion function can be applied in the "Steps".

I found a function which converts the date so that it appears correctly. This function can be added easily in to the "Steps" approach. The function fSQLDates() provides the correct date format. I reckon nother YouTube is necessary, and will follow shortly!

I've put the code on the web here:-
Convert USA Dates to UK Dates

Here is a link to the "Insert VBA Code Snippets Video":- https://www.niftyaccess.com/nifty-tips/#VBACode
 
without wishing to change this thread - I generally use this for dates only

sqldate = "#" & format(ukdate,"long date") & "#"

I know everyone points out this does not work in all domains, but I just find it very easy. Long date is "8 April 2021", and therefore there is no ambiguity.
 
gemma-the-husky said:
therefore there is no ambiguity
Click to expand...

That's a good solution to the ambiguity issue. I can't help wondering why "they" didn't go for the other Date which is not ambiguous, a date in the format of:- Year/Month/Day = 2021/04/09 = this is an internationally recognised Date format. This format has the added advantage that it sorts correctly!

What I'm saying is, it is unusual, (and inconvenient) for international software to have a country specific date format. The international date format is not tied to any specific country/nationality, and I would have thought it would have been much better choice for an international software company like Microsoft.
 
One more problem:
I have added the other two tables via nested loops to my function, this is just part of the function to get values from 3 tables and insert them into 3 other tables,
The problem is in the values returned by the INSERT INTO statement, (I think)
Code: 
  ' lngQuoteID is previously set in code
                    Dim rsQuoteDetailsList As DAO.Recordset
   
                    Dim strSQL_QuoteDetailsRSL As String
                    strSQL_QuoteDetailsRSL = "SELECT QuoteDetailID, ItemNo, EstID, ModelNo, Description, Qty, Price, QuoteID, AccessPrice FROM tblQuoteDetails WHERE QuoteID = " & lngQuoteID
   
                    'Details Variables
                   
                    Dim lngQuoteDetailID As Long
                    Dim strItemNo As String
                    Dim lngEstID As Long
                    Dim strModelNo As String
                    Dim strDescription As String
                    Dim lngQty As Long
                    Dim curPrice As Currency
                    'Dim lngQuoteID As Long
                    Dim curAccessPrice As Currency
                   
                    Set rsQuoteDetailsList = curDB.OpenRecordset(strSQL_QuoteDetailsRSL, 8)
               
                        Do Until rsQuoteDetailsList.EOF
                           
                            lngQuoteDetailID = rsQuoteDetailsList!QuoteDetailID
                            strItemNo = rsQuoteDetailsList!ItemNo
                            lngEstID = rsQuoteDetailsList!EstID
                            strModelNo = rsQuoteDetailsList!ModelNo
                            strDescription = rsQuoteDetailsList!Description
                            lngQty = rsQuoteDetailsList!Qty
                            curPrice = rsQuoteDetailsList!Price
                            lngQuoteID = rsQuoteDetailsList!QuoteID
                            curAccessPrice = rsQuoteDetailsList!AccessPrice
                           
                            strItemNo = Chr(34) & strItemNo & Chr(34)
                            strModelNo = Chr(34) & strModelNo & Chr(34)
                            strDescription = Chr(34) & strDescription & Chr(34)
                           
                            Dim strSQLQuoteDetailsInsert As String
                           
                            strSQLQuoteDetailsInsert = "INSERT INTO tblOrderDetails(ItemNo, EstID, ModelNo, Description, Qty, Price, OrderID, AccessPrice) VALUES (" & strItemNo & ", " & lngEstID & ", " & strModelNo & ", " & strDescription & ", " & lngQty & ", " & curPrice & ", " & lngQuoteID & ", " & curAccessPrice & ")"
                            curDB.Execute strSQLQuoteDetailsInsert

The code breaks at the last line: curDB.Execute strSQLQuoteDetailsInsert
Upon testing - the immediate window gives the following results.


?strSQLQuoteDetailsInsert
INSERT INTO tblOrderDetails(ItemNo, EstID, ModelNo, Description, Qty, Price, OrderID, AccessPrice) VALUES ("122", 6, "PSF30144-3R10-U6", "Pot Sink with rolled rim, 30" wide x 12'-0" long, with (3) 30" long x 26" wide x 15" deep. (3) lever drains. Faucets by others.", 1, 2961.0539, 11, 224.0304)

I think it is the string below that is the result of the field strDescription from the strSQL statement that is causing the INSERT problem:

"Pot Sink with rolled rim, 30" wide x 12'-0" long, with (3) 30" long x 26" wide x 15" deep. (3) lever drains. Faucets by others."
Any Ideas?
 
You must log in or register to reply here.

Similar threads

asteropi
Solved Connecting sums from order detail to order list
Replies
18
Views
658
asteropi
asteropi
brucemc777
Sort a continuous form by the "TXDate" column (yes, dates) in ascending order
2
Replies
20
Views
445
Mark_
M
asteropi
Solved Open a filtered query from a Cmd Button
2
Replies
24
Views
435
asteropi
asteropi
J
Pareto Graphic
2
Replies
25
Views
560
JtrilloJ
J
G
Solved Error #size! on calculated control based on subform value
Replies
2
Views
413
GaP42
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom