Sql syntax

[Mcgrco]

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

 

[Rich]

Why not just use a query?

 

[The Member]

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

 

[David R]

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.

 

[Mcgrco]

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??