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

Posted on 2003-10-29
Last Modified: 2010-04-25
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?
Question by:stewboy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
LVL 19

Accepted Solution

webwoman earned 135 total points
ID: 9645693
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.

Author Comment

ID: 9646275
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?


Author Comment

ID: 9646309
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.

Author Comment

ID: 9651548
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, "'", "''") + "#)

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question