User Defined Function in SQL Select

[GK in the UK]

As an enhancement to an existing form I need to bind its recordsource with a SQL select in code, because I need to optionally display one of the columns negated and the criteria for that isn't available when the sub form loads.

So, I deleted the recordsource SQL in the data source for the sub form, and put it in the main form code.
The sub form has a field, txtSalePrice, which is the (optionally) negated value of the bound field tlSalePrice.

In the SQL select in the main form code one of the selections reads:
tblTransLines.tlSalePrice AS txtSalePrice
But nothing displays in the field txtSalePrice.

So that's one issue, but I also need to insert a function into the SQL select to display it 'as is' or negated.
So somehow I need to insert this function into the SQL select in the main form code.

txtSalePrice = fSign(bCredit(tlSalePrice))
Where fSign returns the value negated if bCredit is true

So that when the datasheet is bound it displays the value of tlSalePrice negated (for one or many lines)

Can it be done ?

 

[Ranman256]

in a query ,you can call a field on a form by using the full path:

select forms!myForm!txtSalePrice from table

 

[jdraw]

Remember, the form must be open in order to access the value.

 

[theDBguy]

Hi. Not sure if you're telling us the name of the Textbox or what's in its Control Source.

 

[GK in the UK]

field txtSalePrice is unbound with no data source
field tlSalePrice is the table field
the sub form has no data source (until it's bound in the main form)

When the SQL query runs, the sub form is already loaded.

The datasheet fills with data, but the unbound field txtSalePrice is empty.
So I want txtSalePrice to appear with the (optionally) negated value of tlSalePrice. I don't want to see tlSalePrice

The function fSign is public so I could put it in the form data source, BUT the parameter bCredit isn't available until main form loads.

Here's the binding but I need to get the function in there somehow, so that txtSalePrice is populated with the (optionally) negated value

ctlPaymentSourceDsheet.Form.RecordSource = _
        "SELECT tblTransLines.TransLineID," & _
        "tblTransLines.tlTransHeaderFK ," & _
        "tblTransLines.tlCustSuppID ," & _
        "tblTransLines.tlConversionFactor ," & _
        "tblTransLines.tlSalePrice AS txtSalePrice ," & _
        "tblTransLines.tlDiscPercent " & _
        "FROM tblTransLines " & _
        "WHERE (((tblTransLines.tlTransHeaderFK)=" & Me.TransHeaderID & "));"

 

[theDBguy]

Hi. If I understand it correctly, you should bind txtSalePrice to tlSalePrice and simply adjust your SQL to display something or nothing.

 

[isladogs]

in a query ,you can call a field on a form by using the full path:

select forms!myForm!txtSalePrice from table

But not like that! But you can do this

SELECT SalePrice
FROM TableName
WHERE SalePrice=Forms!FormName.txtSalePrice;

 

[GK in the UK]

OK thanks DB Guy, I've bound txtSalePrice to tlSalePrice but I get (as I expected) the 'naked' value of tlSalePrice appearing.
So I can now see £234.56- but I want it to appear as £234.56
The table value of tlSalePrice is 234.56-, I don't want to change it in this routine, I just want it to appear negated.
But not every time the form loads, only sometimes, according to the value of bCredit which isn't available until main form loads.
The sub form is a datasheet so I may want all lines negating.

How do I adjust the SQL to optionally negate it ?

 

[GK in the UK]

PS I can't use ABS or negate it regardless, the whole point is that I want to see the reversed value according to bCredit. Sometimes the table value might actually be 234.56 and I would want to see that as 234.56-

 

[theDBguy]

OK thanks DB Guy, I've bound txtSalePrice to tlSalePrice but I get (as I expected) the 'naked' value of tlSalePrice appearing.
So I can now see £234.56- but I want it to appear as £234.56
The table value of tlSalePrice is 234.56-, I don't want to change it in this routine, I just want it to appear negated.
But not every time the form loads, only sometimes, according to the value of bCredit which isn't available until main form loads.
The sub form is a datasheet so I may want all lines negating.

How do I adjust the SQL to optionally negate it ?

Hi. Would the user ever have to update/edit the value in tlSalePrice while viewing it on this form?

 

[GK in the UK]

Yes quite possibly. The user would edit txtSalePrice. Then I would negate it in BeforeUpdate and assign it to tlSalePrice.



It's a payment form which I want to serve as a receipts form. But users don't expect to have to key payments as minus values (as in, credit bank account: book keeping).



So when we ask the user for a value 'to pay a supplier' they expect to key it as an unsigned value.
When we ask the user for a value 'received from a customer' they also expect to key it as an unsigned value.
But in the table, I need to save it signed, according to whether it's a payment or a receipt.


Bit it's not beyond the realms of possibility that reversed (negative) values could be keyed, I've got to allow for that.

 

[theDBguy]

Yes quite possibly. The user would edit txtSalePrice. Then I would negate it in BeforeUpdate and assign it to tlSalePrice.

It's a payment form which I want to serve as a receipts form. But users don't expect to have to key payments as minus values (as in, credit bank account: book keeping).

So when we ask the user for a value 'to pay a supplier' they expect to key it as an unsigned value.
When we ask the user for a value 'received from a customer' they also expect to key it as an unsigned value.
But in the table, I need to save it signed, according to whether it's a payment or a receipt.

Bit it's not beyond the realms of possibility that reversed (negative) values could be keyed, I've got to allow for that.

Hi. That's not what I meant, but it does answer my question. So, my idea was to modify your SQL to "check" the main form value (somehow) to display the tlSalePrice value or blank it out, maybe by using an IIf() statement. When the main form opens, it will replace the value with the negated value. Or, if you can't refer to the main form in your SQL statement, then maybe just blank it out; and by the time the main form loads, the desired value should display. Sorry I can't give you any specific steps because I have no idea what your database is doing or what your data look like.

 

[jdraw]

I'm with the others --uncertain of your requirement -- but it seems that for presentation you could multiply by -1 to negate a value. However, the logic involved between the table field and the form control's value escapes me.
Perhaps if we had some real data (even a mock up) in context we coud offer more focused responses.

 

[GK in the UK]

Not sure how I can explain it better. The bottom line is I want to optionally negate one of the SQL select fields before it's displayed in the datasheet. I don't want to change the value (not yet anyway), I want to display it reversed. Not ABS, not necessarily positive, I want to display it the opposite sign to what is in the table.
So normally if I set the RecordSource of a datasheet form, each field displays as it comes from the table. -123.45 appears as, -123.45
But, I might want -123.45 to appear as 123.45.

But I need to be able to control when it appears negated because sometimes I want to see it exactly the same as it is in the table.

If the boolean value (bCredit) was available when the sub form is loaded, I would say txtSalePrice = iif(bCredit, -tlSalePrice, tlSalePrice). txtSalePrice is an unbound text box, tlSalePrice is the table field. But bCredit isn't in scope, not until the main form is loaded, by which time the sub form is already in place. So if I could do the iif *within* the SQL of the main form that might do it. It's a datasheet I'm showing with many lines.

I'm going to multiply it by 1 or -1 as you're suggesting. So the user-keyed value will be, as keyed, and displayed, as keyed. The 1 or -1 will be saved in the record. That's how I did it for a dual function invoice form but I wondered if there might be an easier way.

Sorry I seem to be not explaining myself very well. I've lived double entry book keeping and it's second nature to know how the various transactions need to be signed in the table to get a trial balance. The user often neither knows nor cares. Thanks for your input.

 

[theDBguy]

And just the same, I can't understand why this is so hard to do. You should be able to update the SQL statement for the subform to display the value you want. Saying that "sometimes" you want to display a value a certain way doesn't help us understand what "sometimes" mean. However, if you're saying that the value should be displayed based on bCredit, then why not try adding bCredit to the Record Source (SQL) of the subform?

 

[GK in the UK]

Because it's not available when the sub form loads, it's evaluated in the main form. Sub forms always load first, don't they? Hence the reason I cut the recordsource out of the sub form and into the main form.



Is it possible to put a user-defined function *within* a SQL select ? Like post #5 but with an embedded function to do the test and format txtSalePrice.



I would want to do the test, then assign txtSalePrice *within* the SQL select, for each datasheet line.

 

[theDBguy]

Because it's not available when the sub form loads, it's evaluated in the main form. Sub forms always load first, don't they? Hence the reason I cut the recordsource out of the sub form and into the main form.

Is it possible to put a user-defined function *within* a SQL select ? Like post #5 but with an embedded function to do the test and format txtSalePrice.

I would want to do the test, then assign txtSalePrice *within* the SQL select, for each datasheet line.

Hi. I just posted a sample db trying to show how to access a value on the main form from the subform, so I think this should be possible. If you were able to calculate it on the main form, you should also be able to do the same from the subform. If you can post a sample db, we would be able to show you what we mean (or at least agree with you, after seeing what you got, that it's not possible). Right now, we're flying blind because we can't see what you're seeing, and we're not very familiar with the inner workings of your database.

 

[GK in the UK]

I realise I can reference main form stuff from the sub form. Sorry to keep saying it, I must be misunderstanding the working of Access (very likely) but main form or bCredit isn't available when the sub form loads. I realise I can 'late bind' when the main form loads, that's the aim, but with some way of formatting txtSalePrice


Would something like this do it:


SELECT Format(tlSalePrice, "Currency") AS txtSalePrice
FROM tblTransLines;


BUT "currency" would have to be a function to test for bCredit.
Maybe I could just define two SQL statements, for for each state of bCredit.


Where do I find your upload ?

 

[isladogs]

See post #7. it MAY help

 

[GK in the UK]

Thanks Colin, I'm not sure that it's the answer. It would be good to know if there's a solution although I'll go with my multiply by -1 method. Because it works well with a dual function invoice form, it edits a sales invoice or a purchase invoice, the user keys positive unsigned values on either form but when it's saved to the table it's correctly signed for accounting purposes, according to whether it's sales or purchase.


txtSalePrice is on the sub form, it isn't a single text box anywhere. So it 'exists' (my way of thinking) for each datasheet line (and I'm displaying a datasheet in a sub form, not just a single record)


I can stick txtSalePrice on the datasheet and (I think) put this in the data source: =iif(bCredit, -[tlSalePrice],[tlSalePrice]). But bCredit isn't available, it's not even initialised when sub form loads.


Edit: When I say either form, it's the same form, presented to create either a sales invoice or a purchase invoice