Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2004
    Posts
    10

    Unanswered: Adding Query Fields

    Is there a way to add fields to a query based on what checkbox you have selected?

    The form we have has checkboxes for the user to make their selections and instead of hard coding all the possible variables, it would be nice if there was a way if we could have the query change as we change what is checked.

    For example, lets say that we have 4 jobs available and 3 different shifts. Now lets say that one person is available during all 3 shifts and would work 2 of the jobs. If we select that we are just looking for some one available during the "day shift" and for "job a" how can I pass that data to a query as my fields?

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    WHERE is this query stored? In code? A "stored" query (made by QBE and named)? If the former, yes you can ... If the latter, unknown (but suspect - no) ..
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Aug 2004
    Posts
    10
    The Query would be stored. I thought about trying to use one that is coded but I would still have the same problem with updateing it dynamically.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    But thats the differnece between a stroed query and one you write in the form. you can tailor the where clause to suit your requirements. for example you could offer teh user the ability to specify a specific value, a wildcard comparison or a range or nothing at all.

    for the reqirement you outline I think you will almost certainly have to go down the writing the sql on the fly within a form.

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    I don't know of a pretty way or the proper easy way, but in your Query in DesignMode (QBE), in the fields that you wish to populate, just place -1 in that Criteria cell of the query and it will then pull on the records that have the CheckBoxes checked. But then that's not good if this is being used by an end user who knows nothing about working queries too.

    BUD

  6. #6
    Join Date
    Aug 2004
    Posts
    10
    So if I have SQL code in my form, lets say in an on_click, how could I have the WHERE change to what is clicked. I would prefer to not have to have the users run the query wizard for each time they needed to change which job they are looking for.

    Thanks,

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    VBA Code:
    Code:
    Dim SQLString as string
    
    SQLString="SELECT "
    SQLString=SQLString & IIf(My1stCheckBox.Value=True,"FieldIWant1","")
    ...
    SQLString=SQLString & " FROM SomeTableSomewhere;"
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    say you had three check boxes identifying the shift (lets be original and call them ckbShift1, ckbShift2 & ckbShift3, and that you have 3 columns in your table called Shift1,Shift2 & Shift3

    when you build your SQL statement its soemthing like

    strSQL= "select blah from tblBlah"
    strWhereClause=""
    if ckbShift1.checked=true then
    strwhereclause="[shift1]=TRUE";
    endif
    if ckbShift2.checked=true then
    if len(strWhereClause)>0 then strwhereclause=strwhereclause & " AND "
    strwhereclause=strwhereclause & "[shift2]=TRUE";
    endif
    if ckbShift3.checked=true then
    if len(strWhereClause)>0 then strwhereclause=strwhereclause & " AND "
    strwhereclause=strwhereclause & "[shift3]=TRUE";
    endif
    if len(strWhereClause)>0 then strsql=strsql & " WHERE " & strwhereclause
    'add any sort order
    strSQL=strSQL & " ORDER BY blah;";

    strsql is now the rowsource for wantever - your form / report.
    your strwherclause has the items that could be used for a filter - it may need tweaking but the general princile is there
    the advantage of thius route is that it allows you to selectively include or exclude specific riteria

    you could search for people available on shift1 & shift 2, & ignore shift 3

    you could add other parameters, eg a date range
    if fromdate is set then only search for that date
    if 'todate' is set then only search for that date (say employees available upto and includign that date)
    if both are set then critria is where [MyateColumn] between " & fromdate & " AND " & ToDate
    if nothing is set then don't apply the date term(s)
    if you wanted to search for an employee called smith on Shift3.....
    etc...
    HTH

  9. #9
    Join Date
    Aug 2004
    Posts
    10
    I will give that a shot. I'll let you know how it goes.

  10. #10
    Join Date
    Aug 2004
    Posts
    10
    I have set up the code you supplied for use with my table/form. However I get a "Object doesn't support this property or method" error.

    Dim strSQL As String
    Dim strWhereClause As String
    strSQL = "SELECT [Employee Number], [Last Name], [First Name], [Phone Number] FROM [EE INFO]"
    strWhereClause = ""
    If Forms![Query form]![poker].Checked = True Then
    strWhereClause = "[poker] = true"
    End If
    If Forms![Query form]![usher].Checked = True Then
    If Len(strWhereClause) > 0 Then strWhereClause = strWhereClause & " AND "
    strWhereClause = strWhereClause & "[usher]=TRUE"
    End If
    If Forms![Query form]![Line Expeditor].Checked = True Then
    If Len(strWhereClause) > 0 Then strWhereClause = strWhereClause & " AND "
    strWhereClause = strWhereClause & "[Line Expeditor]=TRUE"
    End If
    If Len(strWhereClause) > 0 Then strSQL = strSQL & " WHERE " & strWhereClause
    'add any sort order
    strSQL = strSQL '& " ORDER BY blah;"

    Any ideas?

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there's a couple of typos in my sample SQL - thats all ways a problem with air code - its not tested

    Dim strSQL As String
    Dim strWhereClause As String
    strSQL = "SELECT [Employee Number], [Last Name], [First Name], [Phone Number] FROM [EE INFO] " 'note the addition of a space after the table name
    strWhereClause = ""
    If Forms![Query form]![poker].Checked = True Then
    strWhereClause = "[poker] = true
    End If
    If Forms![Query form]![usher].Checked = True Then
    If Len(strWhereClause) > 0 Then strWhereClause = strWhereClause & " AND "
    strWhereClause = strWhereClause & "[usher]=TRUE"
    End If
    If Forms![Query form]![Line Expeditor].Checked = True Then
    If Len(strWhereClause) > 0 Then strWhereClause = strWhereClause & " AND "
    strWhereClause = strWhereClause & "[Line Expeditor]=TRUE"
    End If
    If Len(strWhereClause) > 0 Then strSQL = strSQL & " WHERE " & strWhereClause
    'add any sort order
    strSQL = strSQL & " ORDER BY blah;" 'note delete the apostrophe (')

    At to why you get I get an "Object doesn't support this property or method" error, at this point I don't know. usuallu this indicates that the recordset you are trying to open doesn't accept the type of SQL. However I suspect that it may be that the lack of the space between the table name and the where clauses is causing the grief

    as a suggestion, if you get this type of message, or any time when you build an SQL statement I find it worthwhile to display the the SQL prior to applying it to the recordset to check for spelling or grammer typos like the space. If you debug.print it rather than msgbox it then you can even cut & paste it into the query designer SQL window and see what data you should expect to see when it runs.

    HTH
    Last edited by healdem; 12-29-05 at 04:02.

  12. #12
    Join Date
    Aug 2004
    Posts
    10
    Should I be putting this in my VB code or in a query? I have tried both. With putting it in VB I get the "object blah, blah ,blah " error. In putting it in a query I get an "Invalid SQL statement" error.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so the SQL is corrupted

    As suggested earlier do a debug.print strSQL after you have formed th SQL but before you attemtp to execute it and try to spot where the SQl is bolluxed.

    its probably spacing, it could be a misspelling

    dumb question what is your sort order- I hope your sort clause isn't still " ORDER BY blah;", ie you have set what ever your sort columns are eg " ORDER BY [Last Name], [First Name];"

    it may be that you don't need the semi colon as the last character in the SQL, can't remember most SQL's require it, I think JET may not.

    As an aside can I suggest you get out of the habit of putting sapces in your column (field) names eg
    [Last Name], [First Name]
    should be
    [LastName], [FirstName]
    - the reason its a pain writing some queries if the column names have spaces every time you refer to a column you have to enclose it in square brakets [].

    also consider using a coding / abbreviation system
    eg
    Employee Number becomes EmpNo
    Phone Number becomes PhoneNo
    - the reason its a lot of typing, some SQL's limit the number of relevant characters. The column is for internal use not for display to human viewers on reports, so it doesn't need to be human readable.

  14. #14
    Join Date
    Aug 2004
    Posts
    10
    Nope I am using Order by 1.

    It looks like the SQL does not like the strSQL= When put into a straight SQL query.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    [QUOTE=lxryycht]Nope I am using Order by 1. /QUOTE]

    what you mean you have a column called 1 in your db - serious bad news - it can be done but its a b!!!er to maintain. I have had to maintain a db designed by a comedian who used numbers for most of his column names (1..120) - the applicaiton still gives me the creeps every time the customer wants to make a change.

    The error messages you are getting infer that its the SQL that is malformed, ie there is a fault in the grammer of your query. For now I'd consider dropping the order by clause and see if that clears the fault.

    A suggstion why not post the strSQL statement here so we can look at the detail of what the problem is. If you do a debug.print, and set a watch immediately after the debug line you can then cut and paste the SQL into a reply to the thread.

    if you are usuing a select * from statement then please include the column names of the relevant table(s) and we'll have a look at what your SQL statement is actually saying.

Posting Permissions

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