Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: SQL Query in Form does work for text (string) not for numbers

Results 1 to 2 of 2
Share This Thread →
  1. #1
    Paris79 is offline Registered User
    Join Date
    Feb 2004
    Rep Power

    Post SQL Query in Form does work for text (string) not for numbers


    I use an Access-Form to make a SQL-Query in order to select certain data.
    The form is rather simple:

    3 Comboboxes (cboOffice, cboDepartment, cboGender)
    and one execute button (cmdOK_Click).

    The form works fantastic for all data fields which are set as "TEXT". But now I want to use the same query to perform it on "NUMBER" fields too. I tried to adapt the query but nothing works!

    If I set "strOffice As Number" nothing works...

    Here is the query:

    Private Sub cmdOK_Click()
    ' Pointer to error handler
    On Error GoTo cmdOK_Click_err
    ' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strOffice As String
    Dim strDepartment As String
    Dim strGender As String
    Dim strSQL As String
    ' Identify the database and assign it to the variable
    Set db = CurrentDb
    ' Check for the existence of the query, create it if not found,
    ' and assign it to the variable
    If Not QueryExists("qryStaffListQuery") Then
    Set qdf = db.CreateQueryDef("qryStaffListQuery")
    Set qdf = db.QueryDefs("qryStaffListQuery")
    End If
    ' Get the values from the combo boxes
    If IsNull(Me.cboOffice.Value) Then
    strOffice = " Like '*' "
    strOffice = "='" & Me.cboOffice.Value & "' "
    End If
    If IsNull(Me.cboDepartment.Value) Then
    strDepartment = " Like '*' "
    strDepartment = "='" & Me.cboDepartment.Value & "' "
    End If
    If IsNull(Me.cboGender.Value) Then
    strGender = " Like '*' "
    strGender = "='" & Me.cboGender.Value & "' "
    End If
    ' Build the SQL string
    strSQL = "SELECT tblStaff.* " & _
    "FROM tblStaff " & _
    "WHERE tblStaff.Office" & strOffice & _
    "AND tblStaff.Department" & strDepartment & _
    "OR tblStaff.Gender" & strGender & _
    "ORDER BY tblStaff.LastName,tblStaff.FirstName;"
    ' Pass the SQL string to the query
    qdf.SQL = strSQL
    ' Turn off screen updating
    DoCmd.Echo False
    ' Check the state of the query and close it if it is open
    If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryStaffListQuery") = acObjStateOpen Then
    DoCmd.Close acQuery, "qryStaffListQuery"
    End If
    ' Open the query
    DoCmd.OpenQuery "qryStaffListQuery"
    ' Turn on screen updating
    DoCmd.Echo True
    ' Clear the object variables
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub
    ' Handle errors
    MsgBox "An unexpected error has occurred." & _
    vbCrLf & "Please note of the following details:" & _
    vbCrLf & "Error Number: " & Err.Number & _
    vbCrLf & "Description: " & Err.Description _
    , vbCritical, "Error"
    Resume cmdOK_Click_exit
    End Sub

  2. #2
    supersubra's Avatar
    supersubra is offline Contributing User
    Join Date
    Aug 2003
    Coimbatore, India
    Rep Power
    For textfield criteria you should add single quote before and after the value but for numbers you should not.

    Sample usage

    For Text
    "Where FirstName = '" & me!txtEnteredname & "'"

    For Number field

    "Where AccountNo = " & me!txtEnteredNo

    For Date Field

    "Where BirthDate = #" & me!txtEnteredDate & "#"

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

Similar Threads

  1. ASP form to SQL query parameter
    By judo1 in forum SQL Development
    Replies: 0
    Last Post: February 10th, 2004, 02:37 PM
  2. beginner login script --sql query wont work...plz help!
    By huge81 in forum ASP Development
    Replies: 3
    Last Post: December 20th, 2003, 04:18 PM
  3. create SQL query drynamically from form results
    By shamrog12 in forum ASP Development
    Replies: 8
    Last Post: November 28th, 2003, 11:15 PM
  4. sql string does not work on host server
    By Steve Schofield in forum ASP Development
    Replies: 1
    Last Post: February 6th, 2002, 08:30 PM
  5. date-sensitive SQL query doesn't work after 2-1-01
    By Steve Schofield in forum ASP Development
    Replies: 0
    Last Post: January 3rd, 2001, 07:31 PM

ASP Free Advertisers and Affiliates