1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Age range based on DOB in query by form

Discussion in 'Business Applications' started by hollinshead, Apr 14, 2010.

Thread Status:
Not open for further replies.
  1. hollinshead

    hollinshead Thread Starter

    Aug 5, 2009
    Hi there i wonder if you can help. I am trying to create a query by form which i have mostly working, apart from one control on the criteria form. In the database i have stored a candidates date of Birth, and on this form instead of using date or birth as a critieria i wanted to use a range in the form of a combo box, so the user can select one of the following.


    I have created the QBF so that it creates a new query everytime. The code for this is displayed below. Any ideas how i can implement this range into the code. Any guidance or advice would be greatly appreciated. Thanks

    Option Compare Database
    Private Sub cmdSearchStudent_Click()
    Dim rs As Object, strsql As String, qdfNew As Object, var As Variant, tempquery As String
    Dim sSELECT As String, sFROM As String, swhere As String, itemcount As Integer, dbs As Object
    On Error GoTo errorcatch
    swhere = " WHERE not isnull([Student ID])"
    If Not IsNull(Me.txtStudentID) Then
    swhere = swhere & " AND [Student ID] = " & Me.txtStudentID
    End If
    If Not IsNull(Me.txtNameEngSearch) Then
    swhere = swhere & " AND [Student Name (English)] LIKE ""*" & Me![txtNameEngSearch] & "*"""
    End If

    If Not IsNull(Me.txtNameArabicSearch) Then
    swhere = swhere & " AND [Student Name (Arabic)] LIKE ""*" & Me![txtNameArabicSearch] & "*"""
    End If

    If Not IsNull(Me.txtDOBSearch) Then
    swhere = swhere & " AND [Date Of Birth] = #" & Me.txtDOBSearch & "#"
    End If

    If Not IsNull(Me.txtCNIANoSearch) Then
    swhere = swhere & " AND [CNIA ID Number] = " & Me.txtCNIANoSearch
    End If

    If Not IsNull(Me.txtTSCNoSearch) Then
    swhere = swhere & " AND [TSC ID Number] = " & Me.txtTSCNoSearch
    End If

    If Not IsNull(Me.txtCityAndGuildsNoSearch) Then
    swhere = swhere & " AND [City & Guilds Reg Number] = " & Me.txtCityAndGuildsNoSearch
    End If

    strsql = "SELECT [Student ID], [Student Name (English)] " & _
    "FROM tblStudents " & swhere & ";"
    Set dbs = Application.CurrentData
    tempquery = "no"
    For Each obj In dbs.AllQueries
    If obj.Name = "Search Student Query" Then
    tempquery = "yes"
    End If
    Next obj
    If tempquery = "yes" Then
    DoCmd.DeleteObject acQuery, "Search Student Query"
    End If
    With CurrentDb
    Set qdfNew = .CreateQueryDef("Search Student Query", strsql)
    End With
    DoCmd.OpenForm "frmSearchResults"
    Exit Sub
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
    End Sub
    Private Sub Command39_Click()
    DoCmd.OpenForm "frmStudentSearchCourses", acViewNormal
    End Sub
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/916790

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice