We help IT Professionals succeed at work.

date formatting and multiline query

suniljohn
suniljohn asked
on
Medium Priority
232 Views
Last Modified: 2010-05-02
hello
How to write sql querries in multilines in VB?
Also if your database is MSAccess how to
find the date between two dates represented by DTPickers?
thanks
sunil
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Dim SQL as string

SQL = "Select *" + vbCRLF
SQL = SQL + " From YourTable" + vbCrLf
SQL = SQL + " Where [YourDate] Between #mm/dd/yy# and #mm/dd/yy#" + vbcrlf
sql = sql + " Order By [YourDate];"

Notes:

"+ vbCRLF" at the end of the line is optional Access does not need them.
But when debugging the SQL it is easier to read if the CRLF are included.

But when debugging the SQL string you can say:

If InIDE Then
    On error resume next
Else
    On Error Goto ErrorHandle
End If

Set RS = DB.OpenRecordSet(SQL, dbOpenSnapshot)
If Err.Number<>0 Then
    Clipboard.Clear
    Clipboard.Settext SQL
    stop ' do this only when testing in IDE
End If


Hope this helps, inthedark

CERTIFIED EXPERT

Commented:
I don't use DTPicker dates but I have had a look at the documentation and they should return a datevalue.

So I would create a Function that coverts the dates to sql format.

For Example:

Function SLQDate(YourDTPicker.Value) as string
   SLQDate = "#" + Format(YourDTPicker, "mm/dd/yyyy") + "#"
End Function

So:

SQL = SQL + "Where MyDate Beyween " + SQLDate(dtpDate.value)
SQL = SQL + " And "+ SQLDate(dtpDate.value)

 

Commented:
its less messy to multiline your string assignments like so



strSQL = "select whatever from" & _
         " where something=something and " & _
         "Anotherthing=anothernumber " & _
         group by whatever having blah blah blah"

Commented:
listening
CERTIFIED EXPERT

Commented:
Crazyman, your right for simple statements but in real life things just aren?t that simple. But there is a place for both cases so here are the pros and cons so suniljohn can be aware of the limitations and advantages of both methods.

Using Line-Continuation

Example:

SQL=?Select * From YourTable? & _
           ?Where A=1234?
Pros

1) More readable code
2) Faster execution (so much quicker in loops)
 
Cons

1) Infelxable -In a real world situation the operator maybe want's a report for one account, or maybe all accounts or maybe just a range.  And then the selects the data to be printed, will it be last month, last year, the first quarter, etc.  So you can?t put ?IF??s within a line-continuation block.

2) Limited to just 25 continuation lines.  In many cases a complex, but readable, sql statement will extend beyond 25 lines.  For example: an update query updating several fields, creating a table, generating a stored procedure, etc.

3) You can?t put comments within a continuation-line block.

4) Increased requirements for variables to enable flexibility

Using Concatenation

Example

SQL=?Select * From ?
If HistoryReport Then ? Select the history or live data
   SQL=SQL+? [HistoryTable]?
Else
   SQL=SQL+? [LiveTable]?
End If
If Not PrintAll Then ? Did the op. put in a date range
    SQL = SQL + ? Where [TransDate] Between #1/1/2002# And #31/1/2001#
End If

Pros

1) Flexible
2) Unlimited by size
3) Put comments anywhere

Cons

1) Slower execution ? but compared to the time taken up reading a recordset from a database this extra time is negligible.
2) Esthetically less readable for simple statements

CERTIFIED EXPERT

Commented:
Sorry I pasted from a Word document so ? should read as "
CERTIFIED EXPERT

Commented:
Here is the above comment reposted from Notepad instead of Word:

Crazyman, your right for simple statements but in real life things just aren?t that simple. But there is a place for both cases so here are the pros and cons so suniljohn can be aware of the limitations and advantages of both methods.

Using Line-Continuation

Example:

SQL="Select * From YourTable" & _
           "Where A=1234"
Pros

1) More readable code
2) Faster execution (so much quicker in loops)
 
Cons

1) Infelxable -In a real world situation the operator maybe want's a report for one account, or maybe all accounts or maybe just a range.  And then the selects the data to be printed, will it be last month, last year, the first quarter, etc.  So you can?t put ?IF??s within a line-continuation block.

2) Limited to just 25 continuation lines.  In many cases a complex, but readable, sql statement will extend beyond 25 lines.  For example: an update query updating several fields, creating a table, generating a stored procedure, etc.

3) You can?t put comments within a continuation-line block.

4) Increased requirements for variables to enable flexibility

Using Concatenation

Example

SQL="Select * From"
If HistoryReport Then ? Select the history or live data
   SQL= SQL + "[HistoryTable]"
Else
   SQL= SQL + "[LiveTable]"
End If
If Not PrintAll Then ? Did the op. put in a date range
    SQL = SQL + " Where ([TransDate] Between #1/1/2002# And #31/1/2002#)"
End If

Pros

1) Flexible
2) Unlimited by size
3) Put comments anywhere

Cons

1) Slower execution - but compared to the time taken up reading a recordset from a database this extra time is negligible.
2) Esthetically less readable for simple statements

CERTIFIED EXPERT

Commented:
Further suggestion:

But suniljohn the real cute way of handling SQL which requires very little debugging.

1) Create an SQL.MDB which you keep in the same folder as your application.

2) Create a table called zSQL with:

Fields: SQLKey (Set as primary key), Description and SQL a memo field.

When ever you need a new sql statement.

3) Create the query using Access in your data DB.

Change the settings to View-SQL  then copy and paste into your SQL table.

If you need to add parameters to you query you can use tokens like ##STARTDATE#-, for example.


So now in your simple bug-free code you can get your sql from a function:

SQL = GetSQL("SALES REPORT") ? Read SQL from table

Sql = Replace(SQL, "##STARTDATE#-",  SQLDate(dtpStartDate))

' SQL now ready for use.

RS = DB.OpenRecordSet(SQL, dbOpenSnapshot)

Further Advancement:

Now the cute thing about this technique is that you can code your tokens in such a manner that they can tell you what to ask the operator before the report starts running.  So when ever you start a report you don?t need a start screen that asks for ranges of dates, account numbers, etc.

Just INSTR the SQL for "##" and then "#-" go the your parameters.

For example:

##Message For The Screen; DataType#-

##Start Account;Text#-
##Start Date;SDATE#-
##End Date;EDATE#-


The data type keys EDate and SDate tells you that you have to validate the input as a date range pair.
Commented:
Query writing in VB is

sql = "insert into tbltemp value ('" & CharField1.text & _
      "','" & charfield2.text & "'," & _
      NumberFields1.Text & ")"

OR

sql = "insert into tbltemp value ('"
sql = sql & CharField1.text "','"
sql = sql & charfield2.text & "',"
sql = sql & NumberFields1.Text & ")"

con.Execute (sql)

Find Data between tow dates as;

sql = "select * from saledetail where date between #" & frmSale.DTPicker1.Value & "# and #" & frmSale.DTPicker2.Value & "#"
Set rec = con.Execute(sql)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.