Solved

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

Posted on 2003-10-29
4
1,954 Views
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:

SELECT GrvDtFil
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?
0
Comment
Question by:stewboy
  • 3
4 Comments
 
LVL 19

Accepted Solution

by:
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.
0
 

Author Comment

by:stewboy
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?

0
 

Author Comment

by:stewboy
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.
0
 

Author Comment

by:stewboy
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, "'", "''") + "#)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now