Return value in first row of table only in VBA

[katep74]

I am trying to retrieve a date value from the first row of a table using VBA. I have got as far as

Dim RepDate as Date
RepDate = DLookup("ReportDate", "tblMain")

but I can't work out how to retrieve the value contained in the first row of the table, can I use

DoCmd.GoToRecord acDataTable, "tblMain", acGoTo, 1

and if so how do I combine the 2?

[patjones]

Hi -

So that I can understand the problem, what's unique about the first row in the table that you always want the date value from that row? And are you sure that it will always contain the date value that you're looking for (as opposed to the fourth or ninth or 34th or whatever other row)?

DoCmd.GoToRecor d is good when you have a form bound to a table, and should work if that is your situation. But it looks like you're trying to assign the date value in question to a date variable.

One thing you can try is

Dim rstGetFirstDate As Recordset
Dim RepDate As Date

Set rstGetFirstDate = CurrentDb.OpenRecordset("tblMain")
rstGetFirstDate.MoveFirst

RepDate = rstGetFirstDate!ReportDate

rstGetFirstDate.Close
Set rstGetFirstDate = Nothing

Please let me know if this is what you're looking for.

Pat

[katep74]

Pat

Many thanks for your help, that worked great and is useful code I will reuse.

I however realised that I was being really stupid and could just use a totalled query with Group By on ReportDate to extract the value I needed and then do a dlookup on the query, as the value in the ReportDate field is always the same for all records in the table. Apologies, I'm not used to programming in Access.

[NeoPa]

I too, am a little confused. Your DLookup() code, missing any criteria as it is, should find the first matching record. I see no reason why that would be any other than the first record, but that rather depends on what you mean by first. RDBMSs generally treat tables as buckets of data, rather than as ordered series. records only have logical series when access via a specific index. Indexed recordsets can have order, but the order for tables is generally undefined, though an existing Primary index generally drives their access.

[katep74]

When I used DLookup on tblMain (RepDate = DLookup("Report Date", "tblMain") I got the following error, "Syntax error (missing operator) in query expression "ReportDate ", which is why I tried to find another way to get the RepDate value.

Thanks for your help.

[patjones]

katep -

Just for future reference, the reason you got the error is because DLookup needs a third argument. DLookup picks out one column from one row in your table on the basis of some criteria, which is supposed to be the third argument.

Pat

[NeoPa]

I don't think that's it Pat. Criteria is an optional parameter.

I'm wondering if [reportDate] is the correct spelling of the field name?

[patjones]

Ah yes, I see that now. I don't think I've ever used DLookup in that manner. I tried it just now on a test table and it does indeed give the first record in the table.

Pat