HELP with query statement

[Stephanie]

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

[Craig Brown]

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

[Stephanie]

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
>

[Craig Brown]

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
>>
>