[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

query access 200

Posted on 2006-05-14
4
Medium Priority
?
215 Views
Last Modified: 2008-03-17
ok here's my problem i am using a query like the one listed below.i am migrating from dao to ado how do i convert this thanks.
Private Sub cmdRunQuery_Click()
   
      Dim MyDatabase As Database
      Dim MyQueryDef As QueryDef
      Dim where As Variant

      Set MyDatabase = CurrentDb()

      ' Delete the existing dynamic query; trap the error if the query does
      ' not exist.
      If ObjectExists("Queries", "qryDynamic_QBF") = True Then
        MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
        MyDatabase.QueryDefs.Refresh
      End If
      ' Note the single quotation marks surrounding the text fields [Ship
      ' Country] and [CustomerID].
      ' Note that there are no type-casting characters surrounding the
      ' numeric field [EmployeeID].

      where = Null
      where = where & " AND [ShipCountry]= '" + Me![Ship Country] + "'"
      where = where & " AND [CustomerID]= '" + Me![Customer Id] + "'"
      where = where & " AND [EmployeeID]= " + Me![Employee Id]

      If left(Me![Ship City], 1) = "*" Or Right(Me![Ship City], 1) = "*" Then
         where = where & " AND [ShipCity] like '" + Me![Ship City] + "'"
      Else
         where = where & " AND [ShipCity] = '" + Me![Ship City] + "'"
      End If

      If Not IsNull(Me![Order End Date]) Then
         where = where & " AND [OrderDate] between #" + _
         Me![Order Start Date] + "# AND #" & Me![Order End Date] & "#"
      Else
         where = where & " AND [OrderDate] >= #" + Me![Order Start Date] _
         + " #"
      End If


      Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_QBF", _
        "Select * from orders " & (" where " + Mid(where, 6) & ";"))
      DoCmd.OpenQuery "qryDynamic_QBF"
End Sub
0
Comment
Question by:wwstudioinc
  • 3
4 Comments
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 1000 total points
ID: 16677474
as with your other query, you need to modify you where variable, like this:

      where = Null
      where = where & " [ShipCountry] = '" & Me![Ship Country] & "'"
      where = where & " AND [CustomerID]= '" & Me![Customer Id] & "'"
      where = where & " AND [EmployeeID]= " & Me![Employee Id]

      If left(Me![Ship City], 1) = "*" Or Right(Me![Ship City], 1) = "*" Then
         where = where & " AND [ShipCity] like '*" + Me![Ship City] + "*'"
      Else
         where = where & " AND [ShipCity] = '" & Me![Ship City] & "'"
      End If

      If Not IsNull(Me![Order End Date]) Then
         where = where & " AND [OrderDate] between #" & _
         Me![Order Start Date] & "# AND #" & Me![Order End Date] & "#"
      Else
         where = where & " AND [OrderDate] >= #" & Me![Order Start Date] _
         & " #"
      End If


      Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_QBF", _
        "Select * from orders where "  & where & ";")
      DoCmd.OpenQuery "qryDynamic_QBF"
End Sub

AW
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16677479
also, to change to ADO, you cannot use Querydefs at all, since ADO does not recognize the QueryDef model in any way.

AW
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16677483
you will need to totally re-design the entire appraoch to what you are attempting to accomplish with this code.  Querydefs are a TOTALLY DAO concept, and there is no equivalent in ADO.

AW
0
 

Author Comment

by:wwstudioinc
ID: 16677791
i see that i am not going to get any way with this ado thing,i'll just use it with popup forms as i have done for my searches
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

834 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