query a recordset

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;"
 
S

Scott McDaniel

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
 
M

Marshall Barton

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;"
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query a recordset 2
Open recordset on parameterized query 7
recordset on a query 1
howto Create recordset in VBA using SQL statement as string 5
How to write an append query on a recordset 1
How to do an append query to a table from a recordset 2
How to write a parameter query in recordset 3
query a recordset 2

Top