Assigning the results of a query to a variable (cont) 
Author Message
 Assigning the results of a query to a variable (cont)

Dear Nolan:

Alex is right as one way to do it.  Basically, the OpenRecordset method is
NOT going to accept a QueryDef as its first parameter.  It will accept a
string, such as the name of a table or saved query, or the SQL string
itself.  You can also try omitting:

Set qdf = db.CreateQueryDef("PriorMonth", strSQL)

and just use:

Set rs = db.OpenRecordset(strSQL)

or keep the qdf and use:

Set rs = db.OpenRecordset("PriorMonth")

Also, please not there would be problems creating the "PriorMonth" saved
query the second time around after it already exists!

Tom Ellison

Quote:

> Based on a couple of earlier exchanges, I've created the code below.
> When I compile I get an error message "Type mismatch" and OpenRecordset
> is highlighted.  I'm sure my mistake is very simple and I'd appreciate
> being point in the direction of how to correct it.

> TIA,
> Nolan Madson
> ------------------

> Sub CreateNewHistoryTable()
> Dim db As Database
> Dim rs As Recordset
> Dim strSQL As String
> Dim vYearMonth As String
> Dim qdf As QueryDef

> Set db = CurrentDb
> strSQL = "SELECT Max(tblDates.PriorMonth) AS MaxOfPriorMonth FROM
> tblDates"
> Set qdf = db.CreateQueryDef("PriorMonth", strSQL)

> Set rs = db.OpenRecordset(qdf)

> vYearMonth = rs!MaxOfPriorMonth
> End Sub

> Sent via Deja.com http://www.*-*-*.com/
> Before you buy.



Fri, 03 May 2002 03:00:00 GMT  
 Assigning the results of a query to a variable (cont)
Based on a couple of earlier exchanges, I've created the code below.
When I compile I get an error message "Type mismatch" and OpenRecordset
is highlighted.  I'm sure my mistake is very simple and I'd appreciate
being point in the direction of how to correct it.

TIA,
Nolan Madson
------------------

Sub CreateNewHistoryTable()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim vYearMonth As String
Dim qdf As QueryDef

Set db = CurrentDb
strSQL = "SELECT Max(tblDates.PriorMonth) AS MaxOfPriorMonth FROM
tblDates"
Set qdf = db.CreateQueryDef("PriorMonth", strSQL)

Set rs = db.OpenRecordset(qdf)

vYearMonth = rs!MaxOfPriorMonth
End Sub

Sent via Deja.com http://www.deja.com/
Before you buy.



Sat, 04 May 2002 03:00:00 GMT  
 Assigning the results of a query to a variable (cont)
try to use
Set rs = qdf.OpenRecordset()

--

Best regards,
___________
Alex Dybenko
Point Limited
Home Page: http://www.geocities.com/SiliconValley/Heights/5091/
Moscow MS Access User Group
http://www.arimsoft.ru/msaccess

Quote:

> Based on a couple of earlier exchanges, I've created the code below.
> When I compile I get an error message "Type mismatch" and OpenRecordset
> is highlighted.  I'm sure my mistake is very simple and I'd appreciate
> being point in the direction of how to correct it.

> TIA,
> Nolan Madson
> ------------------

> Sub CreateNewHistoryTable()
> Dim db As Database
> Dim rs As Recordset
> Dim strSQL As String
> Dim vYearMonth As String
> Dim qdf As QueryDef

> Set db = CurrentDb
> strSQL = "SELECT Max(tblDates.PriorMonth) AS MaxOfPriorMonth FROM
> tblDates"
> Set qdf = db.CreateQueryDef("PriorMonth", strSQL)

> Set rs = db.OpenRecordset(qdf)

> vYearMonth = rs!MaxOfPriorMonth
> End Sub

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Sat, 04 May 2002 03:00:00 GMT  
 Assigning the results of a query to a variable (cont)
Nolan,

Maybe I don't understand your question, but it seems to me that all this
could be accomplished with just the one line:

   vYearMonth = DMax("PriorMonth", "tblDates")

Sorry if this just confuses your real issue,
Marsh

Quote:

>Based on a couple of earlier exchanges, I've created the code below.
>When I compile I get an error message "Type mismatch" and OpenRecordset
>is highlighted.  I'm sure my mistake is very simple and I'd appreciate
>being point in the direction of how to correct it.

>TIA,
>Nolan Madson
>------------------

>Sub CreateNewHistoryTable()
>Dim db As Database
>Dim rs As Recordset
>Dim strSQL As String
>Dim vYearMonth As String
>Dim qdf As QueryDef

>Set db = CurrentDb
>strSQL = "SELECT Max(tblDates.PriorMonth) AS MaxOfPriorMonth FROM
>tblDates"
>Set qdf = db.CreateQueryDef("PriorMonth", strSQL)

>Set rs = db.OpenRecordset(qdf)

>vYearMonth = rs!MaxOfPriorMonth
>End Sub

>Sent via Deja.com http://www.deja.com/
>Before you buy.

  -----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------
   http://www.newsfeeds.com       The Largest Usenet Servers in the World!
------== Over 73,000 Newsgroups - Including  Dedicated  Binaries Servers ==-----


Sat, 04 May 2002 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Assigning the results of a query to a variable

2. Assign result of qry to variable

3. SQL result assigned to variable - HOW?

4. Assign result of qry to variable

5. assigning query return to a variable

6. assign NULL values from query to variables

7. assign NULL values from query to variables

8. Modules: setting a variable in memory to the results of a query (EXCEL AUTOMATION)

9. Placing Result of Query into Variable

10. Using a query result as a variable in code

11. how do I get an sql query to return result to a variable

12. Saving results of query into variables with multiple types

 

 
Powered by phpBB® Forum Software