Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Location
    Tennessee
    Posts
    31

    Thumbs up Unanswered: Query Help Needed[RESOLVED]

    Hi all,
    I have a query question that I'm sure will be probably be a cinch, but has me flustered.

    If I am only wanting to show a certain set of records in my query in which a check box (on form) isn't checked; what do I use for my criteria?

    Code:
    While [forms]![contacts]!check72.status = false
    will that get it?


    Thanks

    G~
    Last edited by geneweaver; 12-27-04 at 10:28. Reason: Resolved
    The older I get, the more new stuff I tend to forget, the stuff I don't use any more...now that I remember!

    Mail Me

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

    Talking

    Quote Originally Posted by geneweaver
    Hi all,

    Code:
    While [forms]![contacts]!check72.status = false
    G~
    Hi Gene,

    Here is one way to achieve it. If you have your Query already created in the QBE grid, in the Criteria section simply place 0 and that will then pull all your records needed....(that being a ZERO). Below is what it will look like in the SQL view of the same Query:
    Code:
    SELECT tblDateCalc.BeginDate, tblDateCalc.EndDate, tblDateCalc.Name, tblDateCalc.Chosen
    FROM tblDateCalc
    WHERE (((tblDateCalc.Chosen)=0));
    This will pull all the records from the tblDateCalc Table WHERE the CheckBox tblDateCalc.Chosen is NOT checked, or the CheckBox is Unchecked. Hope this helps you out. Oh for the sake of your question, instead of While your statement should use Where. While is what you would use in VBA in performing a Loop of some sort....i.e. DoWhile Loop.

    have a nice one,
    BUD

  3. #3
    Join Date
    Dec 2004
    Location
    Tennessee
    Posts
    31

    Question

    Quote Originally Posted by Bud
    Hi Gene,

    Here is one way to achieve it. If you have your Query already created in the QBE grid, in the Criteria section simply place 0 and that will then pull all your records needed....(that being a ZERO). Below is what it will look like in the SQL view of the same Query:
    Code:
    SELECT tblDateCalc.BeginDate, tblDateCalc.EndDate, tblDateCalc.Name, tblDateCalc.Chosen
    FROM tblDateCalc
    WHERE (((tblDateCalc.Chosen)=0));
    This will pull all the records from the tblDateCalc Table WHERE the CheckBox tblDateCalc.Chosen is NOT checked, or the CheckBox is Unchecked. Hope this helps you out. Oh for the sake of your question, instead of While your statement should use Where. While is what you would use in VBA in performing a Loop of some sort....i.e. DoWhile Loop.

    have a nice one,
    BUD

    Bud,
    Thanks for the quick reply. My only problem with that method is that I'm using aggregate functions in my query; by summing the values. SQL below:
    Code:
    SELECT DISTINCTROW 
    Sum(Contacts.[Hospital Bed]) AS [Sum Of Hospital Bed], 
    Sum(Contacts.BSC) AS [Sum Of BSC], 
    Sum(Contacts.BST) AS [Sum Of BST], 
    Sum(Contacts.[Wheel Chair]) AS [Sum Of Wheel Chair], 
    Sum(Contacts.[Elec_Wheel Chair]) AS [Sum Of Elec_Wheel Chair], 
    Sum(Contacts.[Oxygen Tank]) AS [Sum Of Oxygen Tank], 
    Sum(Contacts.[Oxygen Concentrator]) AS [Sum Of Oxygen Concentrator], Sum(Contacts.[Shower  Chair]) AS [Sum Of Shower  Chair], 
    Sum(Contacts.Nebulizer) AS [Sum Of Nebulizer], 
    Sum(Contacts.Walker) AS [Sum Of Walker], 
    Sum(Contacts.[Tube Feed Pump]) AS [Sum Of Tube Feed Pump]
    FROM Contacts
    When I add the where clause (don't know why I used 'while') it blanks out my sums in my query.
    What I need it to do is check the checkbox (Contacts.Deceased) before doing the sum function. Should I make a separate query for that first? Then do the summing?


    TIA

    G~
    Last edited by geneweaver; 12-27-04 at 09:10.
    The older I get, the more new stuff I tend to forget, the stuff I don't use any more...now that I remember!

    Mail Me

  4. #4
    Join Date
    Dec 2004
    Location
    Tennessee
    Posts
    31

    Talking

    Bud,
    I found a VBA work around that works great.
    Code:
    Private Sub Check72_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
        Me.Text50 = ""
        Me.Text52 = ""
        Me.Text54 = ""
        Me.Text56 = ""
        Me.Text58 = ""
        Me.Text60 = ""
        Me.Text62 = ""
        Me.Text64 = ""
        Me.Text66 = ""
        Me.Text68 = ""
        Me.Text70 = ""
    End Sub
    This enters a null value in each of my fields textboxes for each record that has the Deceased checkbox checked; then when I run my query, the null values are left out!

    Again thanks for the help.


    G~
    The older I get, the more new stuff I tend to forget, the stuff I don't use any more...now that I remember!

    Mail Me

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by geneweaver
    Bud,
    I found a VBA work around that works great.
    [CODE]Private Sub Check72_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.Text50 = ""
    Me.Text52 = ""

    G~
    Hi Gene,
    Glad you got it resolved. That's how it goes when sometimes other aspects of your program are not included in the problem or explanation.
    NOTE: Just a Tip to make things a bit neater and more definitive in the future. It is a good practice to Name your Controls for the sake of easier maintenance of your database, either by yourself or in the case someone else has to peek inside it for you. Seeing Me.Text50 doesn't give a clue to what it really is at a glance other than just being a TextBox as opposed to say.......Me.txtHospitalBed or something to that effect. It is often a bit more easier to remember a Control/FieldName when writing Code than trying to track it down to see what it really is.
    Only a bit of info. from how I was taught, and it has helped greatly and so wishing to pass it on. Keep it as neat and clean as possible. Hope you had a wonderful and happy holiday.

    have a nice one,
    BUD

  6. #6
    Join Date
    Dec 2004
    Location
    Tennessee
    Posts
    31

    Talking

    Quote Originally Posted by Bud
    Hi Gene,
    Only a bit of info. from how I was taught, and it has helped greatly and so wishing to pass it on. Keep it as neat and clean as possible. Hope you had a wonderful and happy holiday.

    have a nice one,
    BUD

    Bud, thanks for the pointer and once again for the help.
    Much appreciated.


    G~
    The older I get, the more new stuff I tend to forget, the stuff I don't use any more...now that I remember!

    Mail Me

Posting Permissions

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