need formula \ code advice. min date (1 Viewer)

[TipsyWolf]

hello guys
and im here today with i think easy question for pro-access users here.

i have 1main table and 1 sub table (one to many)
main table has general info. sub table has few records per 1 record from main table.
all focus to date field in sub table.

how do i return min. date into main table.
for example
1st record of main table has 2 records in sub table. these 2 records have extra info, including date. i need to get minimum date and put it to main table under (lest say min. date field)

i know i can do it by query , but its hard for me later to use this min. date for my further furmals like dcount or iif(sum())

suggestions, advice are very welcome

 

[Gasman]

DMin() ?
However I would not store it again.
Could another record be added with an earlier date?

 

[TipsyWolf]

DMin() ?
However I would not store it again.
Could another record be added with an earlier date?

=DMin( [Tactions]![DueDate] )

it says error... containing a wrong number of arguments,
i put this formula into Control Source of duedate text box

but how does it know that i need min date only records that related to 1 record of parent table ?

 

[Gasman]

Perhaps using criteria?
Have you ever thought of looking up the syntax for anything?

DMIN function - Microsoft Support

Returns the smallest number in a field (column) of records in a list or database that matches conditions that you specify.

support.microsoft.com

I would probably just set it on the current event of the main form, but that is me.

 

[TipsyWolf]

Perhaps using criteria?
Have you ever thought of looking up the sytax for anything?

DMIN function - Microsoft Support

Returns the smallest number in a field (column) of records in a list or database that matches conditions that you specify.

support.microsoft.com

I would probably just set it on the current event of the main form, but that is me.

i read about dmin a bit more and i tired this

=DMin("[duedate]";"[Tactions]";"[RiskID]"=" & [RiskID]")

but my textbox is blank

 

[Josef P.]

The Criteria parameter is false.

Check:

dim Criteria as String
Criteria = "[RiskID]"=" & [RiskID]"
debug.print Criteria, "oops :-)"

You can also test directly in the Immediate window (VBA editor):
? "[RiskID]"=" & [RiskID]"


Use:
"[RiskID]=" & [RiskID]

 

[TipsyWolf]

The Criteria parameter is false.

Check:

dim Criteria as String
Criteria = "[RiskID]"=" & [RiskID]"
debug.print Criteria, "oops :-)"

You can also test directly in the Immediate window (VBA editor):
? "[RiskID]"=" & [RiskID]"


Use:
"[RiskID]=" & [RiskID]

already did it in post #5 here

 

[Josef P.]

Have you looked at the printout in the immediate window?
What was the result?

The expression "[RiskID]"=" & [RiskID]" compares 2 strings ("[RiskID]" and " & [RiskID]") and the result is False.

To compare:
=DMin("[duedate]";"[Tactions]";"[RiskID]"=" & [RiskID]") <-- #5
vs
=DMin("[duedate]";"[Tactions]";"[RiskID]=" & [RiskID])

 

[TipsyWolf]

Have you looked at the printout in the immediate window?
What was the result?

To compare:
=DMin("[duedate]";"[Tactions]";"[RiskID]"=" & [RiskID]") <-- #5
vs
=DMin("[duedate]";"[Tactions]";"[RiskID]=" & [RiskID])

it says #Name? error
i even changed format fot the textbox to 'short date', but still #Name? error

 

[The_Doc_Man]

for example
1st record of main table has 2 records in sub table. these 2 records have extra info, including date. i need to get minimum date and put it to main table under (lest say min. date field)

The problem is related to understanding queries. You NEVER store data in a main table that was derived from an arbitrary number of child records. Instead you make a query that represents a JOIN of the parent to its children. This implies there is a primary key on the main that can / does act like a foreign key in the child tables. If so, you can do group-by operations. The question is whether for this purpose there is any other data from the child table that you needed to pull up to the main table besides this date. If not, this should be quite simple.

i know i can do it by query , but its hard for me later to use this min. date for my further furmals like dcount or iif(sum())

This implies that you are working too hard. You are probably trying to use the main table but instead should be using a query to feed the next set of formulas. Access rarely cares that a recordset is from a table. It likes query-based recordsets just as well.

 

[mike60smart]

Hi
If you base the Main Form on a Select Query then you can add the criteria.

See the attached FRiskPage

 

[LarryE]

i read about dmin a bit more and i tired this

=DMin("[duedate]";"[Tactions]";"[RiskID]"=" & [RiskID]")

but my textbox is blank

Also, never use brackets in the table argument. "Tactions", not "[Tactions]"

 

[Gasman]

already did it in post #5 here

Post #5 syntax is not correct.
It is called a language for a reason.

 

[tvanstiphout]

Also, never use brackets in the table argument. "Tactions", not "[Tactions]"

And never mismatch your double-quotes:
=DMin("duedate";"Tactions";"RiskID=" & Me.RiskID)
(I'm a bit fuzzy on the use of semicolon as an argument separator, but that may be OK in some locales).

 

[TipsyWolf]

Hi
If you base the Main Form on a Select Query then you can add the criteria.

See the attached FRiskPage

hi
this one worked for me. i just copied it to my original db and its cool
thank you for your help !

 

[TipsyWolf]

@The_Doc_Man
thank you for your suggestion !
i made a query

and in my form i put

=DLookUp("minofduedate";"Qmindate";"[riskID]=" & [riskID])

and it shows me #name? for some reason ...

 

[TipsyWolf]

i really would like to learn how to get data from a query. i underestimate power of query as i see, so even though @mike60smart helped me, i would like to use query as alternative and educational purposes and i would like somehow to Dlookup vales from query to form with [riskID]=" & [riskID]) condition

 

[Josef P.]

and it shows me #name?

Does the RiskID data field or a RiskID control exist in this form?

 

[TipsyWolf]

Does the RiskID data field or a RiskID control exist in this form?

yeap

 

[Josef P.]

This is the label text. The name of the AccessField or Control could be different.