calculate minimum value based on table (1 Viewer)

[connected]

Hello,

I have this table where values are stored say about 10, and a another form which a text 'min value need' search the table with the stores values and identifies the lowest values and palce in the tetx in the form, how can this be done, i tried the dmin it doesnt work

thanks in advance

 

[pbaldy]

Since DMin is probably the simplest way to get the lowest value from the table, perhaps you can show us what you tried so we can fix it, or tell us why DMin doesn't work in your situation so we can suggest an alternative.

 

[boblarson]

Also, when you say "value" what datatype are the "values" stored as in the table?

 

[connected]

Also, when you say "value" what datatype are the "values" stored as in the table?

hi, it stored as Currency, and the dmin is used by creating a form by the query, and right clicking the text box and type ing code in expression builder

 

[boblarson]

What is the code you came up with for the DMIN? There is some specific things you have to do to use it correctly:

=DMin("[YourFieldNameHere]","YourTableOrQueryNameHere")

Or if you are limiting by criteria:

=DMin("[YourFieldNameHere]","YourTableOrQueryNameHere", "[YourFieldToLimitBy]=XXXX")

Or if limiting by a control on the form:

=DMin("[YourFieldNameHere]","YourTableOrQueryNameHere", "[YourFieldToLimitBy]=" & [YourFieldNameOnForm])

Or if text
=DMin("[YourFieldNameHere]","YourTableOrQueryNameHere", "[YourFieldToLimitBy]='" & [YourFieldNameOnForm] & "'")

 

[connected]

What is the code you came up with for the DMIN? There is some specific things you have to do to use it correctly:

=DMin("[YourFieldNameHere]","YourTableOrQueryNameHere")

Or if you are limiting by criteria:

=DMin("[YourFieldNameHere]","YourTableOrQueryNameHere", "[YourFieldToLimitBy]=XXXX")

Or if limiting by a control on the form:

=DMin("[YourFieldNameHere]","YourTableOrQueryNameHere", "[YourFieldToLimitBy]=" & [YourFieldNameOnForm])

Or if text
=DMin("[YourFieldNameHere]","YourTableOrQueryNameHere", "[YourFieldToLimitBy]='" & [YourFieldNameOnForm] & "'")

Hi, ok so in the form in a textbox, in the expression builder for it, the code is =DMin("[bid amount]","top winning bid")

 

[boblarson]

And what isn't working? Are you getting a #Name error in it?

 

[connected]

And what isn't working? Are you getting a #Name error in it?

the lowest value doesnt show for some reason?

 

[boblarson]

Any way you can post your db?

See here for how: http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[connected]

Any way you can post your db?

See here for how: http://www.access-programmers.co.uk/forums/showthread.php?t=140587

hi the is attached

thanks

 

[boblarson]

Okay that was quick and easy.

Your problem is that you put the DMin on the Before Update event and not in the CONTROL SOURCE (on the Data or ALL tabs).

 

[connected]

Okay that was quick and easy.

Your problem is that you put the DMin on the Before Update event and not in the CONTROL SOURCE (on the Data or ALL tabs).

thanks it working perfectly, umm a question if i want to create a login form where the username and password are checked against the table, how can i do this using dlookup, so far i have =DLookUp("[password]","bidders","bidders"=[bidders]), i think the ending is incorrect "=[bidders])"

thanks in advance

 

[boblarson]

In code it would be:

If Me.txtPassword = DLookUp("[password]","bidders","bidder='" & Me.YourLoginFormTextBoxForBidderName & "'") Then

 

[boblarson]

That is, assuming the field name for storing the bidder name is bidder

 

[connected]

In code it would be:

If Me.txtPassword = DLookUp("[password]","bidders","bidder='" & Me.YourLoginFormTextBoxForBidderName & "'") Then

this looks by a vb code?, i want input the code by the expression builder, anyways i try this and let u know

thanks again

 

[connected]

this looks by a vb code?, i want input the code by the expression builder, anyways i try this and let u know

thanks again

thanks got it fully working