Passing A selected Month FromCombo To Query

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Hi, This is my first attempt at a "clever" procedure.
I have a form [Start menu] with an unbound combo box which lists the 12
months of the year. I want a report to open displaying invoices for the
selected month. The report is linked to a query in which under the date field
I have this expression (October for example) -
IIf([forms]![start menu]![combo64]="OCT",([Invoice].[Date]) Between
#01/10/2008# And #31/10/2008#). The report displays no data. I have tried
the US date format but still no joy. Even when (with your help) I get the
expression corrected do I really need to type 12 version of the query or is
their a simpler way.
At present I haven't progressed to VBA level - just using commands,
parameters an d built in functions.
 
With combo boxes you sometimes need to add the Column property.

[forms]![start menu]![combo64].Column(0)

The 0 is not a typo. The first column is 0, second is 1, etc.

Also I noticed that your IIf statement is missing an argument. That is
unless you are putting each month in it.

Another problem is what are you going to do next year? Right now you are
tied into only dates in 2008.
 
In your query, you can filter the [Invoice].[Date] field by what is select
in the combo by adding a calculated field to your query.
In the Field Name row:
SelectedMonth: Format([Date], "mmm") = [forms]![start menu]![combo64]

In the Criteria Row:
True

You will need only one query. It will return records for the month
selected. Now, there is one glitch. It will return all records for
October, regardless of the year. You need to determine how you are going to
allow the user to select a year.
 
Thanks to both for your advice. I see the problems inherent with both. I
dropped "clever" in favour of practicality and got report wizard to generate
a "Report By Month" which will do fine. If the boss can do any better, let
him have a go.
Cheers gents!

Jerry Whittle said:
With combo boxes you sometimes need to add the Column property.

[forms]![start menu]![combo64].Column(0)

The 0 is not a typo. The first column is 0, second is 1, etc.

Also I noticed that your IIf statement is missing an argument. That is
unless you are putting each month in it.

Another problem is what are you going to do next year? Right now you are
tied into only dates in 2008.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Brian said:
Hi, This is my first attempt at a "clever" procedure.
I have a form [Start menu] with an unbound combo box which lists the 12
months of the year. I want a report to open displaying invoices for the
selected month. The report is linked to a query in which under the date field
I have this expression (October for example) -
IIf([forms]![start menu]![combo64]="OCT",([Invoice].[Date]) Between
#01/10/2008# And #31/10/2008#). The report displays no data. I have tried
the US date format but still no joy. Even when (with your help) I get the
expression corrected do I really need to type 12 version of the query or is
their a simpler way.
At present I haven't progressed to VBA level - just using commands,
parameters an d built in functions.
Click to expand...
Click to expand...
 
Actually, a Jet query will not understand a combo column reference as a
criteria.

Jerry Whittle said:
With combo boxes you sometimes need to add the Column property.

[forms]![start menu]![combo64].Column(0)

The 0 is not a typo. The first column is 0, second is 1, etc.

Also I noticed that your IIf statement is missing an argument. That is
unless you are putting each month in it.

Another problem is what are you going to do next year? Right now you are
tied into only dates in 2008.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Brian said:
Hi, This is my first attempt at a "clever" procedure.
I have a form [Start menu] with an unbound combo box which lists the 12
months of the year. I want a report to open displaying invoices for the
selected month. The report is linked to a query in which under the date
field
I have this expression (October for example) -
IIf([forms]![start menu]![combo64]="OCT",([Invoice].[Date]) Between
#01/10/2008# And #31/10/2008#). The report displays no data. I have
tried
the US date format but still no joy. Even when (with your help) I get
the
expression corrected do I really need to type 12 version of the query or
is
their a simpler way.
At present I haven't progressed to VBA level - just using commands,
parameters an d built in functions.
Click to expand...
Click to expand...
 
Back
Top