Function in Query (1 Viewer)

AJJJR

Registered User.
Local time
Today, 02:46
Joined
Mar 19, 2018
Messages
56
Hi

I taught myself access a few years ago so I'm not only rusty but don't really understand what I'm doing. I mostly blunder along until I figure out how to do something. Also, I'm very poor at getting a question across, so I'm going to break this one up and start really simple.

I am trying to use a function as criteria in a query. The function has a parameter that is a variable and the query is the recordsource for a form

I declared the variable in a standard module, at the top as:

Public strTitle as String

and in the same module defined the function, as:

Function Title(strTitle as String) as String
Title = strTitle
End Function

Then, as the query criteria I am trying to put Title(strtitle) and Access insists on putting quotes around strTitle and it returns nothing. I am assigning a value to strTitle in VBA and then opening the form with DoCmd.OpenForm

If I define the function as

Function Title()
Title = strtitle
End Function

It works, but I don't remember it working like that and don't want to run into trouble further down the line when I plan on using that function as a Form's Title.

Hope that makes sense and thanks in advance for any help or advice.

A/R
 

vba_php

Forum Troll
Local time
Today, 04:46
Joined
Oct 6, 2019
Messages
2,884
here's an example of a way to accomplish what you want to do, using the example function and sql you provided in your question
Code:
'VBA called function
Function Title(strTitle As String) As String
Title = strTitle
End Function
query statement:
Code:
SELECT table.field1, table.field2, etc, etc....
FROM table
WHERE [criteria field here] = Title("criteria string here");
 

AJJJR

Registered User.
Local time
Today, 02:46
Joined
Mar 19, 2018
Messages
56
Hi and thanks for the reply. Where you have "criteria string here" I want to put the variable strTitle and that doesn't work because access automatically surrounds the variable name (strTitle) with quotes, and I get this:

SELECT MenuItems.title, MenuItems.InMenu
FROM MenuItems
WHERE (((MenuItems.InMenu)=title("strTitle")));

Which gives the literal "strTitle" as the criteria. I want to be able to change the criteria of the query and that is why I am trying to use a variable
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:46
Joined
May 21, 2018
Messages
8,463
As written it assumes you are passing in a string argument to the function
Function Title(strTitle As String) As String
Title = strTitle
End Function

However you say you are setting the public variable in code and not passing in an argument. So you are not passing an argument.
Code:
public Sub strTitle as string
Function Title() As String
  Title = strTitle
End Function
 

AJJJR

Registered User.
Local time
Today, 02:46
Joined
Mar 19, 2018
Messages
56
O.K. I get it. Like I said I'm self taught so I don't always get subtle stuff like that. Thanks a lot for the reply, really appreciated.
 

Users who are viewing this thread

Top Bottom