Variables in sql queries

[mojoes]

Hi again,

I have a sql query in which I have to substract the quert results. The parameter is

Datepart ("y", date (), "Monday")

The sql is a count of a table. When I run it in sql design view it runs fine but I have to manually type the parameter.

But when I use the vba code, it does not recognize the variable and I still have to type it.

How can I make the code to use the value that comes from the date formula?

[OBP]

So in the query have you got a field that contains the day of the Year?
How have you entered the "Parameter"?
Is it on the first "Criteria Row" or are you trying to do so with VBA code?

[mojoes]

I am trying to do it in vba code. This is what I have:

myday = DatePart("y", Date, vbMonday)

strsql = "SELECT" & myday& "- Count(t_desc2.idtaskname), Dailytask.TaskName FROM Dailytask INNER JOIN t_desc2 ON Dailytask.ID = t_desc2.IDTaskName GROUP BY Dailytask.TaskName, t_desc2.idtaskname"

[OBP]

The problem is you using "myday" as if it was a field in the table that the query is using a a record source.
Are you trying to use it as a parameter or criteria?

[mojoes]

I am trying to use it as a parameter, but everytime the query is run, it ask me to type it and I need it automatically

[OBP]

To use it as a parameter you have to include it along with the field name in a Where statement.
As an example

  SQL = "SELECT LabourRates.* " & _

    "FROM LabourRates " & _

    "WHERE Date1 <= #" & Format(Me.Date, "mm/dd/yyyy") & "#" & _

    "And Date2 > #" & Format(Me.Date, "mm/dd/yyyy") & "#"

or

SQL = "SELECT Inventory.* " & _

  "FROM Inventory " & _

  "WHERE TagNumber = '" & Me.TagNumber & "' "

[PhilS]

I am trying to use it as a parameter, but everytime the query is run, it ask me to type it and I need it automatically

The code you posted does not run the query at all. It just builds a SQL String. - BTW: Add a blank immediately after the SELECT.
Where/How do you run the query?
I suspect, you are not using the SQL you built, because that would have triggered a different error due to the missing space.

[PhilS]

To use it as a parameter you have to include it along with the field name in a Where statement.

You can use parameters or rather hardcoded values in the column list as well. Mojoes' approach is perfectly valid.

[mojoes]

I am trying to use it in the form_load for a dashboard so all calculations must be automatically done. not sure what you mean with hardcoded values *sorry*

[PhilS]

I am trying to use it in the form_load for a dashboard so all calculations must be automatically done. not sure what you mean with hardcoded values *sorry*

You are embedding the variable's value into the SQL String in VBA. From a pure SQL perspective this value then becomes a hardcoded valued in the SQL. - But that remark was mainly directed towards OBP, who brought up the term parameter, which he used somewhat imprecisely, not to say incorrectly.

My question to you, mojoes, remains: Where/How do you run the query?
I understand you do it somewhere inside a Form_Load event procedure, but please show the VBA code that (supposedly) does it.
I still suspect you do not run this SQL at all. There are a couple of problems with your SQL concatenation in VBA that are not in-line with the problem description you posted.

Here are the problems I spotted:
1. A missing space after SELECT
2. Another missing space after myday
3. The column t_desc2.idtaskname should not be in the GROUP BY. You are not using this column in the output, only its aggregation COUNT(t_desc2.idtaskname).

I created a tutorial (text and video) on building SQL Strings in VBA. I think this might be helpful to you.

[mojoes]

Hi! This is the code:

Option Compare Database
Dim myday, myweek, task As Integer
Dim strsql As String




Private Sub Form_Load()
Dim i As Integer


myday = DatePart("y", Date, vbMonday)
txtday.SetFocus
txtday.Text = myday


myweek = DatePart("ww", Date, vbMonday)
txtweek.SetFocus
txtweek.Text = myweek


strsql = "SELECT " & myday & " - Count(t_desc2.idtaskname), Dailytask.TaskName" & _
"FROM Dailytask INNER JOIN t_desc2 ON Dailytask.ID = t_desc2.IDTaskName" & _
"GROUP BY Dailytask.TaskName, t_desc2.idtaskname"


List19.RowSource = strsql


End Sub

Thanks for the tutorial

[OBP]

You are embedding the variable's value into the SQL String in VBA. From a pure SQL perspective this value then becomes a hardcoded valued in the SQL. - But that remark was mainly directed towards OBP, who brought up the term parameter, which he used somewhat imprecisely, not to say incorrectly.

My question to you, mojoes, remains: Where/How do you run the query?
I understand you do it somewhere inside a Form_Load event procedure, but please show the VBA code that (supposedly) does it.
I still suspect you do not run this SQL at all. There are a couple of problems with your SQL concatenation in VBA that are not in-line with the problem description you posted.

Here are the problems I spotted:
1. A missing space after SELECT
2. Another missing space after myday
3. The column t_desc2.idtaskname should not be in the GROUP BY. You are not using this column in the output, only its aggregation COUNT(t_desc2.idtaskname).

I created a tutorial (text and video) on building SQL Strings in VBA. I think this might be helpful to you.

Thank you for the vote of confidence.
May I bring to your attention post #1 and I quote
"The sql is a count of a table. When I run it in sql design view it runs fine but I have to manually type the parameter."
As you are obviously far more proficient at Access , SQL and VBA so I will leave you to it, on this and all the other posts asking for an not getting any help previously.

[PhilS]

Hi! This is the code:

Thank you, that was I was asking for.
You should still remove the t_desc2.idtaskname from the GOURP BY clause of the SQL, but otherwise the code looks good to me.

Do you still get the dialog to enter the parameter? (@OBP: Acknowledged, you did not bring up that term.)
If yes, then open your form in design view and look what's in the Rowsource property of your listbox. It should be empty if you always assign the Rowsource by code.

[mojoes]

It worked!! Thank you for you help