Re: Data Changing Issue

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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));



"Michel Walsh" wrote:

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

Vanderghast, Access MVP


"Karen" <Karen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:04564386-A4EA-46A9-9AE7-F3AE7ECD7EBE@xxxxxxxxxxxxxxxx
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
    ... In the query, on the field line, I added the above expression. ... *if* you are in VBA. ... the data of your table from a recordset. ... I am running some queries using a form I created and for some ...
    (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: 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)
  • Re: How to Automatically close an empty query result?
    ... "Michel Walsh" wrote: ... >>I have a few queries running in macro. ... >> How can i automatically close the query based on it's results? ...
    (microsoft.public.access.queries)
  • Re: Merging content from access
    ... My hunch at the moment is that this is a problem in my access query. ... I agree with you that Word VBA is a more elegent solution. ... The other field-based technique requires you to have a "parent" table as ... the mail merge data source and a child table inserted using a DATABASE ...
    (microsoft.public.word.docmanagement)