Reference a function result in a query 2

[RealKiwi]

I'm trying to reference the result of a function in a query.

The function's job is to parse a string into a certain format. I have set up a public variable for the function output in the form's VBA code, strResult. So to generate strResult my code is:

strResult = fnParseString(Me.textCompanyName)

How would I refer back to the form public var in the query? Im assuming once I create the value of strResult, its value is persistent until the form is closed.

Here's the ones I tried and failed on:

Forms!formNameHere.strResult
Forms!formNameHere.strResult.value
Forms!formNameHere.Form.strResult
Forms!formNameHere.Form.strResult.value

I know I can reference control objects in queries... how about variables??

thanks, as always
RK

 

[KenReay]

Hi

Why not just put it in the query as the function, so in the query builder:

X:fnParseString(FORMS!MyFormName!textCompanyName)

in an SQl string

SELECT ... fnParseString(FORMS!MyFormName!textCompanyName) As X FROM ...

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RealKiwi]

Thanks Ken

I've given that suggestion a go but the query builder doesn't like the reference to the function alias in the WHERE statement:

SELECT co_id, co_name, fnParseString([Forms]![fmAddCustomer]![textCompanyName]) AS X
FROM tblCompany
WHERE co_name LIKE "*" & "X" & "*"

It always wants to surround the alias variable X in quotes - I know this is wrong, so how do I force a reference to the alias variable?. Maybe my SQL is just too rusty but I can't see whats wrong with this.

cheers

 

[scriverb]

You cannot access a Global variable in an ACCESS query. You can however retrieve the value of the function as KenReay says by a direct reference to the Function call in the query. The problem with your code is that in the WHERE statement you ahve tried to reference the Alias. You must reference the Function again here also. Since the returned value is a string then the comparison must be made with quotes around the function call value. Thus the incoroporation of singe-quotes within the double-quote string:

SELECT co_id, co_name, fnParseString([Forms]![fmAddCustomer]![textCompanyName]) AS X
FROM tblCompany
WHERE co_name LIKE "[b][red]'[/red][/b]*" & fnParseString([Forms]![fmAddCustomer]![textCompanyName]) & "*[b][red]'[/red][/b]";

Post back with the results of this change.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[RealKiwi]

Thanks Bob & Ken for your feedback.

It was the SQL alias reference in the WHERE statement that was incorrect. My SQL is *indeed* very rusty

cheers
RK

 

[RealKiwi]

Oops I spoke too soon. Seems Access SQL doesnt like
that version of the WHERE statement. I get a syntax error in the Query Builder.

 

[scriverb]

RealKiwi: Post your SQL that is acting up and let us see exactly what you are using.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[RealKiwi]

This SQL (without quotes)works in the Query Builder window, but I get a "too few parameters: expected 1" error when driven from the form.

When I added single quotes, the Query Builder doesnt return a result, but has the same error in the form.


SELECT tblCOMPANY.co_ID, tblCOMPANY.co_NAME, fnSearchStr([Forms]![fmAddCustomer]![txtCompName].value) AS X
FROM tblCOMPANY
WHERE (((tblCOMPANY.co_NAME) Like "*" & fnSearchStr([Forms]![fmAddCustomer]![txtCompName].[value]) & "*" Or (tblCOMPANY.co_NAME) Like fnSearchStr([Forms]![fmAddCustomer]![txtCompName].[value]) & "*"));

 

[scriverb]

Change the SQL to this:

SELECT tblCOMPANY.co_ID, tblCOMPANY.co_NAME, fnSearchStr([Forms]![fmAddCustomer]![txtCompName].value) AS X
FROM tblCOMPANY
WHERE (((tblCOMPANY.co_NAME) Like "'*" & fnSearchStr([Forms]![fmAddCustomer]![txtCompName].[value]) & "*'") Or ((tblCOMPANY.co_NAME) Like "'" & fnSearchStr([Forms]![fmAddCustomer]![txtCompName].[value]) & "*'"));

Post back with the reesults.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[RealKiwi]

Unfortunately single quotes dont work. The version with single quotes returned an empty set when I knew the answer was at least one record.

Before heading to the form I ran the query in Query Builder with this syntax - it works:

SELECT tblCOMPANY.co_ID, tblCOMPANY.co_NAME, fnSearchStr([Forms]![fmAddCustomer]![txtCompName]) AS X
FROM tblCOMPANY
WHERE (((tblCOMPANY.co_NAME) Like "*" & fnSearchStr([Forms]![fmAddCustomer]![txtCompName]) & "*") Or ((tblCOMPANY.co_NAME) Like fnSearchStr([Forms]![fmAddCustomer]![txtCompName].[value]) & "*"));

(.value was redundant but I had included it just to be on the safe side.)

When run in the form, I get the "Error 3061: Too few parameters" error. in the form.

Ive got no idea what that means since the code to open the query set is so simple:

Set rs = db.Openrecordset("SELECT * from myQueryName;")

 

[scriverb]

It may not be the SQL at all but rather the function call. I created a query using the exact SQL with table and fields modified and it works correctly without the call to the function and the where statement works just fine as coded.

Remove the WHERE clause and see what is being returned from your function. I believe your Function call may be giving you the problem. Just make the call for the select portion and view all the results. If you still have the error then we know that it is coming from your function. Make sure that your form is opened and data entered in the control before you run the query. Sounds simple but I have seen this as the problem in testing. You are making a reference to a control and the SQL is referencing a control. If not opened it can't find it. Also, make sure that there is an entry in the control.

Post back with your findings.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[RealKiwi]

All appears to be well at the function end. I wrote a query just to call the function and that worked returning the expected result onscreen.

I used the original query without the WHERE clause and that worked.

I called both queries from the form and got the same "Too few parameters" error.

It would appear that you cannot open a recordset from within a form, if the recordset calls a query containing a function calling back to the form for a value to evaluate in the query's function?!? Make sense...???

hmmm I feel further from a solution than when I started. I think I'll try returning the function call result back to a form variable, then create a SQL string with the WHERE criteria and run it from the form.

 

[scriverb]

If you want to, package up a database with a few records in a table, your form, your query, and your module with the function. Send it to me at my email address in my profile. I will take a look at it for you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[RealKiwi]

Thanks Bob, but Ive wasted too much time on this one already. Pressure from the client has forced me to move on to other more important things. I'll just implement something less elegant than what I was aiming for.

cheers
RK

"The customer is always right, when they're paying for it.