Access Query Involving an Awkward Field

[Guest]

Hello,
I am currently trying to use Access VBA to work on a solution for a
client. My question is relatively straightforward (I think). I currently
have a Access database which contains a field in a table called Lot # My
question is: I am trying to issue a query to select the field Lot # from the
table (Customer Order Details). In addition I am trying to place it into a
recordset for later access. How exactly would you issue such a query in
Access? Thanks in advance.

Regards,

James Simpson
Straightway Technologies Inc.

 

[Graham R Seach]

James,

Queries referencing fields that contain spaces, special characters or
reserved keywords must be enclosed in square brackets:
SELECT [Lot #] FROM [my table name]

In a VBA recordset (DAO or ADO), you can refer to such a field using either
the square brackets, or as a string:
strLotNo = rst![Lot #]
strLotNo = rst("Lot #")
strLotNo = rst.Fields("Lot #")

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

Hello again,
I have used the suggestions you have given and am receiving debug errors
from VBA. Here is the code I am using :
sSQLQuery = "SELECT [Lot #] FROM [Customer Order Details]"
Set rstLotNumber = CurDbs.OpenRecordset(sSQLQuery)
MsgBox (rstLotNumber![Lot #])
The error that I get back from VBA is :
Runtime error 94:
Invalid use of null.
Any suggestions to remedy the problem? Thanks in advance.

Sincerely,

James Simpson

 

[Douglas J Steele]

Show some more of your code (what you're doing once you open the recordset)

The error is implying that rstLotNumber![Lot #] doesn't contain a value
(that it's Null), and that you're trying to assign it to a variable that's
been declared as a string or number (Long, Integer, Single, Double). The
only data type that can hold a Null value is a variant, so either redeclare
the variable as a variant, or use the Nz function to substitute a given
value when Null is returned.

 

[Guest]

Hello again,
As per your request here is a more complete snippet of the code in
question :

Dim CurDbs As Database ' The current database we are working with
Dim rstLotNumber As Recordset ' Lot # Recordset
Dim rstWarehouse As Recordset ' Warehouse # Recordset
Dim rstProcessor As Recordset ' Processor # Reocrdset
Dim rstVariety As Recordset ' Variety Name Recordset
Dim sSQLQuery As String ' The string containing the current query we
wish to issue
Dim sProductsToExport() As String ' This string will hold all the
products we want to export
Dim iProductsToExportCount As Integer ' This will hold how many
products we want to export
Dim iCounter As Integer ' This is a loop counter we will use during
the reconciliation generation
Dim iNumOfProductsToExport As Integer

iNumOfProductsToExport = 0
Set CurDbs = Application.CurrentDb() ' Set the current database
equal to a function that captures our current database
' Now we will issue a query to the Customer Order Details table to
grab all lot numbers
sSQLQuery = "SELECT [Lot #] FROM [Customer Order Details]"
Set rstLotNumber = CurDbs.OpenRecordset(sSQLQuery)
sSQLQuery = "SELECT Warehouse FROM [Customer Order Details]"
Set rstWarehouse = CurDbs.OpenRecordset(sSQLQuery)
sSQLQuery = "SELECT [Productname] FROM [Customer Order Details]"
Set rstVariety = CurDbs.OpenRecordset(sSQLQuery)
sSQLQuery = "SELECT Processor FROM [Customer Order Details]"
Set rstProcessor = CurDbs.OpenRecordset(sSQLQuery)
MsgBox (rstLotNumber![Lot #])
' This code will find out which products to export based on the
criteria set forth

Hope this helps. Thanks in advance.

Sincerely,
James Simpson

 

[Douglas J Steele]

I can't figure out what you're doing. You're creating 4 separate recordsets
based on the same table, each returning a single field. You have no way of
knowing which fields go together! Surely all you need is a single recordset
that contains the 4 fields.

I'm assuming that the error is being raised at statement:

MsgBox (rstLotNumber![Lot #])

That should return the Lot # for the first record in the recordset. However,
if that's Null, the message box will fail, as you can't pass a Null value to
the MsgBox function.

Try replacing that with:

MsgBox Nz(rstLotNumber![Lot #], "No Lot # found")

By the way, it would probably be a good idea to change your declaration from

As Recordset

to

As DAO.Recordset.

 

[chris.nebinger]

First, I would never include the # sign in a field name, and I also
would not use spaces. If you use either, you have to inclose the field
name in []'s. The same goes for the table names.

BUT:

Select [Lot #] as LotNum from [Customer Order Details]


Chris Nebinger