Simple sql simply unhelpful (1 Viewer)

The 4th line of the sql part doesn't want to oblige. [I know it's Fate. It always is when I'm in a hurry.] Be very pleased if the error can be seen.

[How can it be wrong when it's been filched from the QBE department where it works well!]

Cheers, masters.

Private Sub cmdSubmittedTable_Click()

Dim strSQL As String
Dim strFilter As String
strSQL = ""

strSQL = strSQL & "SELECT tblAll.MinOfID, tblAll.OldBusArea, tblAll.ShortUrl, tblAll.SumOfSumOfHits, tblAll.Live, tblAll.Status, tblAll.Memo, tblAll.NEWBusArea, tblAll.TheDate, tblAll.Approved "
strSQL = strSQL & "INTO tblLinksSUBMITTED "
strSQL = strSQL & "FROM tblAll "
strSQL = strSQL & "WHERE ((([tblAll].[Status])=1) And (([tblAll].[Approved])="yes")) Or ((([tblAll].[Status])="0")) "


End Sub

change "yes" to 'yes' and "0" to '0'

Also, you have status = 1 .... Status = "0"
It is either status = 1 .... Status = 0
or status = "1" .... Status = "0"

Also remove all those Access *hatefull* brackets
strSQL = strSQL & "WHERE ([tblAll].[Status]=1 And [tblAll].[Approved]='yes') Or [tblAll].[Status]=0 "


Regards

A few things?

• Do you really have a query called tblAll?
• Should the "Yes" be Yes or True (i.e. no inverted commas?)
• Should the "0" actually be 0? Is it a text or numerical field?
• You can't explicitly use inverted commas in a string. "Yes" becomes ""yes"" and "0" becomes ""0""

No need for the part in bold either:

Suitably chastened. Suitably educated. And more than suitably grateful. (Ug, the learning curve slopes steeply at times.) Thank you, gentlemen.

Mile-O-Phile and Mailman

I'm a beginner which is why I am here seeking advice. The difference between coming here with a problem today and not coming is only that bit of humble pie you have had me swallow.

I have a query that is happy to make the required table. I would lilke to replace that query with code. I have taken the sql from the QBE sql pane and put into the VBE.

I came here hoping you wouldn't mind saying how to put it right, rather than inviting your having a bit of a sling at me.

MoP points out my tblAll, asking if this is a query. But I want the information to come from the table. I wouldn't have expected to have a query as well.

I respect you both very much. I'll be very grateful if you might have a second look to see if there's something that you have overlooked that I have overlooked or is beyond my knowledge.

(I've tried all of the suggestions plus variations on them. The matter of "strSQL = strSQL & " is something that I see a lot of programmers doing.) Any suggestions please about getting the code to work?

kupe said:

strSQL = strSQL & "SELECT tblAll.MinOfID, tblAll.OldBusArea, tblAll.ShortUrl, tblAll.SumOfSumOfHits, tblAll.Live, tblAll.Status, tblAll.Memo, tblAll.NEWBusArea, tblAll.TheDate, tblAll.Approved "
strSQL = strSQL & "INTO tblLinksSUBMITTED "
strSQL = strSQL & "FROM tblAll "
strSQL = strSQL & "WHERE ((([tblAll].[Status])=1) And (([tblAll].[Approved])="yes")) Or ((([tblAll].[Status])="0")) "

Click to expand...

There are some things wrong with it.

You say it's a table. Can I then assume that, with field names like MinOfID and SumOfSumOfHits that the table was created with a Make Table Query?

If so, okay; let's move on.

Dim strSQL As String

strSQL = ""
strSQL = strSQL & "SELECT tblAll......"

Click to expand...

So, as strSQL = "" then "" & "SELECT tblAll...." is simply: "SELECT tblAll...." - it becomes redunadant on the first line; on the other lines it is okay.

Now the penultimate thing is the field: Status. Is it a text field or a numeric field? Your SQL does not know. In one case you have encloed a numeric value within quotation marks (i.e. "0") yet the other status value is not within quotation marks (i.e. 1) so you must decide which it is. Again, is it text or numeric. Only text goes within the quotation marks.

Finally, your SQL is missing the semi-colon at the end.

All this however, is pointless as - through the course of your database running - that SQL code doesn't change making it completely unnecessary. You are better to stay with a query built in the QBE as once it is saved it is stored and will add to database bloat once. Every time you run that code a new query will be created (and terminated) but the size of your database, with continued executions of that code, will start to bloat. Bigtime!

On another note, as I said previously, you can't explicitly put a quotation mark into a string: VBA will determine the quotation mark within the string to be the point where the string terminates and will deem the actual end of the string quotation mark to be either a floating orphan or the start of another string which doesn't have its own 'terminator'

Thanks very much, Mile-O-Phile. Yes, I can follow all of your points and I appreciate your going to the trouble of explaining. I hadn't realised the risk of bloat so I'll follow your good advice (as I almost always do) and stick with just the query for that task. I do appreciate your help. Cheers