Obvious Newbile Question--How do I create a variable within SQL?

I'm making an online Quiz and I have a table with a list of Category names associated with CategoryIDs that remains static.
Another table collects data associated with these categories, and in addition to the data, contains the CategoryID for the data.

I want to populate the select options of a drop down menu with the Categories NOT YET ENTERED for a specific Quiz. I have a form which posts to itself.

SQL 7, ASP Javascripting goin' on.

I did some more looking around yesterday and came up with something that works, I just have to determine how to create a variable in SQL and then pass the CaseStudyID from an ASP page...

SELECT DISTINCT
tblDetailCaseStudyCategory.intDetailCaseStudyCateg oryID,
tblDetailCaseStudyCategory.strCaseStudyCategory
FROM tblDetailCaseStudyCategory, tblCaseStudyData
WHERE (tblDetailCaseStudyCategory.intDetailCaseStudyCate goryID NOT
IN
(SELECT intCaseStudyCategory
FROM tblCaseStudyData
WHERE intCaseStudyID = 69))

Can anyone help with this?
Thanks,Are you using a recordset object in your asp code to loop through to create the options ? Since you have a form that posts to itself, you can use that to pass the id that you need to populate the listbox - that loops through the recordset.

obtain a date from week data

Hi all,

I have a problem with date functions...

In my program I use weeks, and with this variable I need to know which is the first day of the selected week...

For example... I put week 52 in my program... how I can obtain the first day of this week? -> (22/12/2003).

I have proved with this functions...

SET DATEFIRST 1 (to configure Monday as first day of week)

SELECT DATEADD(ww,DATEDIFF(ww,0,GETDATE()),0)

With this I have the day of the current week... but I can't put my week in this function... aarrgggg.

Please help me...

Thanks a lot.declare @.week tinyint, @.firstdate varchar(4)
-- Choose the week number
set @.week = 30
-- Choose the year
set @.firstdate = '2003-01-01'
select dateadd(day, (datepart(weekday, @.firstdate) - 1) * -1, dateadd(week, @.week, @.firstdate))|||Originally posted by joejcheng
declare @.week tinyint, @.firstdate varchar(10)
-- Choose the week number
set @.week = 30
-- Choose the year
set @.firstdate = '2003-01-01'
select dateadd(day, (datepart(weekday, @.firstdate) - 1) * -1, dateadd(week, @.week, @.firstdate))|||Simply Outstanding ... why did I not think of it first ?? :)

Object variable or With block variable not set (was "Frank")

When trying to upsize an access database to sql server using the upsize wizard, I get the following error:
"Object variable or With block variable not set."
Any assistance is greatly appreciated.sounds like an error in the vb code. try compiling in the vba window in access and see if it kicks out any errors. i have never used the upsizing wizard and I am not sure why sql server would care about the VBA|||Upsizing wizard creates an updateable recordset using DAO (!!!) and then attempts to insert rows using rs.AddNew...rs.Update sequence within a loop over the source. The error posted means that there was a failure to create a target recordset, most likely due to datatype incompatibility (this is all most likely unless some silly things are happaning like intermittent network connectivity failures and such).|||Thanks all for most helpful comments. Not too sure how to correct this in order to get a target recordset in that this is my first time doing this.

Any further comments welcomed.

Thanks once again for your time and effort in assistingme.

Object variable or With block variable not set

I was wondering if you have any knowledge of what I can be doing wrong.
Basically, here is what I want to do in SQL Reporting Services:
If field: Fields!Opportunity_Won_Loss_Open.Value = "Open" then SUM the
value for this field: Fields!Opportunity_EstRev
Here is the syntax I am using:
=SUM(Fields!Opportunity_EstRev.Value
(iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",
Fields!Opportunity_Won_Loss_Open.Value, Nothing)))
When I run it, it returns an error: "Object variable or With block
variable not set."
I know independently that each separate syntax works because I have
them working in the same report:
=SUM(Fields!Opportunity_EstRev.Value)
=COUNT(iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",
Fields!Opportunity_Won_Loss_Open.Value, Nothing))
When I try to combine them, they don't work. Logically, they make
sense to me combined, but I am sure I am messing up the syntax
somewhere. Would you have an idea? Any help would be greatly
appreciated
try:
=SUM(iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",
Fields!Opportunity_EstRev.Value, 0))
=COUNT(iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",1, Nothing))
=SUM(iif(Fields!Opportunity_Won_Loss_Open.Value = "Open", 1, 0))
-oj
<hendrix.craig@.gmail.com> wrote in message
news:1144162065.659636.72790@.j33g2000cwa.googlegro ups.com...
>I was wondering if you have any knowledge of what I can be doing wrong.
> Basically, here is what I want to do in SQL Reporting Services:
> If field: Fields!Opportunity_Won_Loss_Open.Value = "Open" then SUM the
> value for this field: Fields!Opportunity_EstRev
> Here is the syntax I am using:
> =SUM(Fields!Opportunity_EstRev.Value
> (iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",
> Fields!Opportunity_Won_Loss_Open.Value, Nothing)))
> When I run it, it returns an error: "Object variable or With block
> variable not set."
> I know independently that each separate syntax works because I have
> them working in the same report:
> =SUM(Fields!Opportunity_EstRev.Value)
> =COUNT(iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",
> Fields!Opportunity_Won_Loss_Open.Value, Nothing))
> When I try to combine them, they don't work. Logically, they make
> sense to me combined, but I am sure I am messing up the syntax
> somewhere. Would you have an idea? Any help would be greatly
> appreciated
>

Object variable or With block variable not set

I was wondering if you have any knowledge of what I can be doing wrong.
Basically, here is what I want to do in SQL Reporting Services:
If field: Fields!Opportunity_Won_Loss_Open.Value = "Open" then SUM the
value for this field: Fields!Opportunity_EstRev
Here is the syntax I am using:
=SUM(Fields!Opportunity_EstRev.Value
(iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",
Fields!Opportunity_Won_Loss_Open.Value, Nothing)))
When I run it, it returns an error: "Object variable or With block
variable not set."
I know independently that each separate syntax works because I have
them working in the same report:
=SUM(Fields!Opportunity_EstRev.Value)
=COUNT(iif(Fields!Opportunity_Won_Loss_O
pen.Value = "Open",
Fields!Opportunity_Won_Loss_Open.Value, Nothing))
When I try to combine them, they don't work. Logically, they make
sense to me combined, but I am sure I am messing up the syntax
somewhere. Would you have an idea? Any help would be greatly
appreciatedtry:
=SUM(iif(Fields!Opportunity_Won_Loss_Ope
n.Value = "Open",
Fields!Opportunity_EstRev.Value, 0))
=COUNT(iif(Fields!Opportunity_Won_Loss_O
pen.Value = "Open",1, Nothing))
=SUM(iif(Fields!Opportunity_Won_Loss_Ope
n.Value = "Open", 1, 0))
-oj
<hendrix.craig@.gmail.com> wrote in message
news:1144162065.659636.72790@.j33g2000cwa.googlegroups.com...
>I was wondering if you have any knowledge of what I can be doing wrong.
> Basically, here is what I want to do in SQL Reporting Services:
> If field: Fields!Opportunity_Won_Loss_Open.Value = "Open" then SUM the
> value for this field: Fields!Opportunity_EstRev
> Here is the syntax I am using:
> =SUM(Fields!Opportunity_EstRev.Value
> (iif(Fields!Opportunity_Won_Loss_Open.Value = "Open",
> Fields!Opportunity_Won_Loss_Open.Value, Nothing)))
> When I run it, it returns an error: "Object variable or With block
> variable not set."
> I know independently that each separate syntax works because I have
> them working in the same report:
> =SUM(Fields!Opportunity_EstRev.Value)
> =COUNT(iif(Fields!Opportunity_Won_Loss_O
pen.Value = "Open",
> Fields!Opportunity_Won_Loss_Open.Value, Nothing))
> When I try to combine them, they don't work. Logically, they make
> sense to me combined, but I am sure I am messing up the syntax
> somewhere. Would you have an idea? Any help would be greatly
> appreciated
>