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

  • Thread starter Thread starter Aldo
  • Start date Start date
A

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.
 
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.
Click to expand...

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


John W. Vinson said:
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.
Click to expand...

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.
Click to expand...
 
I used Nz() to manage null values.
Thanks for your help!
Aldo.




John W. Vinson said:
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.
Click to expand...

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.
Click to expand...
 
Hi John, Thanks for answering.
I am trying intSex = DLookup("[SexID]", "[Sex]", "[SexDes] = 'Male'")
, but getting Error 94 "Invalid use of null"
Thanks,
Aldo.
Click to expand...

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.
 
Back
Top