Set static data for each record in query based on public variable

Question

I want to create a query that has a static value for a particular column based on a public variable

How can I do this

I tried a query with a function

Function GetEstNo() As String

GetEstNo = strEstNo

End Function

SELECT Table1.*, GetEstNo() AS ENo, GetTrade() AS TradeDesc, GetTradeCode() AS TradeCode

FROM Table1;

but this doesn't work  :-(

any ideas where I am going wrong

Thanks

Answer 1

Thanks.

It would appear that Access doesn't like the Table1.* when there are other "static" fields

I just used the reference to the form (which is what I tried initially before the function thing) to get the static variable and this worked when I selected every field individually in the query.  although this was a little querky

If I changed the Table1.* to every field listed in the query it didn't work.

If I started a new query and used the every field in the query approach it did work

Not sure what's going on there.

Answer 2

Here are a few things you could test:

Check to see whether your public variable is null.  If a null value is acceptable you can set the data type to variant.

Make sure you aren't having problems with your other functions (GetTrade() and GetTradeCode()).  Try eliminating them temporarily from your query.

Verify that Table1 actually contains data and none of the fields are named with the functions listed above.

Make sure your function is in a module.  Try testing it in the Immediate Window.