Re: Translation of SQL for VBA

Aaahh...So just add another Replace string.

strSQL=Replace(strSQL, "OldCriteria","NewCriteria")

I can put this below the Replace TableName string. I'll try that, thanks!

"Ken Snell (MVP)" wrote:

> Not knowing what the criteria are in the SQL string, my first thought is
> that you would modify the criteria similarly to how your posted code
> modifies the tablename in the string.
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
>
> "Kou Vang" <KouVang@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:C1E90443-CDA1-4164-B3EE-63B72942308B@xxxxxxxxxxxxxxxx
> > Thanks for the help Ken! I think I'm almost there, this is getting closer
> > to
> > what I wanted. If I wanted to get into modifying the criteria of the
> > query,
> > would I then have to write out my SQL line and modify it in my string?
> > I'm
> > using a form with text and combo boxes as the values in the criteria of
> > the
> > Append Query. The code you have given me only changes the target table
> > correct? How do I access the criteria of the query now?
> >
> > "Ken Snell (MVP)" wrote:
> >
> >> Ahhhhhh... ok, what I gave you isn't going to work directly. Instead,
> >> you'll
> >> need to create a new query, evaluate the parameters in the "new" query,
> >> and
> >> then execute it.
> >>
> >> Let's change your code to this (I've also corrected the code, as
> >> TableDefs
> >> do not contain queries):
> >>
> >>
> >> Dim strSQL as String
> >> Dim dbf as Database
> >> Dim par as DAO.Parameter
> >> Dim qdf As DAO.QueryDef
> >>
> >> 'Set up object variables
> >> Set dbf = CurrentDb
> >>
> >> 'Get the SQL string for the query
> >> strSQL = dbf.QueryDefs("MyQueryName").SQL
> >>
> >> 'Replace the old name with the new name in the SQL
> >> strSQL = Replace(strSQL, "OldTableName", "NewTableName")
> >>
> >> 'Create temporary query
> >> Set qdf = dbf.CreateQueryDef("", strSQL)
> >>
> >> 'Evaluate the parameters in the new query
> >> For Each par in qdf.Parameters
> >> par.Value = Eval(par.Name)
> >> Next par
> >>
> >> 'Runs the append query with the selected table name
> >> qdf.Execute
> >>
> >> 'Clear object variables
> >> qdf.Close
> >> Set qdf = Nothing
> >> dbf.Close
> >> Set dbf = Nothing
> >>
> >> --
> >>
> >> Ken Snell
> >> <MS ACCESS MVP>
> >>
> >>
> >> "Kou Vang" <KouVang@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:5C13B9B1-F1FA-4928-B183-D6F1A7B0FD3D@xxxxxxxxxxxxxxxx
> >> > I'm trying to do it in VBA, so I can change the target table in my
> >> > append
> >> > query, and not have to make 4 or 5 different queries. Instead, I just
> >> > want
> >> > one query, while modifying the criteria and the target table of the
> >> > Append
> >> > Query. Someone gave me a start with:
> >> >
> >> > Dim strSQL as String
> >> > Dim dbf as Database
> >> > Dim tdfs as TableDefs
> >> > Dim tdf as TableDef
> >> >
> >> > 'Set up object variables
> >> > Set dbf = CurrentDb
> >> > Set tdfs = dbf.TableDefs
> >> > Set tdf = tdfs("MyQueryName")
> >> >
> >> > 'Get the SQL string for the query
> >> > strSQL = tdf.SQL
> >> > 'Replace the old name with the new name in the SQL
> >> > strSQL = Replace(strSQL, "OldTableName", "NewTableName")
> >> >
> >> > 'Runs the append query with the selected table name
> >> > dbf.Execute(strSQL)
> >> >
> >> > 'Note, we don't change the name in the original saved query so we don't
> >> > know
> >> > what it is next time we need it. The Execute method above is the same
> >> > as
> >> > running the query any other way and is about 5 times faster than then
> >> > RunSql
> >> > method.
> >> >
> >> > 'Destroy object variables
> >> > Set tdf = Nothing
> >> > Set tdfs = Nothing
> >> > Set dbf = Nothing
> >> >
> >> > So I was just trying to put my SQL string together and was having
> >> > problems.
> >> >
> >> > "Ken Snell (MVP)" wrote:
> >> >
> >> >> What are you doing with the SQL string when it's built? That can
> >> >> affect
> >> >> how
> >> >> you would handle this.
> >> >>
> >> >> In general, you would concatenate the actual value from the parameter
> >> >> into
> >> >> the string:
> >> >>
> >> >> MyString = "WHERE VcValue.Site=" & [forms]![VcValueSubform]![site] & _
> >> >> " AND VcValue.Year=" & [forms]![VcValueSubform]![txtSubyear]
> >> >>
> >> >> (above assumes that both fields are numeric).
> >> >>
> >> >> --
> >> >>
> >> >> Ken Snell
> >> >> <MS ACCESS MVP>
> >> >>
> >> >> "Kou Vang" <KouVang@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:AAA8A202-0E7F-4CE1-864D-ACFAB3317B8F@xxxxxxxxxxxxxxxx
> >> >> >I have not been able to find a translation of 2 conditions of a query
> >> >> >to
> >> >> >be
> >> >> > properly coded in VBA. I know this is pretty basic, but I'm
> >> >> > struggling.
> >> >> > How
> >> >> > do I say this in my strSQL string in VBA? Thanks.
> >> >> >
> >> >> > WHERE (((VcValue.Site)=[forms]![VcValueSubform]![site]) AND
> >> >> > ((VcValue.Year)=[forms]![VcValueSubform]![txtSubyear]));
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
.

Relevant Pages

  • RE: Multi select List box to filter query for editing
    ... Dim mFilter As String ... ' Complete string for filter to apply to query ...
    (microsoft.public.access.formscoding)
  • Programatically Changing Query Criteria
    ... Save your query in SQL. ... the following to determine your criteria. ... Dim strSQL As String ... MsgBox "There was a problem building the SQL String" ...
    (microsoft.public.access.macros)
  • Re: Selecting certain items for a report
    ... I created the query called tmpSelectProducts ... > highlights the Dim qdf as DAO.QueryDef line. ... > Dim StrWhere As String ... > End Sub ...
    (microsoft.public.access.forms)
  • RE: Recordset looping (and debug looping!)
    ... create a new query using the following SQL: ... I named it "Sndx" with a datatype of String. ... Dim rst As DAO.Recordset, strNames As String ... ' good name - add soundex code and save record ...
    (microsoft.public.access.formscoding)
  • Re: Dynamic Query, Using ListBox and VBA
    ... SELECT DISTINCT tblCompanies.strCompanyCountries FROM tblCompanies UNION ... Using a Microsoft Access Listbox to pass criteria to a query ... Dim MyDB As DAO.Database ... Dim strWhere As String ...
    (microsoft.public.access.modulesdaovba)