Pass a variable to a query but not with textbox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a form to gather information which is used to perform a query.

Up until now I have placed the information in textboxes, and then referred
to the textbox in the criteria section of the query. eg.

Between [Forms]![fdlgReportsByModelAndDate]![txtBeginningDate] And
Forms]![fdlgReportsByModelAndDate]![txtEndDate]

I don’t want to use a textbox to pass information to the query. I want to
pass data directly from a variable (varEndDate) to the query.

Is this possible?

Thanks,
Seth
 
Access / JET cannot get the value of a VBA variable directly. However, you
can write a VBA "wrapper" function to get the date and then use the function
in the Query.

The function (in a Standard Module) is something like:

Public Function fnGetRequiredDate() As Date
fnGetRequiredDate = varEndDate
End Function

The function need to have access to the varEndDate.

HTH
Van T. Dinh
MVP (Access)
 
I've had the same issues..

My work-around that I use in almost ALL my databases is a lookup table. In
this table I'd have a lookupType field (essentially a 'variable' name) and
then another field for the actual value..

you can then write functions that query the lookup table based on LookupType
(variable name), and use them to read and write values to the table.
 
Thanks Van,

How do I refer to the function in the query design view?

Cheers,
Seth

Van T. Dinh said:
Access / JET cannot get the value of a VBA variable directly. However, you
can write a VBA "wrapper" function to get the date and then use the function
in the Query.

The function (in a Standard Module) is something like:

Public Function fnGetRequiredDate() As Date
fnGetRequiredDate = varEndDate
End Function

The function need to have access to the varEndDate.

HTH
Van T. Dinh
MVP (Access)



Seth said:
I am using a form to gather information which is used to perform a query.

Up until now I have placed the information in textboxes, and then referred
to the textbox in the criteria section of the query. eg.

Between [Forms]![fdlgReportsByModelAndDate]![txtBeginningDate] And
Forms]![fdlgReportsByModelAndDate]![txtEndDate]

I don't want to use a textbox to pass information to the query. I want to
pass data directly from a variable (varEndDate) to the query.

Is this possible?

Thanks,
Seth
Click to expand...
Click to expand...
 
I’m still having trouble here as I don’t know too much about functions. I
suspect the criteria line in the query is incorrect.

In my code I have:

Public Function fnGetStartDate() As Date
fnGetStartDate = varStartDate
End Function

Public Function fnGetEndDate() As Date
fnGetEndDate = varEndDate
End Function

DoCmd.OpenQuery "qryReportsByModelAndDate"
DoCmd.Close acForm, "fdlgReportsByModelAndDate"

In the query I have:

Criteria: Between fnGetStartDate() And fnGetEndDate()

Thanks for your help.
Seth
 
1. Are the functions in a Standatd Module? Standard Modules are Modules
that you can see in the Module tab of the Database Containers window.

2. Are varStartDate and varEndDate declared as Public? They need to be
Public so that oher Moldules can "read" them.

Are they in Standard Module or Class Module? Class Modules are those behind
Forms / Reports and those Modules that you declared as Class Modules.

Also, please describe the results. "having trouble" doesn't give any hint
about what you did or what was wrong.
 
I have now created a standard module. My variable declerations and functions
are within the standard module.

It is now working correctly.

Thank you very much for your help. I have learnt a lot.

Best regards,
Seth
 
Back
Top