Need help using QueryDef to create SQL

[AaronC]

Any help with this is appreciated, I'm treading new territory here:

I am creating a QueryDef object so that I can dynamically build a simple select statement using a value entered on a form as the field to return. It builds my SQL statement just fine (as I see in the MsgBox display). It produces:

SELECT WkndAbs_Small.[02/12/05]
FROM WkndAbs_Small;

But, when it tries to execute the query it asks for the user to 'Enter Parameter Value'.

I am using the following code:

'*** Create QueryDef Object with SQL
Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL =
"SELECT WkndAbs_Small.[" & Forms!WeekendDatesEntry!SAT1_ENTRY & "] FROM WkndAbs_Small"

MsgBox (strSQL)

Set qdf = dbs.CreateQueryDef("Sat1 Test", strSQL)

'*** Execute SQL Statement
Dim stDocName As String
stDocName = "Sat1 Test"
DoCmd.OpenQuery stDocName, acNormal, acEdit

The value from SAT1_ENTRY on the WeekendDatesEntry form is the field name for the table WkndAbs_Small (the field name is a date value). The CreateQueryDef creates it As Expr1, thereby wanting a parameter?

If I execute it manually, it works just fine. Please help if you can! Thanks!

 

[modest]

Set qdf = dbs.CreateQueryDef("Sat1 Test", strSQL)

Change to:

qdf=dbs.CreateQueryDef("Sat1 Test")
qdf.SQL = strSQL

 

[AaronC]

Stills requests user to 'Enter Parameter Value"

Thanks, but to no avail. Still prompts for user to enter a parameter value. When you view the generated query in Design View, it shows the field to retrieve:

Expr1: WkndAbs_Small.[2/12/2005]

 

[modest]

strSQL = [B]_ 'notice the underscore[/B]
"SELECT WkndAbs_Small.[" & Forms!WeekendDatesEntry!SAT1_ENTRY & "] FROM WkndAbs_Small"

MsgBox [B]strSQL[/B]

Make changes to bold

WkndAbs_Small.[2/12/2005]

[WkndAbs_Small] must be a table
[2/12/2005] must be a valid field name (this is probably where your problem is. you're probably leaving out a space or something small.

Make sure you are spelling both correctly and not using underscores when you shouldn't be.

Next:
Define variables as either DAO.Database,DAO.QueryDef or ADO.Database,ADO.QueryDef (preference is DAO).
And:
set qdf = db.QueryDefs("Sat1 Test")

 

[modest]

Also, make sure you actually have data in your table. Furthermore, a field name cannot be a "date" it can only be text -... it may look like a date, but it is still text.

If none of the suggestions work attach the database as a zip.

 

[AaronC]

Thanks Modest

Thanks for your help...things are working fine now