Looping though a recordset returns the 'Epoch' date

[easyrider]

Hi guys,



I have a a form that during the On Load event assigns a date to a textbox based on a public function. This public function loops through the records in a certain table and looks for a specific type of record. The function then returns that date to the textbox.

The problem that I'm having is that if a date for the specific type of record exists, it returns the correct date. If it does not exist, then it returns the 'Epoch' date of 12/30/1899.

How do I get the textbox to display nothing instead of the Epoch date if there are no matching records?

sample db is attached. To demonstrate, open it and on the main form, click the "Show Final Visit" button for the first user. On the Show Visit form, the final visit date is 12/30/1899. Close the show visit form and navigate (little blue buttons at the bottom of the main form) to the next user that does have a record with final date.

STNG_Planets.zip

[xps35]

Change the first line of the function to:

Public Function GetFinalVisitDate(User_ID As Integer) As Variant

[easyrider]

Change the first line of the function to:

Public Function GetFinalVisitDate(User_ID As Integer) As Variant

Well, THAT was an easy fix. Thanks!!

[moke123]

Why a recordset and not a dlookup?

dlookup("VisitDate","Visit_T","User_ID = " & User_ID & " And VisitType_ID = 3")

[easyrider]

Why a recordset and not a dlookup?

dlookup("VisitDate","Visit_T","User_ID = " & User_ID & " And VisitType_ID = 3")

A while back, I had watched several videos by Sean McKenzie on youtube about looping through a recordset and thought it was a cool new tool to add to my problem solving toolbox. Certainly your suggestion will work tho, thanks!

[moke123]

As long as you realize your not really looping the recordset, assuming there is only one final visit for each userID. You are only opening one record.
You can just as easily use the dlookup as the controlsource of the textbox.

=dlookup("VisitDate","Visit_T","User_ID = " & me.User_ID & " And VisitType_ID = 3")

[Welshgasman]

A while back, I had watched several videos by Sean McKenzie on youtube about looping through a recordset and thought it was a cool new tool to add to my problem solving toolbox. Certainly your suggestion will work tho, thanks!

You could use a wood chisel as a screwdriver, but not really the correct tool?

[NoellaG]

be careful, looping through a recordset is sometimes necessary, but is a painstaking, slow process and should be kept as a last resort solution. See also RBAR (Row By Agonizing Row)

[easyrider]

You could use a wood chisel as a screwdriver, but not really the correct tool?

What about using a screwdriver as a wood chisel? Lol. Dlookup only returns a single value based on the criteria specified. In this case, I wanted to find any and all records that met a certain criteria. Since you don't like my choice of tools, what would you use?

[Welshgasman]

Going on post #6, it would be DlookUp()
However looking at your code, there is no order, so if I added a record, that had a date before the last date of visit, because I forgot it at the time, then you would get that, if more than one record exists, which I would have thought is very likely?
In which case DMax() seem more appropriate?

Still using a recordset, it would be sorted by date descending and pick first record. However why retieve a set of records, when only one is required?

[moke123]

Dlookup only returns a single value based on the criteria specified. In this case, I wanted to find any and all records that met a certain criteria.

Perhaps we need some context.

"Final Visit" suggests that there would only be 1 final visit and your table structure suggest there would only be 3 visits total per user.

[NoellaG]

I wanted to find any and all records that met a certain criteria

In this case a select statement with the correct WHERE criterium does the trick. If you only want 1 value add TOP 1 and the correct ORDER BY. I really don't see why you would want to loop through a recordset.

[Welshgasman]

In fact if you made the subform a little bigger, big enough for 3 records, there would be no need for the second form. Everything would be visible on the main form?

[easyrider]

Sorry for the hiatus - I had to go out of town the other week unexpectedly. Thanks to Peter for answering my initial question regarding the Epoch date.

After reviewing the other comments/suggestions about looping through the recordset, I've decided to follow the suggestion by moke123 and use DLookup instead. However due to the alleged performance issues of the domain aggregate functions like DLookup, I created a query to get the record I'm after and then run DLookup against that query.

Cheers,
-Bill

[ArviLaanemets]

What about using a screwdriver as a wood chisel? Lol. Dlookup only returns a single value based on the criteria specified. In this case, I wanted to find any and all records that met a certain criteria. Since you don't like my choice of tools, what would you use?

I'd create an UDF, e.g. LastVisit(parUserID, parQueryType), which will return a value from a query like
SELECT TOP 1 VisitDate FROM visit_T WHERE (QueryConditions using parameter values of UDF) ORDER BY VisitDate DESC