Hi,
Dim the variable and function as Date instead of as Long
There is 3 main scopes, in VBA: global scope, class (and form, and
report) scope and procedure scope.
As example, under a form (class):
Option Explicit
Dim x As Long
Public Sub sub1()
Dim y As Long
x=33 ' <- the form scope
y=11
z=22 ' only if z is define 'globally'
Debug.Print y
End Sub
Public Sub sub2()
Dim x As Long
x=44 ' <- the procedure scope
End Sub
In Sub2, the x is the x defined in the procedure scope that is visible,
the x defined at the form level (and an x defined at a global level) is
not directly visible. Inside Sub1, the x we reached is the x defined at
the class level; the y we reach is the one defined at the procedure level
and for z, that would be the z defined at the global level.
So, in your case, be sure var1 and var2 are at the right level, or, if you
prefer, defined the one in the declaration section of the module as:
Dim globalVar1 As Date
Dim globalVar2 As Date
then, define, in the same module:
Public Function MyVar1() As Date
MyVar1 = globalVar1
End Function
in the form, use:
globalVar1 = Me.fldPeriodFrom
and DO NOT DIM a variable with the name globalVar1 in the form
declaration, neither in the subroutine having that line of code!
You can, for debug purposes, modify your query like:
SELECT fldRef, fldSurname, fldDateOn, MyVar1(), MyVar2()
FROM tblProject
WHERE (((fldDateOn) Between MyVar1() And MyVar2()));
and thus, see if the query get the right values, or not.
Hoping it may help,
Vanderghast, Access MVP
Tezza said:
I am using this in a query...
Between [forms]![frmReports].[fldPeriodFrom] And
[forms]![frmReports].[fldPeriodTo]
but i really want to assign the two fields to variables and use the
variables in the query. What's the correct format of the query criteria
to achieve this?
This is the variable coding on the button that runs the report (that is
fed by the query in question)
Dim strDateFrom As Date
Dim strDateTo As Date
strDateFrom = Me.fldPeriodFrom
strDateTo = Me.fldPeriodTo
I have just seen the post 'Global Var in queries' but i'm not yet
experienced in Function coding. So I hope that's not the only way to do
it.
TIA
ty
Hi,
You cannot refer to VBA variables inside a query. But you can use a
public function (in a standard module, not a class, not a form) that
will return your variable, from within your query.
-----start of a standard module---
Dim var1 As long
Dim var2 As long
Public Function MyVar1() AS Long
MyVar1=var1
End Function
Public Function MyVar2() AS Long
MyVar2=var2
End Function
----------end--------------------------------
In your SQL statement, use MyVar1() and MyVar2().
Hoping it may help,
Vanderghast, Access MVP
Well... I get no errors now but i get no records returned either. This is
what i have done...
Module1........
Option Compare Database
Dim var1 As Long
Dim var2 As Long
Public Function MyVar1() As Long
MyVar1 = var1
End Function
Public Function MyVar2() As Long
MyVar2 = var2
End Function
also tried assigning variables as Date
Button on Form (that runs query)...........
var1 = Me.fldPeriodFrom
var2 = Me.fldPeriodTo
Query criteria on date field.............
Between MyVar1() And MyVar2()
SQL View...
SELECT fldRef, fldSurname, fldDateOn
FROM tblProject
WHERE (((fldDateOn) Between MyVar1() And MyVar2()));
breakpoints show that var1 and var2 have the appropriate dates assigned,
but MyVar1 and MyVar2 = 0
What have i missed?
TIA
ty
