Build Dynamic Query SQL with Variables 1

[jdegeorge]

Hi:

I have a table with the following fields:

tblUser
UserID, User, 2003_Days, 2004_Days (etc.)

I want to build a dynamic query so that I can select either [2003_Days] or [2004_Days] based on the year of an input date.

I'm trying something like:

tblUser."["&str(year([InputDate])&"_Days"][\color]

but get error messages.

I would appreciate any help with this!

Jim DeGeorge

 

[Rick39]

Try:

tblUser.[" & CStr(Year([InputDate])& "_Days]"

 

[AskMan]

Create a empty query named myQRY

In a module, put this code

Public Sub DynamicQuery()
Dim strQuery as String, myValue as Date

On error goto error_handler ' I let you define it

MyValue = InputBox("Date ?&quot

mystr = "SELECT " & format(myValue,"yyyy&quot & "_Days FROM tblUser"

CurrentDb.QueryDefs("myQRY&quot.SQL = myStr

End sub

... the query is updated

 

[jobo123]

Hello Jim,

By the time I had figured this out you had already had some good responses but here it is anyway. I made a table with fields as you suggested:

UserID User 2003_Days 2004_Days
1 Jo 22 36
2 Bob 29 31
3 Tim 88 76

I then made the following parameter query:

SELECT tblUser.UserID, tblUser.User, IIf([Input Date]="2003","2003",IIf([Input Date]="2004","2004","Out of range&quot) AS [Year requested], IIf([Input Date]="2003",[tblUser]![2003_Days],IIf([Input Date]="2004",[tblUser]![2004_Days],"-&quot) AS Days
FROM tblUser;

When you run it, it asks for a year. If you enter 2003, it gives:

UserID User Year requested Days
1 Jo 2003 22
2 Bob 2003 29
3 Tim 2003 88

If you enter 2004, it gives:

UserID User Year requested Days
1 Jo 2003 36
2 Bob 2003 31
3 Tim 2003 76

If you enter a year with no data (e.g. 1999) it gives:

UserID User Year requested Days
1 Jo Out of range -
2 Bob Out of range -
3 Tim Out of range -

All the best

John Davy-Bowker
www.dbLetterWriter.com

 

[jdegeorge]

Hey, guys!

Thanks for responding so quickly. Star for Rick39 because he gave me the missing link to get my code to work. Enjoy the star!

Here's my actual Module code:

Function PersonalSummary()

Dim dbs As Database
Dim rsTemp As Recordset
Dim strSQL As String

Set dbs = CurrentDb()
strSQL = "SELECT tblInput.UserID, tblInput.InputDate, tblInput.InputText, First([FirstName] & ' ' & [LastName]) AS Employee, tblAllotment.[" & CStr(Year(Date)) & "_Days] AS vAllotment "
strSQL = strSQL + "FROM (tblInput INNER JOIN tblUser ON tblInput.UserID = tblUser.UserID) "
strSQL = strSQL + "INNER JOIN tblAllotment ON tblInput.UserID = tblAllotment.UserID "
strSQL = strSQL + &quot;WHERE(((tblInput.UserID) <> 26 And (tblInput.UserID) <> 4)) &quot;
strSQL = strSQL + &quot;GROUP BY tblInput.UserID, tblInput.InputDate, tblInput.InputText, tblAllotment.[&quot; & CStr(Year(Date)) & &quot;_Days] &quot;
strSQL = strSQL + &quot;ORDER BY tblInput.UserID, tblInput.InputDate, tblInput.InputText;&quot;

Set rsTemp = dbs.OpenRecordset(strSQL)

Set rsTemp = Nothing
Set dbs = Nothing

End Function

Now I just have to work on getting the rsTemp values into my form. I tried setting the form's Record Source to &quot;=PersonalSummary()&quot; but that didn't work.

How do I do this part? This is very new to me. Thanks!

Jim DeGeorge

 

[AskMan]

You can't define recordsource = PersonnalSummary because there no value defined for PersonnalSummary()

... I don't really understand all your code, but if this recordset is necessary, you can define the form's recordet using Form.Recordset = rsTemp.Name while the recordset is still defined.
...Else, don't define this recordset and just define Form.Recordsource = strSQL