Dfirst function

  • Thread starter Thread starter ToniS
  • Start date Start date
T

ToniS

I would like to get the first valid record out of a table, (the first record
has to have a valid store number) therefore I was trying to use an inner
join for the criteria but ran into problems., below is what I have.

docNum1 = DFirst("DocNum", "MasterFLIFLC", "INNER JOIN TRAN_MEMBER ON
MASTERFLIFLC.StoreNum = TRAN_MEMBER.CMCUNO")

can an inner join be done with a Dfirst?

Basically I just need to grab the first valid record out of MasterFLiFLC
and capture the doc number.

Thanks
Toni
 
ToniS said:
I would like to get the first valid record out of a table, (the first
record
has to have a valid store number) therefore I was trying to use an inner
join for the criteria but ran into problems., below is what I have.

docNum1 = DFirst("DocNum", "MasterFLIFLC", "INNER JOIN TRAN_MEMBER ON
MASTERFLIFLC.StoreNum = TRAN_MEMBER.CMCUNO")

can an inner join be done with a Dfirst?

Basically I just need to grab the first valid record out of MasterFLiFLC
and capture the doc number.
Click to expand...


Yuo can't apply an inner join that way, and you can't rely on DFirst to give
you the "first" record unless you apply it to a sorted query that would
define what "first" means -- and I'm not sure it will do that even then.
DFirst isn't really good for much.

You can probably use a DLookup with a subquery in the criteria argument, if
you can define what ordering of records can be used to identify the first
record. Is there a date/time field you can sort by? Or is it the record
with the minimum value in a particular field? Or what?

If you just want *some* record with a valid StoreNum, you might use your
DFirst to get an arbitray valid record, like this:

docNum1 = DFirst("DocNum", "MasterFLIFLC", _
"StoreNum In (SELECT CMCUNO FROM TRAN_MEMBER)")

But that won't reliably bring you a record that could be described as
"first", except in the sense that it's the first one that was convenient for
the query engine to lay its hands on.
 
Domain aggregate functions (like DFirst, DLookup and the like) do not allow
joins.

Be aware, too, that First isn't really a meaningful concept in relational
databases. The only time you can rely on First or Last is if you've using a
query with an ORDER BY clause on it.

I'm afraid you'll have to give a bit more of an explanation if you want a
suggestion for an alternative. What's the relationship between MasterFLIFLC
and TRAN_MEMBER? What defines a "valid record out of MasterFLiFLC "? What
defines "the first valid record out of MasterFLiFLC"?
 
Thanks this is what I am looking for, thanks for the reminder about dfirst
not actually grabbing the 'first' record. I basically just need to grab a
record that has a valid store number and capture the doc number. I then will
use the doc number to determine if an append has taken place or not..... I am
not sure the approach I am taking is the right one, but I think it will work

thanks again
 
Thank you for the quick response, I guess I did not make it clear on what I
was trying to do... I think Dirk gave me what I was looking for.

Thanks again
Toni
 
Back
Top