Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-16-2019, 10:17 AM   #31
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,286
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

So, for clarity, bCredit is a boolean variable declared within a module?
Please post the code for fDocSign .

__________________

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-16-2019, 10:28 AM   #32
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 bCredit is boolean within frmPayment only


fDocSign is due for revision. tblDocTypes is being developed but here's the current lookup


Code:
Public Function fDocSign(strDocType As String) As Boolean
    
    Dim db As Database
    Dim rec As DAO.Recordset

  Set db = CurrentDb
    Set rec = CurrentDb.OpenRecordset("tblDocTypes", dbOpenDynaset)


    With rec
        .FindFirst "DocTypesID = '" & strDocType & "'"
        If Not .NoMatch Then
            fDocSign = (rec!dtCredit = True)
        Else
            fDocSign = False
        End If
        .Close
    End With


    Set rec = Nothing
    
End Function
And tblDocTypes where we flag bCredit and other stuff relating to thDocType


https://photos.app.goo.gl/53mEzwF7PgK5xA6HA
GK in the UK is offline   Reply With Quote
Old 11-16-2019, 12:16 PM   #33
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,656
Thanks: 58
Thanked 1,463 Times in 1,444 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: User Defined Function in SQL Select

Hi. Not in front of a computer right now. Will have to wait for a more coherent reply later. Sorry.

Sent from phone...

__________________
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-17-2019, 09:03 AM   #34
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,656
Thanks: 58
Thanked 1,463 Times in 1,444 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: User Defined Function in SQL Select

Hi. Good morning. There are so many moving parts to this thread, so I went all the way back to post #1 to try and put them all together. Barring a sample db, I think I have all the pieces I need except for the table name for the main form where the thDocType can be found. For example, here is what I think we have at play.

Main Form Table Source: ???
Primary Key Field of Main Form: TransHeaderID
DocType Field of Main Form: thDocType
Doctype lookup table to determine if Credit: tblDocTypes
tblDocTypes Field to determine if Credit: dtCredit
Subform Table Source: tblTransLines
Foreign Key Field in Subform: tlTransHeaderFK
Field in Subform to negate or not: tlSalePrice

So, if you can give us the name of the table where the Primary Key field TransHeaderID is located, I think I can try to give you a Record Source for your subform where tlSalePrice will be either negated or not.

Either that or post a sample db, so we can be more certain. Cheers!
__________________
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-17-2019, 12:11 PM   #35
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

You're right the parts are moving as I continue to develop it. The form is my most complex one with two datasheets, each one a view on to a different table and they must be kept in sync to work.

Appreciate the help, and you've mentioned posting a db. Do you mean the whole thing ?

It has about 50 forms with code, 25 tables, 20 code modules, and I haven't built a navigation/menu form yet. If I uploaded it without data, I'd have to send you an essay on how to add some records and make sense of it. Plus it's my one and only Access project and probably has scope for improvement everywhere ...

Anyway if I can refer to you to the screenshot of frmPayment

Main form (frmPayment) table: tblTransHeaders
Primary Key Field of tblTransHeaders: TransHeaderID
Bottom half sub form is called: sfrmPaymentLineDsheet
Source object is also called: sfrmPaymentLineDsheet
Record source is: tblTransLines
The table name: tblTransLines
Link master field: TransHeaderID
Master field table name: tblTransHeaders
Link child field: tlTransHeaderFK
DocType lookup table to determine if bCredit is true according to thDocType: tblDocTypes

thDocType is a field in tblTransHeaders but it's also copied into tblTransLines as tlDocType
GK in the UK is offline   Reply With Quote
Old 11-17-2019, 12:15 PM   #36
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,656
Thanks: 58
Thanked 1,463 Times in 1,444 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
You're right the parts are moving as I continue to develop it. The form is my most complex one with two datasheets, each one a view on to a different table and they must be kept in sync to work.

Appreciate the help, and you've mentioned posting a db. Do you mean the whole thing ?

It has about 50 forms with code, 25 tables, 20 code modules, and I haven't built a navigation/menu form yet. If I uploaded it without data, I'd have to send you an essay on how to add some records and make sense of it. Plus it's my one and only Access project and probably has scope for improvement everywhere ...

Anyway if I can refer to you to the screenshot of frmPayment

Main form (frmPayment) table: tblTransHeaders
Primary Key Field of tblTransHeaders: TransHeaderID
Bottom half sub form is called: sfrmPaymentLineDsheet
Source object is also called: sfrmPaymentLineDsheet
Record source is: tblTransLines
The table name: tblTransLines
Link master field: TransHeaderID
Master field table name: tblTransHeaders
Link child field: tlTransHeaderFK
DocType lookup table to determine if bCredit is true according to thDocType: tblDocTypes

thDocType is a field in tblTransHeaders but it's also copied into tblTransLines as tlDocType
Hi. Thanks for the info. I'll try to work something out.


When we ask for a copy of the db, we really only need to see a demo version of it. It should only contain enough objects to understand and duplicate the problem in question to help us come up with a working solution. Without seeing all the items in play with the problem, it's hard to provide a good solution. If data is sensitive, it can be replaced with test or dummy data, and we only need a small set of it.
__________________
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-17-2019, 12:29 PM   #37
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,656
Thanks: 58
Thanked 1,463 Times in 1,444 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
thDocType is a field in tblTransHeaders but it's also copied into tblTransLines as tlDocType
Hi. If I understand the above correctly, that makes it even easier, since we don't have to worry about the DocType from the main form.


So, I know you said you deleted the Record Source of the subform because you wanted to change how txtSalePrice is diplayed based on the record on the main form. You also have some code to assign the Record Source for the subform in the Load event of your main form.


At this time, just as a test, I will ask you to comment out the code in the Main Form's Load event for assigning the Record Source of the subform. And then, I will ask you to actually assign a Record Source to the subform using the following SQL statement:


Code:
SELECT T1.*,  T1.tlSalePrice * IIf(T2.dtCredit,-1,1) AS txtSalePrice FROM tblTransLines T1

INNER JOIN tblDocTypes T2

ON T1.tlDocType=T2.DocTypesID

If that doesn't work, seeing a sample db would really help speed up the process of finding a solution for you. Hope it helps though...

__________________
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-17-2019, 12:50 PM   #38
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 I put your code in the record source and it works (I think)
I get the same records that I expect to show in the bottom datasheet
I need to check some stuff out as I've done quite a bit today

Should the values show reversed ?

Does this mean it's going to tblDocTypes for *every* datasheet line that is displayed ? I have difficulty with that. It strikes me as odd to have to go to a table a dozen times or more when the same condition applies to every datasheet line.
GK in the UK is offline   Reply With Quote
Old 11-17-2019, 12:56 PM   #39
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,656
Thanks: 58
Thanked 1,463 Times in 1,444 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 I put your code in the record source and it works (I think)
I get the same records that I expect to show in the bottom datasheet
I need to check some stuff out as I've done quite a bit today

Should the values show reversed ?

Does this mean it's going to tblDocTypes for *every* datasheet line that is displayed ? I have difficulty with that. It strikes me as odd to have to go to a table a dozen times or more when the same condition applies to every datasheet line.
Hi.

Re: "Should the value show reversed?"

If it needs to be, try switching the expression I used to:

Code:
T1.tlSalePrice * IIf(T2.dtCredit,1,-1) AS txtSalePrice
And see if that helps.

As for going through all the records in a query, that's what a database's job is, so it shouldn't be a problem. But if you're seeing an issue with the speed (is it running slow now?), then we could try it another way.
__________________
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-17-2019, 01:47 PM   #40
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 I posted a payment of 」500 for a supplier where bCredit = true
I posted a receipt of 」500 for a customer where bCredit = false (the same form)
I've checked the table values and tlSalePrice is 」500 in each case, two records in the table

On sfrmPaymentLineDsheet, txtSalePrice was bound to tlSalePrice so it just displayed 」500 for each case of bCredit (no reversal)

So I deleted the control source for txtSalePrice thinking that was the problem, so txtSalePrice on sfrmPaymentLineDsheet now has no control source

txtSalePrice on the datasheet now displays nothing, it's an empty field
The other fields display so the binding must be there

just to be clear: I dropped your code into the Record Source of the form: sfrmPaymentLineDsheet

I can't see any difference between the expression in your first example and the second
GK in the UK is offline   Reply With Quote
Old 11-17-2019, 01:52 PM   #41
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,656
Thanks: 58
Thanked 1,463 Times in 1,444 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: User Defined Function in SQL Select

Hi. Not in front of a computer again but try binding the Textbox to txtSalePrice.
__________________
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
The Following User Says Thank You to theDBguy For This Useful Post:
GK in the UK (11-17-2019)
Old 11-17-2019, 02:04 PM   #42
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 ! That did it.


I can sort of see what's going on with the SQL select. It's beyond my current skill level, I do have SQL selects and updates but I can see it can do some very powerful stuff that I should learn.


Thanks for spending the time on this, really pleased with the result
GK in the UK is offline   Reply With Quote
Old 11-17-2019, 02:54 PM   #43
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,656
Thanks: 58
Thanked 1,463 Times in 1,444 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 ! That did it.

I can sort of see what's going on with the SQL select. It's beyond my current skill level, I do have SQL selects and updates but I can see it can do some very powerful stuff that I should learn.

Thanks for spending the time on this, really pleased with the result
Hi. Glad to hear you got it to work and sorry it took so long. It would probably have gone faster if we only had a sample db to look at to make understanding the situation sooner. Good luck with your project.

__________________
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 04:39 AM.


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