Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2003-10-29
Medium Priority
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 405 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

604 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