Next Issue....records between dates

[thewabit via AccessMonster.com]

I have a form that basically has you enter criteria to view some records. It
has a start date and an end date, At the bottom, I have a "retrieve button...
other than "openform" to see the records, what code do I need to put behind
this button that makes it retrieve the records that meets my criteria?

Thanks!

 

[John W. Vinson]

I have a form that basically has you enter criteria to view some records. It
has a start date and an end date, At the bottom, I have a "retrieve button...
other than "openform" to see the records, what code do I need to put behind
this button that makes it retrieve the records that meets my criteria?

Thanks!

Base the display form on a query with a criterion

= [Forms]![Searchform]![txtStartDate] AND < DateAdd("d", 1, [Forms]![Searchform]![txtEndDate])

Other than that... just open the form...

 

[thewabit via AccessMonster.com]

John,

My apologies...I know this is a newbie question.

I have made a query of the table these records are in. It has things l;ike
ObservationID, ObservationDate, Etc. Which field would get the criteria you
listed...ID, Date? And where you put "searchform", is that the form I put the
date into or the display form?

thanks!

I have a form that basically has you enter criteria to view some records. It
has a start date and an end date, At the bottom, I have a "retrieve button...
other than "openform" to see the records, what code do I need to put behind
this button that makes it retrieve the records that meets my criteria?

Thanks!

Base the display form on a query with a criterion

= [Forms]![Searchform]![txtStartDate] AND < DateAdd("d", 1, [Forms]![Searchform]![txtEndDate])

Other than that... just open the form...

 

[John W. Vinson]

John,

My apologies...I know this is a newbie question.

I have made a query of the table these records are in. It has things l;ike
ObservationID, ObservationDate, Etc. Which field would get the criteria you
listed...ID, Date?

If you're searching for records with a particular range of dates... you put
the criterion on the field containing the date.

You might want to take a look at some of these resources: Crystal's intro
tutorial might be helpful. Learning to use queries is absolutely ESSENTIAL to
using Access, and you'll benefit from putting in some study:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

And where you put "searchform", is that the form I put the
date into or the display form?

You didn't post any of your form names so I had to make some approximations. I
assumed that the form into which you are typing the criteria is named
"searchform". If it's named something else, use that name.

 

[thewabit via AccessMonster.com]

John,

Thanks a bunch.

It is getting closer. It works except when the form opens, I get the box that
asks for the start date and end date parameter. What am I doing wrong that
doesn't allow it to understand what dates I have already put in my criteria
form?

John,

[quoted text clipped - 3 lines]

ObservationID, ObservationDate, Etc. Which field would get the criteria you
listed...ID, Date?

If you're searching for records with a particular range of dates... you put
the criterion on the field containing the date.

You might want to take a look at some of these resources: Crystal's intro
tutorial might be helpful. Learning to use queries is absolutely ESSENTIAL to
using Access, and you'll benefit from putting in some study:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

And where you put "searchform", is that the form I put the
date into or the display form?

You didn't post any of your form names so I had to make some approximations. I
assumed that the form into which you are typing the criteria is named
"searchform". If it's named something else, use that name.

 

[John W. Vinson]

It is getting closer. It works except when the form opens, I get the box that
asks for the start date and end date parameter. What am I doing wrong that
doesn't allow it to understand what dates I have already put in my criteria
form?

I don't know, because I don't know what you're doing. Please post the code
you're using to open the form, the names of the two forms involved, and the
SQL of the query.

 

[thewabit via AccessMonster.com]

docmd.openform "frmLOSA_Review"
Form 1, used to enter criteria (start and end date), is named
"frmLOSA_Report".
Form 2, used to display the records is named "frmLOSA_Review"

SELECT tblObservations.ObID, tblObservations.Observer, tblObservations.
ObEmpID, tblObservations.ObDate, tblObservations.ObFrom, tblObservations.ObTo,
tblObservations.FlightNumber, tblObservations.ScheduleBlock, tblObservations.
ActualBlock, tblObservations.Captain, tblObservations.CaptainEmpID,
tblObservations.CrewBase, tblObservations.FirstOfficer, tblObservations.
FirstOfficerID, tblObservations.FirstOfficerCrewBase, tblObservations.
AircraftTailNumber, tblObservations.AircraftType, tblObservations.LineCheck,
tblObservations.ObRemark, tblObservations.Approach, tblObservations.
P1_VerticalSpeed, tblObservations.P1_Course, tblObservations.P1_Glideslope,
tblObservations.P2_VerticalSpeed, tblObservations.P2_Course, tblObservations.
P2_Glideslope, tblObservations.P2_Airspeed, tblObservations.P3_VerticalSpeed,
tblObservations.P3_Course, tblObservations.P3_Glideslope, tblObservations.
P3_Airspeed
FROM tblObservations
WHERE (((tblObservations.ObDate)>=[Forms]![frmLOSA_Report]![StartDate] And
(tblObservations.ObDate)<DateAdd("d",1,[Forms]![frmLOSA_Report]![EndDate])));

 

[John W. Vinson]

docmd.openform "frmLOSA_Review"
Form 1, used to enter criteria (start and end date), is named
"frmLOSA_Report".
Form 2, used to display the records is named "frmLOSA_Review"

SELECT tblObservations.ObID, tblObservations.Observer, tblObservations.
ObEmpID, tblObservations.ObDate, tblObservations.ObFrom, tblObservations.ObTo,
tblObservations.FlightNumber, tblObservations.ScheduleBlock, tblObservations.
ActualBlock, tblObservations.Captain, tblObservations.CaptainEmpID,
tblObservations.CrewBase, tblObservations.FirstOfficer, tblObservations.
FirstOfficerID, tblObservations.FirstOfficerCrewBase, tblObservations.
AircraftTailNumber, tblObservations.AircraftType, tblObservations.LineCheck,
tblObservations.ObRemark, tblObservations.Approach, tblObservations.
P1_VerticalSpeed, tblObservations.P1_Course, tblObservations.P1_Glideslope,
tblObservations.P2_VerticalSpeed, tblObservations.P2_Course, tblObservations.
P2_Glideslope, tblObservations.P2_Airspeed, tblObservations.P3_VerticalSpeed,
tblObservations.P3_Course, tblObservations.P3_Glideslope, tblObservations.
P3_Airspeed
FROM tblObservations
WHERE (((tblObservations.ObDate)>=[Forms]![frmLOSA_Report]![StartDate] And
(tblObservations.ObDate)<DateAdd("d",1,[Forms]![frmLOSA_Report]![EndDate])));

Is the form in fact name frmLOSA_Report (not "frmLOSA Report")? Do you have
textboxes namd StartDate (not Start Date or Start_Date) and EndDate on
frmLOSA_Report? Is frmLOSA_Report open at the time (I'd put a command button
on it to open frmLOSA_Review)? Or how are you opening the Review form?

 

[thewabit via AccessMonster.com]

John,

That was the problem. I had the form set to close. I reprogrammed the button
to only open frmLOSA_Review and it works fine.

Kind of related, in frmLOSA_Report, I have the calendar popup to fill in the
startdate and enddate and it seems to work fine. But in the Code screen, I do
a compile and I get a syntax erroron this: =CalendarFor([StartDate], "Select
the sale date") Any ideas?

docmd.openform "frmLOSA_Review"
Form 1, used to enter criteria (start and end date), is named

[quoted text clipped - 17 lines]

WHERE (((tblObservations.ObDate)>=[Forms]![frmLOSA_Report]![StartDate] And
(tblObservations.ObDate)<DateAdd("d",1,[Forms]![frmLOSA_Report]![EndDate])));

Is the form in fact name frmLOSA_Report (not "frmLOSA Report")? Do you have
textboxes namd StartDate (not Start Date or Start_Date) and EndDate on
frmLOSA_Report? Is frmLOSA_Report open at the time (I'd put a command button
on it to open frmLOSA_Review)? Or how are you opening the Review form?

 

[thewabit via AccessMonster.com]

Next question...On that same frmLOSA_Report form is some other filter
criteria I.E. I can select to only display records by observer name via a
combobox and some radio buttons to display records grouped by comments. To
get the date to work, we based the display form on a query and filtered the
date column. How do these other criteria boxes and radio buttons come into
play? In other words, can I filter by date AND other things? if so, how?

docmd.openform "frmLOSA_Review"
Form 1, used to enter criteria (start and end date), is named

[quoted text clipped - 17 lines]

WHERE (((tblObservations.ObDate)>=[Forms]![frmLOSA_Report]![StartDate] And
(tblObservations.ObDate)<DateAdd("d",1,[Forms]![frmLOSA_Report]![EndDate])));

Is the form in fact name frmLOSA_Report (not "frmLOSA Report")? Do you have
textboxes namd StartDate (not Start Date or Start_Date) and EndDate on
frmLOSA_Report? Is frmLOSA_Report open at the time (I'd put a command button
on it to open frmLOSA_Review)? Or how are you opening the Review form?

 

[John W. Vinson]

John,

That was the problem. I had the form set to close. I reprogrammed the button
to only open frmLOSA_Review and it works fine.

It's sometimes useful to have the button set the button's own form's Visible
property to No. It vanishes from the screen but its parameter values are still
available. You should close the calling form in the Close event of the called
form (or report) if you do this.

Kind of related, in frmLOSA_Report, I have the calendar popup to fill in the
startdate and enddate and it seems to work fine. But in the Code screen, I do
a compile and I get a syntax erroron this: =CalendarFor([StartDate], "Select
the sale date") Any ideas?

I've never seen a function named CalendarFor; is that a custom routine in your
database? If so post the code.

 

[John W. Vinson]

Next question...On that same frmLOSA_Report form is some other filter
criteria I.E. I can select to only display records by observer name via a
combobox and some radio buttons to display records grouped by comments. To
get the date to work, we based the display form on a query and filtered the
date column. How do these other criteria boxes and radio buttons come into
play? In other words, can I filter by date AND other things? if so, how?

Any query can have multiple criteria. Just open the query in design view and
apply criteria just as you did for the date. The details will depend on the
nature of the query that you want to run.

 

[thewabit via AccessMonster.com]

I'm not sure what that code is. It's on another button in DB...that's what
happens when I experiment too much. But hey, that's how I learn.

Let me make sure I understand. In the Close event of the form that is
displaying my records, set it to close the previous form?

John,

That was the problem. I had the form set to close. I reprogrammed the button
to only open frmLOSA_Review and it works fine.

It's sometimes useful to have the button set the button's own form's Visible
property to No. It vanishes from the screen but its parameter values are still
available. You should close the calling form in the Close event of the called
form (or report) if you do this.

Kind of related, in frmLOSA_Report, I have the calendar popup to fill in the
startdate and enddate and it seems to work fine. But in the Code screen, I do
a compile and I get a syntax erroron this: =CalendarFor([StartDate], "Select
the sale date") Any ideas?

I've never seen a function named CalendarFor; is that a custom routine in your
database? If so post the code.

 

[John W. Vinson]

I'm not sure what that code is. It's on another button in DB...that's what
happens when I experiment too much. But hey, that's how I learn.

Open the VBA editor and use the binoculars icon. Search "Current Project" and
you should be able to find the code. Or, select the CalendarFor line and right
mouseclick, and select "Definition". Several ways to find the actual code!

Let me make sure I understand. In the Close event of the form that is
displaying my records, set it to close the previous form?

Yes; a line

DoCmd.Close acForm, "frmLOSA_Report"

will do so.

 

[thewabit via AccessMonster.com]

Thanks for your help on this John. Hopefully, talk to you soon with my next
issue. This is a big project.

 

[thewabit via AccessMonster.com]

I am using Allan Browns calendar. It seems to be part of the module he has
you download and put in your DB. heres the part flags.


Public Function CalendarFor(txt As TextBox, Optional strTitle As String)
'On Error GoTo Err_Handler
'Purpose: Open the calendar form, identifying the text box to return
the date to.
'Arguments: txt = the text box to return the date to.
' strTitle = the caption for the calendar form (passed in
OpenArgs).

Set gtxtCalTarget = txt
DoCmd.OpenForm "frmCalendar", windowmode:=acDialog, OpenArgs:=strTitle

 

[thewabit via AccessMonster.com]

I added an additional filter to filter by "observer". So I had the " date"
filtered and the "observer" ..It worked great...the first time. When I went
back in to do it again, it returned no records and the "Date field of the
viewing form had the #name? thing. Did I need to put a requery somewhere?

 

[John W. Vinson]

I added an additional filter to filter by "observer". So I had the " date"
filtered and the "observer" ..It worked great...the first time. When I went
back in to do it again, it returned no records and the "Date field of the
viewing form had the #name? thing.

Please post the query and your code.

 

[thewabit via AccessMonster.com]

SELECT tblObservations.ObID, tblObservations.Observer, tblObservations.
ObEmpID, tblObservations.ObDate, tblObservations.ObFrom, tblObservations.ObTo,
tblObservations.FlightNumber, tblObservations.ScheduleBlock, tblObservations.
ActualBlock, tblObservations.Captain, tblObservations.CaptainEmpID,
tblObservations.CrewBase, tblObservations.FirstOfficer, tblObservations.
FirstOfficerID, tblObservations.FirstOfficerCrewBase, tblObservations.
AircraftTailNumber, tblObservations.AircraftType, tblObservations.LineCheck,
tblObservations.ObRemark, tblObservations.Approach, tblObservations.
P1_VerticalSpeed, tblObservations.P1_Course, tblObservations.P1_Glideslope,
tblObservations.P2_VerticalSpeed, tblObservations.P2_Course, tblObservations.
P2_Glideslope, tblObservations.P2_Airspeed, tblObservations.P3_VerticalSpeed,
tblObservations.P3_Course, tblObservations.P3_Glideslope, tblObservations.
P3_Airspeed
FROM tblObservations
WHERE (((tblObservations.Observer)=[Forms]![frmLOSA_Report]![Observer]) AND (
(tblObservations.ObDate)>=[Forms]![frmLOSA_Report]![StartDate] And
(tblObservations.ObDate)<DateAdd("d",1,[Forms]![frmLOSA_Report]![EndDate])));