If [statement date] is a Date/Time field, then it does contain a full
date
(month, day, and year), even if you format it so as to suppress some of
that
in the way it is displayed.
If the text box on teh form doesn't match the same data type, JET could
reject the query statment.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
I got the ctrl+G codes to all work now, but when i made the other
correction,
i still get the improper use of '.' , '!' or '()' error message. I
think
we're 99% of the way there. I have it as follows:
(Select [statement date] from [correction letters - step 1] where
[statement
date] = [forms] ! [active cards] ! [statements Received] . [Form] !
[Statement date]
I don't know if it's because i have statement date set up in a
"MM/YYYY"
format, and when i ran the ctrl+g command, it came up with 4/1/07, and
i'm
mainly just looking for a 4/2007 return.
Thanks for your help.
--
Scott
:
Okay, perhaps the subform *control* (i.e. the control on the main
form)
has
a name that is different from the form that gets loaded into it (its
Source
Object.)
Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
The Title-bar of the Properties window must read "Subform..."
Click on the Other tab.
What is the Name of this subform control?
You need that name in place of:
[statement query]
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
I made it this far:
? [forms]![active cards].name
Active Cards
? typename ([forms]![active cards]![statement query])
That is where it pulled runtime error 2465.
Maybe if i spell out my design that could help...
The form name is active cards.
The subform name is statement query
The field within the statement query i'm trying to do a select query
on
is
statement date.
You have no idea how much I appreciate your help.
--
Scott
:
Okay, I take it that the extra spaces are just for clarity (not
really
in
your query.)
To debug this, open the Immediate Window (Ctrl+G), and enter this:
? [Forms]![active cards]![statment query].[Form]![statement
date]
Does it show the correct date?
If not, build up a bit at a time until you pinpoint where the
problem
is.
Try:
? [Forms]![active cards].Name
When you press Enter, it should respond with:
active cards
If that works, try:
? Typename([Forms]![active cards]![statment query])
It should respond with:
Subform
If that works, try:
? [Forms]![active cards]![statment query].Form.Name
That should yield:
statment query
Note that form needs to be open (not in design view) for this query
to
work.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
I used the following formula as suggested:
(Select [statement date] from [correction letters - step 1] where
[statement
date] = [forms] ! [active cards] ! [statement Query] . [Form] !
[Statement
date]
I got the below error message:
Invalid use of '.', '!', or '()'.
I think we're getting in the right direction. I greatly
appreciate
your
help.
--
Scott
:
Ah, lights go on.
The query is for a report, and it attempts to limit the record
to
only
the
[statement date] of the active record that is open in the
subform
when
you
fire up this report?
The query to use as the RecordSource of your report would look
like
this:
Select [statement date]
FROM [correction letters - step 1]
WHERE [statement date] =
[Forms]![active cards]![statment query].[Form]![statement date];
If the query pops up a parameter dialog, it can't find the name.
Post back for details on how to verify the name.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
There is no statement date on the main form. There are many
statement
dates
on the subform that are linked to the main table by [card #].
I'm trying to run a select query that will run a report, to
display
only
the
current card # on the main form, and the current statement on
the
subform.
I
just can't figure out the correct way to do the select query.
I
hope
this
helps you more.
--
Scott
:
So, you want the subform to show only the same [card #] and
[statement
date]
as the record in the main form?
1. Open the main form in design view.
2. Right-click the edge of the subform control and choose
Properties.
3. On the Data tab of the Properties box, set Link Master
Fields
to:
[card #], [statement date]
and set Link Child Fields the same way.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
I'm not quite exactly sure what you meant. The form and
subform
are
in
a
one
to many relationship. On the query, I also have a query
where
it
is
selecting only the current master record, but i want to
limit
it
further
by
also only pulling data from the current record on the
subform.
They
are
currently linked by a field [card #].
Any further help you can provide would be greatly
appreciated.--
Scott
:
Presumably you have this query statement in the
RecordSource
of
the
subform?
If so, omit the WHERE clause from the query statement.
