Question!!!!!!

[rupes_mahal]

Hi..

please please help.....im desperate

Knowing that an SQL statement will bring back 1 record only and I only require 1 field value, Can I assign the value of the SQL statement to a variable and if so what is the syntax in VB please.

You help will be much appreciated.

Thank you in advance

Ruby

[This message has been edited by rupes_mahal (edited 09-08-2001).]

[This message has been edited by rupes_mahal (edited 09-08-2001).]

 

[Rich]

What do you mean an SQL statement will only return one record? Mine returns as many as I ask it to, well sometimes. Please explain.

 

[rupes_mahal]

Well.. I'm trying to write an algorithm (for the 1st time!) in VB that will calculate a 'score' for each record in a table, based on values in another table. If I can execute an SQL statement that I know will return a value, I can then assign that value to a variable and do other things to derive the output....hope that makes scense!!! ...otherwise I will need to go on a bit more!!...

Thanks for your help

 

[Alexandre]

In VBA, if you open a recordset based on your query, you will be able to feed, say ,a variant with the content of whatever field from the record the pointer is set on.

Dim dbMyDatabase as Database
Dim rDAOMyRecords as DAO.Recordset
Dim strMyQuery as String
Dim vMyresult as Variant

strMyQuery = "(Here goes your SQL statment)"

dbMyDatabase = currentdb()
rDAOMyRecords = dbMyDatabase.OpenRecordset(strMyQuery , dbReadOnly)
'When opening a DAO recordset, the pointer is by default on the first
'record (if any). So if you are sure the record is unique, the following is sufficient
vMyresult = rDAOMyRecords ![MySQLfield]

Set dbMyDatabase = Nothing
Set rDAOMyRecords = Nothing

Alex

[This message has been edited by Alexandre (edited 09-08-2001).]

 

[rupes_mahal]

Alexandre, thanks for your reply.

Could you just let me know if my understandi g is correct...

The declarations I understand

dbMyDatabase = currentdb()
//where currentdb is the name of my database?

rDAOMyRecords = dbMyDatabase.OpenRecordset(strMyQuery , dbReadOnly)
//what are these 2 arguments (strMyQuery , dbReadOnly) and what do they do?

your response is appreciated...

 

[kaspi]

currentdb - a pointer to your database (write as you see)
strMyQuery - your SQL statement (string) - substitute it with your SQL
dbReadOnly - constant (write as you see),agument of the recordset open method

 

[Atomic Shrimp]

I know it's probably unprofessional and dirty, but this sort of situation is exactly where I usually decide to use DLookup.

 

[rupes_mahal]

Hi Mike...

Thank you for responding.

Could you please help me code this using DLookup. I have not used DLookup before.

Ruby

 

[Rich]

Use the code builder.