HELP with query statement


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: HELP with query statement

Hybrid View

  1. #1
    Stephanie Guest

    HELP with query statement


    I have been stuck for days & close to finishing up my first project.
    I have a form tied to an access DB using a Data1.
    I need to create a SELECT FROM WHERE statement that will display (in a list
    box) the name of each record that the WHERE statment fits.
    Example I need WHERE status1 = "CR" AND Fax1 = vbChecked AND mydate1 < date1.

    mydate is declared as:
    Dim h As Variable
    Dim mydate As Variable
    Dim mydate1 As Variable

    h = 1
    CurrentdateTime = Now
    mydate = CurrentdateTime
    mydate1 = mydate - 1

    This takes 24 hours from todays date, so that when I search the database
    & the date1 is less than 24 hours, status1 = "CR", & Fax1 = vbChecked.
    I need to run through each record searching for each of these & the display
    the name of each one in the listbox.

    Each of the mention fields are using a Data1 Enviroment & I have tied then
    using DataSource & DataField.

    I have tried so many different ways, my brain feels cluttered so if anyone
    has any suggestions I really, really appreciate it!.

    Thanks guys,
    Stephanie

  2. #2
    Craig Brown Guest

    Re: HELP with query statement


    Stephanie,

    I'm not quite sure I understand your question?

    Are you asking how to write the SQL to return a particular resultset? Or
    are you asking how to put the returned resultset in the list box?

    Craig Brown

    "Stephanie" <Steph31@excite.com> wrote:
    >
    >I have been stuck for days & close to finishing up my first project.
    >I have a form tied to an access DB using a Data1.
    >I need to create a SELECT FROM WHERE statement that will display (in a list
    >box) the name of each record that the WHERE statment fits.
    >Example I need WHERE status1 = "CR" AND Fax1 = vbChecked AND mydate1 < date1.
    >
    >mydate is declared as:
    >Dim h As Variable
    >Dim mydate As Variable
    >Dim mydate1 As Variable
    >
    >h = 1
    >CurrentdateTime = Now
    >mydate = CurrentdateTime
    >mydate1 = mydate - 1
    >
    >This takes 24 hours from todays date, so that when I search the database
    >& the date1 is less than 24 hours, status1 = "CR", & Fax1 = vbChecked.
    >I need to run through each record searching for each of these & the display
    >the name of each one in the listbox.
    >
    >Each of the mention fields are using a Data1 Enviroment & I have tied then
    >using DataSource & DataField.
    >
    >I have tried so many different ways, my brain feels cluttered so if anyone
    >has any suggestions I really, really appreciate it!.
    >
    >Thanks guys,
    >Stephanie



  3. #3
    Stephanie Guest

    Re: HELP with query statement


    Hello Craig, I guess I am saying both, I can get the first returned reslult
    set to appear but not all of them. I do have an ID field that is autonumbered
    if that may help.

    Thank you for your time,
    Stephanie


    "Craig Brown" <Brown.C3@ING-AFS.com> wrote:
    >
    >Stephanie,
    >
    >I'm not quite sure I understand your question?
    >
    >Are you asking how to write the SQL to return a particular resultset? Or
    >are you asking how to put the returned resultset in the list box?
    >
    >Craig Brown
    >
    >"Stephanie" <Steph31@excite.com> wrote:
    >>
    >>I have been stuck for days & close to finishing up my first project.
    >>I have a form tied to an access DB using a Data1.
    >>I need to create a SELECT FROM WHERE statement that will display (in a

    list
    >>box) the name of each record that the WHERE statment fits.
    >>Example I need WHERE status1 = "CR" AND Fax1 = vbChecked AND mydate1 <

    date1.
    >>
    >>mydate is declared as:
    >>Dim h As Variable
    >>Dim mydate As Variable
    >>Dim mydate1 As Variable
    >>
    >>h = 1
    >>CurrentdateTime = Now
    >>mydate = CurrentdateTime
    >>mydate1 = mydate - 1
    >>
    >>This takes 24 hours from todays date, so that when I search the database
    >>& the date1 is less than 24 hours, status1 = "CR", & Fax1 = vbChecked.
    >>I need to run through each record searching for each of these & the display
    >>the name of each one in the listbox.
    >>
    >>Each of the mention fields are using a Data1 Enviroment & I have tied then
    >>using DataSource & DataField.
    >>
    >>I have tried so many different ways, my brain feels cluttered so if anyone
    >>has any suggestions I really, really appreciate it!.
    >>
    >>Thanks guys,
    >>Stephanie

    >



  4. #4
    Craig Brown Guest

    Re: HELP with query statement


    Stephanie,

    Below are two (of many...) subroutines. One that builds a query and then
    calls the other one to execute the query and load a listbox. The gist of
    the form was to provide something of simplified user interface that allowed
    people to pick tables and fields from the database. It then would build the
    SQL and save it for them. Anyway, the lists of tables and fields were generated,
    on the fly, by the database that was selected, when you pick a different
    database, it loaded a different set of tables, fields, etc.

    Take a look, see if it's heading, generally, where you want to go with your
    project. Then if you need more, send me an e-mail and I'll send an attachment
    - it's just far too big to post.

    Craig Brown


    '- - - - - - - - - - - - - -
    Private Sub InitGrpList()
    Dim s As String

    lstGrpQrys.Clear

    txtGrpDesc.Text = ""

    s = "SELECT DISTINCT GroupName" _
    & " FROM tblQueryGroup" _
    & " ORDER BY GroupName;"

    GetNLoadList s, cboExistGrps

    cboExistGrps.ListIndex = -1

    '-- Enable the up/down buttons appropriately
    SetUpDownBtns lstGrpQrys, cmdUp3, cmdDown3

    End Sub

    '- - - - - - - - - - - - - -
    Private Function GetNLoadList( _
    sSQL As String, _
    ctl As Control)

    Dim bMoreRecs As Boolean

    If TypeOf ctl Is ListBox _
    Or TypeOf ctl Is ComboBox Then
    'Control type is ok
    Else
    'We don't understand this control...
    GoTo lblExit
    End If

    '-- Update the rec-set
    With rsQryResults
    '-- Temporarily, turn off error handling
    On Error Resume Next

    '-- Close out the record set
    .Close

    '-- Turn error handling back on
    On Error GoTo lblErr

    '-- Reopen the record set
    .Open sSQL, frmResultsDisp.cnn

    '-- Clear the contents of the control
    ctl.Clear

    '-- Start with record #1
    .MoveFirst

    bMoreRecs = True

    'Get all the records from the record set
    Do While bMoreRecs
    If .EOF Then
    bMoreRecs = False
    Else
    '-- Add the value to the control's list
    ctl.AddItem .Fields(0).Value

    '-- Go to the next record
    .MoveNext
    End If
    Loop

    '-- Set the index to the top
    ctl.ListIndex = cTopOfList
    End With

    '-- Skip over the error processing
    GoTo lblExit

    lblErr:

    Dim adoErr As ADODB.Error

    For Each adoErr In frmResultsDisp.cnn.Errors
    'display all the errors
    MsgBox adoErr.Description & " - " & adoErr.Number
    Next

    '-- If we didn't find anything, tell the user
    If rsQryResults.State = cRecordSetEmpty Then
    With ctl
    '-- Blow away the contents of the control
    .Clear

    '-- Add msg line
    .AddItem "Nothing Found"

    '-- Set this to the visible line
    .ListIndex = cTopOfList
    End With
    End If

    lblExit:

    End Function


    "Stephanie" <stephanie31@excite.com> wrote:
    >
    >Hello Craig, I guess I am saying both, I can get the first returned reslult
    >set to appear but not all of them. I do have an ID field that is autonumbered
    >if that may help.
    >
    >Thank you for your time,
    >Stephanie
    >
    >
    >"Craig Brown" <Brown.C3@ING-AFS.com> wrote:
    >>
    >>Stephanie,
    >>
    >>I'm not quite sure I understand your question?
    >>
    >>Are you asking how to write the SQL to return a particular resultset?

    Or
    >>are you asking how to put the returned resultset in the list box?
    >>
    >>Craig Brown
    >>
    >>"Stephanie" <Steph31@excite.com> wrote:
    >>>
    >>>I have been stuck for days & close to finishing up my first project.
    >>>I have a form tied to an access DB using a Data1.
    >>>I need to create a SELECT FROM WHERE statement that will display (in a

    >list
    >>>box) the name of each record that the WHERE statment fits.
    >>>Example I need WHERE status1 = "CR" AND Fax1 = vbChecked AND mydate1 <

    >date1.
    >>>
    >>>mydate is declared as:
    >>>Dim h As Variable
    >>>Dim mydate As Variable
    >>>Dim mydate1 As Variable
    >>>
    >>>h = 1
    >>>CurrentdateTime = Now
    >>>mydate = CurrentdateTime
    >>>mydate1 = mydate - 1
    >>>
    >>>This takes 24 hours from todays date, so that when I search the database
    >>>& the date1 is less than 24 hours, status1 = "CR", & Fax1 = vbChecked.
    >>>I need to run through each record searching for each of these & the display
    >>>the name of each one in the listbox.
    >>>
    >>>Each of the mention fields are using a Data1 Enviroment & I have tied

    then
    >>>using DataSource & DataField.
    >>>
    >>>I have tried so many different ways, my brain feels cluttered so if anyone
    >>>has any suggestions I really, really appreciate it!.
    >>>
    >>>Thanks guys,
    >>>Stephanie

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center