[RESOLVED] More SQL Questions

[jhize]

In this thread (http://www.vbforums.com/showthread.p...hlight=sql+sum) the following code was posted:

"SELECT type, SUM(amount) AS totals FROM history GROUP BY date, type HAVING date = '" & Combo1.Text & "' ORDER BY date"

My question is simply this:

Where does the "..As totals" come from? I need to do something similiar but I'm not sure about this portion of the above code. I need something like this, I think:

"SELECT SUM(Payment) FROM Paydate WHERE CommonPropertyName ='" & Propertyname & "'"

[GaryMazzone]

As Totals is naming the column Sum(amount). That is it, it's just a name

[Hack]

Where does it come from?

You make it up.

Example:

SELECT SUM(Payment) AS MyPay FROM etc etc

[jhize]

Ok. So it is not really needed? Or does it make 'totals a variable that I can use later (ex. Text1.text = totals?)

Yeah, I'm thinking that was a stupid question. But it would be more stupid not asking. .....I think.

[Hack]

It makes it easier to deal with the result as you can take what is stored and use it more easily.

[jhize]

So 'totals' DOES become a variable that I can use?

[si_the_geek]

Not quite a variable, just the name of the field... in your original SQL there is a field called "type", so you can use it with something like rst.Fields("type").Value

However, SUM(amount) is not a field (it is an expression), and what the 'field' would be called depends on the database system you are using - so it makes things harder. By specifying an alias ("as totals" or "AS MyPay"), you know what it will be called within the recordset (eg: rst.Fields("totals").Value ).

But it would be more stupid not asking. .....I think.

Definitely.. we don't learn unless we ask questions, even if they may appear stupid.

[Hack]

And this is not a stupid question.

I would venture to say that each and every person that has posted in this thread had, at one time in their career, the exact same question. I know I did.

Moreover, you will have no way of knowing this, but I'll be willing to bet that a lot of people will read your thread and go "Ohhhhhhhh....that is what that means."

[jhize]

Ok. The first code I posted was not mine, the second was.

I have a field called payment in a table called paydate. I am wanting to return the sum of payments only if the propertycommonname = text1.text on frmMain. I dont have a 'type' field.

so....

SELECT Sum(payment) as Total FROM Paydate.....

I believe I might be on the verge of understanding this here.

On a side note, you guys are awesome !!!

[si_the_geek]

In that case, your SQL statement could be like this:

strSQL = "SELECT Sum(payment) as Total FROM Paydate WHERE propertycommonname = '" & frmMain.text1.text & "'"

..and once you have opened the recordset, you can get the value like this:

MsgBox rst.Fields("Total").Value

..or like this, which just gets the value of the first field:

MsgBox rst.Fields(0).Value

(this method doesn't require the alias in the SQL statement, but is more prone to hard-to-find errors)

[szlamany]

Would be something like this:

"SELECT SUM(Payment) FROM Paydate WHERE CommonPropertyName ='" & frmmain.text1.text & "'"

[edit]Or like Si just said!

btw - that first query in your first post is not a good query. The HAVING clause should be a WHERE clause and the group by is flawed - bad example for you to learn from!
[/edit]

[salvelinus]

Assuming PropertyCommonName is a field in the table, just use a WHERE clause in your SELECT statement.

"SELECT Sum(payment) as Total FROM Paydate " & _
"WHERE Paydate.PropertyCommonName = '" & Text1.Text & "'"

although I'd probably put Text1.Text into a variable instead.

[jhize]

Ok. The sql SUM works.

Now how would I add a second condition in my where clause?

After the propertyname variable, I need:
WHERE TransactionType(a field) = TransactionType( a variable). I'm pretty sure I use AND but the punctuation is throwing me off.

EDIT:
I currently have this, but it's not right.

 sqlTotal = "SELECT SUM(Amount) as Total FROM Transaction WHERE PropertyCommonName ='" & Propertyname & "'" AND "AccountType=' & TransactionType & "'"

[salvelinus]

Your quotation marks and spacing are off.

sqlTotal = "SELECT SUM(Amount) as Total FROM Transaction " & _
"WHERE PropertyCommonName = '" & Propertyname & "' " & _
"AND AccountType = '" & TransactionType & "'"

assuming AccountType is a text field. If numeric, drop the single quotes around TransactionType.

[jhize]

Thanks,

Would you happen to have a good source that shows the 'Rules' of punctuation for these sql statements?

I keep finding statements like this:

SELECT NAME_LAST, NAME_FIRST 
FROM INDIVIDUAL 
WHERE MARITAL_STATUS = 'Married' AND BIRTH_DT <= '1959-12-31';

and I know that this is not what is needed inside a vb6 app. I'll check the tutorial again but I;m pretty sure this is not there.

[Hack]

I'm not precisely sure what you mean by "rules of punctuation", but strings need to be encapsulated in single quotes and numbers do not.

[szlamany]

It was not a SQL issue.

You were not building the string properly - that's a VB syntax issue

you had

& Propertyname & "'" AND "AccountType=' &

That " quote in from of AccountType doesn't make any sense - as the " quote is a VB delimiter.

[jhize]

OK. Um...I'm confused about the (') and (") delimiters.

I understand this is a string. So, I understand this first line.

sqlTotal = "SELECT SUM(Amount) as Total FROM Transaction " & _

I'm CORNfused right here (for example):

 '" & Propertyname & "' "

I understand that the last (") closes the string. However, & Propertyname & is enclosed by a (')(") and then in reverse (")(')...
So I'm guessing that the (")......(") shows that it is a continuation of the string? and the (').....(') shows that it is not numerical.

This is what I meant by Rules of Punctuation. In Post #15, this sql statement is "stand-alone?" meaning not assigned as a string? And if it were it would be something like this?

sqlquestion = "SELECT NAME_LAST, NAME_FIRST FROM INDIVIDUAL" & _ 
"WHERE MARITAL_STATUS = '"& Married & "' & _
"AND " & BIRTH_DT & " <= #1959-12-31#""

[szlamany]

SQL wants character datatypes delimited by single quotes.

So - Select * From SomeTable Where Status='XYZ' - is a valid sql statement. Note that I have single-quotes around the XYZ.

Now if we want to put that into a VB string it would be

strSQL = "Select * From SomeTable Where Status='XYZ'"

Now this makes sense - right??

Now the real issue - if the XYZ is going to come from a string in VB - let's say strStatus - then you need to build the string as:

strSQL = "Select * From SomeTable Where Status='" & strStatus & "'"

I simply replaced the XYZ with strStatus. Put it in with the & operator - which is string concatenation. Now I'm taking 2 fixed strings and putting them together with the strStatus variable.

All along being careful to remember that SQL wants that XYZ value in single quotes.

[salvelinus]

You're checking that the value of the field in your table named MARITAL_STATUS is equal to whatever value your string variable Married contains. Married might be S, W, D, M, etc. If you put Married within the double quotes like this:

"WHERE MARITAL_STATUS = 'Married' " & _

you'll be checking that MARITAL_STATUS is equal to the actual word Married.
This may be what you want, but I'd normally advise against doing that because then your query will be static - you won't be able to check for Single, Divorced, etc. But it will work.
You can also use parameters for queries, but the specifics depend on your particular db.

[jhize]

That answers my question perfectly. Plus I re-re-re-read the tutorial. Thank you.

BTW, changed the title of Thread to better reflect the topic.

[si_the_geek]

Post #18 was fairly close jhize - you are missing an " at the end of the second line (before & _ ) and have an extra " at the end of the last line.

For the actual VB string, simply read the amount of " characters up to that point (an odd number means you are in a string, an even number means you are not).

Here is the corrected version with the actual VB strings highlighted in yellow:

sqlquestion = "SELECT NAME_LAST, NAME_FIRST FROM INDIVIDUAL" & _ 
"WHERE MARITAL_STATUS = '" & Married & "'" & _
"AND " & BIRTH_DT & " <= '1959-12-31'"

The strings within the SQL statement are indicated with ' as explained by the guys above. As it can be hard to read the mixture of VB and SQL strings (and append the values of the variables), I'd recommend using a little trick that most of us use - once you have the VB string working (the " characters match) don't try to build up the final SQL statement in your head, simply print it to the immediate window, by using: Debug.Print sqlquestion


One thing that may have confused you (it confuses others!) is the line continuation in VB, which is simply the character _ after a space, and the code continues on the next line.

The & character joins two strings, so by using & _ you are simply joining a string on one line to the string on the next (just like using "a" & "b" on the same line).

[jhize]

YES !I get it! I Understand! WooHoo!

I dont know how you guys are able to answer all of these questions (especially duplicates) but.....WOW! I am so thoroughly impressed. I truly hope I get to where you are one day. Thanks again.

[salvelinus]

One thing to note in si's last post is the single quotes surrounding the date. This is correct if your date is actually a string datatype, or if it's a date datatype in a SQL Server or MySql database, but if it's a date datatype in Access, use # signs rather than single quotes, as you indicate in post 18.
Dates can be confusing (lots of threads lately). There's a good FAQ in the FAQ link at the top of this forum, if you run into problems there.

[jhize]

Yeah, it's me again.
I'm having a bit of trouble with sql statement where I'm looking for a date (Today).

Assuming that the connection is already established and the recordset is already defined - where am I going wrong here?

The field "Date" is in a table called Transactions, it is a date/time data type.
Mm/dd/yyyy format.

I am trying to set a variable that will always be the current date.

Dim Tdate as date
Tdate = format(Date, "mm/dd/yyyy")

Now, I know that's probably wrong, so I'll continue...

 sql = "SELECT Note FROM Transactions" & _
"WHERE Date=" ......

I have tried the (='" & Tdate & "'") approach. No go. I have tried dropping the (') and replacing with (#). Still no go.

What is it that I am doing wrong here?

[szlamany]

What is your backend DB?

Datatime fields can also store a time component. Do you have "times" in those fields as well as dates??

[si_the_geek]

There are two problems there... first of all, this is an extremely bad idea, as it gives your program a guaranteed bug:

Dim Tdate as date
Tdate = format(Date, "mm/dd/yyyy")

That second line should use just the Date function (which returns the current date) by itself, as using Format converts it into a formatted String - which then needs to be converted back to a Date data type so that it can be stored in your variable, a process that is very prone to errors (you will often find the mm/dd parts swapped around - but not always!). You can then use Format while appending it to your SQL string.

For more information on why that happens, see the article Why are my dates not working properly from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)


If you had actually declared Tdate as a String (to hold the String that Format returns), that code would be fine - assuming that the database system you are using wants dates to be specified in that format (most do).

You can see the usual formats wanted (and a reminder to never store the results of the Format function to a Date) in the article How do I use values (numbers, strings, dates) in SQL statements from our Database Development FAQs/Tutorials (at the top of this forum)


The other issue is here:

 sql = "SELECT Note FROM Transactions" & _
"WHERE Date=" ......

Having a field called Date is asking for problems, as it is a Reserved Word, and so will cause many problems in your queries. If you have any choice at all, change the name of that field - it will make working with this much easier.



If you need any more assistance after this, it would be a very good idea to answer szlamany's questions.

[jhize]

I must have missed that tutorial. I'll get on it asap. As far as my backend db it's Access 2003 and there is no time component just mm/dd/yyyy.

I will be changing the db field to Tdate

and as far as I understand, I'll be doing something like this:

dim Tdate as Date
Tdate = Date
Dim sql as String
sql = "Select Note From Transactions Where Tdate = '" & _
"Format(Date,"mm/dd/yyyy") & "'"

This will look for any entries in the Transaction Table for Today's date. Moving on...

I will be needing to incorporate Text fields that hold a user entered date. The values of which will become variables. I will need to run a query for Transaction BETWEEN Date1 and Date2.

I do see this at http://www.vbforums.com/showthread.php?t=489286 and this should help quite a bit in getting the user input date to a textbox and then to a variable:

'This example is assumes that the text was entered in the format of mm/dd/yyyy
Dim MyDate as Date
Dim TempArray() as String

    'Separate the items by the delimiter (in this example, "/") 
  TempArray() = Split(Text1.Text, "/")

    'Note: you should check here that the values are valid 
    '(such as the month is a whole number, between 1 and 12)

    'Place each item in the relevant part of DateSerial to build the date
  MyDate = DateSerial(TempArray(2), TempArray(0), TempArray(1))

[si_the_geek]

That sounds OK, apart from using a Textbox to input a Date - as you are making assumptions about the way the user formats the date, and it isn't as easy for the user to use (or for you to validate) as a proper date based control, like the DateTimePicker.

For more information on that, see the article What controls can I use to input a date/time?

[salvelinus]

Also, if you're using Access, Date datatypes are surrounded by # signs, similar to the way strings are surrounded by single quotes. But if your date is not a Date datatype but rather a string, back to single quotes.

[jhize]

WoW! Even with the tutorial and your help I struggled on this one BUT! I got it.

Dim sql As String
Dim Tdate As Date
Tdate = Date
sql = "Select Note From Transactions Where Tdate = #" & Tdate & "#"
rst.Open sql, 
Note.Text = rst!Note

So Next on my list is attempting a sql BETWEEN.

By the way, I use a datepicker (Calendar1) to populate textboxes formatted the way I want them (not the way the user wants it). So far, it works good.

AND ! How do you Highlight a piece of code in this forum? I've seen it before and can't find it now.

[si_the_geek]

That isn't quite right I'm afraid.. while you can't store a Formatted date back into a Date variable, you need to use a Formatted date within an SQL statement - as shown in the "How do I use values" article.

This means you should have something like this:

Dim Tdate As String
Tdate = format(Date, "mm/dd/yyyy")
sql = "Select Note From Transactions Where Tdate = #" & Tdate & "#"

or this:

Dim Tdate As Date
Tdate = Date
sql = "Select Note From Transactions Where Tdate = #" & format(Tdate, "mm/dd/yyyy") & "#"

By the way, I use a datepicker (Calendar1) to populate textboxes formatted the way I want them (not the way the user wants it). So far, it works good.

Why do you want to put a Date value into a TextBox (as a string), rather than keep it in the DateTimePicker (as a date)?

AND ! How do you Highlight a piece of code in this forum? I've seen it before and can't find it now.

That depends what you mean by highlight.. if you mean the yellow background like I used in post #22, it is [hl] text here [/hl]

[jhize]

Thanks Si,

The code I posted worked so I dodnt see a problem with using it. HOWEVER, I changed it to your first example and that works too. So! I'll be using yours. Thanks Again.

[si_the_geek]

The problem with your version is that it needs luck - it will only work properly if the computer your program is running on is set to use the US (or ISO) styles for the short-date format (the implicit CStr in yours uses the PC's short-date format).

By explicitly formatting the string that is created from the date, you can be sure that it will work, no matter what the PC settings are.

[szlamany]

You had this:

Dim Tdate As Date
Tdate = Date
sql = "Select Note From Transactions Where Tdate = #" & Tdate & "#"

Problem I see is that TDate is a datatype of Date.

And you are concatenating it with a string into a string variable.

That is bad form.

Si's suggestion to use format is the proper way to turn a datatype of Date into a string.

[jhize]

And Now, that makes perfect sense. I knew there was a reason you suggested your code in favor of mine. Thanks a million to you both.

[jhize]

OK, the above code works perfectly. However, If the sql finds no match it returns an error "Either EOF or BOF is flase or the record has been deleted". I think that is the way it's worded.

The problem is....There will be times when the sql will find no matches for today's date. I added an error handler but that did not work they way I expected.

How would you code say, if there are no matches then return a msgbox?
Something like this but I know this is wrong.

...
rst.Open sql, 
if rst!Note <> "" then
Note.Text = rst!Note
else
Msgbox "No Match."
end if

The highlighted code is where Im having the most difficulty. How can I capture if the field is empty?

[si_the_geek]

First off, to see if there are any records, check the EOF property just after opening the recordset (and BOF too if you do it later), eg:

rst.Open sql, ...
If rst.EOF Then   'or:  If (rst.BOF And rst.EOF) Then
  Msgbox "no records"
Else
  '... code to work with the records
  rst.Close
End If
Set rst = Nothing

You should perform this kind of check every time you open a recordset, as otherwise you are likely to get the error you saw.


Note that you don't want to "capture if the field is empty", as the problem is that there are no records - and thus no fields (each record contains one or more fields).

Just for info... To check if a field is empty, it depends on what you mean by empty (an empty string? Null? either?). These are the usual methods:

if rst.Fields("Note").Value <> "" then  'check if it is an empty string
if IsNull(rst.Fields("Note").Value) then  'check if it is Null
if rst.Fields("Note").Value & "" <> "" then  'check if it is an empty string or Null

[szlamany]

Si - I'm guessing that this is a typo

if rst.Fields("Note").Value & "" then 'check if it is an empty string or Null

should be

if rst.Fields("Note").Value & "" <> "" then 'check if it is an empty string or Null

Right?

[si_the_geek]

That's right.

Thanks for pointing it out.