SQL Query Select * where except certain feilds from access database

[origdman]

Does any one know the sql statement to pull back all data and write and except function not to include certain fields?

thanks in advanced!

 

[Aivars]

I think you may write such function by yourself. It isn't so hard.

Oh! Just now I wrote function that you need. It is ready for using - you may copy following code and paste into any module and use this function always when you need. Of course you can develop it and make it better and better, but there you are idea and little more:

Function UserSetSQL(strTableName As String, _
Optional strExcludingFieldList As String = "", _
Optional strDelimiter As String = ";", _
Optional strWhere As String = "", _
Optional strGroupBy As String = "", _
Optional strOrderBy As String = "&quot

Dim strSQL As String
Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field
Dim arrExclude
Dim i As Integer

arrExclude = Split(strExcludingFieldList, strDelimiter)
Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTableName)
For Each fld In tdf.Fields
If UBound(arrExclude) <> -1 Then
For i = LBound(arrExclude) To UBound(arrExclude)
If Trim(arrExclude(i)) = fld.Name Then
GoTo NextField
End If
Next i
End If
If strSQL <> &quot;&quot; Then
strSQL = strSQL & &quot;,&quot;
End If
strSQL = strSQL & &quot;[&quot; & fld.Name & &quot;]&quot;
NextField:
Next fld
dbs.Close
Set dbs = Nothing

If Trim(strWhere) <> &quot;&quot; Then
strWhere = &quot;Where &quot; & strWhere
End If
If Trim(strGroupBy) <> &quot;&quot; Then
strGroupBy = &quot;Group By &quot; & strGroupBy
End If
If Trim(strOrderBy) <> &quot;&quot; Then
strOrderBy = &quot;Order By &quot; & strOrderBy
End If
strSQL = &quot;Select &quot; & strSQL & &quot; From [&quot; & strTableName & &quot;] &quot;
strSQL = Trim(strSQL & strWhere & &quot; &quot; & strGroupBy & &quot; &quot; & strOrderBy) & &quot;;&quot;
UserSetSQL = strSQL

End Function

Examples:
1. All fields are included in SQL clause without Where and Order statements:

?UserSetSQL(&quot;SKL&quot
Select [PersonalCode],[SkillCode],[SkillDate],[RevOrgNo],[USERID],[RevisionDate] From [SKL];

2. Several fields are excluded and fields for Where and Order statements are defined:

?UserSetSQL(&quot;SKL&quot;, &quot;USERID;RevisionDate;RevOrgNo&quot;,&quot;;&quot;,&quot;RevisionDate<Date()-15&quot;,,&quot;PersonalCode,RevisionDate&quot
Select [PersonalCode],[SkillCode],[SkillDate] From [SKL] Where RevisionDate<Date()-15 Order By PersonalCode,RevisionDate;

In addition there is sub program that create new query:

Function NewQuery(strSQL As String, strQueryName As String)
'This function create new query
'It check for existing query with same name and remove if find it
Dim dbs As Database, myQueryDefine As QueryDef

' Return reference to current database.

Set dbs = CurrentDb

' Delete old query with same name

On Error GoTo Err_NewQuery
dbs.QueryDefs.Delete strQueryName

Err_NewQuery:
' Create new query.
Set myQueryDefine = dbs.CreateQueryDef(strQueryName, strSQL)
Set dbs = Nothing

End Function

Example:
dim strMySQL as string

strMySQL = UserSetSQL(&quot;SKL&quot;, &quot;USERID;RevisionDate;RevOrgNo&quot;,&quot;;&quot;,&quot;RevisionDate<Date()-15&quot;,,&quot;PersonalCode,RevisionDate&quot

call NewQuery(strMySQL,&quot;MyQueryName&quot
forms!MyForm.recordsource=&quot;MyQueryName&quot;

Aivars