Select * Except for ?

[Adrianna]

Okay, I've never had a reason to do this before, but like everything else in life, there comes a time when you will need to learn something new (daily for me).
I have code right now that runs correctly to allow selection of a parent, determine it's children, and then run through all of the selected tables to output all records for each child into one of three "asset type" workbooks containing individual worksheets for each table!

As I was working on this, our webmaster added ModifiedBy and ModifiedDate fields to all the tables (yes, instead of a tblModified tracking the Identity fields of the records that have been changed). So, ALL tables have two additional fields and it no interferes with my procedure.

I would like to suggest that we create a Modified table, but I'm not sure whether I have enough justification to go that route, so I'd like to
Select * Except ModifiedBy, ModifiedDate
Since the tables are all different, I can't specify the fields that I would like to see, rather it would be much easier to specify the two fields that I do not want to see.

If this isn't possible....maybe I can do a column count and .hide the last two columns.

 

[daveUK]

Hi Adrianna

You definately have justification for setting up a TblModification, however, if the query results are displayed within a report, just don't create textboxes for the results of those fields, otherwise you're going to have to select the fields you do want to display individually.

 

[Adrianna]

Unfortunately the data is being written into Excel and not Access Reports. I've found a way of doing it using yet ANOTHER recordset to query for the field names for each table. So, all should be good. Sucks that this seems to be getting so complicated.

Hi Adrianna

You definately have justification for setting up a TblModification, however, if the query results are displayed within a report, just don't create textboxes for the results of those fields, otherwise you're going to have to select the fields you do want to display individually.

 

[Adrianna]

Stuck Again

Okay....I can't get this dynamic "fields" portion of the query to work. Can someone please let me know what I'm getting "To Few Parameters, Expected 5"

...
" SELECT " & strFieldNames & ", '" & strMoreFields & "'" & _
            " FROM " & strShtNm & "" & _
            " WHERE (((ActivityID) IN (SELECT dbo_tActivity.ActivityID FROM dbo_tActivity WHERE UIC = '" & strCriteria & "')))", dbOpenDynaset, dbSeeChanges)

If you're wondering why there are two strings, it's because the field names were so long that they exceeded the 255 character limit, hence two fields are being used to represent the fields. I used a MsgBox to view strFieldNames & strMoreFields and all of the comma and spaces look fine.

I will mention that only the field names are present, as you may have noticed that the table is dynamic as well.

 

[Adrianna]

Pulling my hair out

First issue resolved. It wasn't an issue with the code in the query, it was actually the fact the the strings were compounding without resetting before each inner loop


So, my only issue now is why does the below code not work:

                Set rs = dbITRepositoryTest.OpenRecordset( _
                " SELECT * " & _
                " FROM '" & strShtNm & "'", dbOpenDynaset, dbSeeChanges)

Syntax Error. Incomplete Query CLause

What the heck. I passed the same SQL statement on another form with no issues.....the only difference is that the dynamic table in "strShtNm" is not a query.

All I want to do is set variable queries to the recordset each time I loop through the code. The code works perfectly fine with tables...so what's so different about queries?
Could it be because my queries have a where clause that grabs a Me.Field from the current form? THe form is open and filled out before anyone is able to launch this code, but maybe it can not pass into the record set until the variable is satisfied?
_______________
Read a post my Pat and she'd answered the question for someone else....I needed to declare the parameters prior to setting the recordset = the query defs. So all is good now