Change Form/Report Query Based on User Selection
in [Queries]
|
From: Wayne on 7 Dec 2009 13:25 I have a form and report based on a query. I need to change the query based on what the user selects on a form. Using VBA in MS Access 2002, can I change a single field in an existing query from GROUP BY to LAST? This process would be initiated by a click event on a form. Thanks in advance for any help.
From: John Spencer on 7 Dec 2009 15:04 I would say only if you are willing to construct the entire query string in VBA and then apply that string as the source of the report. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Wayne wrote: > I have a form and report based on a query. I need to change the query > based on what the user selects on a form. Using VBA in MS Access > 2002, can I change a single field in an existing query from GROUP BY > to LAST? This process would be initiated by a click event on a form. > Thanks in advance for any help.
From: Wayne on 8 Dec 2009 13:58 On Dec 7, 12:04 pm, John Spencer <spen...(a)chpdm.edu> wrote: > I would say only if you are willing to construct the entire query string in > VBA and then apply that string as the source of the report. > > John Spencer > Access MVP 2002-2005, 2007-2009 > The Hilltop Institute > University of Maryland Baltimore County > > > > Wayne wrote: > > I have a form and report based on a query. I need to change the query > > based on what the user selects on a form. Using VBA in MS Access > > 2002, can I change a single field in an existing query from GROUP BY > > to LAST? This process would be initiated by a click event on a form. > > Thanks in advance for any help.- Hide quoted text - > > - Show quoted text - Thanks. Putting the SQL into a string seems to be the best way to do it but now I have a new problem. When I change the query GROUP BY field to LAST in VBA the Control Source name changes from ServiceFrom to LastOfServiceFrom. On the form I have tried many ways to display both fields like: =IIf(IsError([LastOfServiceFrom]), [ServiceFrom],[LastOfServiceFrom]) This displays the [LastOfServiceFrom] correctly but gives an #ERROR when the query is changed to the [ServiceFrom] field. =IIf(IsError([ServiceFrom]),[LastOfServiceFrom],[ServiceFrom]) This displays the same as the above???? I thought it would be the opposite but its not. Only the [LastOfServiceFrom] displays correctly. Weird. How can I get both field names to display correctly in my form when the Record Source query is changed?
From: John Spencer on 8 Dec 2009 14:26 You could alias the name in the query you are building so the name is consistent. SELECT Last(ServiceFrom) as ServiceStart FROM SomeTable SELECT ServiceFrom as ServiceStart FROM SomeTable GROUP BY ServiceFrom There are other ways, but they tend to cause parameter prompts to occur. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Wayne wrote: > On Dec 7, 12:04 pm, John Spencer <spen...(a)chpdm.edu> wrote: >> I would say only if you are willing to construct the entire query string in >> VBA and then apply that string as the source of the report. >> >> John Spencer >> Access MVP 2002-2005, 2007-2009 >> The Hilltop Institute >> University of Maryland Baltimore County >> >> >> >> Wayne wrote: >>> I have a form and report based on a query. I need to change the query >>> based on what the user selects on a form. Using VBA in MS Access >>> 2002, can I change a single field in an existing query from GROUP BY >>> to LAST? This process would be initiated by a click event on a form. >>> Thanks in advance for any help.- Hide quoted text - >> - Show quoted text - > > Thanks. Putting the SQL into a string seems to be the best way to do > it but now I have a new problem. > > When I change the query GROUP BY field to LAST in VBA the Control > Source name changes from ServiceFrom to LastOfServiceFrom. On the > form I have tried many ways to display both fields like: > > =IIf(IsError([LastOfServiceFrom]), [ServiceFrom],[LastOfServiceFrom]) > This displays the [LastOfServiceFrom] correctly but gives an #ERROR > when the query is changed to the [ServiceFrom] field. > > =IIf(IsError([ServiceFrom]),[LastOfServiceFrom],[ServiceFrom]) > This displays the same as the above???? I thought it would be the > opposite but it�s not. Only the [LastOfServiceFrom] displays > correctly. Weird. > > How can I get both field names to display correctly in my form when > the Record Source query is changed?
|
Pages: 1 Prev: Displaying Yes or No rather than 0 or 1 Next: ADODB.Connection to Teradata Timesout |