Getting Query result into VB variable

[Daniel Cardoso]

Hi everyone,

I'm working on a Access application.
When I click on a button on a particular form I would like
to run a SELECT query and retrieve the result of this
query into a variable for the button_clck() sub. Because I
need the value of this variable to make some tests after.

Any help would be really appreciated !
Thank you

 

[Andrew Smith]

If you're just after one value it's probably easiest to use a DLookup
function in your code, eg

Private Sub button_click()

dim strValue as String
dim strWhere as String

strWhere = "NameOfAField = 'Something'"
strValue = DLookup("NameOfAnotherField","NameOfTable",strWhere)
rest of code...

 

[Daniel Cardoso]

Thank you for the advice but what if strWhere contains
many criteria. What is syntax.

This what I've put and I have a "syntax error"

plan_stat = DLookup
("[plan_status]", "plan_tbl", "[plan_date] =" & Me![date]
& " AND [pad] =" & Me![pad])

Me! is a form where there are 2 combo boxes : [date] and
[pad]

Thanx in advance.

 

[Andrew Smith]

Firstly, "date" is not a good name for a combo box control as it could get
confused with the Access date function, so I would change the name of the
control to cboDate, or something similar. That said, I don't think this is
the problem here.

I would create a string variable, strWhere, and use that in the DLookup
function as that makes the code easier to read and debug.

In your example you don't say whether "pad" is a numerical or string value -
this makes a difference to the syntax. Assuming it is a string you need to
enclose the value in single or double quotation marks.

In SQL strings dates have to be enclosed in # marks, and have to be in US
date format of mm/dd/yyyy. If your regional settings are different from this
then you will get an error here.

I think you should call the combo box "pad" cboPad, and then change the code
to:

strWhere = "plan_date =" & Format(Me.cboDate,"\#mm\/dd\/yyyy\#") _
& " AND pad = '" & Me.cboPad & "'"
plan_stat = DLookup("plan_status","plan_tbl",strWhere)

This assumes that pad is a string value - omit the apostrophes if it's a
number.

Thank you for the advice but what if strWhere contains
many criteria. What is syntax.

This what I've put and I have a "syntax error"

plan_stat = DLookup
("[plan_status]", "plan_tbl", "[plan_date] =" & Me![date]
& " AND [pad] =" & Me![pad])

Me! is a form where there are 2 combo boxes : [date] and
[pad]

Thanx in advance.

-----Original Message-----
If you're just after one value it's probably easiest to use a DLookup
function in your code, eg

Private Sub button_click()

dim strValue as String
dim strWhere as String

strWhere = "NameOfAField = 'Something'"
strValue = DLookup ("NameOfAnotherField","NameOfTable",strWhere)
rest of code...




.