DLookup Challenge

  • Thread starter Thread starter box2003
  • Start date Start date
B

box2003

The below segments are examples of a problem I am trying to figure out when
the code snips are applied to command buttons. The test fails whether varx
is declared as String or Variant. Please let me know what is wrong here.


Thank you.


Test 1, This works:

Private Sub btn1_Click()

Dim output As String

output = "c:\nsi_dev\test.xls"

DoCmd.OutputTo acOutputTable, "test", acFormatXLS, output

end Sub


Test 2, This does not work:

Private Sub btn1_Click()

Dim varx As variant

varx = DLookup("outputpath", "config")
me.text1 = varx 'produces "C:\nsi_dev\test.xls" in textbox me.text1 as a
result

DoCmd.OutputTo acOutputTable, "test", acFormatXLS, varx

End Sub
 
My guess, the OutputTo method is looking for a string as the file name, not a
variant. Is there any chance that there are extra spaces embedded at the end
of your "outputpath" field in your table?

Try:

DoCmd.OutputTo acOutputTable, "test", acFormatXLS, Trim(cstr(varx))

Dale
 
What error do you get?

Just in case there are extra characters that you can't see, try adding

Private Sub btn1_Click()

Dim output As String

output = "c:\nsi_dev\test.xls"
MsgBox output & " contains " & Len(output) & " characters."

DoCmd.OutputTo acOutputTable, "test", acFormatXLS, output

end Sub


Private Sub btn1_Click()

Dim varx As variant

varx = DLookup("outputpath", "config")
me.text1 = varx
MsgBox varx & " contains " & Len(varx) & " characters."

DoCmd.OutputTo acOutputTable, "test", acFormatXLS, varx

End Sub


Do you get the same message box in both cases?
 
The value for "outputpath" in table "config" is "C:\nsi_dev\test.xls", typed
exactly into the table field, no leading or trailing spaces. The correct
value appears in the text box me.text1 as "C:\nsi_dev\test.xls". I attempted
your modification and the error still results.

Error:

"Microsoft Access can't save the output data to the file you've selected."
 
I modified the tests for differentiation using your code and it is
interesting to note that a character count difference of 2 exists between the
two segments, the difference being the "" in the first code segment for btn1,
19, where the string value is supplied, and for the second segment, btn 2, a
value of 21, where the string value is retreived from the table, which
included the "". The value of outputpath in table config is
"C:\nsi_dev\test.xls"

When removing the "" in the table field for outputpath, the functions return
same character counts and both perform the same, successfully. I am a little
confused why my problem existed to begin with.

Private Sub btn1_Click()

Dim output As String

output = "c:\nsi_dev\test.xls"
MsgBox output & " contains " & Len(output) & " characters."

'DoCmd.OutputTo acOutputTable, "test", acFormatXLS, output

End Sub

Private Sub btn2_Click()

Dim varx As Variant

varx = DLookup("outputpath", "config")
Me.Text1 = varx
MsgBox varx & " contains " & Len(varx) & " characters."

'DoCmd.OutputTo acOutputTable, "test", acFormatXLS, varx

End Sub
 
When comparing the below

Dim outputpath as string
outputpath = "C:\nsi_dev\test.xls"
Len(outputpath) produces 19

From table "config", field "outputpath" using DLookup function.
outputpath = "C:\nsi_dev\test.xls", typed exactly as shown, no leading or
trailing spaces were added.
len(outputpath) produces 21

Should these values be the same, regardless of how they were obtained?
 
I don't understand what you mean by "When removing the "" in the table field
for outputpath, the functions return same character counts and both perform
the same, successfully". Are you saying that you added a preceding quote and
a trailing quote when you saved the value in the table? There was no need
for that, although I would have thought that the OutputTo code would still
have worked.
 
That is correct, in the table, I saved the value C:\nsi_dev\test.xls with a
preceding and trailing quote giving, "C:\nsi_dev\test.xls". But when the
table value is pulled into the OutputTo method by the varx variable from
DLookup, it would throw the error. When removing the preceding and trailing
quotes from the table value, the function performs as it would be expected.

This is why I am so puzzled with the differences between what would
seemingly appear to be the same in both situations. The table field value
for outputpath "C:\nsi_dev\test.xls" evidently is not the same as the string
value for outputpath = "C:\nsi_dev\test.xls"

In the OutPutTo method, for docmd.OutputTo ,,, OutputFile, the string value
for outputpath = "C:\nsi_dev\test.xls" is evidently not the same when the ,,,
OutputFile value was derived using the DLookup() function for variable varx,
pulling the table value of "C:\nsi_dev\test.xls"

Summary:

Ex1:
Dim output as string
output = "C:\nsi_dev\test.xls"
docmd.OutputTo acOutputTable, "test", acFormatXLS, output

apparently is not the same as

Ex2:
dim varx as variant
DLookup("outputpath", "config"), where outputpath in table is
"C:\nsi_dev\test.xls", with quotes.
docmd.OutputTo acOutputTable, "test", acFormatXLS, varx

It apparently is necessary to not include preceding and trailing quotes in
the table field when using the DLookup function.
 
In the preceding, please note the typo, should be:

dim varx as variant
varx = DLookup("outputpath", "config")
docmd.OutputTo acOutputTable, "test", acFormatXLS, varx
 
I can't think of any reason why you'd ever want to include preceding and
trailing quotes in a field's value.
 
The OutputFile parameter of the OutputTo method requires a full path as a
string value, either as a full string path or a string variable represented
by the string path.

The thought was that if the full path was pulled from a table field, that it
to needed to be in the string format required by the parameter, preceded and
ended with a quote, "<some full path>"

Since that appears to no longer hold true, I guess in the future, I will
drop the quotes when using the DLookup function in the manner I used.
 
This is why I am so puzzled with the differences between what
would seemingly appear to be the same in both situations. The
table field value for outputpath "C:\nsi_dev\test.xls" evidently
is not the same as the string value for outputpath =
"C:\nsi_dev\test.xls"
Click to expand...
The quotes issue you see comes from the need to differentiate a
literal string from a variable name in Visual basic.

Take the following examples
String1 = "C:\nsi_dev\test.xls"
debug.print string1
' Result is C:\nsi_dev\test.xls
String2 = string1
debug.print string2
' Result is C:\nsi_dev\test.xls
' If by mistake you did
String2 = "string1"
debug.print string2
' Result is string1

If you enter data into a field via a form or a datasheet, it already
has delimiters which are hidden from the user.
 
Back
Top