Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2008
    Posts
    3

    Unanswered: My QBF not functioning well

    Hello everyone on this forum.

    I am glad to join you and i have this problem. I am creating a database for motor vehicles. The code i am trying to customise is from this link: http://kbalertz.com/210242/implement...ef-Access.aspx

    However, i am using ms access 2003. I intend to have three text boxes (VIN, Begin Date, End date) on the Query form, such that if i only have the VIN the query will return it if it is in the record, or alternatively by dates.

    Please inspect the attached database for me. The code behind the search button is as follows (i left comments as originally provided):


    Private Sub cmdRunQuery_Click()
    Dim db As DAO.Database
    Dim QD As QueryDef
    Dim where As Variant

    Set db = CurrentDb()

    ' Delete the existing dynamic query; trap the error if the query does
    ' not exist.
    On Error Resume Next
    db.QueryDefs.Delete ("Dynamic_Query")
    On Error GoTo 0

    ' 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 [VIN]= '" + Me![VIN] + "'"
    where = where & " AND [Date of theft]= '" + Me![Begin date] + "'"
    where = where & " AND [Date of theft]= " + Me![End Date]

    'NOTE: In Microsoft Access, when you use the plus sign (+) in an
    'expression in which you are concatenating a variable of the numeric
    'data type, you must use parenthesis around the syntax, as in the
    'following example:
    '
    ' where = where & (" AND [EmployeeID]= " + Me![Employee Id])
    '
    'You must also use a conversion function to make sure that the proper
    'conversion (to either NULL or String) takes place.

    ' The following section evaluates the ShipCity criteria you enter.
    ' If the first or last character of the criteria is the wildcard
    ' character (*), then the function uses the "LIKE" operator in the
    ' SQL statement instead of "=". Also note the single quotation
    ' marks surrounding the text field [ShipCity].
    If Left(Me![VIN], 1) = "*" Or Right(Me![VIN], 1) = "*" Then
    where = where & " AND [VIN] like '" + Me![VIN] + "'"
    Else
    where = where & " AND [VIN] = '" + Me![VIN] + "'"
    End If

    ' Note the number signs (#) surrounding the date field [Order Date].
    If Not IsNull(Me![Begin date]) Then
    where = where & " AND [Date of theft] between #" +
    Me![Begin date] + "# AND #" & Me![End Date] & "#"
    Else
    where = where & " AND [Date of theft] >= #" + Me![Begin date] + " #"
    End If

    ' Remove the following MsgBox line if you do not want to display the
    ' SQL statement.
    ' NOTE: The Mid function is used in the following MsgBox function to
    ' remove the word AND that follows the first Where clause. If you do
    ' not use the Mid function, the SQL statement contains the word AND
    ' at the beginning of the WHERE clause, for example:
    '
    ' Select * from Orders where AND [CustomerID] = 'CACTU'

    MsgBox "Select * from Orders " & (" where " + Mid(where, 6) & ";")
    Set QD = db.CreateQueryDef("Dynamic_Query",
    "Select * from orders " & (" where " + Mid(where, 6) & ";"))
    DoCmd.OpenQuery "Dynamic_Query"
    End Sub



    Can someone please examine and modify it for me.

    I will appreciate.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    We don't do homework on this site.

    However, we may be able to assist you with any specific questions you may have
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •