Chap 8 Data management techniques
Auth R Smith and D Sussman

There is code in this chapter to build a SQL String which for the most part I understand. The Check Box and Combo box code is logical as also is most of the string build up but I feel that I am missing something important because:

I cant figure out the following:

Why does the single letter (i) without brackets appear in the code. What is its function It doesnt seem to a variable
There is no
Dim i as (whatever)
I have emboldened one of the lines in which (i) appears

Why is the variable strSelect = "s.* "

I append the code on the assumption that not everyone has the book

If someone an explain the above whys to me I would be grateful

Regards

Man Friday


Dim sSELECT As String
Dim sFROM As String
Dim sWHERE As String

sSELECT = "s.SalesID "

sFROM = "tblSales s "
If chkIngredientID Then
    sFROM = sFROM & " INNER JOIN tblIceCreamIngredient i " & _
        "ON s.fkIceCreamID = i.fkIceCreamID "
    sWHERE = " AND i.fkIngredientID = " & cboIngredientID
End If

If chkCompanyID Then
    sWHERE = sWHERE & " AND s.fkCompanyID = " & cboCompanyID
End If

If chkIceCreamID Then
    sWHERE = sWHERE & " AND s.fkIceCreamID = " & cboIceCreamID
End If

If chkDateOrdered Then
    If Not IsNull(txtDateFrom) Then
        sWHERE = sWHERE & " AND s.DateOrdered >= " & _
        "#" & Format$(txtDateFrom, "mm/dd/yy") & "#"
    End If
    If Not IsNull(txtDateTo) Then
        sWHERE = sWHERE & " AND s.DateOrdered <= " & _
        "#" & Format$(txtDateTo, "mm/dd/yy") & "#"
    End If
End If

If chkPaymentDelay Then
    sWHERE = sWHERE & " AND (s.DatePaid - s.DateOrdered) " & _
    cboPaymentDelay & txtPaymentDelay
End If

If chkDispatchDelay Then
    sWHERE = sWHERE & " AND (s.DateDispatched - s.DateOrdered) " & _
    cboDispatchDelay & txtDispatchDelay
End If

sSQL = "SELECT " & sSELECT
sSQL = sSQL & "FROM " & sFROM
If sWHERE <> "" Then sSQL = sSQL & "WHERE " & Mid$(sWHERE, 6)

BuildSQLString = True

End Function

Hi ManFriday

These are not VBA variables, they are part of the SQL (i.e. inside the strings). They are table name aliases - look at the sFROM string, where tblSales has an "s" after it, and tblIceCreamIngredient has an "i". All this is doing is making it easier to refer to fields of each table in the SELECT and WHERE clauses of the SQL. It's much easier to type "i.fkIngredientID" than it is to type "tblIceCreamIngredient.fkIngredientID" each time.

Does that help?

Richard

Richard

Thank you I did not know about the "Alias" concept.
To clarify my understanding then
There are at least two ways to create an alias

1. Declare a string variable as Dim sSELECT As String
   then making sSELECT = s.SalesID
(The combination of Dim sSelect and sSELECT causes s to be understood
 by the VBA Editor as referring to the table tblSales.

2. To place (in this case) the letter (i) immediately after a table
   name within a string declaration as per

  ( " INNER JOIN tblIceCreamIngredient i " )

 The VBA Editor now recognises both i and s as aliases for each table??

On a slightly different tack I have not yet tried out the code.

Would you know if if have to change the way in which dates are expressed as I work on UK and not USA date (dd mm yy) and not (mm dd yy)

Apologies for sneaking this one. thanks in anticipation

Mel
aka Man Friday

Richard
Hi

I just looked up Alias in the help file. When I started to read about the Alias property it range a bell. In the past I had "reset" column names using QBFs.

The SQL code generated usu used the keyword AS and tended to use "recognisable" words and not just single letters. Which I think is what threw me ten left.

In the meantime I have looked up the Alias property in the Help File

Would still appreciate your input with regard to any changes in syntax when using UK Date as opposed to USA Date

I believe that when dealing with Teach Yourself Texts (They tend to be USA Authors)who tend to use native syntax. I have found tho that they are usu more than helpful if you can get an email address for them and contact them directly by e-mail

Mel

There are several things going on here. To answer your new questions:

Dim sSELECT declares a variable and reserves memory for it at its proper size. All variable types have different sizes, and you want to declare each one so that you remember what its name is and reserve the proper amount of memory for it before the process runs. Basically.

So declaring a variable has nothing to do with the value you assign to is.

Using aliases is a SQL issue, not a variable issue. So the SQL engine interprets the string you pass to it when you pass it. Access uses SQL ANSI 89, which allows aliases using "TableName i", for example. If you were using an alias for a column within the table, you would use "As", like "SELECT [PrimaryKey] As PK..." or more commonly "SELECT Count([PrimaryKey]) As CountOfPK..."

But it is SQL interpreting this, not the code itself. The VBA engine just passes what you type, and then the SQL (or Jet) engine passes an error if the syntax is wrong.

In any event, it is not the combination of the declaration and the syntax that does it, it is the syntax only. Declarations are optional, but very good practice. It is this syntax "tblSalesEtc s" that sets up the alias, not "s.SalesID". The fact that the SELECT statement shows up before the alias declaration in the code is bad form for this book, but not fatal. The entire string might look like this when compiled:

sSQL = "SELECT s.SalesID
FROM tblSales s
INNER JOIN tblIceCreamIngredient i ON s.fkIceCreamID = i.fkIceCreamID...

The SQL engine will know to look for aliases in the string if it doesn't recognize the table name (s as in s.SalesID) in the FROM or JOIN parts of the statement.

Did any of that help?



mmcdonal

mmcdonal

Very helpful indeed. I think that the Authors ought to have taken the trouble to set aside a paragraph to explain the presence of the alias in the code.

Particularly as the book does go thru the code piece by piece to explain what is going on. I did check the books index and there is no mention vis a vis the use of the alias in SQL

I am most grateful to you for this additional and detailed explanation

Regards

Mel

Well, mmcdonal has already answered most of your questions, and pretty much exactly as I would have done. However, there is still the question of the date format in SQL, and the Americo-centric assumption of "mm/dd/yyyy". The easiest answer is to write yourself a global function, something like this (typed from memory, so it may not be exactly 100% working code - but I have posted this before, so it might be worth searching for that topic):-

Public Function FormatSQLDate(datDate as Date) As String

FormatSQLDate = "#" & VBA.Format(datDate, "mm/dd/yyyy") & "#"

End Function

Then when building your SQL, do something like this (I made up the tables!):

strSQL = "SELECT OrderId, CustomerId, OrderTotal FROM tblOrders WHERE OrderDate = " & FormatSQLDate(cboOrderDate)

Does that make sense?

Richard

Thank you for getting back to me on that.

I will try it out and let you know how I get on

The dates can be vexing. I have two books on Access by the Author John Viescas whose texts are really excellent. Particularly his "Building Microsoft Applications"

I wish I could say the same for the Smith Sussman book

I have just discovered there is very little explanation
regarding the row source in an unbound txt object in a form used to
allow the client to create queries

RowSource which is set to
">";"More than";"<";"Less than"

RowType which is set to
ValueList

I can see how it works but there is little explanation as to
WHY it works if you know what I mean. Tho to be fair now that my
knowledge is improving some of the other unexplained items have become clearer

But back to the Time Issue

There are occasions when the code written for USA formats has required to be changed to suit UK format

Again Thank You for taking the time to answer my question

Also may I say thanks to those others who have responded to my questions

I will be sticking to this forum like glue

MF