WHERE... Date() clause???

[christrinder]

Can anybody spot the problem with my WHERE statement? I want it to screen the records that were inputted within 6 months of the current date. I get the following runtime error at the moment: Type mismatch: '[string: "& FormatDateTime(22/"]'

strSQLcount = "SELECT count(*) As count FROM foraction WHERE franchiseid = "& request.querystring("id") &" AND "& FormatDateTime(inputdate, 2) &" =< "& DateAdd("m",-6,"& FormatDateTime("& date() &", 2)&") &""

Cheers for any help.
Chris

[arnyinc]

strSQLcount = "SELECT count(*) As count FROM foraction WHERE franchiseid = "& request.querystring("id") &" AND "& FormatDateTime(inputdate, 2) &" =< "& DateAdd("m",-6, FormatDateTime(date(), 2))

[christrinder]

Many thanks arnyinc, but that's what I started with and it dosn't work. As you can see from the error message, there seems to be a problem with the inputdate because that should read 22/04/03. It dosn't seem to be selecting it from the table. Any ideas?

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'franchiseid = 2 AND 30/12/99 =< 22/10/02'.

[raf]

2 problems:
- count is a reserved word in SQL as in some other applacatios so you should choose another alias (like countrecords)
- "& FormatDateTime(inputdate, 2) &" =< "& DateAdd("m",-6,"& FormatDateTime("& date() &", 2)&") &"" --> no idea what this is, but its not gonna work. the ("& FormatDateTime(inputdate, 2) &" =) should be your variablename in the table. If its a date datatype, which should be for the value your checking it for, you need to enclose the value in #
So i think you need something like

strSQLcount = "SELECT count(*) As countrecords FROM foraction WHERE franchiseid = "& request.querystring("id") & " AND datevariable > #" & DateAdd("m",-6,Date) & "#"

(don't know if the date needs to be formatted for the selectquery) If it does, you'd need something like
strSQLcount = "SELECT count(*) As countrecords FROM foraction WHERE franchiseid = "& request.querystring("id") & " AND datevariable > #" & FormatDateTime(DateAdd("m",-6,Date),2) & "#"

[christrinder]

Thanks Raf,

I have tried putting the # around the dates, but no luck. The 'inputdate' variable is currently stored in a text field. I have put it in a text field because the db is currently in Access, but the db is soon to be moved across to SQL, and I didn't know whether it would support it the same way... Does SQL allow date fields?

The syntax is right now, but the inputdate variable is being extracted as 31/12/99, when it should be 22/04/03... what's happening there?

strSQLcount = "SELECT count(*) As count FROM foraction WHERE sref = "& objRSsubsec("sectiongroup") &" AND ssref = "& objRSsubsec("subsectionid") &" AND franchiseid = "& request.querystring("id") &" AND #"& FormatDateTime(inputdate, 2) &"# > #"& DateAdd("m",-6, FormatDateTime(date(), 2)) &"# "

[raf]

I don't get it. What are you trying to do?
You have this condition where you evaluate two variables (franchiseid and inputdate). Right? Well, then you should have the name of the variable on the left side of the condition. WHERE variablename=value . Not WHERE value = value.

I gues your trying to convert the textstring into a date, and this date should then be compared with the date-6 months? No?
But this is not possible with your syntax. FormatDateTime is a VB function, not an SQL function. The webserver will parse this code, and translate the variablename into a date and then use that in the sql-function as a variablename and send this to the RDBM. (Which is probably not what you want :( ) You need a sql function that the RDBM then uses to convert the values in the inputdate column to dates before evaluationg the condition

Probably something like
{ fn CONVERT( inputdate, SQL_DATE ) }

more info here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odappepr_5.aspI never used these, but i remember seeing some threads here where it was used.
I don't quite understand why you save it in a textfield. Access also support datatime fields? The easiest sollution is probably just changing the datatype of the variable. Or insertig a new (datatine) one where you copy the inputdate values in.

[christrinder]

Basically, the businesses fill-in a form online. The page I'm working on basically counts the number of businesses answering 'no' to a particular set of questions and lists them. The trouble is, the businesses fill in the form and produce a new set of answers every 6months. This means that a business answering 'no' to one of the questions more than once, will appear more than once in the list, and therefore, the count number may be higher than the actual number of businesses. As the years go on, the problem will obviously escalate.

What the select statement was trying to do, was count from only the records inputted within the last month. I have changed the field to a date field, but need some help in getting the select statement to count(*) only those records where the date is greater than the (current date -6).

Hope that makes the problem clearer?

[raf]

Sorry. Posts crossed. The easiest sollution is this
- create a new variable in the access db. As default value, you set the default value to =Date()
- select on this variable

Each time a record is inserted, the RDBM will fill in this variable automatically (like the autonumber variables). Values will follow your system settings (the servers !)

Select Distinct will prevent the businesses from being listed twice, buth the count will then return the number of times the businesses occured in the variable. It will give you absolutely no idea about the records from the last month. The above is the easiest to do this.

[raf]

Our posts keep on crossing. Wait for a moment wail i reread it all.

[raf]

This will give you one value: the number of records, less then 6 months old, with franchiseid=something

strSQLcount = "SELECT count(*) As countrecords FROM foraction WHERE franchiseid = "& request.querystring("id") & " AND inputdate > #" & DateAdd("m",-6,Date) & "#"

This will give you all values from the records less then 6 months old, with franchiseid=something
strSQLcount = "SELECT * FROM foraction WHERE franchiseid = "& request.querystring("id") & " AND inputdate > #" & DateAdd("m",-6,Date) & "#"


They only thing you need to check in the db is if the dates are in dd/mm/yyyy or mm/dd/yyyy format. Don't know your regional settings. ASP uses mm/dd/yyyy

[christrinder]

Thanks for that Raf. It seems to all make sense, there's no errors reported on the page, but I'm still not getting any results. It counts zero! I'm using Window's PWS to develop it, and Access stores the dates as dd/mm/yyyy, whereas the server is returning dates in the dd/mm/yy format... could this be the problem?

I've tried adding <% response.write inputdate %> to see what value it is evaluating it against, but it simply dosn't return anything there either! This all just seems ridiculously complicated for what I'm trying to do. It is easy in principal nes pa?

[raf]

OK. Lets take one step back.
<% response.write inputdate %> will probably not return anything because its a variable in your db. Your RDBM will recognize it as a column name when it executes the statement. But to ASP, this is not known as a variable so this will return Nothing. (Its just a word in a string (= the sqlstring is the value of variable strSQLCount) for ASP)

After the sql statement, insert this

response.write strSQLCount
response.end

then browse to the page. The sql statement that is executed will be displayed here.
Check that and post it here.

dd/mm/yyyy, whereas the server is returning dates in the dd/mm/yy format... could this be the problem?
Server will probably return mm/dd/yyyy. No? But this is fixable. Lets get the statement right first

It is easy in principal nes pa?
--> We'll see. N'est pas ?

[christrinder]

here we go then Raf. The Sql statement as requested. Sorry about the spelling mistake in the francais... it's been a while since those dark GCSE days!!!

SELECT count(*) As nocount FROM foraction WHERE sref = 1 AND ssref = 1 AND franchiseid = 2 AND inputdate > #22/10/02#

[raf]

OK So we got thisfar.
Now, you say this doesnt return anything ?

after the execute statement, insert this
response.write ("<font color='red'><br />Number = " & recordsetname.Fields("nocount") & "records")

There are a few reasons why this could return nothing. Most obvious, because no records meet the criteria, or a problem with the dates. But lets first see if this indeed returns nothing. If you wanna be shure it's not because no records meet the condition, just remove the condition (after you copied the line and commented it out :) )

[christrinder]

"Number = 0records"

I'm certain that the problem is with the date because it works fine without the WHERE DATE etc, but it just counts ALL of them.

[raf]

ok. so it's probably the dateformat. The date that ASP generates with the dataadd function willbe sent as mm/dd/yyyy. So the dates in your db need to be in the same format. You can do this by setting the format to mm/dd/yyyy. See attached file.

I'm going home now but if i'll get back aroun 8 to check how it went.

[christrinder]

Thanks Raf, we're nearly there... I know how to change the date format in Access. My db is currently using dd/mm/yyyy and my PWS is using dd/mm/yy. I can't seem to make Access use dd/mm/yy... for some reason it always returns a four digit year date, so the problem lies with my PWS. I have replaced the ..DateAdd("m",-6,Date).. code in the SQL statement with a hard-coded date with four year digits... and it all works fine. So, to sort this, we need to change the code returned by ..DateAdd("m",-6,Date).. to a dd/mm/yyyy format. Any ideas?

[raf]

Think this is the easiest way

dim startdd, startmm, startyyyy, startdate
startdate=Date
startdd=Day(startdate)
startmm=Month(startdate) - 6
startyyyy=Year(startdate)

' rebuild the dates for dubble storing in database in mm/dd/yyyy format
startdate=startdd & "/" & startmm & "/" & startyyyy

<edit>forgot to sett the startdate to date at the top</edit>

Don't know about PWS, buth with IIS, this should work

your statement then becomes something like

"SELECT count(*) As nocount FROM foraction WHERE sref = 1 AND ssref = 1 AND franchiseid = 2 AND inputdate > #" & startdate &"#"

[christrinder]

Thanks Raf. I'm very appreciative of your help. Unfortunately I'm not quite understanding your last post. I get the gist, and think I'm working along the same lines, the trouble is, the DateAdd function is reformatting my date to a two-digit year figure. I could use the replace() function but that makes it all pretty useless. Any ideas how I could reformat the below?

dim todaydate
todaydate = ""& day(date()) &"/"& month(date()) &"/"& year(date()) &""
dim lastsixmonth
lastsixmonth = DateAdd("m",-6,""& todaydate &"")

[christrinder]

OK, I've cracked it, but the below code is the best I can come up with... surely there's a more direct way? Thanks for everybody's help!

dim monthlesssix
monthlesssix = month(date()) - 6
IF monthlesssix =< 0 THEN
monthlesssix = monthlesssix + 12
END IF
dim yearcorrect
yearcorrect = year(date())
IF monthlesssix => 6 THEN
yearcorrect = yearcorrect - 1
END IF
dim lastsixmonth
lastsixmonth = ""& day(date()) &"/"& monthlesssix &"/"& yearcorrect &""

[raf]

Glad you got it working.
but the below code is the best I can come up with... surely there's a more direct way?
Yeah, my code :) Though i now see the month substraction was probably wrong. So it should have been

dim startdate
startdate = DateAdd("m",-6,Date)
' rebuild the dates for dubble storing in database in mm/dd/yyyy format
startdate= Day(startdate)& "/" & Month(startdate) & "/" & Year(startdate)


I tested it and it returns a 4 digits year.