Variable

[Arpan]

I have a MS-Access table wherein two of the columns are named 'Col1' &
'Col2'. Assume that one of the records under the 'Col1' column is 25 &
its corresponding record under the 'Col2' column is 60. Consider the
following query:

PARAMETERS
[ENTER Col1] Short;

SELECT Col2 FROM MyTable WHERE (((MyTable.Col1)=[ENTER Col1]));

As such the above query works fine & when executed, asks the user to
enter a value for 'Col1' & accordingly retrieves the corresponding
'Col2' record. For e.g. if the user enters 25 as the 'Col1' parameter,
then the above query returns 60.

I want to assign the 'Col2' record returned by the query (which is 60
in the above example) to a variable. Depending upon the variable value,
using IF...ELSE conditions, I want to perform different operations.
This is how I have tried to assign the above SELECT statement to a
variable named 'MyVariable':

MyVariable = SELECT Col2 FROM MyTable WHERE (((MyTable.Col1)=[ENTER
Col1]));

But the above throws this error:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT' or 'UPDATE'.

Instead of using a variable, I also tried using the SELECT query
directly in a 'IF' condition in this way:

IF (SELECT Col2 FROM MyTable WHERE (((MyTable.Col1)=[ENTER Col1])))

but this too generates the above error.

How do I overcome this error?

 

[Jerry Porter]

Arpan,


Option 1:

A simpler method would use DLookup instead of a query:

Dim sTemp as string
Dim lngCol1 as Long 'or whatever the right data type is
Dim lngCol2 as Long

sTemp = InputBox("Enter Col1")) 'Prompt user for col1
If IsNumeric(sTemp) Then
lngCol1 = Val(sTemp)
lngCol2 = Dlookup("col2","MyTable","col1=" & lngCol1) 'Look up col2
based on col1
End If


Option 2:

If you do want to get data from a query into a variable, you need to
create a recordset. For example:

Dim rs as DAO.recordset
Dim lngCol2 as Long

set rs = Currentdb.OpenRecordset("MyQuery",dbOpenSnapshot)
lngCol2 = rs!col2

rs.close
set rs = nothing

This assumes you have saved the query as My Query.

Jerry
Personable PC Solutions
www.personablepc.com

 

[Arpan]

Thanks Jerry for the suggestions. I will give them a try. The second
option you have suggested - in that the code snippet that you have
shown, that code snippet has to be put in a module, isn't it? Or should
it be in the query iteself?

To be very honest, my knowledge in MS-Access is limited.

Could you please clarify another doubt? IIF can be used in queries to
evaluate conditions, isn't it? For e.g. something like this:

= IIf([OrderAmount] >= 1000, "Large", "Small")

Can't I use the SELECT or INSERT SQL (or other DML) statements instead
of the strings? For e.g. something like this:

= IIf([OrderAmount] >= 1000, "SELECT * FROM MyTable WHERE OrderAmount >
1000", "UPDATE MyTable SET OrderAmount = 1000 WHERE OrderAmount <
1000")

In other words, what I would like to do is supply a input parameter
value to the query. If the input parameter value happens to be greater
than or equal to 1000, then the query should retrieve all records where
OrderAmount is greater than or equal to 1000. On the other hand, if the
input parameter value supplied to the query is less than1000, then I
want to update all the OrderAmount records that are less than 1000 to
1000.

Can this be done in MS-Access?

Arpan,

Option 1:

A simpler method would use DLookup instead of a query:

Dim sTemp as string
Dim lngCol1 as Long 'or whatever the right data type is
Dim lngCol2 as Long

sTemp = InputBox("Enter Col1")) 'Prompt user for col1
If IsNumeric(sTemp) Then
lngCol1 = Val(sTemp)
lngCol2 = Dlookup("col2","MyTable","col1=" & lngCol1) 'Look up col2
based on col1
End If

Option 2:

If you do want to get data from a query into a variable, you need to
create a recordset. For example:

Dim rs as DAO.recordset
Dim lngCol2 as Long

set rs = Currentdb.OpenRecordset("MyQuery",dbOpenSnapshot)
lngCol2 = rs!col2

rs.close
set rs = nothing

This assumes you have saved the query as My Query.

Jerry
Personable PC Solutionswww.personablepc.com

I have a MS-Access table wherein two of the columns are named 'Col1' &
'Col2'. Assume that one of the records under the 'Col1' column is 25 &
its corresponding record under the 'Col2' column is 60. Consider the
following query:

PARAMETERS
[ENTER Col1] Short;

SELECT Col2 FROM MyTable WHERE (((MyTable.Col1)=[ENTER Col1]));

As such the above query works fine & when executed, asks the user to
enter a value for 'Col1' & accordingly retrieves the corresponding
'Col2' record. For e.g. if the user enters 25 as the 'Col1' parameter,
then the above query returns 60.

I want to assign the 'Col2' record returned by the query (which is 60
in the above example) to a variable. Depending upon the variable value,
using IF...ELSE conditions, I want to perform different operations.
This is how I have tried to assign the above SELECT statement to a
variable named 'MyVariable':

MyVariable = SELECT Col2 FROM MyTable WHERE (((MyTable.Col1)=[ENTER
Col1]));

But the above throws this error:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT' or 'UPDATE'.

Instead of using a variable, I also tried using the SELECT query
directly in a 'IF' condition in this way:

IF (SELECT Col2 FROM MyTable WHERE (((MyTable.Col1)=[ENTER Col1])))

but this too generates the above error.

How do I overcome this error?- Hide quoted text -- Show quoted text -

 

[Jerry Porter]

Arpan,

In both samples I gave, the code would need to go in a module.

No, you can't use Select and Update statements in an IIF. You can only
use expressions there (see Help on what is valid for an expression).
You can't have a query that is both a select query and an update query
at the same time.

These things could be done using code.

Jerry

Thanks Jerry for the suggestions. I will give them a try. The second
option you have suggested - in that the code snippet that you have
shown, that code snippet has to be put in a module, isn't it? Or should
it be in the query iteself?

To be very honest, my knowledge in MS-Access is limited.

Could you please clarify another doubt? IIF can be used in queries to
evaluate conditions, isn't it? For e.g. something like this:

= IIf([OrderAmount] >= 1000, "Large", "Small")

Can't I use the SELECT or INSERT SQL (or other DML) statements instead
of the strings? For e.g. something like this:

= IIf([OrderAmount] >= 1000, "SELECT * FROM MyTable WHERE OrderAmount >
1000", "UPDATE MyTable SET OrderAmount = 1000 WHERE OrderAmount <
1000")

In other words, what I would like to do is supply a input parameter
value to the query. If the input parameter value happens to be greater
than or equal to 1000, then the query should retrieve all records where
OrderAmount is greater than or equal to 1000. On the other hand, if the
input parameter value supplied to the query is less than1000, then I
want to update all the OrderAmount records that are less than 1000 to
1000.

Can this be done in MS-Access?

Arpan,

Option 1:

A simpler method would use DLookup instead of a query:

Dim sTemp as string
Dim lngCol1 as Long 'or whatever the right data type is
Dim lngCol2 as Long

sTemp = InputBox("Enter Col1")) 'Prompt user for col1
If IsNumeric(sTemp) Then
lngCol1 = Val(sTemp)
lngCol2 = Dlookup("col2","MyTable","col1=" & lngCol1) 'Look up col2
based on col1
End If

Option 2:

If you do want to get data from a query into a variable, you need to
create a recordset. For example:

Dim rs as DAO.recordset
Dim lngCol2 as Long

set rs = Currentdb.OpenRecordset("MyQuery",dbOpenSnapshot)
lngCol2 = rs!col2

rs.close
set rs = nothing

This assumes you have saved the query as My Query.

Jerry
Personable PC Solutionswww.personablepc.com

I have a MS-Access table wherein two of the columns are named 'Col1' &
'Col2'. Assume that one of the records under the 'Col1' column is 25 &
its corresponding record under the 'Col2' column is 60. Consider the
following query:
PARAMETERS
[ENTER Col1] Short;
SELECT Col2 FROM MyTable WHERE (((MyTable.Col1)=[ENTER Col1]));
As such the above query works fine & when executed, asks the user to
enter a value for 'Col1' & accordingly retrieves the corresponding
'Col2' record. For e.g. if the user enters 25 as the 'Col1' parameter,
then the above query returns 60.
I want to assign the 'Col2' record returned by the query (which is 60
in the above example) to a variable. Depending upon the variable value,
using IF...ELSE conditions, I want to perform different operations.
This is how I have tried to assign the above SELECT statement to a
variable named 'MyVariable':
MyVariable = SELECT Col2 FROM MyTable WHERE (((MyTable.Col1)=[ENTER
Col1]));
But the above throws this error:
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT' or 'UPDATE'.
Instead of using a variable, I also tried using the SELECT query
directly in a 'IF' condition in this way:
IF (SELECT Col2 FROM MyTable WHERE (((MyTable.Col1)=[ENTER Col1])))
but this too generates the above error.
How do I overcome this error?- Hide quoted text -- Show quoted text -