Access Query Involving an Awkward Field

J

James Simpson

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.
 
G

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
 
J

James Simpson

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
 
D

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.
 
J

James Simpson

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
 
D

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.
 
C

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
 
L

LarryP

Along with all the other good advice you're getting -- seems to me your
sSQLQuery strings need to end with a semicolon. It just might be that
without it, you're generating a no-results recordset. (Actually I would
expect the debugger to throw a syntax error, but hey, it's one more thing to
check out.)

Also, I've read elsewhere on this site that after opening the recordset it's
good practice to throw in a .MoveLast followed by a .MoveFirst to be sure
you're working with the first record in the result set.
 
G

Graham R Seach

Larry,

Semicolons are only useful when opening an ODBCDirect recordset containing
more than one query, which is not what James is trying to do here. In all
other situations, you can omit the semicolon without problem. Also, issuing
..MoveLast prior to checking if the recordset contains any data will result
in an error. You should always check the recordset for data prior to doing
anything else.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

LarryP said:
Along with all the other good advice you're getting -- seems to me your
sSQLQuery strings need to end with a semicolon. It just might be that
without it, you're generating a no-results recordset. (Actually I would
expect the debugger to throw a syntax error, but hey, it's one more thing
to
check out.)

Also, I've read elsewhere on this site that after opening the recordset
it's
good practice to throw in a .MoveLast followed by a .MoveFirst to be sure
you're working with the first record in the result set.

James Simpson said:
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
Click to expand...
Click to expand...
 
Top