Thanks John-
I tried both:
strProd = DLookup("ProdFam", "Product_tbl",
"Product = '" & rstProduct!product.Value & "'")
and
strProd = DLookup("ProdFam", "Product_tbl",
"Product = " & Chr(34) & rstProduct!product.Value & Chr(34))
and received a run time error '94' Invalid Use of Null.
What am I doing wrong?
:
Your third argument (as posted) is missing an apostrophe before the
quote.
strProd = DLookup("ProdFam", "Product_tbl",
"Product = '" & rstProduct!product.Value & "'")
You could write that this way - using the Chr(34) to add the needed
quote marks.
strProd = DLookup("ProdFam", "Product_tbl",
"Product = " & Chr(34) & rstProduct!product.Value & Chr(34))
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Jeff wrote:
I still can't figure out why this is giving me a 3075 syntax error
on
the line:
strProd = DLookup("ProdFam", "Product_tbl", "Product = " &
rstProduct!product.Value & "'")
When I paste the SQL code that I am using for rstProduct (SELECT
DISTINCT
Product FROM Productname_tbl
into a query I get the following
result:
Product
AEEG
CEEG
LTM
NEEG
VEEG
A couple of entries from the Product_tbl table are:
ProdFam Part No Description Type
AEEG 1234 widget 1 1
CEEG 2344 widget 2 3
and the error that I am receiving is:
Run-time error '2075'; Syntax error in string in query expression
'Product =
AEEG".
I have verified that there are no additional spaces in the fields of
either
table but am not sure what else I can do to solve this problem...
My intent is to pull all records out of the Product_tbl that match
the
Product field from the ProductName_tbl and then dump each record
into
an
individual worksheet in excel (that is named by the Product field)
I am at my wit's end on this.... Any ideas?
thanks
=======================
Here is my code which I modified from your example:
Private Sub Command0_Click()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstProduct As DAO.Recordset
Dim strSQL As String, strTemp As String, strProd As String
Const strFileName As String = "Product Part Numbers"
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
strSQL = "SELECT DISTINCT Product FROM Productname_tbl;"
Set rstProduct = dbs.OpenRecordset(strSQL, dbOpenDynaset,
dbReadOnly)
If rstProduct.EOF = False And rstProduct.BOF = False Then
rstProduct.MoveFirst
Do While rstProduct.EOF = False
strProd = DLookup("ProdFam", "Product_tbl", "Product= " &
rstProduct!product.Value & "'")
strSQL = "SELECT * FROM Product_tbl WHERE " & _
"Product= " & rstProduct!product.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strProd
strTemp = qdf.Name
qdf.SQL = strSQL141
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, _
strTemp, "C:\" & strFileName & ".xls"
rstProduct.MoveNext
Loop
End If
rstProduct.Close
Set rstProduct = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
End Sub
===============================
:
The error that you're getting suggests that the ' delimiters are
not
in the
code step that uses the DLookup function -- notwithstanding what
you've
posted.
Check your code again to be sure. And then copy the code from the
procedure
and post it here so that we can review it.
Also, be sure that the structure you're using for the DLookup
arguments is
correct. Your code says this:
Find the value of the ProdFam field in the Product_tbl
table
that is
in the record that has a Product value equal to the value provided
by
the
rstProduct!product field's value.
Is this what you want to accomplish?
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Thanks Ken-
I added the &"'" as you recommend:
strProd = DLookup("ProdFam", "Product_tbl", _
"Product= '" & rstProduct!product.Value & "'")
and now I get the following error:
Runtime error '3075' syntax error in string in query expression
'Product=AEEG".
AEEG is one of the ProdFam values in my Product_tbl.... Is there
a '
missing
somewhere in this syntax?
:
That error occurs when you call DLookup (or any of the other
domain
functions) and there is an error in the syntax of one of the
arguments.
I'm guessing the error in this case is that Product is a text
field, so
change the code step to this:
strProd = DLookup("ProdFam", "Product_tbl", _
"Product= '" & rstProduct!product.Value & "'")
I'm using ' character to delimit the value from the recordset in
order to
show that it's a text string.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Thanks Ken-
I found that I had an "S" missing in the
DoCmd.TranferSpreadsheet
and once I fixed this I now get a new error:
Run-Time error '2001' You canceled the previous operation
on the Dlookup statement in the following code:
Do While rstProduct.EOF = False
strProd = DLookup("ProdFam", "Product_tbl", _
"Product= " & rstProduct!product.Value)
----------
I borrowed this code from your "Create a Query and Export
multiple
"filtered" versions of the Query (based on data in another
table)
to
separate
Worksheets within one EXCEL file via TransferSpreadsheet"
example
but
changed
it to reflect my field names to the following: (and I must have
somehow
messed up the spelling of the transfersheet comand...)
=====================
If rstProduct.EOF = False And rstProduct.BOF = False Then
rstProduct.MoveFirst
Do While rstProduct.EOF = False
strProd = DLookup("ProdFam", "Product_tbl", _
"Product= " & rstProduct!product.Value)
strSQL = "SELECT * FROM Product_tbl WHERE " & _
"Product= " & rstProduct!product.Value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strProd
strTemp = qdf.Name
qdf.SQL = strSQL141
qdf.Close
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9,
_
strTemp, "C:\" & strFileName & ".xls"
rstProduct.MoveNext
Loop
End If
=====================
---------------------------
My Product_tbl:
Field name data type desc.
---------------- ------------- --------------
ProdFam text Product Family
IPROD text part #
IDESC text Part desc
