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
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

685 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