Or perhaps there is a better way 1

[melaniews]

I'm sure the concept is valid. I'm trying to pass the results of multiple combo boxes to a SQL string to be used in a query. Here's what I have so far. The message I get is "can't find the object 'stDocName'"

There may be issues beyond this. But I haven't gotten past this error.


Private Sub cmdSubmit_Click()
On Error GoTo Err_cmdSubmit_Click

Dim strWhere As String 'String variable to store where clause

If Len(Me.cboIncome & "&quot > 0 Then
strWhere = strWhere & " AND INCOME = '" & Me.cboIncome & "'"
End If

If Len(Me.cboGender & "&quot > 0 Then
strWhere = strWhere & " AND GENDER = '" & Me.cboGender & "'"
End If

If Len(Me.cboMaritalStatus & "&quot > 0 Then
strWhere = strWhere & " AND MARITAL_STATUS = '" & Me.cboMaritalStatus & "'"
End If

If Len(Me.cboOwnRent & "&quot > 0 Then
strWhere = strWhere & " AND OWNRENT = '" & Me.cboOwnRent & "'"
End If

If Len(Me.cboEducation & "&quot > 0 Then
strWhere = strWhere & " AND EDUCATION = '" & Me.cboEducation & "'"
End If

'If Len(Me.cboMembers & "&quot > 0 Then
' strWhere = strWhere & " AND MEMBERS_IN_HOUSEHOLD = '" & Me.cboMembers & "'"
'End If

If Len(Me.dtmBeginDate & "&quot > 0 And Len(Me.dtmEndDate & "&quot > 0 Then

' entries in both

strWhere = strWhere & " AND DATE_OF_BIRTH Between #" & Me.dtmBeginDate & "# AND #" & Me.dtmEndDate & "#"
End If

If Len(Me.dtmBeginDate & "&quot > 0 And Len(Me.dtmEndDate & "&quot = 0 Then

' user put something in From date only

MsgBox "You did not enter Both Dates.", vbInformation
GoTo Exit_cmdSubmit_Click:
End If

If Len(Me.dtmBeginDate & "&quot = 0 And Len(Me.dtmEndDate & "&quot > 0 Then
' user put something in to date only
MsgBox "You did not enter Both Dates.", vbInformation
GoTo Exit_cmdSubmit_Click:

End If ' no need to specify anything if nothing selected


'Open the form

If Len(strWhere & "&quot = 0 Then
' no options selected.
MsgBox "No Items have been selected," & (Chr(13)) & (Chr(13)) & _
"Please Select Demographic Criteria", vbExclamation, _
"No Data Error"
GoTo Exit_cmdSubmit_Click:

Else

Dim strSQL As String
Dim stDocName As String

strSQL = "SELECT dbo_INDIVIDUAL.INDIVIDUAL_ID, dbo_INDIVIDUAL.ADDRESS1, dbo_INDIVIDUAL.ADDRESS2, dbo_INDIVIDUAL.CITY, dbo_INDIVIDUAL.STATE, dbo_INDIVIDUAL.ZIP5, dbo_INDIVIDUAL.ZIP4, dbo_DEMOGRAPHIC.DATE_OF_BIRTH, dbo_DEMOGRAPHIC.INCOME, dbo_DEMOGRAPHIC.OWNRENT, dbo_DEMOGRAPHIC.GENDER, dbo_DEMOGRAPHIC.REMINGTON_PRODUCTS_OWNED, dbo_DEMOGRAPHIC.EDUCATION, dbo_DEMOGRAPHIC.MARITAL_STATUS, dbo_DEMOGRAPHIC.MEMBERS_IN_HOUSEHOLD" & _
"FROM dbo_INDIVIDUAL INNER JOIN dbo_DEMOGRAPHIC ON dbo_INDIVIDUAL.INDIVIDUAL_ID = dbo_DEMOGRAPHIC.INDIVIDUAL_ID" & _
"WHERE (Mid(strWhere, 6)"

stDocName = "strSQL"
DoCmd.OpenQuery "strDocName", acViewNormal
DoCmd.OpenForm "frmDemographicResults", acFormDS
DoCmd.Maximize

End If

Exit_cmdSubmit_Click:
Exit Sub

Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click

End Sub


Any help here would be appreciated.
TIA,
Melanie

 

[dan1967]

A couple of things I noticed:
* When you apply the value of strSQL to stDocName, you don't need the double quotes:

stDocName = strSQL

Likewise when you are setting the query for your DoCmd (you also misspelled your variable. I corrected below):

DoCmd.OpenQuery stDocName, acViewNormal

Your main problem is that you do not have a query saved by the name stDocName. Access is looking for a saved query with this name. You will want to run:

DoCmd.RunSQL stDocName

You also need to end your Where clause with a semicolon.

Dan.

 

[melaniews]

OK, I got all that -- thank you very much.

I'm getting another error now.
"A RunSQL actions requires an argument consisting of an SQL statement."

Here's what the code looks like now.....



Dim strSQL As String
Dim stDocName As String

stDocName = strSQL
strSQL = "SELECT dbo_INDIVIDUAL.INDIVIDUAL_ID, dbo_INDIVIDUAL.ADDRESS1, dbo_INDIVIDUAL.ADDRESS2, dbo_INDIVIDUAL.CITY, dbo_INDIVIDUAL.STATE, dbo_INDIVIDUAL.ZIP5, dbo_INDIVIDUAL.ZIP4, dbo_DEMOGRAPHIC.DATE_OF_BIRTH, dbo_DEMOGRAPHIC.INCOME, dbo_DEMOGRAPHIC.OWNRENT, dbo_DEMOGRAPHIC.GENDER, dbo_DEMOGRAPHIC.REMINGTON_PRODUCTS_OWNED, dbo_DEMOGRAPHIC.EDUCATION, dbo_DEMOGRAPHIC.MARITAL_STATUS, dbo_DEMOGRAPHIC.MEMBERS_IN_HOUSEHOLD " & _
"FROM dbo_INDIVIDUAL INNER JOIN dbo_DEMOGRAPHIC ON dbo_INDIVIDUAL.INDIVIDUAL_ID = dbo_DEMOGRAPHIC.INDIVIDUAL_ID " & _
"WHERE (Mid(strWhere, 6)); "

DoCmd.RunSQL strSQL

DoCmd.OpenQuery stDocName, acViewNormal
DoCmd.OpenForm "frmDemographicResults", acFormDS ', WhereCondition:=Mid(strWhere, 6)
DoCmd.Maximize


TIA,
Melanie

 

[CajunCenturion]

As the thread was cross-posted, the duplicate copy has been Red-flagged for removal. Don't need two copies floating around.

I would suggest that you look at a couple of things. Your WHERE clauses seem to be incomplete in both the SQL statement and in the OpenForm statement. Also don't think the OpenQuery statement is necessary.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[melaniews]

Thanks Cajun,

I don't know what is incomplete in the Where clause.
The Where clause is commented out in the OpenForm statement. It worked well there as a filter, but I really need it saved as criteria in a query.

I changed it a little. I did

DoCmd.RunSQL "I put the SQL statement here exactly as it was when it was strSQL"

I get the same error. I'm so new to this. I'm just working by trial and error.... mostly error.

Thanks,
Melanie

 

[CajunCenturion]

You where clause is as follows:

"WHERE (Mid(strWhere, 6)); "

Where Mid(strWhere, 6) is what?


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[melaniews]

Oh, I see what you mean.

This code is higher up in the sub. It captures results from combo boxes on the form -- and I thought it was putting them into the "where".


Dim strWhere As String 'String variable to store where clause

If Len(Me.cboIncome & "&quot > 0 Then
strWhere = strWhere & " AND INCOME = '" & Me.cboIncome & "'"
End If

If Len(Me.cboGender & "&quot > 0 Then
strWhere = strWhere & " AND GENDER = '" & Me.cboGender & "'"
End If

If Len(Me.cboMaritalStatus & "&quot > 0 Then
strWhere = strWhere & " AND MARITAL_STATUS = '" & Me.cboMaritalStatus & "'"
End If

If Len(Me.cboOwnRent & "&quot > 0 Then
strWhere = strWhere & " AND OWNRENT = '" & Me.cboOwnRent & "'"
End If


There's actually more beyond this but you probably get the idea.

TIA,
Melanie

 

[CajunCenturion]

You have "WHERE (Mid(strWhere, 6)); " in quotes, when I think you mean
"WHERE " & Mid(strWhere, 6) & ";"

Plus, is the first word of the where clause "AND"?


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[melaniews]

Thanks -- I really didn't know what it all meant.

Anyway, I've changed my code as you suggested. I'm still getting the same error.

"A RunSQL actions requires an argument consisting of an SQL statement."

I cannot understand this. It appears to me that the argument is there. My exact code is listed below.



DoCmd.RunSQL "SELECT dbo_INDIVIDUAL.INDIVIDUAL_ID, dbo_INDIVIDUAL.ADDRESS1, dbo_INDIVIDUAL.ADDRESS2, dbo_INDIVIDUAL.CITY, dbo_INDIVIDUAL.STATE, dbo_INDIVIDUAL.ZIP5, dbo_INDIVIDUAL.ZIP4, dbo_DEMOGRAPHIC.DATE_OF_BIRTH, dbo_DEMOGRAPHIC.INCOME, dbo_DEMOGRAPHIC.OWNRENT, dbo_DEMOGRAPHIC.GENDER, dbo_DEMOGRAPHIC.REMINGTON_PRODUCTS_OWNED, dbo_DEMOGRAPHIC.EDUCATION, dbo_DEMOGRAPHIC.MARITAL_STATUS, dbo_DEMOGRAPHIC.MEMBERS_IN_HOUSEHOLD " & _
"FROM dbo_INDIVIDUAL INNER JOIN dbo_DEMOGRAPHIC ON dbo_INDIVIDUAL.INDIVIDUAL_ID = dbo_DEMOGRAPHIC.INDIVIDUAL_ID " & _
"WHERE " & Mid(strWhere, 6) & ";"



TIA,
Melanie

 

[dan1967]

The DoCmd.RunSQL statement is not really valid for what you want to accomplish. My bad. This is for SQL that takes action against your database (i.e. Update, Delete, etc.). What you want to do is programatically create a Query object based on you strSQL variable, pass this into your DoCmd.OpenQuery command then delete the Query object. I can't give you any info how to do this at this time.


Dan.

 

[MichaelRed]

I am sure that I am missing something here.

It SEEMS (to Me) that the point of the exercise is to open the second form with the query results in data sheet view. But there are some oddities in the path:

Attempting to "open" the consrtucted query doesn't appear necessary (or even useful), and the actual 'construction' of the query is apparently an exercise (in frustration), as the query string is never assigned to any object (or use). Finally, as has already been noted, the where clause is incomplete.

In bumbling around, I would suggest some changes:

1.[tab]There should be a "permanant" (saved) query, much like the 'construct' in the code, but completing the where clause properly.

2.[tab]the where clause should be 'Parameterized" (see the ubiquitous {F1} (a.k.a HELP) for parameter query. Thus the coded generation of the select string is completly avoided, by filling in the various snippets with the supplied parameters (or their defaults) via code similar to the exhaustive set of "If Blocks" above the counstruction of the Where Clause.

3.[tab]The problem with the "Where" clause stems from the lack of a "comparision". e.g. it needs to 'look like':

WHERE (Mid(strWhere, 6) = Something

Which only confuses the statement's intent. It APPEARS to simply want to Strip the first six characters from the string -which were placed theere as a LIITERAL (" AND &quot. Apparently, their was some other clauses preceeding the first on in some previous incarnation, and the present "Techniac User" is not sufficiently proficient in hte use of VBA/SQL to simply remove the literal, although the entire 'sequence' actually requires changes to omit the " AND " from subsquent clauese if the where clause is 'empty' and the If Block would add to the string.

Although there is a lot which remains awry, I modified the procedure (A LOT) and can at least get it to do what I think it should look like. A major part of the mod is simply shortening the various object names, but the general "Flow" of the process is what MAY be useful, not my 'scribble typing'.

Public Function frmDemOpn(TheFrm As String)

    On Error GoTo ErrExit

    Dim MyFrm As Form
    Set MyFrm = Forms(TheFrm)

    Dim strWhere As String 'String variable to store where clause

    If Len(MyFrm.cboIncome & "") > 0 Then
        strWhere = strWhere & "INCOME = '" & MyFrm.cboIncome & "'"
    End If

    If Len(MyFrm.cboGender & "") > 0 Then
        If Len(strWhere <> o) Then
            strWhere = strWhere & &quot; AND &quot;
        End If
        strWhere = strWhere & &quot;GENDER = '&quot; & MyFrm.cboGender & &quot;'&quot;
    End If

    If (Len(MyFrm.cboMaritalStatus & &quot;&quot;) > 0) Then
        If Len(strWhere <> o) Then
            strWhere = strWhere & &quot; AND &quot;
        End If
        strWhere = strWhere & &quot;MARITAL_STATUS = '&quot; & MyFrm.cboMaritalStatus & &quot;'&quot;
    End If

    If (Len(MyFrm.cboOwnRent & &quot;&quot;) > 0) Then
        If Len(strWhere <> o) Then
            strWhere = strWhere & &quot; AND &quot;
        End If
        strWhere = strWhere & &quot;OWNRENT = '&quot; & MyFrm.cboOwnRent & &quot;'&quot;
    End If

    If (Len(MyFrm.cboEducation & &quot;&quot;) > 0) Then
        If Len(strWhere <> o) Then
            strWhere = strWhere & &quot; AND &quot;
        End If
        strWhere = strWhere & &quot;EDUCATION = '&quot; & MyFrm.cboEducation & &quot;'&quot;
    End If

    'If (Len(Me.cboMembers & &quot;&quot;) > 0) Then
'        If Len(strWhere <> o) Then
'            strWhere = strWhere & &quot; AND &quot;
'        End If
    '   strWhere = strWhere & &quot;MEMBERS_IN_HOUSEHOLD = '&quot; & Me.cboMembers & &quot;'&quot;
    'End If

    If (Len(MyFrm.dtmBeginDate & &quot;&quot;) > 0 And Len(MyFrm.dtmEndDate & &quot;&quot;) > 0) Then
        If Len(strWhere <> o) Then
            strWhere = strWhere & &quot; AND &quot;
        End If
        ' entries in both
        strWhere = strWhere & &quot;DATE_OF_BIRTH Between #&quot; & MyFrm.dtmBeginDate & &quot;# AND #&quot; & MyFrm.dtmEndDate & &quot;#&quot;
     Else
        MsgBox &quot;You did not enter Both Dates.&quot;, vbInformation
        GoTo NormExit
    End If
    
'    If (Len(MyFrm.dtmBeginDate & &quot;&quot;) > 0 And Len(MyFrm.dtmEndDate & &quot;&quot;) = 0) Then
'        ' user put something in From date only
'    End If
'
'    If (Len(MyFrm.dtmBeginDate & &quot;&quot;) = 0 And Len(MyFrm.dtmEndDate & &quot;&quot;) > 0) Then
'        ' user put something in to date only
'        MsgBox &quot;You did not enter Both Dates.&quot;, vbInformation
'        GoTo NormExit:
'    End If ' no need to specify anything if nothing selected

    'Open the form
    If (Len(strWhere & &quot;&quot;) = 0) Then

        'no options selected.
        MsgBox &quot;No Items have been selected,&quot; & (Chr(13)) & (Chr(13)) & _
        &quot;Please Select Demographic Criteria&quot;, vbExclamation, _
        &quot;No Data Error&quot;
        GoTo NormExit:
   
    End If

    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim qdf As QueryDef
    Set dbs = CurrentDb

    strSQL = &quot;SELECT rstIndv.Indiv, rstIndv.Addr1, rstIndv.Addr2, &quot; & _
             &quot;rstIndv.City, rstIndv.St, rstIndv.Zip5, rstIndv.Zip4, &quot; & _
             &quot;rstIndv.DOB, rstIndv.Income, rstIndv.OwnRnt, &quot; & _
             &quot;rstIndv.Sex, rstIndv.RmProds, rstIndv.Educ, &quot; & _
             &quot;rstIndv.MartStat, rstIndv.HsldMbrs &quot; & _
             &quot;From rstIndv INNER JOIN rstDmgrph ON &quot; & _
             &quot;rstIndv.Indiv = rstDmgrph.Indiv &quot; & _
             &quot;Where &quot; & strWhere & &quot;;&quot;

    Set qdf = dbs.QueryDefs(&quot;qryDmgrph&quot;)
    qdf.sql = strSQL
'    DoCmd.OpenQuery &quot;qryDmgrph&quot;, acViewNormal
    DoCmd.OpenForm &quot;frmDemgrph&quot;, acFormDS
    DoCmd.Maximize
    
    
NormExit:
    Exit Function

ErrExit:
    MsgBox Err.Description
    Resume NormExit
    
End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[melaniews]

Wow, that really looks good!

One question, where you have the strSQL = "SELECT ....etc. -- as you mentioned you shortened the names. I have no control over the table names and field names. Should I change those back to the orginal lengthy monsters or does this work just like it is?

Oh yeah, one more question, can I export the results of that query to a .csv file now? The end result I want is for the form to open and the user verifies visually that this is what is desired, then on the form unload event I export the recordset to .csv file. So my question is what goes in here:

DoCmd.TransferText acExportDelim, , "what goes in here??"

Or is it done some other way.

Thanks,
Melanie

p.s. mucho stars for you.

 

[MichaelRed]

You will need to include your original names -somehow. The easiest is probably just the direct substution, but there are alternatives. I do not recommend you trying them, so will not go into detail.


The query name ("qryDmgrph&quot, including the quotation marks and don't forget to add/include the "FileName" and other (optional) arguments if desired.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[melaniews]

Thank you so much. This has helped me tremendously.

Melanie