Problems with a WHERE statement for a Form.RecordSource 2

[snayjay]

Here is what I have.

I've got a table with 43 fields that I want to query for a datasheet(for my example code I'll only use 5). I want the user to be able to enter critereon to filter each field if they want to. So on one form I have listed checkboxes for all the fields. If they want the field listed in the datasheet they click the checkbox (-1). If they don't, then they leave it "0". I'm able to use those checkbox values on the form.load for the datasheet by using Field.ColumnHidden if the checkbox value = "0". All this works fine.

I ran into problems where I tried to add critereon to the query. Here's the code, I appologize in advance if my code or explanation is lacking. I often come up with answers to my problems in a very non-traditional way...so sometimes because of the way I code things, I write way too much code than necessary.

I create the RecordSource for the datasheet(form) with this:

Sub createdatasheetrecordsource()
Dim drs As Variant 'variable designed to concatonate strings to form the RecordSource
drs = "SELECT MEMBERtbl.SSN, MEMBERtbl.RANK, MEMBERtbl.FIRSTNAME, MEMBERtbl.MIDDLENAME, MEMBERtbl.LASTNAME" _
& "FROM MEMBERtbl " _
& "WHERE (((MEMBERtbl.SSN) Like [Forms]![CREATEQUERYfrm]![CREATEQUERYSUBfrm]![SSNz])" 'the code thus far works fine by itself. It will create the datasheet and my ColumnHidden code removes the columns I didn't want in the datasheet
'The problem is here at the point I try to add Critereon to the Where statement... I figure I've misplaced a "space" somewhere, or thru in an extra ( or ". Can't seem to figure out what I'm doing wrong.
If RANK.Value = -1 Then
drs = drs & " AND ((MEMBERtbl.RANK) Like [Forms]![CREATEQUERYfrm]![CREATEQUERYSUBfrm]![RANKz])"
End If
If FIRSTNAME.Value = -1 Then
drs = drs & " AND ((MEMBERtbl.FIRSTNAME) Like [Forms]![CREATEQUERYfrm]![CREATEQUERYSUBfrm]![FIRSTNAMEz] & " * ")"
End If
If MIDDLENAME.Value = -1 Then
drs = drs & " AND ((MEMBERtbl.MIDDLENAME) Like [Forms]![CREATEQUERYfrm]![CREATEQUERYSUBfrm]![MIDDLENAMEz] & " * ")"
End If
If LASTNAME.Value = -1 Then
drs = drs & " AND ((MEMBERtbl.LASTNAME) Like [Forms]![CREATEQUERYfrm]![CREATEQUERYSUBfrm]![LASTNAMEz] & " * ")"
End If
drs = drs & ");" 'ends the WHERE statement
DOT.SetFocus
DoCmd.OpenForm "DATASHEETfrm"
Forms!DATASHEETfrm!DATASHEETRECORDSOURCE = drs
Forms!DATASHEETfrm!DATASHEETSUBfrm.SourceObject = "DATASHEETSUBfrm"
End Sub

If I've totally confused you, again I'm sorry... If you think you can fix it if you could see it... I can try to make a mock .mdb and upload it to a website for download. Just let me know. Thanks again in advance,

Snayjay

 

[traingamer]

try

 If RANK.Value = -1 Then
        drs = drs & " AND ((MEMBERtbl.RANK) Like [COLOR=red]'" & [/color][Forms]![CREATEQUERYfrm]![CREATEQUERYSUBfrm]![RANKz])[COLOR=red] & "'"[/color]
    End If
  If FIRSTNAME.Value = -1 Then
        drs = drs & " AND ((MEMBERtbl.FIRSTNAME) Like [COLOR=red]'" & [/color][Forms]![CREATEQUERYfrm]![CREATEQUERYSUBfrm]![FIRSTNAMEz] & [COLOR=red]"*')"[/color]
    End If

etc. etc.
I'd try testing each of the additional criteria one at a time. Non-numeric fields should be in quotes.

traingamer

 

[traingamer]

sorry - missed the parenthesis:

If RANK.Value = -1 Then
        drs = drs & " AND ((MEMBERtbl.RANK) Like '" & [Forms]![CREATEQUERYfrm]![CREATEQUERYSUBfrm]![RANKz] & [COLOR=red]"')"[/color]
    End If

traingamer

 

[snayjay]

Awesome traingamer, thanks for your post. Your code you offered was just a bit off, but I understood your reasoning for the error and after a bit of thinking I figured it out. The final result was:

If RANK.Value = -1 Then

   drs = drs & " AND ((MEMBERtbl.RANK) Like [COLOR=red]" & "'" &[/color] [Forms]![CREATEQUERYfrm]![CREATEQUERYSUBfrm]![RANKz] [COLOR=red]& "')"[/color]

End If

Again, thanks again so much for responding. I live in Korea and it drives me crazy waiting to have someone answer any questions I've posted because I usually have to wait several hours. This came quicker than most so I'm especially thankful.

 

[snayjay]

Hey another question...I'm sure the problem is similar but I've tried several different ways similar to the last problem yet nothing is working.

I'm trying to export the same SELECT statement to an Excel File.

Here is what I have:

Private Sub EXPORTcmd_Click()
    Dim mySQL
    mySQL = DATASHEETRECORDSOURCE.Value
    DoCmd.TransferSpreadsheet acExport, 8, mySQL, "PMDQUERY.xls", True
End Sub

Examples I've found here seem really easy to follow, but I'm still getting an error saying:

Runtime Error 7871

The Table name you entered doesn't follow Personnel Management Database object-naming rules.

I understand that "Personnel Management Database" is coming from what I have the file named. But don't understand what it means by "My" Object Naming Rules.

I never set any rules up...I thought it was because I'm missing the TABLENAME but I read in the help files that you can substitute a SELECT Statement instead of a TABLENAME. Unless I'm mistaken and it means you can substitute a SELECT Query and not the Statement. If that's the case (which it seems that is)...is there an easy way to make it happen using a SELECT Statement that is stored in a field on the same form as the cmd button that stores the export code.

Any help in this would be greatly appreciated.

 

[VBslammer]

You can create a temporary QueryDef object to use for the export, then delete it afterwards:

Sub MakeQuery()
  Dim qdf As New DAO.QueryDef
  Dim db As DAO.Database
  
  qdf.sql = "SELECT * FROM Customers"
  qdf.Name = "ExportedFromAccess"
  
  Set db = CurrentDb()
  db.QueryDefs.Append qdf
  
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, qdf.Name, "c:\PMDQuery.xls", True
  
  db.QueryDefs.Delete qdf.Name
  
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[snayjay]

Thank you so much....that worked great!

Now I just have to programatically write my SELECT statement over like I did my WHERE statement (above) because I was hiding items on the datasheet, instead of just not including them in my SELECT statement.

Now the export shows all the fields in the table. Which I don't want. But you answered my questions and for that I thank you. Star for you!

~Snay