What I would like to do is create a report from data stored with in a table (tbl_consultations) with

[Barry McConomy]

Hi

New user.

What I would like to do is create a report from data stored with in a table
(tbl_consultations) with the following criteria:-

1) Choosing a name from a drop down list stored in a table (tbl_doctors),
the table (tbl_consultations) has the doctors ID stored
2) From Date, in table (tbl_consultations)
3) To Date, in table (tbl_consultations)

Can somebody advise the best way to proceed.

Regards
Barry

 

[Lynn Trapp]

You will need to do 2 things:

1. Create a form with a ComboBox (that will be used to look up the doctor's
name), and 2 textboxes for the From and To Dates.

2. Create a query that references those three controls in the criteria, and
make that query the recordsource for your report. The general syntax for
referencing that is:

=Forms!YourForm!YourControl

 

[John Vinson]

Hi

New user.

What I would like to do is create a report from data stored with in a table
(tbl_consultations) with the following criteria:-

1) Choosing a name from a drop down list stored in a table (tbl_doctors),
the table (tbl_consultations) has the doctors ID stored
2) From Date, in table (tbl_consultations)
3) To Date, in table (tbl_consultations)

Can somebody advise the best way to proceed.

Create an unbound Form, frmCrit. Put a Combo Box (tables don't have
drop down lists, they contain DATA - see
http://www.mvps.org/access/lookupfields.htm for a critique of
Microsoft's 'lookup wizard' misfeature), let's call it cboDoctor on
the form; also put two textboxes txtFrom and txtTo.

Create a Query with criteria

=Forms!frmCrit!cboDoctor

on the DoctorID field; and

= CDate([Forms]![frmCrit]![txtFrom]) AND < DateValue([Forms]![frmCrit]![txtTo]) + 1

on the datefield.

Base a Report on this query and use the toolbox command button wizard
to put a button on frmCrit to open the report.

 

[Barry McConomy]

Lynn

Thanks for the advice.

Barry

You will need to do 2 things:

1. Create a form with a ComboBox (that will be used to look up the doctor's
name), and 2 textboxes for the From and To Dates.

2. Create a query that references those three controls in the criteria, and
make that query the recordsource for your report. The general syntax for
referencing that is:

=Forms!YourForm!YourControl


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

 

[Barry McConomy]

John

Thanks for the advice

Barry

Hi

New user.

What I would like to do is create a report from data stored with in a table
(tbl_consultations) with the following criteria:-

1) Choosing a name from a drop down list stored in a table (tbl_doctors),
the table (tbl_consultations) has the doctors ID stored
2) From Date, in table (tbl_consultations)
3) To Date, in table (tbl_consultations)

Can somebody advise the best way to proceed.

Create an unbound Form, frmCrit. Put a Combo Box (tables don't have
drop down lists, they contain DATA - see
http://www.mvps.org/access/lookupfields.htm for a critique of
Microsoft's 'lookup wizard' misfeature), let's call it cboDoctor on
the form; also put two textboxes txtFrom and txtTo.

Create a Query with criteria

=Forms!frmCrit!cboDoctor

on the DoctorID field; and

= CDate([Forms]![frmCrit]![txtFrom]) AND <

DateValue([Forms]![frmCrit]![txtTo]) + 1

 

[Barry McConomy]

Hi John

Can you help?

Using the wizard if I type in values for the combo box all works fine.

Using the wizard when I look up values from a table it fails, my report is
blank.

The only criteria I am using until I get it to work is the doctors name.

Regards
Barry

Hi

New user.

What I would like to do is create a report from data stored with in a table
(tbl_consultations) with the following criteria:-

1) Choosing a name from a drop down list stored in a table (tbl_doctors),
the table (tbl_consultations) has the doctors ID stored
2) From Date, in table (tbl_consultations)
3) To Date, in table (tbl_consultations)

Can somebody advise the best way to proceed.

Create an unbound Form, frmCrit. Put a Combo Box (tables don't have
drop down lists, they contain DATA - see
http://www.mvps.org/access/lookupfields.htm for a critique of
Microsoft's 'lookup wizard' misfeature), let's call it cboDoctor on
the form; also put two textboxes txtFrom and txtTo.

Create a Query with criteria

=Forms!frmCrit!cboDoctor

on the DoctorID field; and

= CDate([Forms]![frmCrit]![txtFrom]) AND <

DateValue([Forms]![frmCrit]![txtTo]) + 1

 

[Barry McConomy]

Hi John

Cancel my last request.

I read the information at http://www.mvps.org/access/lookupfields.htm and
now have a better understanding.

Regards
Barry

Hi John

Can you help?

Using the wizard if I type in values for the combo box all works fine.

Using the wizard when I look up values from a table it fails, my report is
blank.

The only criteria I am using until I get it to work is the doctors name.

Regards
Barry

Hi

New user.

What I would like to do is create a report from data stored with in a table
(tbl_consultations) with the following criteria:-

1) Choosing a name from a drop down list stored in a table (tbl_doctors),
the table (tbl_consultations) has the doctors ID stored
2) From Date, in table (tbl_consultations)
3) To Date, in table (tbl_consultations)

Can somebody advise the best way to proceed.

Create an unbound Form, frmCrit. Put a Combo Box (tables don't have
drop down lists, they contain DATA - see
http://www.mvps.org/access/lookupfields.htm for a critique of
Microsoft's 'lookup wizard' misfeature), let's call it cboDoctor on
the form; also put two textboxes txtFrom and txtTo.

Create a Query with criteria

=Forms!frmCrit!cboDoctor

on the DoctorID field; and

= CDate([Forms]![frmCrit]![txtFrom]) AND <

DateValue([Forms]![frmCrit]![txtTo]) + 1

on the datefield.

Base a Report on this query and use the toolbox command button wizard
to put a button on frmCrit to open the report.