Year query based on text

  • Thread starter Thread starter cathyt
  • Start date Start date
C

cathyt

Hi Everyone,
I’m creating a database that has a table called History. History contains 7
fields; ID, Year, Schedule, Hold, LType, Painted, Approved. The Year field
is a combo box with the following values: Every, Even, Odd, Varies, No. The
rest of the fields don’t matter for this query (except ID).

I’ve been trying to create a query that will return the results in the Year
field based on the current year. For example 2010 is an “Even†year, so it
would return those records, as well as “Every†and “Variesâ€. If possible I
don’t want to have to go into the query next year and change anything to
reflect 2011 and “Oddâ€.

I’m starting to think I need to convert those values to numbers and create a
query based on that. Does that sound right or is there another way to
accomplish it? Maybe an IIF statement using numbers/values? Not really sure
here.

Thanks for the help!
Cathy
 
In a standard module (not the module of a form or report), save a function
like the one below.

Then in your query, enter an expression like this into the Field row:
TestYear([Year], [Forms].[Form1].[Combo1])
In the Criteria row under this, enter:
True

Function TestYear(TheYear As Variant, TheCombo as ComboBox) As Boolean
If IsNumeric(TheYear) And Not IsNull(TheCombo) Then
Select Case TheCombo.Value
Case "Every"
TestYear = True
Case "Even"
TestYear = (TheYear Mod 2 = 0)
Case "Odd"
TestYear = (TheYear Mod 2 = 1)
Case "Varies"
TestYear = True
Case "No"
TestYear = False
Case Else
Debug.Print "TestYear() did not handle combo value " &
TheCombo.Value
End Select
End If
End Function

Note that Year is a function name in VBA, and Access can get confused if you
use it as a field name. For a list of names to avoid when naming fields,
see:
http://allenbrowne.com/AppIssueBadWord.html
 
Allen,
Thanks for the help; I'm not quite there yet. I changed my field name from
Year to PMYear. I copied the module text exactly as you've provided into a
module and changed the expression in my query as follows:

TestYear([PMYear], [Forms].[History].[PMYear])

I get an error message saying the expression is typed incorrectly or too
complex to be evaluated. I've tried changing a couple things but no luck.
Thanks,
Cathy

Allen Browne said:
In a standard module (not the module of a form or report), save a function
like the one below.

Then in your query, enter an expression like this into the Field row:
TestYear([Year], [Forms].[Form1].[Combo1])
In the Criteria row under this, enter:
True

Function TestYear(TheYear As Variant, TheCombo as ComboBox) As Boolean
If IsNumeric(TheYear) And Not IsNull(TheCombo) Then
Select Case TheCombo.Value
Case "Every"
TestYear = True
Case "Even"
TestYear = (TheYear Mod 2 = 0)
Case "Odd"
TestYear = (TheYear Mod 2 = 1)
Case "Varies"
TestYear = True
Case "No"
TestYear = False
Case Else
Debug.Print "TestYear() did not handle combo value " &
TheCombo.Value
End Select
End If
End Function

Note that Year is a function name in VBA, and Access can get confused if you
use it as a field name. For a list of names to avoid when naming fields,
see:
http://allenbrowne.com/AppIssueBadWord.html

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

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


cathyt said:
Hi Everyone,
I’m creating a database that has a table called History. History contains
7
fields; ID, Year, Schedule, Hold, LType, Painted, Approved. The Year
field
is a combo box with the following values: Every, Even, Odd, Varies, No.
The
rest of the fields don’t matter for this query (except ID).

I’ve been trying to create a query that will return the results in the
Year
field based on the current year. For example 2010 is an “Even†year, so
it
would return those records, as well as “Every†and “Variesâ€. If possible
I
don’t want to have to go into the query next year and change anything to
reflect 2011 and “Oddâ€.

I’m starting to think I need to convert those values to numbers and create
a
query based on that. Does that sound right or is there another way to
accomplish it? Maybe an IIF statement using numbers/values? Not really
sure
here.

Thanks for the help!
Cathy
Click to expand...
.
Click to expand...
 
Suggestions:

1. If History is a bound form, try renaming the combo to (say) cboYear, so
it's not the same as the field name. (The field contains a number, but the
combo contains text.)

2. In the code window, choose Compile on the Debug, to ensure it understands
the code.

3. Make sure the History form open (not in design view), and the combo has
one of the items chosen. Then open the Immediate Window (Ctrl+G), and enter
this expression:
? TestYear(2010, [Forms].[History].[cboYear])
Try with different years and different values chosen in the combo.
Is the function giving the right results?

4. In query design view, choose Parameters on the Query menu/ribbon.
Access opens the Parameters dialog.
Enter:
[Forms].[History].[cboYear] Text

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

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


cathyt said:
Allen,
Thanks for the help; I'm not quite there yet. I changed my field name
from
Year to PMYear. I copied the module text exactly as you've provided into
a
module and changed the expression in my query as follows:

TestYear([PMYear], [Forms].[History].[PMYear])

I get an error message saying the expression is typed incorrectly or too
complex to be evaluated. I've tried changing a couple things but no luck.
Thanks,
Cathy

Allen Browne said:
In a standard module (not the module of a form or report), save a
function
like the one below.

Then in your query, enter an expression like this into the Field row:
TestYear([Year], [Forms].[Form1].[Combo1])
In the Criteria row under this, enter:
True

Function TestYear(TheYear As Variant, TheCombo as ComboBox) As Boolean
If IsNumeric(TheYear) And Not IsNull(TheCombo) Then
Select Case TheCombo.Value
Case "Every"
TestYear = True
Case "Even"
TestYear = (TheYear Mod 2 = 0)
Case "Odd"
TestYear = (TheYear Mod 2 = 1)
Case "Varies"
TestYear = True
Case "No"
TestYear = False
Case Else
Debug.Print "TestYear() did not handle combo value " &
TheCombo.Value
End Select
End If
End Function

Note that Year is a function name in VBA, and Access can get confused if
you
use it as a field name. For a list of names to avoid when naming fields,
see:
http://allenbrowne.com/AppIssueBadWord.html

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

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


cathyt said:
Hi Everyone,
I’m creating a database that has a table called History. History
contains
7
fields; ID, Year, Schedule, Hold, LType, Painted, Approved. The Year
field
is a combo box with the following values: Every, Even, Odd, Varies, No.
The
rest of the fields don’t matter for this query (except ID).

I’ve been trying to create a query that will return the results in the
Year
field based on the current year. For example 2010 is an “Even†year,
so
it
would return those records, as well as “Every†and “Variesâ€. If
possible
I
don’t want to have to go into the query next year and change anything
to
reflect 2011 and “Oddâ€.

I’m starting to think I need to convert those values to numbers and
create
a
query based on that. Does that sound right or is there another way to
accomplish it? Maybe an IIF statement using numbers/values? Not
really
sure
here.

Thanks for the help!
Cathy
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top