Solved Change quote into an order (1 Viewer)

slharman1

Member
Local time
Today, 17:17
Joined
Mar 8, 2021
Messages
467
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
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.
 

Isaac

Lifelong Learner
Local time
Today, 15:17
Joined
Mar 14, 2017
Messages
8,738
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

Member
Local time
Today, 17:17
Joined
Mar 8, 2021
Messages
467
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.
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!
 

slharman1

Member
Local time
Today, 17:17
Joined
Mar 8, 2021
Messages
467
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!
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!
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 22:17
Joined
Sep 12, 2006
Messages
15,614
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:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:17
Joined
Jul 9, 2003
Messages
16,245
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.

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:-



if you want further information.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:17
Joined
Jul 9, 2003
Messages
16,245
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​

 

slharman1

Member
Local time
Today, 17:17
Joined
Mar 8, 2021
Messages
467
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)
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.
 

slharman1

Member
Local time
Today, 17:17
Joined
Mar 8, 2021
Messages
467
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:-



if you want further information.
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

Nifty Access Guy
Staff member
Local time
Today, 22:17
Joined
Jul 9, 2003
Messages
16,245
That’s the way I will construct SQL statements from here on out.

That's good to hear! At least I'm not wasting my time, well I am wasting time on the forum! Time to get back to work...

Can I use your comment on my testimonial page?

 

slharman1

Member
Local time
Today, 17:17
Joined
Mar 8, 2021
Messages
467
That's good to hear! At least I'm not wasting my time, well I am wasting time on the forum! Time to get back to work...

Can I use your comment on my testimonial page?

Absolutely! And thanks again!
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 22:17
Joined
Sep 12, 2006
Messages
15,614
@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.
 

slharman1

Member
Local time
Today, 17:17
Joined
Mar 8, 2021
Messages
467
@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.
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. 😊
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:17
Joined
Jul 9, 2003
Messages
16,245
without explaining that in enough detail perhaps.
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
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 22:17
Joined
Sep 12, 2006
Messages
15,614
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.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:17
Joined
Jul 9, 2003
Messages
16,245
therefore there is no ambiguity

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.
 

slharman1

Member
Local time
Today, 17:17
Joined
Mar 8, 2021
Messages
467
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?
 

Users who are viewing this thread

Top Bottom