query a recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

first i appologize for reposting this. i posted this in the excel section,
and that was not my intention.

here is my question:

How do i query an open recordset. currently i have a saved query and then i
have a sql statement in VBA which queries the query. I would like to run the
first query in VBA and then have the second SQL statement query the first
recordset. is this possible??

the reason i want this is first of all, i am having problems with the
parameters. secondly, it is neater for me to have this way.

i will post the query and the recordset SQL

thanks,

sam


this is the query (saved as PPAllFctsRetentionQry):

Code:
SELECT DISTINCT PPYearlyIndex.PgpPayGroup, PPYearlyIndex.PgpPeriodStartDate,
PPYearlyIndex.PgpPeriodEndDate
FROM PPYearlyIndex
WHERE (((PPYearlyIndex.PayperiodIndex) Between
[Forms]![RetentionAllFrm]![cboPayperiodIndexStart] And
[Forms]![RetentionAllFrm]![cboPayperiodIndexEnd]) AND
((PPYearlyIndex.PayperiodYear) Between
[Forms]![RetentionAllFrm]![cboPayperiodYearStart] And
[Forms]![RetentionAllFrm]![cboPayperiodYearEnd]));

now i have this SQL statment in my VBA code which i would i refer to as my
second query:

Code:
strSQL = "SELECT PPAllFctsRetentionQry.PgpPayGroup,
Min(PPAllFctsRetentionQry.PgpPeriodStartDate) AS FirstStartDate,
Max(PPAllFctsRetentionQry.PgpPeriodStartDate) AS LastStartDate,
Min(PPAllFctsRetentionQry.PgpPeriodEndDate) AS FirstEndDate,
Max(PPAllFctsRetentionQry.PgpPeriodEndDate) AS LastEndDate " & _
" FROM PPAllFctsRetentionQry GROUP BY
PPAllFctsRetentionQry.PgpPayGroup;"
 
How do i query an open recordset. currently i have a saved query and then i
have a sql statement in VBA which queries the query. I would like to run the
first query in VBA and then have the second SQL statement query the first
recordset. is this possible??
Click to expand...

You can't really query a recordset. You can use the Find or Search functions (depending on what type of recordset you
have), or you can loop through the recordset using .MoveNext and examining the values of the recordset's fields, but you
can't query a recordset

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com
 
SAm said:
first i appologize for reposting this. i posted this in the excel section,
and that was not my intention.

here is my question:

How do i query an open recordset. currently i have a saved query and then i
have a sql statement in VBA which queries the query. I would like to run the
first query in VBA and then have the second SQL statement query the first
recordset. is this possible??

the reason i want this is first of all, i am having problems with the
parameters. secondly, it is neater for me to have this way.

i will post the query and the recordset SQL

thanks,

sam


this is the query (saved as PPAllFctsRetentionQry):

Code:
SELECT DISTINCT PPYearlyIndex.PgpPayGroup, PPYearlyIndex.PgpPeriodStartDate,
PPYearlyIndex.PgpPeriodEndDate
FROM PPYearlyIndex
WHERE (((PPYearlyIndex.PayperiodIndex) Between
[Forms]![RetentionAllFrm]![cboPayperiodIndexStart] And
[Forms]![RetentionAllFrm]![cboPayperiodIndexEnd]) AND
((PPYearlyIndex.PayperiodYear) Between
[Forms]![RetentionAllFrm]![cboPayperiodYearStart] And
[Forms]![RetentionAllFrm]![cboPayperiodYearEnd]));

now i have this SQL statment in my VBA code which i would i refer to as my
second query:

Code:
strSQL = "SELECT PPAllFctsRetentionQry.PgpPayGroup,
Min(PPAllFctsRetentionQry.PgpPeriodStartDate) AS FirstStartDate,
Max(PPAllFctsRetentionQry.PgpPeriodStartDate) AS LastStartDate,
Min(PPAllFctsRetentionQry.PgpPeriodEndDate) AS FirstEndDate,
Max(PPAllFctsRetentionQry.PgpPeriodEndDate) AS LastEndDate " & _
" FROM PPAllFctsRetentionQry GROUP BY
PPAllFctsRetentionQry.PgpPayGroup;"
Click to expand...


No, you can not query a recordset.

But I see no reason to use two queries, all you need is to
use the first query's WHERE clause in the second query. The
parameters can be dealt with something like this:

strSQL = "SELECT PgpPayGroup, " _
& "Min(PgpPeriodStartDate) AS FirstStartDate, " _
& "Max(PgpPeriodStartDate) AS LastStartDate, " _
& "Min(PgpPeriodEndDate) AS FirstEndDate, " _
& "Max(PgpPeriodEndDate) AS LastEndDate " _
& " FROM PPYearlyIndex " _
& "WHERE (PayperiodIndex Between " _
& cboPayperiodIndexStart _
& " And " & cboPayperiodIndexEnd _
& ") AND (PayperiodYear Between " _
& cboPayperiodYearStart _
& " And " & cboPayperiodYearEnd & ") " _
& "GROUP BY PgpPayGroup;"
 
Thanks a bunch. I think this is the real solution to my problem. i can't
believe that i oversaw it. I will try it, and i am confident it will work.

thanks,

sam


Marshall Barton said:
SAm said:
first i appologize for reposting this. i posted this in the excel section,
and that was not my intention.

here is my question:

How do i query an open recordset. currently i have a saved query and then i
have a sql statement in VBA which queries the query. I would like to run the
first query in VBA and then have the second SQL statement query the first
recordset. is this possible??

the reason i want this is first of all, i am having problems with the
parameters. secondly, it is neater for me to have this way.

i will post the query and the recordset SQL

thanks,

sam


this is the query (saved as PPAllFctsRetentionQry):

Code:
SELECT DISTINCT PPYearlyIndex.PgpPayGroup, PPYearlyIndex.PgpPeriodStartDate,
PPYearlyIndex.PgpPeriodEndDate
FROM PPYearlyIndex
WHERE (((PPYearlyIndex.PayperiodIndex) Between
[Forms]![RetentionAllFrm]![cboPayperiodIndexStart] And
[Forms]![RetentionAllFrm]![cboPayperiodIndexEnd]) AND
((PPYearlyIndex.PayperiodYear) Between
[Forms]![RetentionAllFrm]![cboPayperiodYearStart] And
[Forms]![RetentionAllFrm]![cboPayperiodYearEnd]));

now i have this SQL statment in my VBA code which i would i refer to as my
second query:

Code:
strSQL = "SELECT PPAllFctsRetentionQry.PgpPayGroup,
Min(PPAllFctsRetentionQry.PgpPeriodStartDate) AS FirstStartDate,
Max(PPAllFctsRetentionQry.PgpPeriodStartDate) AS LastStartDate,
Min(PPAllFctsRetentionQry.PgpPeriodEndDate) AS FirstEndDate,
Max(PPAllFctsRetentionQry.PgpPeriodEndDate) AS LastEndDate " & _
" FROM PPAllFctsRetentionQry GROUP BY
PPAllFctsRetentionQry.PgpPayGroup;"
Click to expand...


No, you can not query a recordset.

But I see no reason to use two queries, all you need is to
use the first query's WHERE clause in the second query. The
parameters can be dealt with something like this:

strSQL = "SELECT PgpPayGroup, " _
& "Min(PgpPeriodStartDate) AS FirstStartDate, " _
& "Max(PgpPeriodStartDate) AS LastStartDate, " _
& "Min(PgpPeriodEndDate) AS FirstEndDate, " _
& "Max(PgpPeriodEndDate) AS LastEndDate " _
& " FROM PPYearlyIndex " _
& "WHERE (PayperiodIndex Between " _
& cboPayperiodIndexStart _
& " And " & cboPayperiodIndexEnd _
& ") AND (PayperiodYear Between " _
& cboPayperiodYearStart _
& " And " & cboPayperiodYearEnd & ") " _
& "GROUP BY PgpPayGroup;"
Click to expand...
 
Back
Top