Skip to main content
T
tracjerian
Inspiring
October 22, 2007
Question

query problem with date range

  • October 22, 2007
  • 3 replies
  • 580 views
I have a query that needs to pull data between a date range. I've tried several things and nothing seems to work.
Any help would be greatly appreciated.

Here's the code:

<cftransaction>
<cfquery name="QryFollowUp" datasource="NBProdReports">
SELECT TblContractInfo_SubMenuTable.User_ID, TblContractInfo_SubMenuTable.Contract_Number, TblContractInfo_SubMenuTable.Transaction_Type, TblContractInfo_SubMenuTable.Notes, TblContractInfo_SubMenuTable.Num_Checks_Trans, TblMasterTrans.Date_Opened
from TblContractInfo_SubMenuTable ,tblMasterTrans
WHERE Transaction_Type = 'FollowUp'
And TblContractInfo_SubMenuTable.Task_Number = tblMasterTrans.Task_Number
And TblMasterTrans.Date_Opened BETWEEN #form.StartDate# AND #form.EndDate#
ORDER BY TblMasterTrans.Date_Opened ASC, TblContractInfo_SubMenuTable.User_id ASC
</cfquery>

</cftransaction>
This topic has been closed for replies.

3 replies

D
Dan_Bracuk
Inspiring
October 22, 2007
The date format in your db is not relevent for Cold Fusion applications. It is only relevent for viewing data in MS Access.

My first suggestion is to use the cold fusion createdate function on each of your form fields and create new variables. Let's call them d1 and d2. Then run this query

select count(*)
fromTblMasterTrans
Date_Opened where date_opened betwen #d1# and #d2#

If you get a number greater than 0, keep adding stuff until it goes wrong. Then you'll know what the problem is.
N
Newsgroup_User
Inspiring
October 22, 2007
Any help would be greatly appreciated.

Ok, why are you using a <cftransaction...> around a single select query?
It should be unnecessary.

This is probably not helpful to your original question. There is
nothing obviously wrong with your query at least to my glance through.
Thus this important question, the one without which you are unlikely to
get any helpful advice, "How does this not work?" Error Messages? No
Data? Wrong Data? Serves you coffee instead of tea?

It would also probably be helpful to know how your date data is
represented in the database and the form controls, depending on what
your difficulties are.

T
tracjerianAuthor
Inspiring
October 22, 2007
The output is a page that contains no data. There is data in the access db within the date range. The Date_Opened field was set to auto, but was returning all data. I thought that there was a formatting difference between the query and the date field in the db so I changed the form so that the date is manually input form a form field. The form is inserting to the db correctly.
For some reason the query is not pulling the data back out.
The error is "Page cannot be displayed". Robust error reporting is turned on in the admin.

J
JR__Bob__Dobbs-qSBHQ2
Inspiring
October 22, 2007
Try enclosing your #form.date# in single quotes or use queryparam.

Also, if the only query in the cftransaction tags is a select I would remove the cftransaction tags.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices