Is it Possible in a Select Query????

[quest4]

Is it possible to use a numeric variable in a access2000 Select Query? I have part of a WHERE clause which is:

(Att.DateWeekStarting Between
Nz([Enter Start Date],DateAdd("ww",-14,Date()))
And Dateadd("ww",13,Nz([Enter Start Date],DateAdd("ww",-14,Date()))))

What I would like to do is use a numeric variable for the 13 week duration of the BETWEEN. I would like to be able to add to or subtract from that variable. Is this a doable possability in Access? If so, how do you decalare a numeric variable in Access? Thanks you very much in advance for anyone who responses to this question.

 

[Sawatzky]

Dear quest4

as I didn't get all of your Code snippet : nz() ???
I give you another example and hope it helps

this sql-statement lets pop-up an parameter entry-box, that lets you enter [number]

SELECT Tabelle1.id, Tabelle1.text
FROM Tabelle1
WHERE (((Tabelle1.id) Between [number] And [number]+2));


the text in the 2 bracket pairs must equal each other but can be any text you like.

If you want to read the value out of a form you just have to reference the form's control instead.


regards Astrid

 

[quest4]

Thankk you for responding so quickly. The code is part of a WHERE clause and it basically brings up a window to enter a date, with a defult of one week and the it set the end date to 13 weeks earilier than the entered date. That duration, 13 weeks, I would like to change to a numeric variable, but still have it, the 13 weeks that is, as a default. I hope that helps to make things a little clearer on what I would like to do.

 

[Sawatzky]

Dear quest4,

It is a bit tricky with these dynamic queries in Access.

what you could do is: If you have a form open, when you need the query. You could make a textbox on that form.
Put its Visible Property to false and enter there the 13 you want as default. and you put the reference to that textbox into your query

Then you can change it that form, even from code.

This a really a quick and dirty solution , I suppose (shame on me)

but I didn't find another way to do it.


Regards Atrid

 

[quest4]

I am not using a form for this query. It will eventually output to a report, a list of employee's names. If I can use a numeric variable for the 13 week period, then that opens the doors for me to be able to finish this little monster that I have been playing with, it is a select query. Thanks for the effort, but I think we are thinking in two different planes.

 

[tlbroadbent]

You can get the value from a parameter like you get the date.

(Att.DateWeekStarting Between
Nz([Enter Start Date], DateAdd("ww", -1-[Enter the Period], Date()))
And Dateadd("ww", [Enter the Period], Nz([Enter Start Date], DateAdd("ww", -1-[Enter the Period], Date()))))

You can also create a function in VB to provide the value. When you want a different value, change the function.

Function GetNumWeeks() As Integer
GetNumWeeks = 13
End Function

Use it in the query just like the parameter.

(Att.DateWeekStarting Between
Nz([Enter Start Date], DateAdd("ww",-1-GetNumWeeks(), Date()))
And Dateadd("ww",[Enter the Period], Nz(GetNumWeeks(), DateAdd("ww",-1-GetNumWeeks(), Date())))) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[quest4]

Thanks for the response, I have been pulling my hair out trying to get function to work, but after adding your code, I get the samething that I got with mine, "Invalid SQL Statement". I have had someluck with paramater thought, here is what I came up with:
PARAMETERS [RptDur] Long;
SELECT Att.EmployeeNumber,
Emp.NameFirst, Emp.NameInitial,
Emp.NameLast,
COUNT(Att.EmployeeNumber) AS RecCnt
FROM tblEmployees AS Emp LEFT JOIN tblAttendence
AS Att ON Emp.EmployeeNumber=Att.EmployeeNumber
WHERE ([RptDur]=-14)
And (Emp.EmpStatusType="Active" And
Emp.PayType="per Hour&quot
And(Att.DateWeekStarting Between Nz([Enter Start Date],DateAdd("ww",[RptDur],Date()))
And Dateadd("ww",(([RptDur]*-1)-1),Nz([Enter Start Date],DateAdd("ww",[RptDur],Date()))))
The thing is that I still get a pop-up window, I would like it to use the default and no pop-up. Thank you, again, for all of your help.

 

[tlbroadbent]

If you don't want a pop-up wndow, don't use a parameter. You'll have to use the function call instead. Or just hard code the value. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[quest4]

No problem about using the function, except it has not worked for me, yet. Maybe I am putting it in the wrong place, currently, I have tried putting it at the top of the query, like the Parameter clause, is that where it is supposed to be? I have never work ed with the Function clause, yet. Thanks for your assistance, and in my hind-sight I now see what you mean, well at least I am trying things and learning, too.

 

[tlbroadbent]

You must first create the function in a VB module. Then you enter the name just like I did in the example. If the function is GetRtpDur then replace the parameter, [RptDur], with GetRptDur() in the query. That's all there is to it. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[quest4]

I went to the modules tab and created a new module called modGetNumWeeks. I entered the function, just as it was typed and saved it. I then made the changes to the query and ran it. I got and error message which said:
"Wrong number of arguments used with function in query expression" I did do the query first and then the module, would that matter? If not any ideas where I should start looking? Thanks for the help.

 

[tlbroadbent]

There should be no parameters for the function. So either you inluded parameters in the module and not in the query or vice-versa. If you can't find the error, please post both the module and the query. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[quest4]

I did delete the parameter clause completely. Now the query starts out SELECT. The function was put in a module just the three lines you wanted me to put in it. The DateWeekStarting clause was modified exactly the way myou told me. So I guess it back to new postings again.

 

[quest4]

Not trusting anything anymore, I tried that previous cut and paste trick and again it worked, almost. I am not picking up the default, but other wise it is working. Thanks alot.