Re: Same date range in main and sub form

You need to concatenate together the parts to make up the SQL string

When you have finished, it will look like the SQL statement you original
posted, except it will have literal dates in the string instead of the
StartDate and EndDate you originally had.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"upandaway" <upandaway@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:208D678B-D356-46BF-B32A-FDE6E9B31794@xxxxxxxxxxxxxxxx
Sorry Allen,
It looks like I am very slow and I am lost.

I now understood your . business but I am not sure what to put in.
First I tried [Admission Date] as the following FROM [AD Absent Sick] made
sense to me. (At Least I thought it would).

But now the program stopped with Runtime error 2465
Active Duty Absent Sick can't find the field "I" referred to in your
expression.
And the next "Me." Statement was highlited:

Me.[Sub1].Form.RecordSource = strSql & " And ([AD Absent Sick].Clinic =
'BBG');"

Next I substituted the "." between SELECT and FROM with all the text from
the select clause of the SQL statement of the query.

Again the program stopped at the Me.[Sub1] statement. I then made this
statement as a commentary " ' ".
The program then ran thru and in the appropriate fields of the form an
error
came up instead of numbers.

The Immediate Window showed the entered date correct (between and)

It looks like I am a hopeless case, as I don't understand neither what to
put in between the SELECT and FROM statement instead of the dots nor, what
you mean by "Me.[Sub1]" I tried to substitute it with the name of the form
as
well as the name of the query on which the form is based. Both case no
success at all.

Sorry, it looks like in this case you have to start from Adam and Eve and
you probably don't have the time and the nerve for it.

Regards
Bernhard


"Allen Browne" wrote:

The ... was intended to stand for all the text in the SELECT clause of
the
SQL statement you posted. You need all the text in there, not the 3 dots.

If you still get the error after that, add the line:
Debug.Print strSql
immediately above the "Me.Recordset = ..." line.
Then when it runs and fails, you can open the Immediate Window (Ctrl+G),
and
see what is wrong. If you can't see anything wrong, copy it to clipboard,
and paste it into SQL view of a query.

"upandaway" <upandaway@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3BB5C95E-1A50-404B-8730-4589D868F359@xxxxxxxxxxxxxxxx
Hi Allen,

Thank you for your fast response with a very interesting solution.

Naturally, I tried it immediately but I got a Runtime Error '3075'
"Syntax
error (missing operator) in query expression '.' and the highlighted
sentence
in the debug window was:

Me.RecordSource + strSql & ";"
Beneath the "If" statement.

What I also found out was, that in the strSql Statement the words
SELECT,
FROM, BETWEEN and AND did not appear in capital letters as expected
when
ending the line by "enter". Just to check whether or not the entry is
correct, I start out the key words to write with lower case letters.
They
then are changing to upper case letters, when everything is correct.

Besides this I do not understand the three periods between SELECT and
FROM.
Did I miss there something?

Allen, your help is highly appreciated.
By the way, your calendar example, in your webpage, is an outstanding
help
in designing the entry forms.

Regards
Bernhard

"Allen Browne" wrote:

Hi Bernhard

If you use the parameters StartDate and EndDate in the query, you will
need
to put them into the subform's queries as well, and enter them
multiple
times. Clearly, that's what you want to avoid.

You could put them as unbound text boxes on the main form, and use the
AfterUpdate event (or the click of a Go button) to change the
RecordSource
for the main form and subforms.

This example assumes both text boxes have a value:
Dim strSql As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strSql = "SELECT ... FROM [AD Absent Sick] " & _
"WHERE ([AD Absent Sick].[Admission Date] Between " & _
Format(Me.StartDate, strcJetDate) & " And " & _
Format(Me.EndDate, strcJetDate) & ")"

If Me.Dirty Then Me.Dirty = False 'save first.
Me.RecordSource = strSql & ";"

Me.[Sub1].Form.RecordSource = strSql & _
" AND ([AD Absent Sick].Clinic = 'BBG');"

Although it is possible to refer to the text boxes on the form
directly
in
the form's query, e.g.:
[Forms].[Form1].[StartDate]
However, this is likely to still give you parameter boxes when you
first
load the form, so I would not recommend that approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"upandaway" <upandaway@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:58D3B3FF-8D16-4DBF-BF18-1E8C6DD22BBC@xxxxxxxxxxxxxxxx
The main form "frm_Days_At_Hospital" has two sub forms. All forms
should
show
data of the same date range (i.e. Admission Date from 03/01/06 till
03/19/06)
having the user to state the date range for the main form only and
not
for
the two sub forms individually also (In total thus three times).

First I checked all the appropriate answers in this discussion group
and
as
stated in several answers I tried with an unbound text field
"StartDate"
and
another unbound text field "EndDate" in the main form but in all
those
answers to other programmers, I never found out, what to do next.
Since
those
fields are unbound nothing happens, when I enter the date

So, I am still stuck with my only known way to have the main form
come
up
with information between given Admission Date.
How to have the sub forms reply between the given date also?

SQL-Statement of the main form's query:
SELECT [AD Absent Sick].[Admission Date], [AD Absent
Sick].Discharged,
(DateDiff("d",[Admission Date],[Discharged])+1) AS DaysDischarged,
(DateDiff("d",[Discharged],Date())) AS DaysDischargedTillToday,
(DateDiff("d",[Admission Date],Date())+1) AS DaysAdmTillToday,
[DaysDischargedTillToday]+[DaysDischarged] AS Substract, [AD Absent
Sick].Clinic, [AD Absent Sick].[Admission Date] AS StartDate, [AD
Absent
Sick].Discharged AS EndDate
FROM [AD Absent Sick]
WHERE ((([AD Absent Sick].[Admission Date]) Between [StartDate] And
[EndDate]));

SQL-Statement of one of the sub form's query:
SELECT [AD Absent Sick].[Admission Date], [AD Absent
Sick].Discharged,
[AD
Absent Sick].Clinic, (DateDiff("d",[Admission Date],[Discharged])+1)
AS
DaysDischarged, (DateDiff("d",[Discharged],Date())) AS
DaysDischargedTillToday, (DateDiff("d",[Admission Date],Date())+1)
AS
DaysAdmTillToday, [DaysDischargedTillToday]+[DaysDischarged] AS
Substract
FROM [AD Absent Sick]
WHERE ((([AD Absent Sick].Clinic)="BBG"));

This is my third posting, no answer received yet. Does it sound too
stupid?


.