Date variable in query

[jctaylor]

I have a form that has text boxes for beginning date and ending date. I then set these values to public variables and then wrap these variables in a function for each one. In my query I limit the data between the two dates, but when I try to use the values from the function, it returns no records. How do I get the query to use the function value to limit the records? If I just set the values to two different dates and surround them with #01/01/17# that works fine but not if I try to use the value from the function. Thanks for any suggestions.

[ranman256]

select * from table where [dateFld] between forms!myForm!txtStartDate and orms!myForm!txtEndDate

[jctaylor]

I tried that, but I have the form picking the dates and other criteria on one form and then I want to use those values in running several different reports. I read where you can use a function to get a public variable and use that is a query. I am trying to get dates and maybe it has something to do with how you show them(#01/01/2015#)- variable begdate. Do you use # around the variable?

[aytee111]

You can use a function in a query by entering the function name, such as DateField: MyFunction()

This is not normal usage, usually there are parameters being passed. Post the code for your function.

[aytee111]

Read you post again - this is criteria.

One way would be to return a true or false value from the function;
Field: MyFunction(begdate,enddate)
function checks if dates are in the range and sets true or false
Criteria:=True

Note however that this is a very slow process. With more than 100 records or so the query will be very slow. The preferred method would be to have the dates on a form, even if they are hidden fields (or the form itself is hidden). Or even on a temporary table. Both will be better than leaving them as variables in the code.

[jctaylor]

Ok, Thanks. I will try that.

[Micron]

Each report has to be based on something, although in this case, a query or sql statement might be better than a set of table records that are filtered. So if each report has its own query (or if preferred, table), open each report from the form and filter each report according to the date range.
Or pass each sql statement, using the dates as part of the WHERE clause, as the data source for each report as you open it.

What I'm not getting is, if the date variables are truly global (i.e. you know the difference between a project level vs. form level variable) then I see no need for a function to get a global variable value. It would be like using a device to reach for the salt when it is right under your nose. I have to wonder if the problem is the scope of the "public" variable. You realize that declaring a variable in a form (for example) as Public does not make it visible to any other form, report or module?