Dmax

[realspido]

Hi,

I have a simple table [tblPaymentTerms]:

Creditor <text>
Terms <number>

and then form with field from another table: [tblInvoices].[Creditors]

I need to find creditor in tblPaymentTerms for currently displayed value on the form forms![Invoices].[Creditors] and return value [Terms] which I'd like to use for calculation on the form.

Now, the easiest way is to make query, but even if it returns one record I don't know how to pull it out and use on the form, eg. to get that value from query, add it to invoice date and enter into tblInvoices and display on the form at same time.

I was trying to use statement:

termX = DMax("[Payment_Period]", "tblPaymentTerm s", "[Creditor] = " & Me.Creditor)

Me.Payment_Due = termX + Me.Inv_Date

but it returns error: "the expression you entered as a query parameter produced this error 'MSTS'" - which is searched creditor currently displayed on the form.


PLease help.

Thanks.

[puppydogbuddy]

Hi,

I have a simple table [tblPaymentTerms]:

Creditor <text>
Terms <number>

and then form with field from another table: [tblInvoices].[Creditors]

I need to find creditor in tblPaymentTerms for currently displayed value on the form forms![Invoices].[Creditors] and return value [Terms] which I'd like to use for calculation on the form.

Now, the easiest way is to make query, but even if it returns one record I don't know how to pull it out and use on the form, eg. to get that value from query, add it to invoice date and enter into tblInvoices and display on the form at same time.

I was trying to use statement:

termX = DMax("[Payment_Period]", "tblPaymentTerm s", "[Creditor] = " & Me.Creditor)

Me.Payment_Due = termX + Me.Inv_Date

but it returns error: "the expression you entered as a query parameter produced this error 'MSTS'" - which is searched creditor currently displayed on the form.


PLease help.

Thanks.

Try this:
termX = DMax("[Payment_Period]", "tblPaymentTerm s", "[Creditor] = '" & Me.Creditor & "'")

Creditor is text data type and the above is syntax for text. The syntax you originally used is for numeric data types.

[realspido]

Try this:
termX = DMax("[Payment_Period]", "tblPaymentTerm s", "[Creditor] = '" & Me.Creditor & "'")

Creditor is text data type and the above is syntax for text. The syntax you originally used is for numeric data types.

Thanks! Too easy!!!

Could you tell me the other option? I always struggle how to do same using a query; I mean I made the query, but don't know how to pull out the result (eg. if query returns just one record) and use it in form as part of formula.
Other thing is, how to write a statement in Visual Basic which will create temporary query, and use the results of query in formula?

Thanks in advance for a clue...

[sanniep]

Thanks! Too easy!!!

Could you tell me the other option? I always struggle how to do same using a query; I mean I made the query, but don't know how to pull out the result (eg. if query returns just one record) and use it in form as part of formula.
Other thing is, how to write a statement in Visual Basic which will create temporary query, and use the results of query in formula?

Thanks in advance for a clue...

Create a textbox and use a dlookup expression, something like
Dlookup("[Value]","[Query]")

[puppydogbuddy]

Hi,

I have a simple table [tblPaymentTerms]:

Creditor <text>
Terms <number>

and then form with field from another table: [tblInvoices].[Creditors]

I need to find creditor in tblPaymentTerms for currently displayed value on the form forms![Invoices].[Creditors] and return value [Terms] which I'd like to use for calculation on the form.

Now, the easiest way is to make query, but even if it returns one record I don't know how to pull it out and use on the form, eg. to get that value from query, add it to invoice date and enter into tblInvoices and display on the form at same time.

I was trying to use statement:

termX = DMax("[Payment_Period]", "tblPaymentTerm s", "[Creditor] = " & Me.Creditor)

Me.Payment_Due = termX + Me.Inv_Date

but it returns error: "the expression you entered as a query parameter produced this error 'MSTS'" - which is searched creditor currently displayed on the form.


PLease help.

Thanks.

Try this:
termX = DMax("[Payment_Period]", "tblPaymentTerm s", "[Creditor] = '" & Me.Creditor & "'")

You used numeric syntax. Creditor is a text data type.

[realspido]

Create a textbox and use a dlookup expression, something like
Dlookup("[Value]","[Query]")

OK but how to write a query statement in Visual Basic (instead of creating a query in access)?

I mean how should look the following query in VB?:

INSERT INTO tblPaymentTerms ( Creditor )
SELECT tblInvoice.Cred itor
FROM tblInvoice LEFT JOIN tblPaymentTerms ON tblInvoice.[Creditor] = tblPaymentTerms .[Creditor]
GROUP BY tblInvoice.Cred itor, tblPaymentTerms .Creditor
HAVING (((tblPaymentTe rms.Creditor) Is Null));


Thanks

[puppydogbuddy]

OK but how to write a query statement in Visual Basic (instead of creating a query in access)?

I mean how should look the following query in VB?:

INSERT INTO tblPaymentTerms ( Creditor )
SELECT tblInvoice.Cred itor
FROM tblInvoice LEFT JOIN tblPaymentTerms ON tblInvoice.[Creditor] = tblPaymentTerms .[Creditor]
GROUP BY tblInvoice.Cred itor, tblPaymentTerms .Creditor
HAVING (((tblPaymentTe rms.Creditor) Is Null));


Thanks

I don't know VB syntax so I can't help you there. If you use syntax similar to the following sql syntax, you should be able to run this query in both, VB and Access.

INSERT INTO tblPaymentTerms ( Creditor )
WHERE Creditor IN
(SELECT [Creditor] FROM tblInvoice
WHERE tblInvoice.[Creditor] = tblPaymentTerms .[Creditor])
GROUP BY tblInvoice.[Creditor], tblPaymentTerms .[Creditor]
HAVING (((tblPaymentTe rms.[Creditor]) Is Null));