I have an Access XP (2k file format) database with a date field, “dtmdate,” using mm/dd/yyyy format. The table has several thousand records dating back to 1100.

I want to display records that have today’s DAY & MONTH…e.g., 02/08

I've created a field in a query using:

Format([dtmDate],"m/dd"

This will provide the m/dd data for all records.

Adding, in the criteria field:

Format((Date()),"m/dd"

returns all records with today's m/dd.

The entire SQL is:

SELECT tblEvents.dtmDate, tblEvents.memDescription, Util_Country_List.Countries
FROM tblEvents INNER JOIN Util_Country_List ON tblEvents.lngCountry = Util_Country_List.ID WHERE (((Format([dtmDate],"m/dd")=Format((Date()),"m/dd"))
ORDER BY tblEvents.dtmDate;

Works fine in Access. The problem is that, when used with UD, it returns the dreaded "Too few parameters. Expected 1." Error because the field created by the expression does not exist in the table.

Anyone have a suggestion as to how to work around this?

Thanks much.

In UD you have to use dates between # # ex. "SELECT * FROM yourtable WHERE startdate = #" & date & "# "

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
In UD you have to use dates between # # ex. "SELECT * FROM yourtable WHERE startdate = #" & date & "# "
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

That won't work in this case, However, I did resolve the problem in the following fashion:

1. Created a DELETE query to delete all data in the temp table.
2. Created an APPEND query to add the pertinent records for today to the temp table.
3. Added the following ASP code to the page to invoke the two queries as "stored procedures."


BEGIN ASP
' Connection String
' Provide relative path to the .mdb
' database
Dim connStr
connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("path to the mdb"

' Connection Object
Dim con
Set con = Server.CreateObject("ADODB.Connection"

' connecting to database
con.Open connStr

' executing stored procedure
con.Execute "exec sqDeleteProc 'tblName'"
con.Execute "exec sqAddProc 'tblName'"

' closing connection and freeing resources
con.Close
Set con = Nothing
END ASP

Thanks much.