DAO parameter length

[Joseph Walkenhorst]

When I run the code below an error is thrown at line 4 when the string
sc.notes is greater than 255 characters.
Is there any way to allow parameters to contain more than 255 characters?

1 Public Function saveSCToDB(sc As Scorecard, query_Name As String) As Boolean
2 Dim qdf1 As DAO.QueryDef
3 Set qdf1 = EMAT_DB.QueryDefs(query_Name)
4 qdf1.Parameters("[@Notes]") = sc.Notes
....

 

[John Spencer]

Maybe. Try declaring the parameter "@Notes" as a memo type in your query.

Parameters [@Notes] Text;
SELECT ....


Otherwise try truncating your reference to sc.notes to 255 characters. I find
it hard to imagine that you would get many extra records returned with 255
characters for a match.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Bob Barrows [MVP]]

When I run the code below an error is thrown at line 4 when the string
sc.notes is greater than 255 characters.
Is there any way to allow parameters to contain more than 255
characters?

1 Public Function saveSCToDB(sc As Scorecard, query_Name As String)
As Boolean 2 Dim qdf1 As DAO.QueryDef
3 Set qdf1 = EMAT_DB.QueryDefs(query_Name)
4 qdf1.Parameters("[@Notes]") = sc.Notes
...

Unfortunately, according to the Access specifications:

Attribute
Maximum
Number of characters for a parameter in a 255
parameter query


You either have to use dynamic sql or a recordset. I would suggest the
latter.