DW/ASP VB/MS Access date formatting problems. Date won't pass via form.

Don't know if this is a DW, ASP VB, or MS Access question. Let me know if I should move it.

Trying to filter a Master Page recordset based on dates input from a Search Page form.

Access doesn't recognize the dates coming in from the form.

The frustrating thing is, the dates are input from a dynamically generated drop-down list on the search page, so in effect I'm getting a range of date info from Access, selecting one date and sending it right back to Access, but Access says "I don't recognize that." WHADDAYA MEAN!? I JUST GOT IT FROM YOU! (sorry, rant).

Additionally, I'm able to populate the search page's dynamically-generated lists with the full range of dates from Access, provided I don't add a date/time format to them.

If I try to add a format (e.g. DoDateTime((GrvDate1.Fields.Item("GrvDtFil").Value), 2, -1) then I get a "Type Mismatch DoDateTime" error and the search page won't load. Access has several different date formats available, and I've tried matching the date formats in Access to the date formats available through "Server Behaviors" in DW, but any formatting applied in DW causes the error.

If I leave the search page's formatting as "none", then the search page loads and the date info is passed to the master page. I have the form data displayed on the Master page, so I know it goes through, and displays as a date in whatever format I select on the Master Page.

However, the RS on the Master Page doesn't recognize the dates for the purposes of filtering the RS. For troubleshooting I've made the SQL statement as simple as possible:

FROM grieveAction
WHERE GrvDtFil > BegDte

Here's the truly wacked part. When I set the default for the variable BegDate to 03/03/03 and test the RS, I get returns for all range of dates, even those before 03/03/03. So the db isn't recognizing 03/03/03 as a date.

When I create SQL through BrioQuery, the date format it generates in the SQL is {ts '2003-03-03 00:00:00.000'}. If I plug that format into the default for the BegDate variable and test the RS, I get the RS returns I want.

So the question is, how do I get DW/ASP to pass the correct Date format through so my Access db will recognize it and use it?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You make it a date. Anything that comes from a form is a STRING, not a date.

You also have to be sure that it's an actual date field in Access, and you have to construct the SQL query right. Access doesn't have 'several different date formats' -- it has ONE. It can DISPLAY it in various formats, but the underlying date is mm/dd/yyyy.

The SQL should be
SELECT GrvDtFil FROM grieveAction WHERE GrvDtFil >#" &cdate(request("BegDte"))&"#"

I'm assuming that BegDte is your variable from your form, and that you've checked that it is in a date format.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stewboyAuthor Commented:
String, date, tomato, tomahto. Code-side is not my residence of comfort.

I know that I'm submitting a date-like string because it's coming from a dynamically-generated menu that derives its value from the same Access date column that I'm querying. It submits as appended to the URL as follows ?BegDte=10%2F23%2F1999 and I can apply date formatting and display it as a date on the page to which it's submitted.

So on the search page I'm getting a date-range from Access, selecting one of the dates, submitting it to a Master page via form, and trying to use it to query the same table and column from which the date originally came.

To which Access replies "Doesn't look like a date to me!"

The SQL looks like this in the code:

FileDate.Source = "SELECT GrvDtFil  FROM grieveAction  WHERE GrvDtFil > " + Replace(Recordset1__FilBegDte, "'", "''") + ""

I've performed this same function numerous times with DW MX, ASP VB and another type of db, and never had to mess with the code, so I guess I'm wondering what's the difference with Access and dates?

stewboyAuthor Commented:
Ignore the disconnect between FileDate and Recordset1. They are both FileDate in the actual RS and fileBegDate is just BegDate. I'm not copy/pasting well, but the problem isn't with incorrect variable or RS names.
stewboyAuthor Commented:
It's the pound sign. You have to explain a date to Access by using the pound sign.

Glad I wasted a full work day to discover THAT little tidbit. Thank God for Google.

Webwoman, I'll fire the points in your direction because your solution contained #.

Final, working SQL snippet:

(AL3.GrvDtFil BETWEEN #" + Replace(GrvDateSrch__FilBegDte, "'", "''") + "# AND #" + Replace(GrvDateSrch__FilEndDte, "'", "''") + "#)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Adobe Dreamweaver

From novice to tech pro — start learning today.