Data Changing Issue

[Karen]

Hi

I am running some queries using a form I created and for some strange reason
some people are falling off and coming back to the report when I run the
queries and then re-run them immidiately after. I am not changing any of my
parameters and the data is not changing in the tables I am linked to. Can
you help?

I am using Access 2003. I have several different queries running as make
table queries from Foxpro database linked tables. Within these queries I
have one query that has a parameter set for "Married Date" What this does is
prompt the user to enter a start date and then I have an expression that
calculates the number of months from that "start date" to an actual
"Marriage Date" field in one of the tables I am linking from foxpro. The
expression looks like this:

DateDiff("m",[smf]![smfwedding],[WEDDINGDATE])

** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table from
foxpro**

Would this be the reason data is changing like this?
Help.
Thanks

 

[Michel Walsh]

Depends where this statement is used.

If inside a query, the table sm should be part of the FROM clause (or,
graphically, in the upper half of the query designer) and the preferred
syntax is a dot, not a bang.

DateDiff("m", smf.smfwedding, WEDDINGDATE)



If in a form or in a report, then the table value should be 'bound' to a
control (can be invisible, if you prefer so), and the preferred syntax would
be like:


DateDiff("m", Me.ControlName , WEDDINGDATE)



Note that even if your table name is sm, there is no VBA-object that is
open, by default, with that name. So, in the immediate debug window, as
example,


? table1!myid


is very unlikely to print some value of myid present in table1... unless
table1 is an existing VBA object, at that moment.





Hoping it may help,
Vanderghast, Access MVP

 

[Karen]

Hi Michel - Thanks for the info. To clarify (I am not an expert in Access,
(yet))
but I understood what you said below except for

"Note that even if your table name is sm, there is no VBA-object that is

open, by default, with that name. So, in the immediate debug window, as
example,
? table1!myid
is very unlikely to print some value of myid present in table1... unless
table1 is an existing VBA object, at that moment."

Can you please clarify this?
I am verifying the data now with your suggestion on changing the expression
within the query. Hoping the data is correct. Thanks

Depends where this statement is used.

If inside a query, the table sm should be part of the FROM clause (or,
graphically, in the upper half of the query designer) and the preferred
syntax is a dot, not a bang.

DateDiff("m", smf.smfwedding, WEDDINGDATE)



If in a form or in a report, then the table value should be 'bound' to a
control (can be invisible, if you prefer so), and the preferred syntax would
be like:


DateDiff("m", Me.ControlName , WEDDINGDATE)



Note that even if your table name is sm, there is no VBA-object that is
open, by default, with that name. So, in the immediate debug window, as
example,


? table1!myid


is very unlikely to print some value of myid present in table1... unless
table1 is an existing VBA object, at that moment.





Hoping it may help,
Vanderghast, Access MVP

Hi

I am running some queries using a form I created and for some strange
reason
some people are falling off and coming back to the report when I run the
queries and then re-run them immidiately after. I am not changing any of
my
parameters and the data is not changing in the tables I am linked to. Can
you help?

I am using Access 2003. I have several different queries running as make
table queries from Foxpro database linked tables. Within these queries I
have one query that has a parameter set for "Married Date" What this does
is
prompt the user to enter a start date and then I have an expression that
calculates the number of months from that "start date" to an actual
"Marriage Date" field in one of the tables I am linking from foxpro. The
expression looks like this:

DateDiff("m",[smf]![smfwedding],[WEDDINGDATE])

** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table
from
foxpro**

Would this be the reason data is changing like this?
Help.
Thanks

 

[Michel Walsh]

I mean that even if the table exists on the hard disk, you cannot reach it
simply by using its name, *if* you are in VBA. In fact, in VBA, you can read
the data of your table from a recordset. Note that if you are using a Form
or a Report, you may give the name of the table as record-source (see this
property), without having to explicitly open it in VBA. Access will just
open a recordset for you, in that case. You can reach that recordset, in VBA
code behind the form, with the expression: Me.Recordset.

If you are in a query, rather than being in VBA, the table name must be in
the FROM clause.

Otherwise, the table won't be OPEN to you, even if it exists on the hard
disk.


(Sure, you can also use Access end user interface to reach your data, such
as seeing a table in data-view, as example, but your question leads me to
think you were using either VBA code, either a query.)


Vanderghast, Access MVP

Hi Michel - Thanks for the info. To clarify (I am not an expert in
Access,
(yet))
but I understood what you said below except for

"Note that even if your table name is sm, there is no VBA-object that is

open, by default, with that name. So, in the immediate debug window, as
example,
? table1!myid
is very unlikely to print some value of myid present in table1... unless
table1 is an existing VBA object, at that moment."

Can you please clarify this?
I am verifying the data now with your suggestion on changing the
expression
within the query. Hoping the data is correct. Thanks

Depends where this statement is used.

If inside a query, the table sm should be part of the FROM clause (or,
graphically, in the upper half of the query designer) and the preferred
syntax is a dot, not a bang.

DateDiff("m", smf.smfwedding, WEDDINGDATE)



If in a form or in a report, then the table value should be 'bound' to a
control (can be invisible, if you prefer so), and the preferred syntax
would
be like:


DateDiff("m", Me.ControlName , WEDDINGDATE)



Note that even if your table name is sm, there is no VBA-object that is
open, by default, with that name. So, in the immediate debug window, as
example,


? table1!myid


is very unlikely to print some value of myid present in table1... unless
table1 is an existing VBA object, at that moment.





Hoping it may help,
Vanderghast, Access MVP

Hi

I am running some queries using a form I created and for some strange
reason
some people are falling off and coming back to the report when I run
the
queries and then re-run them immidiately after. I am not changing any
of
my
parameters and the data is not changing in the tables I am linked to.
Can
you help?

I am using Access 2003. I have several different queries running as
make
table queries from Foxpro database linked tables. Within these queries
I
have one query that has a parameter set for "Married Date" What this
does
is
prompt the user to enter a start date and then I have an expression
that
calculates the number of months from that "start date" to an actual
"Marriage Date" field in one of the tables I am linking from foxpro.
The
expression looks like this:

DateDiff("m",[smf]![smfwedding],[WEDDINGDATE])

** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table
from
foxpro**

Would this be the reason data is changing like this?
Help.
Thanks

 

[Karen]

Hi

OK, what you suggested did not work. I updated my expression to what you
indicated and re-ran all the queries (using form) and when I saw the results
on my report the numbers kept changing again.

Could it be something else?
Thanks

I mean that even if the table exists on the hard disk, you cannot reach it
simply by using its name, *if* you are in VBA. In fact, in VBA, you can read
the data of your table from a recordset. Note that if you are using a Form
or a Report, you may give the name of the table as record-source (see this
property), without having to explicitly open it in VBA. Access will just
open a recordset for you, in that case. You can reach that recordset, in VBA
code behind the form, with the expression: Me.Recordset.

If you are in a query, rather than being in VBA, the table name must be in
the FROM clause.

Otherwise, the table won't be OPEN to you, even if it exists on the hard
disk.


(Sure, you can also use Access end user interface to reach your data, such
as seeing a table in data-view, as example, but your question leads me to
think you were using either VBA code, either a query.)


Vanderghast, Access MVP

Hi Michel - Thanks for the info. To clarify (I am not an expert in
Access,
(yet))
but I understood what you said below except for

"Note that even if your table name is sm, there is no VBA-object that is

open, by default, with that name. So, in the immediate debug window, as
example,
? table1!myid
is very unlikely to print some value of myid present in table1... unless
table1 is an existing VBA object, at that moment."

Can you please clarify this?
I am verifying the data now with your suggestion on changing the
expression
within the query. Hoping the data is correct. Thanks

Depends where this statement is used.

If inside a query, the table sm should be part of the FROM clause (or,
graphically, in the upper half of the query designer) and the preferred
syntax is a dot, not a bang.

DateDiff("m", smf.smfwedding, WEDDINGDATE)



If in a form or in a report, then the table value should be 'bound' to a
control (can be invisible, if you prefer so), and the preferred syntax
would
be like:


DateDiff("m", Me.ControlName , WEDDINGDATE)



Note that even if your table name is sm, there is no VBA-object that is
open, by default, with that name. So, in the immediate debug window, as
example,


? table1!myid


is very unlikely to print some value of myid present in table1... unless
table1 is an existing VBA object, at that moment.





Hoping it may help,
Vanderghast, Access MVP


Hi

I am running some queries using a form I created and for some strange
reason
some people are falling off and coming back to the report when I run
the
queries and then re-run them immidiately after. I am not changing any
of
my
parameters and the data is not changing in the tables I am linked to.
Can
you help?

I am using Access 2003. I have several different queries running as
make
table queries from Foxpro database linked tables. Within these queries
I
have one query that has a parameter set for "Married Date" What this
does
is
prompt the user to enter a start date and then I have an expression
that
calculates the number of months from that "start date" to an actual
"Marriage Date" field in one of the tables I am linking from foxpro.
The
expression looks like this:

DateDiff("m",[smf]![smfwedding],[WEDDINGDATE])

** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table
from
foxpro**

Would this be the reason data is changing like this?
Help.
Thanks

 

[Karen]

Maybe I should simplify my problem by asking this question.

I have a query with a married date of students. I want to a list of people
that have been married =>24 months (2 years). I do not want the 2 years to
be calculated from today's date (Now()). I want to enter a date that it
should calculate 2 years from.

How would you recommend I do this ?
Thanks

I mean that even if the table exists on the hard disk, you cannot reach it
simply by using its name, *if* you are in VBA. In fact, in VBA, you can read
the data of your table from a recordset. Note that if you are using a Form
or a Report, you may give the name of the table as record-source (see this
property), without having to explicitly open it in VBA. Access will just
open a recordset for you, in that case. You can reach that recordset, in VBA
code behind the form, with the expression: Me.Recordset.

If you are in a query, rather than being in VBA, the table name must be in
the FROM clause.

Otherwise, the table won't be OPEN to you, even if it exists on the hard
disk.


(Sure, you can also use Access end user interface to reach your data, such
as seeing a table in data-view, as example, but your question leads me to
think you were using either VBA code, either a query.)


Vanderghast, Access MVP

Hi Michel - Thanks for the info. To clarify (I am not an expert in
Access,
(yet))
but I understood what you said below except for

"Note that even if your table name is sm, there is no VBA-object that is

open, by default, with that name. So, in the immediate debug window, as
example,
? table1!myid
is very unlikely to print some value of myid present in table1... unless
table1 is an existing VBA object, at that moment."

Can you please clarify this?
I am verifying the data now with your suggestion on changing the
expression
within the query. Hoping the data is correct. Thanks

Depends where this statement is used.

If inside a query, the table sm should be part of the FROM clause (or,
graphically, in the upper half of the query designer) and the preferred
syntax is a dot, not a bang.

DateDiff("m", smf.smfwedding, WEDDINGDATE)



If in a form or in a report, then the table value should be 'bound' to a
control (can be invisible, if you prefer so), and the preferred syntax
would
be like:


DateDiff("m", Me.ControlName , WEDDINGDATE)



Note that even if your table name is sm, there is no VBA-object that is
open, by default, with that name. So, in the immediate debug window, as
example,


? table1!myid


is very unlikely to print some value of myid present in table1... unless
table1 is an existing VBA object, at that moment.





Hoping it may help,
Vanderghast, Access MVP


Hi

I am running some queries using a form I created and for some strange
reason
some people are falling off and coming back to the report when I run
the
queries and then re-run them immidiately after. I am not changing any
of
my
parameters and the data is not changing in the tables I am linked to.
Can
you help?

I am using Access 2003. I have several different queries running as
make
table queries from Foxpro database linked tables. Within these queries
I
have one query that has a parameter set for "Married Date" What this
does
is
prompt the user to enter a start date and then I have an expression
that
calculates the number of months from that "start date" to an actual
"Marriage Date" field in one of the tables I am linking from foxpro.
The
expression looks like this:

DateDiff("m",[smf]![smfwedding],[WEDDINGDATE])

** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table
from
foxpro**

Would this be the reason data is changing like this?
Help.
Thanks

 

[Michel Walsh]

In the query, type:


AgeInMonth: DateDiff("m", fieldName, FORMS!formName!TextBoxName) -
DatePart("d", FORMS!formName!TextBoxName) >= DatePart("d", fieldName)


where fieldName is the field name holding the marriage date
formName is the open form name with the control having the date you
supplied
textBoxName is the control name, in the formName, which hold the date
you have to supply


In the criteria line, add

= 24

Hoping it may help,
Vanderghast, Access MVP

Maybe I should simplify my problem by asking this question.

I have a query with a married date of students. I want to a list of people
that have been married =>24 months (2 years). I do not want the 2 years
to
be calculated from today's date (Now()). I want to enter a date that it
should calculate 2 years from.

How would you recommend I do this ?
Thanks

I mean that even if the table exists on the hard disk, you cannot reach
it
simply by using its name, *if* you are in VBA. In fact, in VBA, you can
read
the data of your table from a recordset. Note that if you are using a
Form
or a Report, you may give the name of the table as record-source (see
this
property), without having to explicitly open it in VBA. Access will just
open a recordset for you, in that case. You can reach that recordset, in
VBA
code behind the form, with the expression: Me.Recordset.

If you are in a query, rather than being in VBA, the table name must be
in
the FROM clause.

Otherwise, the table won't be OPEN to you, even if it exists on the hard
disk.


(Sure, you can also use Access end user interface to reach your data,
such
as seeing a table in data-view, as example, but your question leads me to
think you were using either VBA code, either a query.)


Vanderghast, Access MVP

Hi Michel - Thanks for the info. To clarify (I am not an expert in
Access,
(yet))
but I understood what you said below except for

"Note that even if your table name is sm, there is no VBA-object that
is
open, by default, with that name. So, in the immediate debug window,
as
example,
? table1!myid
is very unlikely to print some value of myid present in table1...
unless
table1 is an existing VBA object, at that moment."

Can you please clarify this?
I am verifying the data now with your suggestion on changing the
expression
within the query. Hoping the data is correct. Thanks



:

Depends where this statement is used.

If inside a query, the table sm should be part of the FROM clause (or,
graphically, in the upper half of the query designer) and the
preferred
syntax is a dot, not a bang.

DateDiff("m", smf.smfwedding, WEDDINGDATE)



If in a form or in a report, then the table value should be 'bound' to
a
control (can be invisible, if you prefer so), and the preferred syntax
would
be like:


DateDiff("m", Me.ControlName , WEDDINGDATE)



Note that even if your table name is sm, there is no VBA-object that
is
open, by default, with that name. So, in the immediate debug window,
as
example,


? table1!myid


is very unlikely to print some value of myid present in table1...
unless
table1 is an existing VBA object, at that moment.





Hoping it may help,
Vanderghast, Access MVP


Hi

I am running some queries using a form I created and for some
strange
reason
some people are falling off and coming back to the report when I run
the
queries and then re-run them immidiately after. I am not changing
any
of
my
parameters and the data is not changing in the tables I am linked
to.
Can
you help?

I am using Access 2003. I have several different queries running as
make
table queries from Foxpro database linked tables. Within these
queries
I
have one query that has a parameter set for "Married Date" What
this
does
is
prompt the user to enter a start date and then I have an expression
that
calculates the number of months from that "start date" to an actual
"Marriage Date" field in one of the tables I am linking from foxpro.
The
expression looks like this:

DateDiff("m",[smf]![smfwedding],[WEDDINGDATE])

** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from
table
from
foxpro**

Would this be the reason data is changing like this?
Help.
Thanks

 

[Karen]

Hi Michel -

Thanks for the quick response. OK, I tried what you indicated below. Here
it is:

DateDiff("m",[smf]![smfwedding],[FORMS]![FRONTVIEW]![TEXT92])-DatePart("d",[FORMS]![FRONTVIEW]![TEXT92])>=DatePart("d",[smf]![smfwedding])

Unfortunetly, it is not working. Now it is giving me 0 records.

Just so I explain what I did; (Making sure I understood correctly) I added
an unbound text box to the form. In the control source added expression
=[Startdate] which is TEXT92

In the query, on the field line, I added the above expression.
In the criteria line I added the >=24

This did not work.
Am I doing something else wrong?
Thanks for the patience and help.

In the query, type:


AgeInMonth: DateDiff("m", fieldName, FORMS!formName!TextBoxName) -
DatePart("d", FORMS!formName!TextBoxName) >= DatePart("d", fieldName)


where fieldName is the field name holding the marriage date
formName is the open form name with the control having the date you
supplied
textBoxName is the control name, in the formName, which hold the date
you have to supply


In the criteria line, add

= 24

Hoping it may help,
Vanderghast, Access MVP

Maybe I should simplify my problem by asking this question.

I have a query with a married date of students. I want to a list of people
that have been married =>24 months (2 years). I do not want the 2 years
to
be calculated from today's date (Now()). I want to enter a date that it
should calculate 2 years from.

How would you recommend I do this ?
Thanks

I mean that even if the table exists on the hard disk, you cannot reach
it
simply by using its name, *if* you are in VBA. In fact, in VBA, you can
read
the data of your table from a recordset. Note that if you are using a
Form
or a Report, you may give the name of the table as record-source (see
this
property), without having to explicitly open it in VBA. Access will just
open a recordset for you, in that case. You can reach that recordset, in
VBA
code behind the form, with the expression: Me.Recordset.

If you are in a query, rather than being in VBA, the table name must be
in
the FROM clause.

Otherwise, the table won't be OPEN to you, even if it exists on the hard
disk.


(Sure, you can also use Access end user interface to reach your data,
such
as seeing a table in data-view, as example, but your question leads me to
think you were using either VBA code, either a query.)


Vanderghast, Access MVP



Hi Michel - Thanks for the info. To clarify (I am not an expert in
Access,
(yet))
but I understood what you said below except for

"Note that even if your table name is sm, there is no VBA-object that
is
open, by default, with that name. So, in the immediate debug window,
as
example,
? table1!myid
is very unlikely to print some value of myid present in table1...
unless
table1 is an existing VBA object, at that moment."

Can you please clarify this?
I am verifying the data now with your suggestion on changing the
expression
within the query. Hoping the data is correct. Thanks



:

Depends where this statement is used.

If inside a query, the table sm should be part of the FROM clause (or,
graphically, in the upper half of the query designer) and the
preferred
syntax is a dot, not a bang.

DateDiff("m", smf.smfwedding, WEDDINGDATE)



If in a form or in a report, then the table value should be 'bound' to
a
control (can be invisible, if you prefer so), and the preferred syntax
would
be like:


DateDiff("m", Me.ControlName , WEDDINGDATE)



Note that even if your table name is sm, there is no VBA-object that
is
open, by default, with that name. So, in the immediate debug window,
as
example,


? table1!myid


is very unlikely to print some value of myid present in table1...
unless
table1 is an existing VBA object, at that moment.





Hoping it may help,
Vanderghast, Access MVP


Hi

I am running some queries using a form I created and for some
strange
reason
some people are falling off and coming back to the report when I run
the
queries and then re-run them immidiately after. I am not changing
any
of
my
parameters and the data is not changing in the tables I am linked
to.
Can
you help?

I am using Access 2003. I have several different queries running as
make
table queries from Foxpro database linked tables. Within these
queries
I
have one query that has a parameter set for "Married Date" What
this
does
is
prompt the user to enter a start date and then I have an expression
that
calculates the number of months from that "start date" to an actual
"Marriage Date" field in one of the tables I am linking from foxpro.
The
expression looks like this:

DateDiff("m",[smf]![smfwedding],[WEDDINGDATE])

** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from
table
from
foxpro**

Would this be the reason data is changing like this?
Help.
Thanks

 

[Michel Walsh]

Can you post the SQL statement appearing in the SQL view of the query?

Vanderghast, Access MVP

Hi Michel -

Thanks for the quick response. OK, I tried what you indicated below.
Here
it is:

DateDiff("m",[smf]![smfwedding],[FORMS]![FRONTVIEW]![TEXT92])-DatePart("d",[FORMS]![FRONTVIEW]![TEXT92])>=DatePart("d",[smf]![smfwedding])

Unfortunetly, it is not working. Now it is giving me 0 records.

Just so I explain what I did; (Making sure I understood correctly) I added
an unbound text box to the form. In the control source added expression
=[Startdate] which is TEXT92

In the query, on the field line, I added the above expression.
In the criteria line I added the >=24

This did not work.
Am I doing something else wrong?
Thanks for the patience and help.

In the query, type:


AgeInMonth: DateDiff("m", fieldName, FORMS!formName!TextBoxName) -
DatePart("d", FORMS!formName!TextBoxName) >= DatePart("d", fieldName)


where fieldName is the field name holding the marriage date
formName is the open form name with the control having the date you
supplied
textBoxName is the control name, in the formName, which hold the
date
you have to supply


In the criteria line, add

= 24

Hoping it may help,
Vanderghast, Access MVP

Maybe I should simplify my problem by asking this question.

I have a query with a married date of students. I want to a list of
people
that have been married =>24 months (2 years). I do not want the 2
years
to
be calculated from today's date (Now()). I want to enter a date that it
should calculate 2 years from.

How would you recommend I do this ?
Thanks

:

I mean that even if the table exists on the hard disk, you cannot
reach
it
simply by using its name, *if* you are in VBA. In fact, in VBA, you
can
read
the data of your table from a recordset. Note that if you are using a
Form
or a Report, you may give the name of the table as record-source (see
this
property), without having to explicitly open it in VBA. Access will
just
open a recordset for you, in that case. You can reach that recordset,
in
VBA
code behind the form, with the expression: Me.Recordset.

If you are in a query, rather than being in VBA, the table name must
be
in
the FROM clause.

Otherwise, the table won't be OPEN to you, even if it exists on the
hard
disk.


(Sure, you can also use Access end user interface to reach your data,
such
as seeing a table in data-view, as example, but your question leads me
to
think you were using either VBA code, either a query.)


Vanderghast, Access MVP



Hi Michel - Thanks for the info. To clarify (I am not an expert in
Access,
(yet))
but I understood what you said below except for

"Note that even if your table name is sm, there is no VBA-object
that
is
open, by default, with that name. So, in the immediate debug
window,
as
example,
? table1!myid
is very unlikely to print some value of myid present in table1...
unless
table1 is an existing VBA object, at that moment."

Can you please clarify this?
I am verifying the data now with your suggestion on changing the
expression
within the query. Hoping the data is correct. Thanks



:

Depends where this statement is used.

If inside a query, the table sm should be part of the FROM clause
(or,
graphically, in the upper half of the query designer) and the
preferred
syntax is a dot, not a bang.

DateDiff("m", smf.smfwedding, WEDDINGDATE)



If in a form or in a report, then the table value should be 'bound'
to
a
control (can be invisible, if you prefer so), and the preferred
syntax
would
be like:


DateDiff("m", Me.ControlName , WEDDINGDATE)



Note that even if your table name is sm, there is no VBA-object
that
is
open, by default, with that name. So, in the immediate debug
window,
as
example,


? table1!myid


is very unlikely to print some value of myid present in table1...
unless
table1 is an existing VBA object, at that moment.





Hoping it may help,
Vanderghast, Access MVP


Hi

I am running some queries using a form I created and for some
strange
reason
some people are falling off and coming back to the report when I
run
the
queries and then re-run them immidiately after. I am not
changing
any
of
my
parameters and the data is not changing in the tables I am linked
to.
Can
you help?

I am using Access 2003. I have several different queries running
as
make
table queries from Foxpro database linked tables. Within these
queries
I
have one query that has a parameter set for "Married Date" What
this
does
is
prompt the user to enter a start date and then I have an
expression
that
calculates the number of months from that "start date" to an
actual
"Marriage Date" field in one of the tables I am linking from
foxpro.
The
expression looks like this:

DateDiff("m",[smf]![smfwedding],[WEDDINGDATE])

** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from
table
from
foxpro**

Would this be the reason data is changing like this?
Help.
Thanks

 

[Karen]

Sure. Here it is. Thanks


SELECT smf.smfnmfid, smf.smflast, smf.smffirsth, smf.smfentrdt3, Count([No
Kollel-NLF].nlftype) AS CountOfnlftype, [No Kollel -SSF 2]!SumOfSSFMonths/12
AS SSFYears, DateDiff("m",smf.smfentrdt3,Date()) AS SMFMonth, [WEDDINGDATE]
AS Chosenmarrieddate, DateDiff("m",smf.smfwedding,[WEDDINGDATE]) AS
Weddingmonths, smf.smfwedding, [No Kollel-CSF 2].csfregid INTO [Married No
Kollel Result]
FROM (((smf LEFT JOIN [NO KOLLEL-PAY KOL*] ON smf.smfnmfid = [NO KOLLEL-PAY
KOL*].paynmfid) LEFT JOIN [No Kollel-NLF] ON smf.smfnmfid = [No
Kollel-NLF].nlfnmfid) LEFT JOIN [No Kollel-CSF 2] ON smf.smfnmfid = [No
Kollel-CSF 2].csfnmfid) LEFT JOIN [No Kollel -SSF 2] ON smf.smfnmfid = [No
Kollel -SSF 2].ssfnmfid
GROUP BY smf.smfnmfid, smf.smflast, smf.smffirsth, smf.smfentrdt3, [No
Kollel -SSF 2]!SumOfSSFMonths/12, DateDiff("m",smf.smfentrdt3,Date()),
[WEDDINGDATE], DateDiff("m",smf.smfwedding,[WEDDINGDATE]), smf.smfwedding,
[No Kollel-CSF 2].csfregid, smf.smfstatus, [NO KOLLEL-PAY KOL*].paynmfid,
smf.smfdltdate
HAVING ((([No Kollel-CSF 2].csfregid) Is Not Null) AND ((smf.smfstatus)="M")
AND (([NO KOLLEL-PAY KOL*].paynmfid) Is Null) AND
((smf.smfdltdate)=#12/30/1899#) AND
((DateDiff("m",[smf].[smfwedding],[WEDDINGDATE]))>=24));

Can you post the SQL statement appearing in the SQL view of the query?

Vanderghast, Access MVP

Hi Michel -

Thanks for the quick response. OK, I tried what you indicated below.
Here
it is:

DateDiff("m",[smf]![smfwedding],[FORMS]![FRONTVIEW]![TEXT92])-DatePart("d",[FORMS]![FRONTVIEW]![TEXT92])>=DatePart("d",[smf]![smfwedding])

Unfortunetly, it is not working. Now it is giving me 0 records.

Just so I explain what I did; (Making sure I understood correctly) I added
an unbound text box to the form. In the control source added expression
=[Startdate] which is TEXT92

In the query, on the field line, I added the above expression.
In the criteria line I added the >=24

This did not work.
Am I doing something else wrong?
Thanks for the patience and help.

In the query, type:


AgeInMonth: DateDiff("m", fieldName, FORMS!formName!TextBoxName) -
DatePart("d", FORMS!formName!TextBoxName) >= DatePart("d", fieldName)


where fieldName is the field name holding the marriage date
formName is the open form name with the control having the date you
supplied
textBoxName is the control name, in the formName, which hold the
date
you have to supply


In the criteria line, add

= 24


Hoping it may help,
Vanderghast, Access MVP


Maybe I should simplify my problem by asking this question.

I have a query with a married date of students. I want to a list of
people
that have been married =>24 months (2 years). I do not want the 2
years
to
be calculated from today's date (Now()). I want to enter a date that it
should calculate 2 years from.

How would you recommend I do this ?
Thanks

:

I mean that even if the table exists on the hard disk, you cannot
reach
it
simply by using its name, *if* you are in VBA. In fact, in VBA, you
can
read
the data of your table from a recordset. Note that if you are using a
Form
or a Report, you may give the name of the table as record-source (see
this
property), without having to explicitly open it in VBA. Access will
just
open a recordset for you, in that case. You can reach that recordset,
in
VBA
code behind the form, with the expression: Me.Recordset.

If you are in a query, rather than being in VBA, the table name must
be
in
the FROM clause.

Otherwise, the table won't be OPEN to you, even if it exists on the
hard
disk.


(Sure, you can also use Access end user interface to reach your data,
such
as seeing a table in data-view, as example, but your question leads me
to
think you were using either VBA code, either a query.)


Vanderghast, Access MVP



Hi Michel - Thanks for the info. To clarify (I am not an expert in
Access,
(yet))
but I understood what you said below except for

"Note that even if your table name is sm, there is no VBA-object
that
is
open, by default, with that name. So, in the immediate debug
window,
as
example,
? table1!myid
is very unlikely to print some value of myid present in table1...
unless
table1 is an existing VBA object, at that moment."

Can you please clarify this?
I am verifying the data now with your suggestion on changing the
expression
within the query. Hoping the data is correct. Thanks



:

Depends where this statement is used.

If inside a query, the table sm should be part of the FROM clause
(or,
graphically, in the upper half of the query designer) and the
preferred
syntax is a dot, not a bang.

DateDiff("m", smf.smfwedding, WEDDINGDATE)



If in a form or in a report, then the table value should be 'bound'
to
a
control (can be invisible, if you prefer so), and the preferred
syntax
would
be like:


DateDiff("m", Me.ControlName , WEDDINGDATE)



Note that even if your table name is sm, there is no VBA-object
that
is
open, by default, with that name. So, in the immediate debug
window,
as
example,


? table1!myid


is very unlikely to print some value of myid present in table1...
unless
table1 is an existing VBA object, at that moment.





Hoping it may help,
Vanderghast, Access MVP


Hi

I am running some queries using a form I created and for some
strange
reason
some people are falling off and coming back to the report when I
run
the
queries and then re-run them immidiately after. I am not
changing
any
of
my
parameters and the data is not changing in the tables I am linked
to.
Can
you help?

I am using Access 2003. I have several different queries running
as
make
table queries from Foxpro database linked tables. Within these
queries
I
have one query that has a parameter set for "Married Date" What
this
does
is
prompt the user to enter a start date and then I have an
expression
that
calculates the number of months from that "start date" to an
actual
"Marriage Date" field in one of the tables I am linking from
foxpro.
The
expression looks like this:

DateDiff("m",[smf]![smfwedding],[WEDDINGDATE])

** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from
table
from
foxpro**

Would this be the reason data is changing like this?
Help.
Thanks