Module1........

Option Compare Database

Dim var1 As Date
Dim var2 As Date

Public Function MyVar1() As Date
   MyVar1 = var1
End Function

Public Function MyVar2() As Date
   MyVar2 = var2
End Function

Button on Form (that runs query)...........

   var1 = CDate(Me.fldPeriodFrom)
   var2 = CDate(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()));

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