Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help writing SQL query

Posted on 2001-06-26
16
Medium Priority
?
187 Views
Last Modified: 2010-05-02
Can someone please Help

I have a rather complex sql query I've been trying to write for a number of weeks, but somehow keep stuffing it up, and I'm ready to rip my hair out (as well as the hair of anyone else who happens to walk by)


I'm trying to write a program for myself that will help me budget my money better, the only problem is that while I can write simple programs, I've had very little need for sql in the past and I'm having enormous problems with the syntax etc. I've obtained a number of VB/Access books from the local library but I finding it a bit of a struggle.

I have form with four dropdown combo boxes, that provide imformation on search criteria for 5 fields in an Access Database

The Database Name is "Budget.mdb"
The Table Name is "Finance"

The Field Names are:
0. [Bill Name]    (text Field)
1. [Payment Type] (text field)
2. [Priority]     (integer field)
3. [Due Date]     (date/time field)
4. [To Pay Date]  (date/time field)
5. [Paid Date]    (date/time field)


Combo1 does a search on "All Payment types or just one selected type"
The database field name to be searched is [Payment Type]

    Combo1.AddItem "All Payment Types"
    Combo1.AddItem "Cash"
    Combo1.AddItem "Cheque"
    Combo1.AddItem "Credit Card"
    Combo1.AddItem "Direct Debit"


Combo2 does a search on "Priority type" field
The databasefield name is [Priority]
   
    Combo2.AddItem "All Priorities"
    Combo2.AddItem "1"
    Combo2.AddItem "2"
    Combo2.AddItem "3"

   
Combo3 does a search on one of 3 Fields
The database field names to be searched are either [Due Date], [Pay Date] or [Paid Date]

    Combo3.AddItem "Due Date"
    Combo3.AddItem "To Pay Date"
    Combo3.AddItem "Paid Date"



Combo4 then restricts the search to "All Dates" in the database ; or "between two dates" which are entered into 2 seperate text boxes (text1 & text2)

    Combo4.AddItem "All Dates"
    Combo4.AddItem "Selected Dates"


Can anyone help write this for me as I'm at my wits end.
0
Comment
Question by:backflash
  • 7
  • 2
  • 2
  • +5
16 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 6227864
can you get as far as opening the database table and selecting records, or do you just need to build the sql string?

Are you using Visual Basic itself or VBA within Access?

0
 
LVL 6

Accepted Solution

by:
andyclap earned 900 total points
ID: 6227903
OK, the easiest way of doing this is to build the SQL at the client side.

Something like (with a couple of helper functions)

public sub SQLAddWhere(byref sWhere as string, byval sAdd as string)
  if sWhere="" then
    sWhere="Where " & sAdd
  else
    sWhere=sWhere & " and " & sAdd
  end if
end sub

public function q(byval s as string) as string
  q="'" & replace(s,"'","''") & "'"
end function

public function SQLDate(byval dt as date)
  SQLDate="#" & format$(dt,"yyyy\/mm\/dd hh:nn:ss") & "#"
end function

private Function MakeQuery() as String

  dim sSQL as string
  dim sWhere as string
  dim sDateWhere as string

  'build up a select string
  sSQL="Select * from [Finance]"
  sWhere=""

  'payment terms
  if combo1.text<>"All Payment Types" then
    SQLAddWhere sWhere,"[Payment Type]=" & q(combo1.text)
  end if

  'priorities  
  if combo2.text<>"All Priorities" then
    SQLAddWhere sWhere,"[Priority]=" & combo2.text
  end if

  'dates
  if combo4.text<>"All Dates" then
    if isdate(text1.text) and isdate(text2.text) then
      sDateWhere="[" & combo3.text & "] between " & SQLDate(cdate(text1.text)) & " and " & SQLDate(cdate(text2.text))
      SQLAddWhere sWhere, sDateWhere
    else
      'dates not correct
      msgbox "Incorrect date entered!"
    end if
  end if
   
  'did we get a where clause? add it to the SQL
  if sWhere<>"" then
    sSQL=sSQL & sWhere
  end if


  'return the query
  MakeQuery=sSQL

end function


you can then use something like

set rs=con.execute(MakeQuery()) to get back a recordset.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6227922
Another variation:

Dim blnAnd As Boolean

strSQL = "Select [Bill Name],[Payment Type],[Priority],[Due Date],[To Pay Date],[Paid Date] From Finance Where "
If Combo1.Text <> "All Payment Types" Then
  strSQL = strSQL & "[Payment Type] = '" & Combo1.Text & "'"
  blnAnd = True
End If
If Combo2.Text <> "All Priorities" Then
  strSQL = strSQL & iif(blnAnd," AND ","") & "[Priority] = '" & Combo2.Text & "'"
  blnAnd = True
End If
If Combo4.Text = "All Dates" Then
  Select Case Combo3.Text
  Case "Due Date"
    strSQL = strSQL & iif(blnAnd," AND ","") & "[Due Date] <> ''"
    blnAnd = True
  Case "To Pay Date"
    strSQL = strSQL & iif(blnAnd," AND ","") & "[To Pay Date] <> ''"
  Case "Paid Date"
    strSQL = strSQL & iif(blnAnd," AND ","") & "[Paid Date] <> ''"
  End Select
Else
  Select Case Combo3.Text
  Case "Due Date"
    strSQL = strSQL & iif(blnAnd," AND ","") & "([Due Date] >= '" & Text1.Text & "' AND [Due Date] <= '" & Text2.Text & "')"
    blnAnd = True
  Case "To Pay Date"
    strSQL = strSQL & iif(blnAnd," AND ","") & "([To Pay Date] >= '" & Text1.Text & "' AND [To Pay Date] <= '" & Text2.Text & "')"
  Case "Paid Date"
    strSQL = strSQL & iif(blnAnd," AND ","") & "([Paid Date] >= '" & Text1.Text & "' AND [Paid Date] <= '" & Text2.Text & "')"
  End Select
End If

Then strSQL should contain the appropriate SQL statement to send to the database.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:mmuruganandam
ID: 6227985
dim sSqlQuery as String
dim sWhereClause as String

sSqlQuery = " SELECT * FROM FINANCE  "

if trim(combo1.text) = "All Payment Types" then
   ' no need to add it to the search criteria
else
  sWhereClause = " PaymentType = '" & trim(combo1.text) & "' "
end if

if (trim(combo2.text) = "All Priorities") then
  ' no need to add this to the search criteria
else
   sWhereClause = sWhereClause & iff(trim(sWhereClause) = "",""," and ") & " PRIORITY = " & trim(combo2.text)
end if


if (trim(combo4.text) = "All Dates") then
  ' no need to be added in the search criteria
else
  sWhereClause = sWhereClause & iff(trim(sWhereClause) = "",""," and ")

if (trim(combo3.text) = "To Pay Date") then
    sWhereClause = sWhereClause & " TOPAYDATE " 
else if (trim(combo3.text) = "Due Date") then
    sWhereClause = sWhereClause & " DUEDATE "
else if (trim(combo3.text) = "Paid Date") then
    sWhereClause = sWhereClause & " PAIDDATE "
end if

sWhereClause = sWhereClause & " BETWEEN " & format(trim(text1.text),"mm/dd/yyyy") & " AND " & format(trim(text2.text),"mm/dd/yyyy")

end if

sSqlQuery = sSqlQuery & iff(trim(sWhereClause) = "", "  ", " WHERE " & sWhereClause)




This is how you have frame the query for your requirement.

Your query is available in the sSqlQuery variable.


All the best


Cheers,
Muru
0
 
LVL 6

Expert Comment

by:devtha
ID: 6228124
I have a function either for Access or VB and all you have to do is to follow a small rule in designing the controls on the form. The function returns the criteria and opens the report or form by applying this criteria.
0
 
LVL 5

Expert Comment

by:gbaren
ID: 6228208
backflash,

Try this:

===================================================
Dim strWhere As String
Dim strDate As String
Dim strDate1 As String
Dim strDate2 As String
Dim strSQL As String

If Combo1.ListIndex > 0 Then
    strWhere = "[Payment Type] = '" & Combo1.Text & "'"
End If
If Combo2.ListIndex > 0 Then
    If Len(strWhere) > 0 Then
        strWhere = strWhere & " AND "
    End If
    strWhere = strWhere & "[Priority] = " & Combo1.Text
End If
If Combo4.ListIndex > 0 Then
    Select Case Combo3
    Case 0
        strDate = "[Due Date]"
    Case 1
        strDate = "[To Pay Date]"
    Case 2
        strDate = "[Paid Date]"
    End Select
    strDate1 = InputBox("Starting " & strDate)
    strDate2 = InputBox("Ending " & strDate)
    strDate = strDate & " BETWEEN #" & strDate1 & "# AND #" & strDate2 & "#"
    If Len(strWhere) > 0 Then
        strWhere = strWhere & " AND "
    End If
    strWhere = strWhere & strDate
End If
   
strSQL = "SELECT [Bill Name], [Payment Type], [Priority], [Due Date], [To Pay Date], [Paid Date] "
strSQL = strSQL & "FROM tblTransaction "
If Len(strWhere) > 0 Then
    strSQL = strSQL & strWhere
End If
0
 
LVL 6

Expert Comment

by:devtha
ID: 6228224
The function I have is dynamic and even in future if you add more controls and follow the rule it will handle that.
0
 
LVL 6

Expert Comment

by:devtha
ID: 6228332
I have a function either for Access or VB and all you have to do is to follow a small rule in designing the controls on the form. The function returns the criteria and opens the report or form by applying this criteria.
0
 
LVL 6

Expert Comment

by:devtha
ID: 6228336
What ths .... is going on The time stams are exchange on my last 2 posts.
0
 
LVL 6

Expert Comment

by:devtha
ID: 6228342
I have a function either for Access or VB and all you have to do is to follow a small rule in designing the controls on the form. The function returns the criteria and opens the report or form by applying this criteria.
0
 
LVL 6

Expert Comment

by:devtha
ID: 6228347
Very funny and strange. The last post was talking about previous posts and look at the time stamps....
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6228443
EE is a strange world, isn't it?
0
 
LVL 6

Expert Comment

by:devtha
ID: 6228449
EE is not strange to me. As a matter of fact it is a great concept. It is just that during and after upgrades these problems has started.
0
 

Author Comment

by:backflash
ID: 6240059
Sorry to keep you people waiting on my grading.
I'm having problems logging on to my ISP due to a relocation of thier business. It is probably going to have limited access for the next week or so. (this is the first time I've been able to get net access in a number of days)

While none of the examples have worked error free
I've just had a reasonable look at all the different approaches you have taken, and I should to be able to get one of them to work,

If you get them to work before I do let me know.
(Most of the more obvious errors are what prompted the question in the first place, ie typo's etc,  although looking through your code has helped me greatly)


Thanks for your help so far, you have some great approaches,  even my hairs starting to grow back (or at least some of it)

Devtha, I would definatly like to have a look at your code, it sounds like a great approach

I'm using VB5 (sp3) I have a dbGrid attached to a data control. I click a command button to execute the query which updates the dataControl and dbGrid control with the results of the query.


0
 

Author Comment

by:backflash
ID: 6243363
None of the solutions have actually worked. The all presented me with the same problems I already had....

But I'll award the points on a first come first serve basis. (I have no way of really knowing if any are drastically incorrect or it's my inexperience with sql)


I have learned a great deal about how to approach the problem of multiple choice sql.

Thanks for your help

Backflash
0
 
LVL 6

Expert Comment

by:andyclap
ID: 6246555
Sorry it didn't work out for you - but thanks for the points anyway.
If it's a particular problem rather than a general one you're having, let us know here and we'll take a look.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question