Same date range in main and sub form

[upandaway]

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?

Regards Bernhard

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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?

Regards Bernhard

 

[upandaway]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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?

Regards Bernhard

 

[upandaway]

Please read correct the high lited sentence:

Me.RecordSource = strSql & ";"

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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?

Regards Bernhard

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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?

 

[Douglas J Steele]

It's not necessary to put the semi-colon at the end: it's optional.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Please read correct the high lited sentence:

Me.RecordSource = strSql & ";"

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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?

Regards Bernhard

 

[upandaway]

Dough,

Thank you for your info.
However as I will state in my next reply, I am a hopeless case. I still
don't understand what text to put between SELECT and FROM and I also don't
get it how to refer to the sub form. The "Me.[Sub1]" sounds good but with
what do I substitute this [Sub1]. I tried the name of the sub form and also
the name of the query on which the sub form is based.

Hopefully, you read my initial submission, other wise you will not know,
what I am talking about..

Thanks again, perhaps I will understand this eventually.

Regards
Bernhard

It's not necessary to put the semi-colon at the end: it's optional.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Please read correct the high lited sentence:

Me.RecordSource = strSql & ";"

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

:

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.

Reply to group, rather than allenbrowne at mvps dot org.

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?

Regards Bernhard

 

[upandaway]

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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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?

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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

:

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.

Reply to group, rather than allenbrowne at mvps dot org.

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?

 

[upandaway]

Allan,

I have to apologise. My response looked very stupid in the aftermath.
However, until now, I never generated a form out of a table; always from a
query.
I am not too god in VSB programming either, if your suggestion is VSB
programming at all. You see I am not very firm in all of this.
Eventually I took the appropriate table as source code for the form and did
then exactly what you suggested.
Bingo, up came the correct data for the date range given.
With the sub form, you told me [Sub1], again I was absolutely out of range.
But you are never too old to learn; even while I am hitting the 70s next
month, logical thinking let me realise, that what could be put into the name
section of a form can be called up by other programs. Actually I came after,
when I realised, that the unbound Text Box works by its name!!
So, I don’t know if you realise, what you have done with your very easy
going tutoring. New dimensions are opening up for me now!

Ok, Allen, thanks again I appreciate your help very much. Greetings from
still winter cold Germany.
Take care
Bernhard.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

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

:

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.

Reply to group, rather than allenbrowne at mvps dot org.

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?

 

[Allen Browne]

Good news.

Well done.