Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-15-2019, 01:46 PM   #16
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

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.

GK in the UK is offline   Reply With Quote
Old 11-15-2019, 01:58 PM   #17
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
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.
__________________
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, 02:10 PM   #18
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

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 ?

GK in the UK is offline   Reply With Quote
Old 11-15-2019, 02:13 PM   #19
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

See post #7. it MAY help
__________________
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, 02:30 PM   #20
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

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

Last edited by GK in the UK; 11-15-2019 at 02:34 PM. Reason: Clarity
GK in the UK is offline   Reply With Quote
Old 11-15-2019, 02:52 PM   #21
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. It might help if you think of the data being in tables and not in forms. For example, what is the formula, if any, for bCredit? If it's actually a field in a table, then I don't understand why it can't be included in the subform. Are you not able to make a small mockup database showing the basics of your tables and forms, so we can get to the bottom of this sooner? Just hoping...
__________________
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, 03:28 PM   #22
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

bCredit is a boolean evaluated in frmPayment (which is also going to do receipts)

So from an index form, user selects a document which could be 'Purchase Payment' or 'Sales Receipt'.
Document type is passed in OpenArgs.

frmPayment opens. Reads OpenArgs, then goes to a config table and finds out if the type of document type is a 'reversal' type.
So now frmPayment knows if the values it reads from the table are presented, as saved, or presented reversed.

That's because some values in the table will be credits, Let's say it's a supplier payment for 」123.45 which would be saved as -123.45 the user doesn't expect to see the payment value presented as a negative value.
So the table fields gets copied in, and are reversed for editing, then on final save are reversed back. (A payment could have several sources hence a datasheet). So if the user amended the payment to 」223.45 it would be saved in the table as -223.45

If frmPayment determines it's a customer receipt, the values are presented as read and saved, as keyed.

So the same form does payments and receipts (and other stuff like allocations/part allocations) and has to maintain book keeping integrity.

The table values are presented in datasheet form and are edited with a dialog pop up.

The function to present the values is typically =fSign(bCredit,tlSalePrice). fSign is public but bCredit is module and frmPayment doesn't evaluate it until it loads, by which time the sub form datasheet is already loaded. If bCredit is true fSign returns the value negated, so, -123.45 appears as 123.45

So, let's keep focus on the issue. I want to be able to set the recordsource of the datasheet, when the main form loads and has evaluated the value of bCredit.
And when main form knows the value of bCredit, devise a SQL select statement which includes function fSign or has some way of negating the value read from the table and landing it in txtSalePrice. Because we know that tlSalePrice could be negative but we don't want to see it like that.

txtSalePrice isn't a single field, it's a text box in the sub form datasheet.

It's late but it's a work in progress. I'll read up on how to upload a db.


edit: tlSalePrice is the table field. txtSalePrice is the 'local' text box to manipulate it before we finally say:
tlSalePrice = fSign(bCredit,txtSalePrice) and save.


Last edited by GK in the UK; 11-15-2019 at 03:34 PM.
GK in the UK is offline   Reply With Quote
Old 11-15-2019, 04:01 PM   #23
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. Thanks for trying to explain, but since I am not an accountant, I will probably just wait until I see your sample db, which I bet will immediately shine the light in my dim mind. To me, what you're trying to do may be simplified by using the right Record Source for the subform. We might be able to make it so it doesn't matter whether the main form has loaded yet or not. In fact, simply opening the subform as a stand-alone form should also work, in my humble opinion. Good night.
__________________
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-16-2019, 04:15 AM   #24
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

I think a screenshot will be more use than some partly developed code.
I thought I had it cracked with the -1 multiply but it's not a complete solution.
The form will do what I want it to, it's all about presentation and shielding the user from negative values when they don't expect to see them, it's a confusing distraction. The form must do payments to suppliers, and receipts from customers, and the reversal treatment is different for each, according to the value of bCredit.

Main form has two subforms, each subform holds a datasheet.
Each datasheet has a different data source.

The form design screenshot is the sub form for the top half of the main form.

I want some way of negating the values coming from the table before they appear on the form, but I want to be able to control when this is done, because a supplier payment will want different treatment to a customer receipt.

I've been playing around with MyTestFunction but it doesn't work, I get ?Name in the Gross Value column. Which when you think about it, won't work, because MyTestFunction is in the main form, which is where I assess the value of bCredit.

So can I put (thinking around this) a user defined function in Control Source, that references the value of bCredit in the main form ?
I could cop out and just copy the form and code into a new form and adjust it for each scenario, frmPayment, and frmReceipt, but I'm hoping I won't need to do that.

Actually, having just typed that, maybe I should just have a different sub form for each scenario?
So if it's a payment, set the sub form to sfrmPaymentMatchDsheet and if it's a receipt, set the sub form to sfrmReceiptMatchDsheet. Which, DBguy, is what you've hinted at. Hmmmm...

https://photos.app.goo.gl/isWZXieEk7ZrBh499
GK in the UK is offline   Reply With Quote
Old 11-16-2019, 07:30 AM   #25
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. Yes, having an image to see what's going on helps a little, but you didn't annotate it to let us know what we're looking at. For example, I couldn't determine where to find bCredit to see its value. Is bCredit a field in a table? Also, I wasn't suggesting using different subforms. You should be able to use the same subform, only change its RecordSource to a more correct version. I think you're stuck in thinking you need a function. You may or you may not. I just need to know how to use bCredit for adjusting the displayed SalePrice to be able to tell you how to use it in the subform. Please remember you're talking to someone who has no clue what your database is about. So, when you describe it, pretend we don't know anything and use common terms. Either that or simply post a small copy of your db, so we can look around it at will.


PS. Just in case it helps, please go ahead and post the code for your function. If nothing else, it should tell us how you're using bCredit.
__________________
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.

Last edited by theDBguy; 11-16-2019 at 07:35 AM.
theDBguy is offline   Reply With Quote
Old 11-16-2019, 08:13 AM   #26
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

bCredit is a main form module boolean set after the two sub forms have loaded. There's a representation of it on the form, the box with the word 'credit' in it, that's just so I know what's going on during development

I was originally asking about the payment side, where tlSalePrice (a table field) would want negating. This is the bottom part of the form. But I realise the issue also applies to the top half datasheet as well. There's a lot going on in this form.

I did think a function embedded in the SQL select would do the job, that's how the thread started, but I'm now thinking, just have two sub form datasheets and select one when the main form has evaluated bCredit. (Actually a choice of two for the top and a choice of two for the bottom)

The sub forms would be the same, except in the signing of the table values.
So, if we're dealing with a supplier payment, one sub form would load and show unsigned values for purchase invoices outstanding.
For example the column Gross Value would have Control Source =[thNetValue_summed] + [thVatValue_summed] (these are table fields)
If we're dealing with a customer invoice, the other sub form loads and shows unsigned values for sales invoices outstanding
and the Gross Value control source might be (negated) : = -([thNetValue_summed] + [thVatValue_summed])

Unless there's a simple way to negate the table values I think this may be the solution.
Let's not lose sight, this is nothing more than a presentational thing. The form works and maintains book keeping integrity whichever way the table values are presented. I just want it to be user friendly

Incidentally, the screenshot shows a purchase invoice PINV created for this account, and a sales invoice SINV created for this account. That wouldn't happen in normal use, I would restrict type of invoice according to whether we're dealing with a customer or supplier. But you can see the effect, a SINV is presenting with a credit value and it's not helpful. If the user sees 」938.24-, well, it's just confusing. I want to see it presented as 」938.24. In use, a single customer or supplier account could have only one type of invoice raised against it.
GK in the UK is offline   Reply With Quote
Old 11-16-2019, 09:01 AM   #27
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. I think we can combine the two subforms idea into one. For example, when you said you are dealing with a "supplier payment" and a "customer invoice," how do we determine that? If we "know" the how, then we can simply do something like:


ControlSource: =IIf(SupplierPayment,1,-1)*([thNetValue_summed] + [thVatValue_summed])


Hope that makes sense...
__________________
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-16-2019, 09:41 AM   #28
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, sorry yes I'm not explaining myself, Supplier Invoice or Customer Invoice are effectively set by bCredit. It's the same thing really.
Likewise Supplier Payment or Customer Receipt are effectively set by bCredit.

The iif is perfect sense except I hadn't thought of using it like that.
So
ControlSource: =IIf(SupplierPayment,1,-1)*([thNetValue_summed] + [thVatValue_summed])

could just as easily be
ControlSource: =IIf(bCredit,1,-1)*([thNetValue_summed] + [thVatValue_summed])

So we're back to the same issue, bCredit isn't available until frmPayment loads. frmPayment has to do a lookup to see if bCredit will be true. frmPayment decides what the form caption will be, and what to describe the current document as (payment or receipt). frmPayment is designed to open and display correctly whichever document it is opened with. Why wasn't Access designed to load the main form with its code *before* the sub forms ?
It's the sub form display that's the issue. Even if I could set ControlSource in frmPayment, I'd have to do it for every field that wants negating.
GK in the UK is offline   Reply With Quote
Old 11-16-2019, 09:45 AM   #29
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, sorry yes I'm not explaining myself, Supplier Invoice or Customer Invoice are effectively set by bCredit. It's the same thing really.
Likewise Supplier Payment or Customer Receipt are effectively set by bCredit.

The iif is perfect sense except I hadn't thought of using it like that.
So
ControlSource: =IIf(SupplierPayment,1,-1)*([thNetValue_summed] + [thVatValue_summed])

could just as easily be
ControlSource: =IIf(bCredit,1,-1)*([thNetValue_summed] + [thVatValue_summed])

So we're back to the same issue, bCredit isn't available until frmPayment loads. frmPayment has to do a lookup to see if bCredit will be true. frmPayment decides what the form caption will be, and what to describe the current document as (payment or receipt). frmPayment is designed to open and display correctly whichever document it is opened with. Why wasn't Access designed to load the main form with its code *before* the sub forms ?
It's the sub form display that's the issue. Even if I could set ControlSource in frmPayment, I'd have to do it for every field that wants negating.
Hi. We are back to the same issue you mentioned about the subform loading before the main form because you got yourself stuck at thinking this way. I have been trying to ask you where is this bCredit coming from. Is it a field in the table? If so, you can add it to the Record Source of your query or use DLookup(). Is it a function? If so, show us the code, so we can tell you how to use it in your subform. The issue of the subform loading before the main form wouldn't come into play in the solution I am suggesting because I am trying to put the value of bCredit into the subform, so we don't have to worry about the main form not being loaded yet.
__________________
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-16-2019, 09:59 AM   #30
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

See post #1.


Code:
txtSalePrice = fSign(bCredit(tlSalePrice))
Where fSign returns the value negated if bCredit is true, it's a public function

Where does bCredit come from ?

thDocType is the form record, it's a table field for the record that the form is opened with

function fDocSign will return bCredit according to the record that frmPayment is opened with.

When we create a new payment record, we assign thDocType in the table. For a supplier payment, it's PPAY and it's a field in the table, ie the record

so function fDocSign returns bCredit as true, if PPAY is identified as needing reversal.
function fDocSign does a lookup because the bCredit flag isn't hard coded, it's a system setting for a PPAY document.

Code:
txtDocType = thDocType              ' what document is it ?
bCredit = fDocSign(txtDocType)      ' is it a 'reversal' type ?
The sequence is
frmPayment loads
frmPayment reads thDocType from the record it's opened with
frmPayment assigns bCredit according to thDocType but it does this by calling a function which does the system setting lookup

GK in the UK 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:34 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