If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Year query based on text
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 |
#2
|
|||
|
|||
Year query based on text
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/Ap****ueBadWord.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "cathyt" wrote in message ... 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 |
#3
|
|||
|
|||
Year query based on text
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" wrote: 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/Ap****ueBadWord.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "cathyt" wrote in message ... 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 . |
#4
|
|||
|
|||
Year query based on text
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "cathyt" wrote in message ... 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" wrote: 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/Ap****ueBadWord.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "cathyt" wrote in message ... 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 . |
Thread Tools | |
Display Modes | |
|
|