Re: Form variable in query
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Wed, 19 Jul 2006 07:27:38 -0400
Can only guess that the problem is somewhere in the code?
Are you assigning the values to Var1 and Var2 in the code, before you
execute the query? Can you post the code for the button event that calls
the query.
"Tezza" <tezza@xxxxxxxx> wrote in message
news:%23sxxG$pqGHA.4492@xxxxxxxxxxxxxxxxxxxxxxx
Ok, so the differences here are module variables assigned as Date (changed
from Long) and CDate on the fields. Breakpoints on module now shows
content of varX and MyVarX = 00:00:00. Breakpoint on form code shows var1
and var2 contained dates (01/01/2005 and 31/03/2006) at runtime.
Thanks for your help so far guys, i'm sure it's something pretty simple
i've missed but i just can't figure it.
Anything further?
ty
"John Spencer" <spencer@xxxxxxxxx> wrote in message
news:O65AkqoqGHA.4912@xxxxxxxxxxxxxxxxxxxxxxx
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()));
"Tezza" <tezza@xxxxxxxx> wrote in message
news:%2301AdjoqGHA.928@xxxxxxxxxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- Re: Form variable in query
- From: Tezza
- Re: Form variable in query
- References:
- Re: Form variable in query
- From: Tezza
- Re: Form variable in query
- From: John Spencer
- Re: Form variable in query
- From: Tezza
- Re: Form variable in query
- Prev by Date: Re: Query based on values in fields
- Next by Date: Re: Linked text and queries
- Previous by thread: Re: Form variable in query
- Next by thread: Re: Form variable in query
- Index(es):
Relevant Pages
|
