Hi, could someone please help me? I am a novice so please try to be patient.
I've created a database to hold members who join my website. I've created the form to enable them to join and their data goes into the database. I've managed to use ColdFusion to display a list of all members.
But I want to refine this list, to only show members who joined this year. How do I do this? Becuase I've tried everything I can think of.
Here's my current code for the page that produces the list of those in who joined in 2007: - <!---newmemberslist.cfm list of all members joined in 2007--->
-
-
<html>
-
<head>
-
<title>Admin Area - List all Members joined in 2007</title>
-
</head>
-
-
<body bgcolor=beige>
-
<CFQUERY name="newmemberslist" DATASOURCE = "jpkelle2-access">
-
SELECT memberID, forename, initial, surname, sex, dob, address, town, county, postCode, email, joinDate
-
FROM members
-
WHERE joinDate > 01/01/2007
-
</CFQUERY>
-
-
<h1><center>Members</h1>
-
<h2>New Members in 2007</h2>
-
-
<p>
-
<table border=1 bgcolor="beige" cellpadding="3" cellspacing="0">
-
<tr>
-
<th>Member ID</th>
-
<th>Name</th>
-
<th>Sex</th>
-
<th>Date of Birth</th>
-
<th>Address</th>
-
<th>Email</th>
-
<th>Date Joined</th>
-
</tr>
-
-
<CFOUTPUT Query="newmemberslist">
-
-
-
<tr>
-
<td><center>#memberID#<center></td>
-
<td width="15%">#forename# #initial# #surname#</td>
-
<td>#sex#</td>
-
<td>#dob#</td>
-
<td>#address#, #town#, #county#, #postCode#</td>
-
<td>#email#</td>
-
<td>#joinDate#</td>
-
</tr>
-
-
-
-
-
</CFOUTPUT>
-
-
</table>
-
-
<hr><p>End of members list.</p>
-
-
</body>
-
</html>
When I execute this it doesn't seem to treat '01/01/2007' as a date, even though I've used the format 'date/time' in Access.
Is it a problem with my code or with my Access design?
thanks, James
4  2906  acoder 16,027
Recognized Expert Moderator MVP
James, welcome to TSDN.
Try the year function: - <CFQUERY name="newmemberslist" DATASOURCE = "jpkelle2-access">
-
SELECT memberID, forename, initial, surname, sex, dob, address, town, county, postCode, email, joinDate
-
FROM members
-
WHERE year(joinDate) = 2007
-
</CFQUERY>
Tip: why not make a generic page, e.g. either through a url variable, or use year(now()) to get the members who joined in the current year, so you don't need to change it every year!
thanks so much for that.
it works, but......the displayed data has rearranged the date format to
yyyy-mm-dd
why has it done this? can I not get it to be dd-mm-yyyy again?
also, how can I get rid of the time next to the date
(eg it displays - 2007-01-12 00:00:00.0) I don't want the time to be displayed.
Can I not get rid of it seeing I have the field as 'date/time' in Access?
Does it not really matter if a date in Access is defined as 'text' in the data type column, rather than date/time.
Because if I set joinDate as 'text' in Access then it works OK. I get just the date and not the time as well, and I get in the format dd/mm/yyyy, which is what I want.
Is it OK that I've used the text format rather than date/time, or will this produce errors somewhere along the line?
acoder 16,027
Recognized Expert Moderator MVP
Does it not really matter if a date in Access is defined as 'text' in the data type column, rather than date/time.
Because if I set joinDate as 'text' in Access then it works OK. I get just the date and not the time as well, and I get in the format dd/mm/yyyy, which is what I want.
Is it OK that I've used the text format rather than date/time, or will this produce errors somewhere along the line?
No keep it as date/time and use the coldfusion dateformat function. You can use a mask, e.g. "dd/mm/yyyy" to format the date as you wish.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: English Teacher |
last post by:
Which would be more useful to learn, PHP or COLDFUSION? I know
Coldfusion is popular in the work force. Is PHP?
Thanks!
|
by: kuhni |
last post by:
Hi everybody!
Writing this time, I'm really desperate. Basically, I have a
conceptual problem of how to solve a certain "problem" in MS Access
97.
General objective:
In order to categorise...
|
by: acoder |
last post by:
How to Upload a File in Coldfusion
Use the cffile tag for uploading files to the server.
Note that allowing people to upload files is fraught with danger and only trusted users should be...
|
by: Victor |
last post by:
I have a strange problem in my website. I configured my website to run under
2 worker processes. (web garden enabled). and I stored my user information
in the current httpcontext(like...
|
by: James890 |
last post by:
I want to enable a user to enter a start and end date to define the period they want to search for records of members who joined on certain dates. Funny thing is...I've got it to work half of the...
| | |
by: jesmi |
last post by:
my code is:
addRecord.cfm
<cfinclude template="head.cfm">
<p>
<table width="100%" border="0" align="center" cellpadding="2" cellspacing="0">
<tr>
<td>
<div align="center">
|
by: kmitchell00 |
last post by:
I am coding in Coldfusion MX7 and using Javascript for some of the functionality. The basic functionality I'm coding is, based on a value the user chooses from a dropdown box, I populate address...
|
by: Blackmore |
last post by:
Following the guidance contained in Dreamweaver, I have declared a cflogin tag in the application.cfc file. This declaration contains an idletimeout setting of 10 seconds (N.B. 10 seconds for...
|
by: johnVarma |
last post by:
Hi All,
Iam facing a problem with bar chart using coldFusion.
if there is only one <cfchartseries> tag then the seriesLabel attribute is not displaying instead of that the items of the...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
