SQL statement in OpenRecordset

[ryan.gillies]

Hi everyone

I'm working on a bit of VBA in Excel that's going to pull out some record counts for me based on various criteria. It's a work in progress, but I'm stuck on a rather annoying hurdle. Here's the code:

Sub ExportFigs()
Dim DBPath As String
Dim db As Database
Dim rs As Recordset
DBPath = "[URL="file://\\gents02\shared\complain\Aardvark\mdbstore\Aardvark_backend.mdb"]\\gents02\shared\complain\Aardvark\mdbstore\Aardvark_backend.mdb[/URL]"
Set db = OpenDatabase(DBPath)
Set rs = db.OpenRecordset("SELECT * FROM tFile WHERE FileName=XXX")
    MsgBox (rs.RecordCount)
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Everytime I run this code I get an error 3061: Too few parameters. Expected 1.

I'm guessing I'm missing something from my OpenRecordset statement, but I can't for the life of me figure out what. I've tried adding dbOpenDynaset as a type, but without success. Any thoughts...?

 

[MarkK]

This is a problem with your SQL ...

SELECT * FROM tFile WHERE FileName=XXX

What's happening is that the query parser can't find a field named 'FileName' or a field named 'XXX', assumes the missing field is a parameter, and then can't find a value for the parameter.

 

[ryan.gillies]

I just found the following solution by stumbling across this website:
http://www.exceltip.com/st/Import_d...l_(DAO)_using_VBA_in_Microsoft_Excel/428.html

db.OpenRecordset("SELECT * FROM tFile WHERE FileName = 'XXX'", dbReadOnly)

If only I'd held out on google a few more minutes before posting. Thanks for the response though lagbolt, it's helpful to understand why it wasn't working the way I thought it should!

 

[ryan.gillies]

I'm afraid I've hit a dead end again, this time with date format.

Set rs = db.OpenRecordset("SELECT * FROM tFile WHERE FileName = 'Telephony' And FileScan = #11/08/2011#", dbReadOnly)
MsgBox (rs.RecordCount)

The code works, but it's returning a 0, when I know for a fact there is one record in the table that matches this criteria (it being the only record in the table).

If I remove the date criteria from second part of the WHERE statement it returns the correct number of 1. I'm assuming I've not formatted my date correctly, but I'm unsure how I can determine the correct format if that's the case...

 

[MarkK]

The first thing I'd try is change ...

#11/08/2011#
[COLOR="Green"]'to [/COLOR]
#8/11/2011#

... and see if that solves it. If so this format is determined by windows in its regional settings.
Mark

 

[MarkK]

Note, too, that a date variable is actually a date/time variable, so if one date includes a time, and the other date doesn't you will never have equality between the two. So...

? #8/11/11# = #8/11/11 12:01#
False

You may need the DateValue() function, which removes times ...

? #8/11/11# = DateValue(#8/11/11 12:01#)
True

Mark

 

[ryan.gillies]

The first thing I'd try is change ...

#11/08/2011#
[COLOR=green]'to [/COLOR]
#8/11/2011#

... and see if that solves it. If so this format is determined by windows in its regional settings.
Mark

Thanks Mark, this nailed it.