Using COUNT to query an Access database from VB6

[MarcMellor]

I am a science teacher with only amateur programing abilities trying to write an educational assessment progam in Visual Basic.
I am storing some test questions in an Access database that I will be able to retrieve according to parameters such as "subject", "module", "level of difficulty" etc which are fields in my questions database. I also need to deposit the number of questions available into a text box via the variable, "QuestionCount". What I have so far is the following code for a Button1_Click event:
Dim QuestionCount As Integer
Data1.RecordSource = "SELECT* COUNT AS QuestionCount FROM_
QuestionsDatabase WHERE Subject = 'Biology' AND WHERE_
Module = '2' AND WHERE Level = '1'"
TextBox1.Text = QuestionCount
This just produces a 0 in the textbox and variations on the above don't seem to make any difference.
Also, do I need a separate datacontrol linked to the same database to be able to do these 2 things:
- display the number of questions available in one textbox
- toggle through questions available in another text box
(both according to the above selected parameters)?
Or is there a way of using just one data control?
If there is anyone who can help I would be most appreciative as I am stuck on this one and cannot move on to all the other problems that need attention!
Cheers.

 

[fluteplr]

The proper syntax for your select statment is

Data1.RecordSource = "SELECT COUNT(*) AS QuestionCount FROM_
QuestionsDatabase WHERE Subject = 'Biology' AND WHERE_
Module = '2' AND WHERE Level = '1'"

 

[MarcMellor]

Thanks flutplr,
I've just given it a try but I'm afraid it still gives a zero in the text box. I've run a debug and it is showing the value of QuestionCount as zero which I can't really understand. Help!

 

[SQLBill]

Marc,

I suggest posting your query in both an Access Forum and a Visual Basic forum; not the Microsoft SQL Server forum. You might get better help from one of those.