Sql syntax

Mcgrco

Registered User.
Local time
Today, 16:24
Joined
Jun 19, 2001
Messages
118
I want to write a function that will check for the reporting date in a table. Where tblname is the table name and repfield is the field in tblname that contains the reporting date. I cant get it to work .

Can anyone help.
Much apprciated

Function sTableReportingDate(tblname As String, Repfield As Field)

Dim rs, rs1 As Recordset
Dim sSql, repdate As String
Dim db As Database
Set db = CurrentDb

sSql = sSql & ""
sSql = sSql & " SELECT DISTINCT [" & tblname & "].[" & Repfield & "]"
sSql = sSql & " FROM [" & tblname & "];"


Set rs = db.OpenRecordset(sSql)

repdate = rs.Fields(0)

sTableReportingDate = repdate


End Function
 
Dudes,

Do you think the following code will work??

Option Compare Database
Option Explicit
Dim SQLReportDate As String
Dim tblName As String
Dim repfield As String


Public Sub SelectReportdate()

tblName = YourForm.ControltblName '(i.e. the place that say the name of the table. Think of ComboBox on a form)

repfield = YourForm.ControlRepfield '(i.e. the place that say the name of the field. Think of a list or ComboBox on a form, dynamic refresh with the Table combo box.)

SQLReportDate = "SELECT distinct " & tblName & "." & repfield & " FROM tblName;"

SQLReportDate = YourForm.SubFormIsQuery.Forms.RecordSource


End Sub
 
I'm with Rich, why are you making it so complicated? Use a query, or a Dlookup at the least.

David R - still a big fan of simple solutions.
 
The reason I want it in code rather than a query is that the function is used in conjuntion with a report. There are 8 tables and 8 reports some have differnt headers for the reporting date so I thought why no write a funtion to be used in the format part of the report that could query a variable table with a variable field name. If is still overkill??
 

Users who are viewing this thread

Back
Top Bottom