Re: Data Changing Issue

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.

"Michel Walsh" wrote:

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


"Karen" <Karen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:379399E4-4F0E-4373-AEB2-3628AB754719@xxxxxxxxxxxxxxxx
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

"Michel Walsh" wrote:

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



"Karen" <Karen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4B467934-13C8-490C-97F0-6234B39DB1CE@xxxxxxxxxxxxxxxx
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



"Michel Walsh" wrote:

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" <Karen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B7439289-0B67-40CB-9AE1-DF3CC1DC0DB4@xxxxxxxxxxxxxxxx
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











.

Relevant Pages

  • Re: Data Changing Issue
    ... "Michel Walsh" wrote: ... In the query, on the field line, I added the above expression. ... *if* you are in VBA. ... I am running some queries using a form I created and for some ...
    (microsoft.public.access.queries)
  • Re: EDI export
    ... To put each column in one line you'll have to use VBA & a recordset. ... > export a text file from a query or table. ... > It has to all be in a single column. ...
    (microsoft.public.access.queries)
  • Re: Inserting data from 2 tables into 1
    ... been busy with Access and VBA for about 6 ... I would make all the queries and then use a set of macros to ... > Do you perhaps mean look for duplicates in tbResultsTemp? ... > The first is easier and lets you use all the nice interfaces like query ...
    (microsoft.public.access.modulesdaovba)
  • Re: MS Access 2003 CrossTab Query Question using VBA--Item not fou
    ... VBA code runs & hits the break point the code will stop and the VBA ... Your code only reads the first row of the recordset. ... before running a crosstab ones does not know how many columns will appear and what their name will be..that is the natureof a crosstab. ... going to appear based on parameter criteria fed to an underlying query that a crosstab query is created from. ...
    (microsoft.public.access.queries)
  • Re: Data Changing Issue
    ... *if* you are in VBA. ... think you were using either VBA code, either a query.) ... is very unlikely to print some value of myid present in table1... ... I am running some queries using a form I created and for some strange ...
    (microsoft.public.access.queries)