Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-15-2019, 09:11 AM   #1
GK in the UK
Newly Registered User
 
Join Date: Dec 2017
Posts: 101
Thanks: 13
Thanked 0 Times in 0 Posts
GK in the UK is on a distinguished road
User Defined Function in SQL Select

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 ?

GK in the UK is offline   Reply With Quote
Old 11-15-2019, 09:27 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,378
Thanks: 0
Thanked 747 Times in 732 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: User Defined Function in SQL Select

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

select forms!myForm!txtSalePrice from table
Ranman256 is offline   Reply With Quote
Old 11-15-2019, 09:38 AM   #3
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,285
Thanks: 97
Thanked 2,032 Times in 1,979 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: User Defined Function in SQL Select

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

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 11-15-2019, 09:39 AM   #4
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: User Defined Function in SQL Select

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-15-2019, 09:54 AM   #5
GK in the UK
Newly Registered User
 
Join Date: Dec 2017
Posts: 101
Thanks: 13
Thanked 0 Times in 0 Posts
GK in the UK is on a distinguished road
Re: User Defined Function in SQL Select

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

Code:
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 & "));"
GK in the UK is offline   Reply With Quote
Old 11-15-2019, 10:05 AM   #6
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: User Defined Function in SQL Select

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-15-2019, 10:09 AM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,369
Thanks: 115
Thanked 3,111 Times in 2,829 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: User Defined Function in SQL Select

Quote:
Originally Posted by Ranman256 View Post
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

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-15-2019, 10:18 AM   #8
GK in the UK
Newly Registered User
 
Join Date: Dec 2017
Posts: 101
Thanks: 13
Thanked 0 Times in 0 Posts
GK in the UK is on a distinguished road
Re: User Defined Function in SQL Select

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 is offline   Reply With Quote
Old 11-15-2019, 10:23 AM   #9
GK in the UK
Newly Registered User
 
Join Date: Dec 2017
Posts: 101
Thanks: 13
Thanked 0 Times in 0 Posts
GK in the UK is on a distinguished road
Re: User Defined Function in SQL Select

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-
GK in the UK is offline   Reply With Quote
Old 11-15-2019, 10:23 AM   #10
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: User Defined Function in SQL Select

Quote:
Originally Posted by GK in the UK View Post
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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-15-2019, 10:30 AM   #11
GK in the UK
Newly Registered User
 
Join Date: Dec 2017
Posts: 101
Thanks: 13
Thanked 0 Times in 0 Posts
GK in the UK is on a distinguished road
Re: User Defined Function in SQL Select

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.
GK in the UK is offline   Reply With Quote
Old 11-15-2019, 11:05 AM   #12
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: User Defined Function in SQL Select

Quote:
Originally Posted by GK in the UK View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-15-2019, 12:09 PM   #13
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,285
Thanks: 97
Thanked 2,032 Times in 1,979 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: User Defined Function in SQL Select

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 11-15-2019, 01:25 PM   #14
GK in the UK
Newly Registered User
 
Join Date: Dec 2017
Posts: 101
Thanks: 13
Thanked 0 Times in 0 Posts
GK in the UK is on a distinguished road
Re: User Defined Function in SQL Select

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.
GK in the UK is offline   Reply With Quote
Old 11-15-2019, 01:35 PM   #15
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: User Defined Function in SQL Select

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?

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
User Defined Function ismailr General 3 10-19-2015 10:32 AM
User-Defined Function descriptions irish634 Excel 1 07-08-2009 04:46 AM
Question Adding a User Defined Function lynxbci General 1 01-14-2009 02:37 AM
User Defined Function not recalculating standrd Excel 1 02-23-2006 04:11 AM
First User-Defined Function slrphd Modules & VBA 4 05-19-2004 03:02 PM




All times are GMT -8. The time now is 11:33 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World