Using value entered as query criteria

[morganba]

I have an Access 2007 form CLUB INFO which uses a query function to populate fields using data from a table called [mbrlist] with the count of members who have spent less than $400.

The query function I use to populate the CLUBINFO field clubinfo.[CasualPatron] looks like: =GetQueryValue("SELECT count(*) from [mmbrlst] where [FBSpnd]<400")

I would like to be able to substitute ”<400” with “<[lowspend]” and be able to enter a value into clubinfo.[lowspend] so that the criteria for the query could be based on this value.

However, [Mmblist] and the control source table for the CLUB INFO form [clubinfo] do not share any common fields so I have nothing with which to join the two tables.

I want to enter the amount into ClubInfo.[lowspend] once and change this value from time to time.


I do not want to use a parameter query which would call for input each time ClubInfo opened.

Thanks

[NTC]

If I understand your question (and I'm not sure I do) - you seek to have the criteria of query to be semi permanent - and that a user can change it from a screen.

One approach is to make a table just for this value. You can have it appear in a form independently as its own little subform. The criteria of the query can be based on this value.

[morganba]

Hi NTC - thanks for the response:

The query function that I am using to populate the CasualPatron field on the form CLUBINFO is: GetQueryValue("SELECT count(*) from [mmbrlst] where [FBSpnd]<400")

This function returns a value from the bold query between the two double quotes and places this value in the [CasualPatron] field in clubinfo (I enter this function as the ControlSource for the Field.

The table Clubinfo is the Record source for the form CLUBINFO. However, the table clubinfo and the table mmbrlst do not share a common field to join the two tables. Therefore, if I replace <400 with <[lowspend] in the query function above, I get an error. I need a way to get the query to use the value in the field clubinfo.[lowspend].

I hope to be able to change the value in [lowspend] to in order to increase or decrease the count generated by the query.

[morganba]

I realised that I do not need a common field in the function. All I had to do was list the table as an additional record source. This made the fields from both tables available to the function. Since I am merely doing a count of the records in one table and only have one record in the second table the function returns what I require. It's easy when you know how!