How do I read a value from table and store to variable using VBA

[Aldo]

Hi guys,
How do I read a value from a table and store it in a variable using VBA?
I need to read the value using
SELECT Sex.SexID FROM Sex WHERE Sex.SexDes = "male"
Thanks in advance for any help,
Aldo.

 

[John W. Vinson]

Hi guys,
How do I read a value from a table and store it in a variable using VBA?
I need to read the value using
SELECT Sex.SexID FROM Sex WHERE Sex.SexDes = "male"
Thanks in advance for any help,
Aldo.

intSex = DLookUp("[SexID]", "[Sex]", "[SexDes] = 'male'")

This take the place of your query. You could open the query and use a
Recordset but that would involve several additional lines of code.

 

[Aldo]

Hi John, Thanks for answering.
I am trying intSex = DLookup("[SexID]", "[Sex]", "[SexDes] = 'Male'")
, but getting Error 94 "Invalid use of null"
Thanks,
Aldo.

Hi guys,
How do I read a value from a table and store it in a variable using VBA?
I need to read the value using
SELECT Sex.SexID FROM Sex WHERE Sex.SexDes = "male"
Thanks in advance for any help,
Aldo.

intSex = DLookUp("[SexID]", "[Sex]", "[SexDes] = 'male'")

This take the place of your query. You could open the query and use a
Recordset but that would involve several additional lines of code.

 

[Aldo]

I used Nz() to manage null values.
Thanks for your help!
Aldo.

Hi guys,
How do I read a value from a table and store it in a variable using VBA?
I need to read the value using
SELECT Sex.SexID FROM Sex WHERE Sex.SexDes = "male"
Thanks in advance for any help,
Aldo.

intSex = DLookUp("[SexID]", "[Sex]", "[SexDes] = 'male'")

This take the place of your query. You could open the query and use a
Recordset but that would involve several additional lines of code.

 

[John W. Vinson]

Hi John, Thanks for answering.
I am trying intSex = DLookup("[SexID]", "[Sex]", "[SexDes] = 'Male'")
, but getting Error 94 "Invalid use of null"
Thanks,
Aldo.

An Int datatype cannot accept a NULL, so if there are no Male records in [Sex]
(radical feminist databases!? Who knew?) you'll get this error, since DLookUp
will return NULL. If you Dim intSex as a Variant you can avoid this error.